I have a table of financial data with the following schema:
Table "public.candles"
Column | Type | Modifiers
------------+----------------+-----------
posix_time | bigint | not null
low | numeric(8,2) | not null
high | numeric(8,2) | not null
open | numeric(8,2) | not null
close | numeric(8,2) | not null
volume | numeric(23,16) | not null
Indexes:
"candles_pkey" PRIMARY KEY, btree (posix_time)
Each candle spans a one-minute interval. I would like to aggregate the data into candles spanning intervals of 5 minutes, 1 hour, 1 day, etc.
I can aggregate posix_time
, high
, low
, and volume
over five minute intervals with
SELECT posix_time/(60*5)*(60*5) AS new_posix_time,
max(high) AS new_high,
min(low) AS new_low,
sum(volume) AS new_volume
FROM candles
GROUP BY new_posix_time
and calculate the the new open
and close
values with the appropriate variation of
SELECT posix_time/(60*5)*(60*5) AS new_posix_time,
open AS new_open
FROM (SELECT open,
posix_time,
ROW_NUMBER() OVER (PARTITION BY posix_time/(60*5)*(60*5)
ORDER BY posix_time ASC) AS r
FROM candles
) AS o
WHERE o.r = 1
as suggested in this question, but I can't figure out how to combine them into one query.
Do I need to use joins? Subqueries? Totally restructure the query?
You can use generate_series()
to get the timeframe you are looking for. Then you can use left join
and aggregation. Something like this:
select t.ts,
min(low) as low, max(high) as high, sum(volume) as volume
from generate_series('2016-01-01'::timestamp, '2016-01-02'::timestamp, interval '5 minute'
) t(ts) left join
candles c
on '1970-01-01' + c.posix_time * interval '1 second' between t.ts and t.ts + interval '5 minute'
group by t.ts;
EDIT:
Getting the open and close time requires one more level of processing:
select ts, min(low) as low, max(high) as high, sum(volume) as volume,
min(open) as open, min(close) as close
from (select t.*, c.*,
first_value(open) over (partition by t.ts order by c.posix_time asc) as open,
first_value(open) over (partition by t.ts order by c.posix_time desc) as close
from generate_series('2016-01-01'::timestamp, '2016-01-02'::timestamp, interval '5 minute'
) t(ts) left join
candles c
on '1970-01-01' + c.posix_time * interval '1 second' between t.ts and t.ts + interval '5 minute'
) t
group by ts;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With