PostgreSQL/TimescaleDB group by计算成交量delta值方法

直接在聚合层混用lag无法得到正确结果，核心原因是窗口函数计算层级不匹配：lag如果和聚合函数写在同一层，是基于原始明细行计算的，而非基于聚合完成后的时间桶结果计算。正确实现需要分两步：先完成时间桶维度的OHLC+周期末累计成交量聚合，再基于聚合结果用窗口函数取上一周期的累计值做差。

可直接运行的SQL #

WITH bucketed_calc AS (
    SELECT
        time_bucket('1 minute', "timestamp") AS time,
        -- 处理跨日累计场景时取消下行注释
        -- time_bucket('1 day', "timestamp") AS trade_date,
        symbol,
        max(price) AS high,
        first(price, timestamp) AS open,
        last(price, timestamp) AS close,
        min(price) AS low,
        -- 取当前时间桶最后一条记录的累计成交量，比max更适配乱序/修正数据场景
        last(volume, timestamp) AS period_end_cum_volume
    FROM candle_ticks
    GROUP BY time, symbol
    -- 启用trade_date时，需要把trade_date加入GROUP BY字段列表
)
SELECT
    time,
    symbol,
    high,
    open,
    close,
    low,
    period_end_cum_volume - COALESCE(
        lag(period_end_cum_volume) OVER (
            -- 跨日场景下改为 PARTITION BY symbol, trade_date
            PARTITION BY symbol
            ORDER BY time ASC
        ),
        0 -- 首个无前置周期的桶默认前置累计量为0，可根据你的初始累计规则调整
    ) AS volume
FROM bucketed_calc
ORDER BY time DESC, symbol;

注意事项 #

• 计算逻辑完全匹配需求：单周期成交量 = 当前周期结束时的累计成交量 - 上一个相邻周期结束时的累计成交量
• 推荐用last(volume, timestamp)取周期末累计值，比max(volume)更适配数据乱序、事后修正的场景：累计成交量理论上随交易推进单调递增，时间最晚的记录才是该周期最终的有效累计值；如果你的业务逻辑确认要用周期内最大累计值，直接替换为max(volume)即可
• 由于表中volume是当日累计值，如果存储跨日数据，必须打开SQL中trade_date相关的注释逻辑，避免将前一日收盘的累计成交量与当日开盘的累计成交量做差，导致跨日成交量计算错误
• 你给出的示例结果中12:42周期volume为3，是因为示例隐去了12:42之前的累计成交量基数（11），在有完整前置数据的前提下，上述SQL会输出符合预期的计算结果

内容的提问来源于stack exchange，提问作者Arvind S.A