在ClickHouse中针对分钟级数据计算五日扩展平均值

核心思路 #

1. 映射交易日归属：将每条分钟级数据的时间戳，转换为其所属的交易日起始时间（规则：前一日21:00至当日21:00为一个交易日，对应起始时间为前一日21:00）。
2. 标记交易日序号：对所有交易日的起始时间按顺序编号，通过序号偏移找到当前交易日往前数5个交易日的起始点。
3. 计算扩展平均：基于找到的5日起始点，计算该点到当前时间的所有value的平均值。

具体实现代码 #

假设你的表名为minute_data，包含字段timestamp（DateTime类型）和value（Float64类型）：

WITH
    -- 计算每条数据所属的交易日起始时间
    toDateTime(toDate(timestamp - INTERVAL 3 HOUR)) + INTERVAL 21 HOUR AS trade_day_start,
    -- 生成所有交易日的序号映射表
    (SELECT arrayJoin(arrayEnumerate(trade_days)) AS idx, trade_day FROM (
        SELECT DISTINCT trade_day_start AS trade_day FROM minute_data ORDER BY trade_day
    ) AS t) AS trade_day_index
SELECT
    m.timestamp,
    m.value,
    m.trade_day_start AS five_day_starting_point,
    -- 计算5日扩展平均值，不足5个交易日时返回NULL
    CASE
        WHEN (SELECT idx FROM trade_day_index WHERE trade_day = m.trade_day_start) >=5 THEN
            AVG(m.value) OVER (
                ORDER BY m.timestamp
                RANGE BETWEEN (SELECT trade_day FROM trade_day_index WHERE idx = (SELECT idx FROM trade_day_index WHERE trade_day = m.trade_day_start) - 5) PRECEDING AND CURRENT ROW
            )
        ELSE NULL
    END AS five_day_expanding_avg
FROM minute_data m
ORDER BY m.timestamp;

代码说明 #

• 交易日映射逻辑：toDate(timestamp - INTERVAL 3 HOUR)将时间戳转换为交易日基准日期（例如2023-03-15 00:18减去3小时后转Date为2023-03-14，再加21小时得到交易日起始时间2023-03-14 21:00）。
• 交易日序号标记：通过子查询提取所有唯一交易日起始时间并排序，用arrayEnumerate生成连续序号，快速定位5个交易日前的起始点。
• 扩展平均计算：利用范围窗口函数，以5个交易日前的起始点为窗口左边界，计算到当前行的平均值；当交易日序号小于5时返回NULL，匹配示例输出要求。

优化建议 #

如果数据量较大，可通过以下方式提升性能：

• 对timestamp字段建立索引，加速窗口函数的范围查询。
• 将trade_day_start作为衍生字段提前存储到表中，避免每次查询重复计算。

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