MySQL中实现5分钟股票数据向小时级聚合的正确SQL查询方案

错误原因分析 #

• 第一条SQL错误：FIRST_VALUE和LAST_VALUE属于窗口函数，不能直接在GROUP BY聚合查询中当作聚合函数使用，MySQL不支持这种语法，因此触发语法报错。
• 第二条SQL错误：
  1. 分区条件仅使用HOUR(time)会导致跨天的同一小时数据被错误合并（如2024-01-01 10:00与2024-01-02 10:00被分到同一组），逻辑不符合需求。
  2. 主查询SELECT列表中的open和close是非聚合列，且未包含在GROUP BY子句中，在sql_mode=only_full_group_by模式下，MySQL无法确定分组内要取哪个值，因此触发合规性报错。

正确实现方案 #

方案一：窗口函数+分组（推荐，MySQL 8.0+支持） #

先通过窗口函数标记每个小时组的首尾记录，再聚合得到目标结果：

WITH hourly_groups AS (
    SELECT
        `time`,
        `open`,
        `high`,
        `low`,
        `close`,
        `volume`,
        -- 按日期+小时分区，标记组内第一条记录
        ROW_NUMBER() OVER (PARTITION BY DATE(`time`), HOUR(`time`) ORDER BY `time`) AS rn_first,
        -- 按日期+小时分区，标记组内最后一条记录
        ROW_NUMBER() OVER (PARTITION BY DATE(`time`), HOUR(`time`) ORDER BY `time` DESC) AS rn_last
    FROM stock_data -- 替换为你的实际表名
)
SELECT
    DATE_FORMAT(MIN(`time`), '%Y-%m-%d %H:00:00') AS hour_start,
    MAX(CASE WHEN rn_first = 1 THEN `open` END) AS `open`,
    MAX(`high`) AS `high`,
    MIN(`low`) AS `low`,
    MAX(CASE WHEN rn_last = 1 THEN `close` END) AS `close`,
    SUM(`volume`) AS `volume`
FROM hourly_groups
GROUP BY DATE(`time`), HOUR(`time`)
ORDER BY hour_start;

方案二：关联子查询（兼容MySQL 5.x版本） #

通过子查询获取每个小时组的首尾记录值，结合聚合函数完成转换：

SELECT
    DATE_FORMAT(s.`time`, '%Y-%m-%d %H:00:00') AS hour_start,
    (SELECT `open` FROM stock_data 
     WHERE DATE(`time`) = DATE(s.`time`) AND HOUR(`time`) = HOUR(s.`time`)
     ORDER BY `time` LIMIT 1) AS `open`,
    MAX(s.`high`) AS `high`,
    MIN(s.`low`) AS `low`,
    (SELECT `close` FROM stock_data 
     WHERE DATE(`time`) = DATE(s.`time`) AND HOUR(`time`) = HOUR(s.`time`)
     ORDER BY `time` DESC LIMIT 1) AS `close`,
    SUM(s.`volume`) AS `volume`
FROM stock_data s -- 替换为你的实际表名
GROUP BY DATE(s.`time`), HOUR(s.`time`)
ORDER BY hour_start;

关键说明 #

• 分区逻辑：必须同时按DATE(time)和HOUR(time)分组，避免跨天的同一小时数据被错误合并。
• 首尾值提取：
  • 方案一中用ROW_NUMBER()标记首尾记录，通过CASE+MAX确保每个分组仅取到一条首尾记录的对应值。
  • 方案二中用关联子查询+LIMIT 1直接获取首尾记录的open和close，适配MySQL 5.x等旧版本。
• 表名替换：将代码中的stock_data替换为你实际的股票数据表名。

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