Postgresql: Help calculating delta value in Postgres while using group by function-CodePudding

I am building a stockmarket database. I have one table with timestamp, symbol, price and volume. The volume is cumulative volume traded per day. for e.g.

|         timestamp          | symbol |  price   | volume |
|----------------------------|--------|----------|--------|
| 2022-06-11 12:42:04.912 00 | SBIN   | 120.0000 |      5 |
| 2022-06-11 12:42:25.806 00 | SBIN   | 123.0000 |      6 |
| 2022-06-11 12:42:38.993 00 | SBIN   | 123.4500 |      8 |
| 2022-06-11 12:42:42.735 00 | SBIN   | 108.0000 |     12 |
| 2022-06-11 12:42:45.801 00 | SBIN   | 121.0000 |     14 |
| 2022-06-11 12:43:43.186 00 | SBIN   | 122.0000 |     16 |
| 2022-06-11 12:43:45.599 00 | SBIN   | 125.0000 |     17 |
| 2022-06-11 12:43:51.655 00 | SBIN   | 141.0000 |     20 |
| 2022-06-11 12:43:54.151 00 | SBIN   | 111.0000 |     24 |
| 2022-06-11 12:44:01.908 00 | SBIN   | 123.0000 |     27 |

I want to query to get OHLCV (open high low close and volume) data. I am using the following to get OHLC data but not volume and i am getting proper OHLC. Note that i am using timescale db timebucket function similar to date_trunc

SELECT
time_bucket('1 minute', "timestamp") AS time,
symbol,
max(price) AS high,
first(price, timestamp) AS open,
last(price, timestamp) AS close,
min(price) AS low,
FROM candle_ticks
GROUP BY time, symbol
ORDER BY time DESC, symbol;

So for volume, I need to calculate the difference of max / last volume in the same time and max/last volume in the previous time frame. to get the following data

|          time          | symbol |   high   |   open   |  close   |   low    | volume |
|------------------------|--------|----------|----------|----------|----------|--------|
| 2022-06-11 12:44:00 00 | SBIN   | 123.0000 | 123.0000 | 123.0000 | 123.0000 |   14   |
| 2022-06-11 12:43:00 00 | SBIN   | 141.0000 | 122.0000 | 111.0000 | 111.0000 |   10   |
| 2022-06-11 12:42:00 00 | SBIN   | 123.4500 | 120.0000 | 121.0000 | 108.0000 |    3   |

What should be sql be like? I tried to use lag, but lag and group buy together is not playing well..

CodePudding user response：

Would it work if you put your query in a CTE?

with ivals as (
  SELECT time_bucket('1 minute', "timestamp") AS time,
         symbol,
         max(price) AS high,
         first(price, timestamp) AS open,
         last(price, timestamp) AS close,
         min(price) AS low,
         max(volume) AS close_volume
    FROM candle_ticks
   GROUP BY time, symbol
)
select i.*,
       close_volume - coalesce(
                        lag(close_volume) 
                          over (partition by symbol, time::date
                                    order by time),
                        0 
                      ) as time_volume
  from ivals i
;

CodePudding user response：

Similar to Mike Organek's answer, you can collect the data into buckets via CTE and then in your main query, subtract a minute from the time column to get the time value for the previous bucket. You can use that value to LEFT JOIN the row for the previous time bucket within the same day:

WITH buckets as (

SELECT
  time_bucket('1 minute', "timestamp") AS time,
  symbol,
  max(price) AS high,
  first(price, timestamp) AS open,
  last(price, timestamp) AS close,
  min(price) AS low,
  max(volume) AS close_volume
FROM candle_ticks
GROUP BY time, symbol
ORDER BY time DESC, symbol
)

SELECT
  b.*,
  coalesce(b.close_volume - b2.close_volume,0) time_volume

FROM
  buckets b
  LEFT JOIN buckets b2 ON (b.time::date = b2.time::date and b.time - interval '1 minute' = b2.time)

This method will avoid the restrictions that TimescaleDB places on window functions.