如何通过SQL合并分钟K线为5分钟OHLC并统计高低价与总成交量

现有SQL的错误点 #

• 收盘价获取错误：关联收盘价表close_t时误用了分组的最小idmin_id，导致收盘价和开盘价取的是同一条分钟数据的值，应改用分组的最大idmax_id匹配
• 最低价计算错误：计算low_val时错误使用了取最大值的GREATEST函数，应改用取最小值的LEAST函数
• 表名引用不一致：内层子查询用了全路径intraday_data.intraday，外层分组查询直接用了intraday，非当前库下表会触发不存在报错

修正后的SQL #

SELECT 
    open_close_t.cross_id,
    open_close_t.open_val,
    open_close_t.close_val,
    high_low_t.high,
    high_low_t.low,
    high_low_t.total_volume 
FROM (
    SELECT 
        open_close.max_id AS cross_id,
        open_t.open AS open_val,
        close_t.close AS close_val 
    FROM 
        (SELECT MAX(id) AS max_id, MIN(id) AS min_id FROM intraday_data.intraday GROUP BY FLOOR(id/5)) AS open_close
        INNER JOIN intraday_data.intraday AS open_t ON open_t.id = open_close.min_id
        INNER JOIN intraday_data.intraday AS close_t ON close_t.id = open_close.max_id
) AS open_close_t
LEFT JOIN (
    SELECT 
        MAX(id) AS cross_id,
        MAX(high_val) AS high,
        MIN(low_val) AS low,
        SUM(volume) AS total_volume 
    FROM (
        SELECT 
            id,
            GREATEST(open, high, low, close) AS high_val,
            LEAST(open, high, low, close) AS low_val,
            volume 
        FROM intraday_data.intraday
    ) AS _t
    GROUP BY FLOOR(id/5)
) AS high_low_t ON open_close_t.cross_id = high_low_t.cross_id

可选优化方案（支持窗口函数的数据库可使用，性能更优） #

SELECT 
    MAX(id) AS cross_id,
    FIRST_VALUE(open) OVER (PARTITION BY FLOOR(id/5) ORDER BY id ASC) AS open_val,
    LAST_VALUE(close) OVER (PARTITION BY FLOOR(id/5) ORDER BY id ASC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS close_val,
    MAX(GREATEST(open, high, low, close)) AS high,
    MIN(LEAST(open, high, low, close)) AS low,
    SUM(volume) AS total_volume
FROM intraday_data.intraday
GROUP BY FLOOR(id/5)

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