Search code examples
sqlpostgresqlcandlestick-chartohlc

select max and min values every x amount of rows-postgresql

I'm trying to create OHLC bars in postgresql starting from tick data. I want to create bars every 1000 ticks or every 500 ticks. Or every X amount of ticks.

The database I'm saving has the bid/ask and a timestamp. I know that I could do a groupby and group them by timestamp, but the desired output is using the amount of ticks.

One tick is composed by a timestamp, a bid price and an ask price.

The tick database looks something like this:

-------------------------------------------------
|            date           |   bid   |   ask   |
|2020-03-20 19:33:56.044533 | 1.06372 | 1.06384 |
|2020-03-20 19:33:37.205241 | 1.06372 | 1.06384 |
|2020-03-20 19:33:54.943593 | 1.06372 | 1.06383 |
|2020-03-20 19:33:55.183255 | 1.06372 | 1.06384 |

I would like to group every X amounts of ticks to create this output:

---------------------------------------------------------------------------
|            date           |   open   |    high    |    low   |   close   |
|2020-03-20 19:33:56.044533 | 1.06372  |   1.07104  |  1.06001 |  1.06579  |

That is 1 candle. The numbers came from the bid column. The open price is the first price registered, the close price is the last price registered and the high and low are the max and min prices registered in those X ticks.

So, if X is 1000 and assuming that the index starts from 0, the OHLC prices would be as follows: - open: price at index 0 - high: max price between index 0 and 999 - low : min price between index 0 and 999 - close: price at index 999

That is for the first 1000 ticks. Then the next candles is created by the next following 1000 ticks. - open: price at index 1000 - high: max price between index 1000 and 1999 - low : min price between index 1000 and 1999 - close: price at index 1999

How can I achieve this?

Thank you in advance!

Solution

  • You can aggregate fixed numbers of rows using row_number() and arithmetic:

    select min(date),
       (array_agg(bid order by seqnum asc))[1] as open,
       (array_agg(bid order by seqnum desc))[1] as close,
       min(bid) as min_bid, max(bid) as max_bid
from (select t.*, row_number() over (order by date) as seqnum
      from ticks t
     ) t
group by floor((seqnum - 1) / 500);

    This uses "hack" to get the open and close -- by using arrays.