求助：将minute bars合并为15 minute bars视图的实现方案

核心思路是将分钟K线按15分钟区间分组，通过聚合函数计算对应K线的开、高、低、收及成交量总和。以下是主流数据库的实现代码：

MySQL 实现 #

CREATE VIEW fifteen_minute_bars AS
SELECT
    -- 生成15分钟区间起始时间（格式：YYYY-MM-DD HH:MM:00）
    DATE_FORMAT(datetime, '%Y-%m-%d %H:%02d:00') AS bar_datetime,
    -- 取区间内最早时间的开盘价
    SUBSTRING_INDEX(GROUP_CONCAT(open ORDER BY datetime), ',', 1) AS open,
    -- 区间最高价
    MAX(high) AS high,
    -- 区间最低价
    MIN(low) AS low,
    -- 取区间内最晚时间的收盘价
    SUBSTRING_INDEX(GROUP_CONCAT(close ORDER BY datetime), ',', -1) AS close,
    -- 区间成交量总和
    SUM(volume) AS volume
FROM minute_bars
-- 按15分钟区间分组
GROUP BY DATE_FORMAT(datetime, '%Y-%m-%d %H:%02d:00')
ORDER BY bar_datetime;

PostgreSQL 实现 #

CREATE VIEW fifteen_minute_bars AS
SELECT
    -- 生成15分钟区间起始时间
    date_trunc('hour', datetime) + INTERVAL '15 minutes' * FLOOR(date_part('minute', datetime)/15) AS bar_datetime,
    -- 区间首个开盘价
    FIRST_VALUE(open) OVER (PARTITION BY interval_start ORDER BY datetime) AS open,
    MAX(high) AS high,
    MIN(low) AS low,
    -- 区间最后收盘价
    LAST_VALUE(close) OVER (PARTITION BY interval_start ORDER BY datetime RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS close,
    SUM(volume) AS volume
FROM (
    SELECT
        *,
        date_trunc('hour', datetime) + INTERVAL '15 minutes' * FLOOR(date_part('minute', datetime)/15) AS interval_start
    FROM minute_bars
) t
GROUP BY interval_start
ORDER BY interval_start;

SQL Server 实现 #

CREATE VIEW fifteen_minute_bars AS
SELECT
    -- 生成15分钟区间起始时间
    DATEADD(MINUTE, DATEDIFF(MINUTE, 0, datetime)/15*15, 0) AS bar_datetime,
    -- 区间首个开盘价
    FIRST_VALUE(open) OVER (PARTITION BY interval_start ORDER BY datetime) AS open,
    MAX(high) AS high,
    MIN(low) AS low,
    -- 区间最后收盘价
    LAST_VALUE(close) OVER (PARTITION BY interval_start ORDER BY datetime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS close,
    SUM(volume) AS volume
FROM (
    SELECT
        *,
        DATEADD(MINUTE, DATEDIFF(MINUTE, 0, datetime)/15*15, 0) AS interval_start
    FROM minute_bars
) t
GROUP BY interval_start
ORDER BY interval_start;

注意事项 #

• 确保datetime字段为标准时间类型（如DATETIME、TIMESTAMP），保证排序准确性
• 跨天的K线区间会自动被正确分组，无需额外处理
• 若存在分钟K线缺失的情况，聚合逻辑会自动忽略缺失时段，仅统计已有数据

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