CODEWITHSUNDEEP
sqlpostgresql
SamuelNLP
SamuelNLP

Reputation: 4136

Keep first value in date in resampling aggregations in PostgreSQL

I have values by minute and I am trying to resample them by hour. As they are stock prices I have the columns open and closed. When resampling I need in the open columns to keep the price from the first minute every hour...

It would be something like this if there was a FIRST aggregation function:

SELECT date_trunc('hour', date_) AS date_by_hour, 
FIRST("open") AS "open", MAX(high) AS high, MIN(low) AS low, LAST("close") AS "close", AVG(weighted_price) AS price
FROM btc_usd
GROUP BY 1

example:

open,high,low,close,date_
262.50,262.20,262.80,262.28,2015-01-27 16:22:00
262.20,262.20,262.28,262.28,2015-01-27 16:23:00
262.15,262.15,262.15,262.15,2015-01-27 16:24:00
262.15,262.15,262.15,262.15,2015-01-27 16:25:00
262.09,262.15,262.09,262.15,2015-01-27 16:26:00
262.07,262.20,261.87,262.28,2015-01-27 16:27:00
262.10,262.10,262.18,262.18,2015-01-27 16:28:00
262.10,262.10,262.18,262.18,2015-01-27 16:29:00
262.15,262.50,262.15,262.49,2015-01-27 16:30:00

would give:

open,high,low,close,date_
262.50,262.50,261.87,262.49,2015-01-27 16:00:00

UPDATE:

SELECT DISTINCT ON
( date_trunc( 'hour', date_ )) "open", date_
FROM btc_usd
ORDER BY date_trunc( 'hour', date_ ), date_

this gives the first and using DESC on close i can get the last

Upvotes: 0

Views: 171

Answers (1)

sticky bit
sticky bit

Reputation: 37472

One solution is to use DISTINCT and various window functions, first_value() and last_value() amongst others, that is.

SELECT DISTINCT
       first_value("open") OVER (PARTITION BY date_trunc('hour', "date_")
                                 ORDER BY "date_"
                                 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) "open",
       max("high") OVER (PARTITION BY date_trunc('hour', "date_")) "high",
       min("low") OVER (PARTITION BY date_trunc('hour', "date_")) "low",
       last_value("close") OVER (PARTITION BY date_trunc('hour', "date_")
                                 ORDER BY "date_"
                                 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) "close",
       date_trunc('hour', "date_") "date_"
       FROM "btc_usd";

Upvotes: 1

Related Questions