在Snowflake分组查询中获取首尾记录列值实现OHLC统计

问题原因 #

你当前的查询中，FIRST_VALUE和LAST_VALUE是窗口函数，并非聚合函数，直接在GROUP BY语句中使用时无法正确工作：

• 子查询中的ORDER BY在没有LIMIT的情况下会被Snowflake忽略，分组时无法保证数据顺序；
• 未指定窗口范围时，FIRST_VALUE/LAST_VALUE默认作用于整个结果集，而非每个分组内部。

另外注意：你原来的加权平均价计算逻辑有误，正确的加权平均价应为总成交额除以总成交量，即SUM(size * price) / SUM(size)，而非SUM(size)/SUM(price)。

解决方案1：使用ARRAY_AGG聚合函数（推荐） #

通过ARRAY_AGG将每个分组内的价格按时间排序后存入数组，直接取数组首尾元素作为开盘价和收盘价：

SELECT 
    SUM(T.size) AS volume,
    SUM(T.size * T.price) / SUM(T.size) AS weighted_avg_price,
    ARRAY_AGG(T.price ORDER BY T.sip_timestamp ASC)[0] AS open_price,
    ARRAY_AGG(T.price ORDER BY T.sip_timestamp DESC)[0] AS close_price,
    MAX(T.price) AS high_price,
    MIN(T.price) AS low_price,
    FLOOR(T.sip_timestamp / 3600000000000) AS hour_bucket
FROM trades AS T
WHERE 
    T.symbol = 'NTAP' AND 
    T.sip_timestamp >= 1640995200000000000 AND
    T.sip_timestamp < 1672531200000000000
GROUP BY hour_bucket
ORDER BY hour_bucket;

解决方案2：使用ROW_NUMBER窗口函数标记首尾行 #

先给每个小时桶内的交易按时间排序并排名，再通过聚合筛选出首尾行的价格：

WITH ranked_trades AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY FLOOR(sip_timestamp / 3600000000000) ORDER BY sip_timestamp ASC) AS rn_asc,
        ROW_NUMBER() OVER (PARTITION BY FLOOR(sip_timestamp / 3600000000000) ORDER BY sip_timestamp DESC) AS rn_desc,
        FLOOR(sip_timestamp / 3600000000000) AS hour_bucket
    FROM trades
    WHERE 
        symbol = 'NTAP' AND 
        sip_timestamp >= 1640995200000000000 AND
        sip_timestamp < 1672531200000000000
)
SELECT 
    SUM(size) AS volume,
    SUM(size * price) / SUM(size) AS weighted_avg_price,
    MAX(CASE WHEN rn_asc = 1 THEN price END) AS open_price,
    MAX(CASE WHEN rn_desc = 1 THEN price END) AS close_price,
    MAX(price) AS high_price,
    MIN(price) AS low_price,
    hour_bucket
FROM ranked_trades
GROUP BY hour_bucket
ORDER BY hour_bucket;

内容的提问来源于stack exchange，提问作者Woody1193