Postgresql:在使用按函数分组时帮助计算Postgres中的增量值

Question

我正在建立一个股票市场数据库。我有一张带有时间戳、符号、价格和数量的桌子。成交量为每日累计成交量。例如：

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

我想查询得到的OHLCV (开放，高，低，关闭和体积)数据。我正在使用以下获得的OHLC数据，但不是卷，我得到了适当的OHLC。请注意，我使用的时间刻度db时间桶函数类似于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;

因此，对于卷，我需要计算最大/最后的体积在同一时间和最大/最后的体积在前一个时间帧。获取以下数据

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

sql应该是什么样的呢？我试着用滞后性，但是滞后期和团购在一起玩得不好。

Answer 1

如果您将查询放在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
;

Answer 2

与Mike Organek's答案类似，您可以通过CTE将数据收集到桶中，然后在主查询中从time列中减去一分钟，以获得上一桶的time值。您可以使用该值在同一天内为上一次时间桶的行设置LEFT JOIN：

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)

此方法将避免TimescaleDB对窗口函数的限制。