postgresql：在使用function组时，请帮助计算Postgres中的增量值

Question

我正在构建一个Stockmarket数据库。我有一个带时间戳，符号，价格和数量的桌子。该量是每天累积交易的累积交易。对于例如，

|         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。请注意，我正在使用类似于date_trunc的时间尺度db时数函数

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的答案，您可以通过CTE收集数据，然后在您的主疑问中，从您的主疑问中，从您的主疑Time列获取time上一个存储桶的值。您可以使用该值
左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在窗口功能上放置的限制。