OHLC candles every X amount of ticks

Question

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 am currently able to create OHLC candles with this code (thanks to @GordonLinoff, who answered my previous question on this link):

select max(date) as date,
       (array_agg(bid order by seqnum asc))[1] as open,
       max(bid) as high,
       min(bid) as low,
       (array_agg(bid order by seqnum desc))[1] as close


from (
    select t.*, row_number() over (order by date) as seqnum
      from ticks t) as a

group by floor((seqnum - 1) / 1000);

And the output looks like this:

---------------------------------------------------------------------------
|            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

The part that I'm missing is that I would like to create candles only if I have the required number of ticks. For example, if I have 10500 ticks, then I would like to have only 10 rows and don't consider the other 500 remaining ticks until the amount is 11000.

How can I achieve this?

Thank you in advance!

Gordon Linoff · Accepted Answer · 2020-05-06T20:00:27.777

I see what you are asking. You just want a having clause:

select max(date) as date,
       (array_agg(bid order by seqnum asc))[1] as open,
       max(bid) as high,
       min(bid) as low,
       (array_agg(bid order by seqnum desc))[1] as close
from (select t.*, row_number() over (order by date) as seqnum
      from ticks t
     ) t
group by floor((seqnum - 1) / 1000)
having count(*) = 1000;

I actually thought what you needed was more complicated.

If you want the most recent data, then define the row_number() in the reverse order:

row_number() over (order by date desc)

EDIT:

Let's get rid of the array functions:

select max(date) as date,
       max(bid) filter (where mod(seqnum, 1000) = 1) as open,
       max(bid) as high,
       min(bid) as low,
       max(bid) filter (where mod(seqnum, 1000) = 999) as close
from (select t.*, row_number() over (order by date) as seqnum
      from ticks t
     ) t
group by floor((seqnum - 1) / 1000)
having count(*) = 1000;

By the way, the query does exactly what I needed, but it takes around 34 seconds to execute. I was planning to save this in a view, so I don't need to do an actual table to use it. My idea was to monitor using a Python script, but it seems its not a good idea if the query is taking so long. Is there a way to make the view faster or I should create the new table with the ticks? — Pedro Pablo Severin Honorato, May 06 '20 at 18:51

OHLC candles every X amount of ticks

1 Answers1