如何在ClickHouse中将Tick盘口数据转换为1分钟等时长K线数据？

针对你的Tick盘口数据（包含symbol、timestamp、ask_amount、ask_price、bid_price、bid_amount字段），以下是两种ClickHouse查询方案，分别解决有数据的分钟生成K线和**严格连续的等时长K线（含空分钟）**的需求：

一、基础版：仅生成有数据的1分钟K线 #

这个方案会对每个有Tick数据的1分钟区间生成K线，核心是用toStartOfMinute将时间对齐到每分钟起始点作为分组键：

SELECT
    symbol,
    toStartOfMinute(timestamp) AS kline_time,
    -- 开盘价：该分钟第一个快照的最优买价（可替换为ask_price或中间价）
    first_value(bid_price) OVER (PARTITION BY symbol, toStartOfMinute(timestamp) ORDER BY timestamp) AS open,
    -- 收盘价：该分钟最后一个快照的最优买价
    last_value(bid_price) OVER (PARTITION BY symbol, toStartOfMinute(timestamp) ORDER BY timestamp) AS close,
    -- 最高价：该分钟内的最高最优卖价
    max(ask_price) AS high,
    -- 最低价：该分钟内的最低最优买价
    min(bid_price) AS low,
    -- 累计买盘量
    sum(bid_amount) AS bid_volume,
    -- 累计卖盘量
    sum(ask_amount) AS ask_volume
FROM tick_data
GROUP BY symbol, kline_time
ORDER BY symbol, kline_time;

关键说明： #

• toStartOfMinute(timestamp)是实现等时长分组的核心，它会把任意时间戳对齐到当前分钟的0秒（比如2024-05-20 14:35:22会转为2024-05-20 14:35:00）。
• 盘口数据没有成交价格，所以K线的开/高/低/收基于最优买卖价计算，你可以根据业务需求调整（比如用中间价(bid_price + ask_price)/2替代单独的bid/ask价格）。

二、进阶版：生成严格连续的1分钟K线（含空分钟） #

如果需要生成无数据的分钟也保留K线条目的严格等时长序列，需要先生成连续的时间桶，再和原数据左连接补全：

WITH
    -- 获取数据覆盖的时间范围，对齐到分钟起始点
    (SELECT toStartOfMinute(min(timestamp)) FROM tick_data) AS start_time,
    (SELECT toStartOfMinute(max(timestamp)) FROM tick_data) AS end_time
SELECT
    s.symbol,
    t.kline_time,
    -- 空分钟补全：用上一个非空分钟的收盘价作为当前开盘/收盘价
    ifnull(
        first_value(d.bid_price) OVER (PARTITION BY s.symbol ORDER BY t.kline_time),
        last_value(d.bid_price) OVER (PARTITION BY s.symbol ORDER BY t.kline_time ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
    ) AS open,
    ifnull(
        last_value(d.bid_price) OVER (PARTITION BY s.symbol ORDER BY t.kline_time),
        last_value(d.bid_price) OVER (PARTITION BY s.symbol ORDER BY t.kline_time ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
    ) AS close,
    -- 空分钟补全：用上一个非空分钟的最高/最低价
    ifnull(
        max(d.ask_price),
        last_value(max(d.ask_price)) OVER (PARTITION BY s.symbol ORDER BY t.kline_time ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
    ) AS high,
    ifnull(
        min(d.bid_price),
        last_value(min(d.bid_price)) OVER (PARTITION BY s.symbol ORDER BY t.kline_time ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
    ) AS low,
    -- 空分钟成交量设为0
    ifnull(sum(d.bid_amount), 0) AS bid_volume,
    ifnull(sum(d.ask_amount), 0) AS ask_volume
FROM
    -- 提取所有交易对
    (SELECT DISTINCT symbol FROM tick_data) s
CROSS JOIN
    -- 生成连续的1分钟时间序列
    (SELECT generateSequence(start_time, end_time, INTERVAL 1 MINUTE) AS kline_time) t
LEFT JOIN
    tick_data d ON s.symbol = d.symbol AND toStartOfMinute(d.timestamp) = t.kline_time
GROUP BY s.symbol, t.kline_time
ORDER BY s.symbol, t.kline_time;

关键说明： #

• generateSequence(start_time, end_time, INTERVAL 1 MINUTE)会生成从起始到结束时间的所有连续1分钟时间点，确保每个分钟都有对应的K线条目。
• 用LEFT JOIN关联原数据，空分钟的字段通过ifnull结合窗口函数取上一个非空分钟的值，保证K线的连续性；成交量直接补0。

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