How can I create range trading bars in PostgreSQL?

Question

I'm trying to create a trading bar by grouping data every X range the price has moved. The data is from tick and every tick has a timestamp and a price vaue.

The data set looks like this:

date                        price
"2020-02-03 02:00:00.207"   1.10890
"2020-02-03 02:00:00.258"   1.10887
"2020-02-03 02:00:00.461"   1.10887
"2020-02-03 02:00:00.613"   1.10886

I would like to "create" a bar when the price moves X amount of distance. This distance is measured in pips and, for example, if the price moves from 1.10000 to 1.10010 that is a 1 pip movement. (A trading bar or candlestick is defined as the variation of price of an asset given a condition. For example, 1 hour bars sample every hour and they have an open price, low price, high price and close price. So if it is the 00:00:00 hours, the bar ends sampling at 01:00:00 hours). In this example, the selected range is 10 pips, so I would like to sample (or create) a bar every 10 pips.

I have a python script that can create the bars I need, but I would like to have this as a SQL trigger. The python function I use is as follows:

def generate_rangebars(data,range_pips=10,point_value=0.0001):
    _o,_h,_l,_c,_d=[],[],[],[],[]
    k = 0
    prices = data.to_numpy()
    while True:
        for j in range(k,len(prices)):
            if np.abs(prices[k]-prices[j])/point_value>=range_pips:
                _d.append(pd.to_datetime(times[j],format= "%d.%m.%Y %H:%M:%S.%f"))
                _o.append(prices[k])
                _h.append(np.max(prices[k:j+1]))
                _l.append(np.min(prices[k:j+1]))
                _c.append(prices[j])
                
                k=j
                break
        if j == len(prices)-1:
            break

So, basically I start at index 0 and iterate through the prices array. If the absolute value of the difference between price[j] and the price at 0 is greater than the desired range, I store the date of the price[j]. The open price of the bar in this case would be the price in k and the close price would be the price in j. To get the high and low prices, I select the max and min prices from k up to j+1. At last, if the condition is not met, then it breaks the loop once we reach the end of the data.

How can I replicate this in SQL, so that the bars are created when the required range is satiesfied and not consider the excess of ticks in the process? For example, if I have 10500 ticks and the first 10000 ticks satisfy the condition to create 10 bars, then I would like to only create those 10 bars and ignore the other 500 ticks until there are enough ticks to create another X pips bar.

How can I achieve this?

Thank you in advance!

EDIT: This is an example of creating two bars. Les suppose that the desired range is 1 pip and we have this data.

    date                        price
    "2020-02-03 02:00:00.207"   1.10880
    "2020-02-03 02:00:00.258"   1.10887
    "2020-02-03 02:00:00.461"   1.10887
    "2020-02-03 02:00:00.613"   1.10886
    "2020-02-03 02:00:00.753"   1.10888
    "2020-02-03 02:00:01.200"   1.10889
    "2020-02-03 02:00:01.268"   1.10890 -->1 Pip!
    "2020-02-03 02:00:01.439"   1.10886

So, our open price is 1.10886, close price is 1.10890 and the high and low prices are the max and min between "2020-02-03 02:00:00.207" and "2020-02-03 02:00:01.268". So our bar looks like this:

---------------------------------------------------------------------------
|            date           |   open   |    high    |    low   |   close   |
|2020-02-03 02:00:01.268000 | 1.10880  |   1.10890  |  1.10880 |  1.10890  |

Now, we take this value "2020-02-03 02:00:01.268" and search for all ticks that are from "2020-02-03 02:00:01.268" and look forward another range of 1 pip.

        date                        price
        "2020-02-03 02:00:01.268"   1.10890 -->Last 1 Pip!
        "2020-02-03 02:00:01.439"   1.10886
        "2020-02-03 02:00:01.607"   1.10883
        "2020-02-03 02:00:01.895"   1.10885
        "2020-02-03 02:00:02.125"   1.10890
        "2020-02-03 02:00:02.971"   1.10895
        "2020-02-03 02:00:03.045"   1.10898
        "2020-02-03 02:00:03.200"   1.10105 --> (More than )1 Pip!

So, in this case, the value of the bar are:

---------------------------------------------------------------------------
|            date           |   open   |    high    |    low   |   close   |
|2020-02-03 02:00:01.268000 | 1.10880  |   1.10890  |  1.10880 |  1.10890  |
|2020-02-03 02:00:03.200000 | 1.10890  |   1.10105  |  1.10883 |  1.10105  |->new bar

Answer 1

Are you looking to create this via SQL query or PL/pgSQL stored procedure? It looks this can be achieved using SQL query with GROUP BY clause/ WINDOW function. Here is the untested version of code. Hope this will give an idea to achieve this?

SELECT first_value (price) OVER w AS open, 
MAX(price) OVER w AS high,
MIN(price) OVER w as low,
last_value(price) OVER w as close,
date_trunc('required interval here', date)
FROM table
WINDOW w AS (PARTITION BY date_trunc('required interval here', date) ORDER BY date)