CODEWITHSUNDEEP
postgresqlselectgroup-bytimescaledb
Arvind S.A
Arvind S.A

Reputation: 1

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

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

Upvotes: 0

Views: 1756

Answers (2)

THX1138
THX1138

Reputation: 1796

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.

Upvotes: 0

Mike Organek
Mike Organek

Reputation: 12494

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
;

Upvotes: 0

Related Questions