Postgres使用Group By分组时如何获取首尾值以生成加密货币1小时K线

你原先的写法存在两个核心问题：

• FIRST_VALUE属于窗口函数，不是聚合函数，无法直接和GROUP BY搭配得到正确的分组首行值
• 使用CEILING计算分组会导致整点时间戳的分组偏移，建议替换为FLOOR，确保同一小时内的所有分钟K线被归到同一分组

方案1：支持窗口函数的数据库（MySQL 8.0+/PostgreSQL/Spark SQL等通用方案） #

通过窗口函数给每个小时分组内的K线按时间排序，标记首行和末行后聚合即可，代码如下：

WITH hourly_ranked AS (
    SELECT
        *,
        -- 计算所属小时分组
        FLOOR(unix / 3600) AS hour_group,
        -- 组内按时间正序排序，行号为1的是当前小时首根K线
        ROW_NUMBER() OVER (PARTITION BY FLOOR(unix / 3600) ORDER BY unix ASC) AS rn_asc,
        -- 组内按时间倒序排序，行号为1的是当前小时末根K线
        ROW_NUMBER() OVER (PARTITION BY FLOOR(unix / 3600) ORDER BY unix DESC) AS rn_desc
    FROM your_kline_table
)
SELECT
    -- 小时分组的起始时间戳
    hour_group * 3600 AS hour_timestamp,
    -- 首行开盘价即小时K开盘价
    MAX(CASE WHEN rn_asc = 1 THEN open END) AS hour_open,
    MAX(high) AS hour_high,
    MIN(low) AS hour_low,
    -- 末行收盘价即小时K收盘价
    MAX(CASE WHEN rn_desc = 1 THEN close END) AS hour_close,
    SUM(volume) AS hour_volume
FROM hourly_ranked
GROUP BY hour_group
ORDER BY hour_group ASC;

方案2：不支持窗口函数的低版本数据库（如MySQL 5.x） #

可以用关联子查询的方式获取分组内的首末行值，代码如下：

SELECT
    FLOOR(t.unix / 3600) * 3600 AS hour_timestamp,
    (SELECT open FROM your_kline_table t2 WHERE FLOOR(t2.unix/3600) = FLOOR(t.unix/3600) ORDER BY t2.unix ASC LIMIT 1) AS hour_open,
    MAX(t.high) AS hour_high,
    MIN(t.low) AS hour_low,
    (SELECT close FROM your_kline_table t2 WHERE FLOOR(t2.unix/3600) = FLOOR(t.unix/3600) ORDER BY t2.unix DESC LIMIT 1) AS hour_close,
    SUM(t.volume) AS hour_volume
FROM your_kline_table t
GROUP BY FLOOR(t.unix / 3600)
ORDER BY hour_timestamp ASC;

注意该方案数据量较大时性能偏低，建议给unix字段添加索引优化查询速度。

方案3：时序类分析数据库（如ClickHouse，性能最优） #

这类数据库内置了分组取首末行值的专用函数，写法更简单，执行效率更高：

SELECT
    FLOOR(unix / 3600) * 3600 AS hour_timestamp,
    anyFirst(open) AS hour_open,
    MAX(high) AS hour_high,
    MIN(low) AS hour_low,
    anyLast(close) AS hour_close,
    SUM(volume) AS hour_volume
FROM your_kline_table
GROUP BY FLOOR(unix / 3600)
ORDER BY hour_timestamp ASC;

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