Clickhouse如何实现GROUP BY后缺失值前向填充并计算周指标

可以实现上述需求，核心思路是通过生成连续周序列、构造全量分组周骨架、配合窗口函数完成前向填充，完整查询如下：

WITH
    -- 生成统计时间范围内的所有连续周（默认取原表时间覆盖的所有周，可手动替换为固定起止时间）
    all_weeks AS (
        SELECT
            toStartOfWeek(min_ts + toIntervalWeek(number), 1) AS week
        FROM
            (SELECT min(Timestamp) AS min_ts, max(Timestamp) AS max_ts FROM your_table_name),
            numbers(toUInt64(dateDiff('week', toStartOfWeek(min_ts, 1), toStartOfWeek(max_ts, 1)) + 1))
    ),
    -- 获取所有唯一的(x_id,y_id,z_id)分组
    all_groups AS (
        SELECT DISTINCT x_id, y_id, z_id FROM your_table_name
    ),
    -- 原有逻辑：统计每个分组每周的最小rank
    group_week_rank AS (
        SELECT
            x_id,
            y_id,
            z_id,
            toStartOfWeek(Timestamp, 1) AS week,
            min(rank) AS rank
        FROM your_table_name
        GROUP BY x_id, y_id, z_id, week
    ),
    -- 左关联得到填充前的全量行
    joined AS (
        SELECT
            g.x_id,
            g.y_id,
            g.z_id,
            w.week,
            r.rank
        FROM all_groups g
        CROSS JOIN all_weeks w
        LEFT JOIN group_week_rank r
            ON g.x_id = r.x_id AND g.y_id = r.y_id AND g.z_id = r.z_id AND w.week = r.week
    ),
    -- 前向填充缺失的rank值
    filled AS (
        SELECT
            x_id,
            y_id,
            z_id,
            week,
            last_value(rank) IGNORE NULLS OVER (
                PARTITION BY x_id, y_id, z_id
                ORDER BY week ASC
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            ) AS filled_rank
        FROM joined
    )
-- 按原有规则统计指标
SELECT
    week,
    SUM(CASE WHEN filled_rank BETWEEN 1 AND 3 THEN 1 ELSE 0 END) AS metric1,
    -- 其他指标按需补充即可
    SUM(CASE WHEN filled_rank BETWEEN 4 AND 10 THEN 1 ELSE 0 END) AS metric2
FROM filled
WHERE filled_rank IS NOT NULL -- 过滤分组首次出现前无有效值的无效行，不需要可删除
GROUP BY week
ORDER BY week;

注意事项 #

• 使用时请将your_table_name替换为实际的表名
• 若需要限定统计的时间范围，可直接修改all_weeks中的min_ts和max_ts为固定日期，例如SELECT toDate('2021-03-01') AS min_ts, toDate('2021-06-01') AS max_ts
• last_value(rank) IGNORE NULLS 会自动跳过空值，取当前分组最近一次出现的非空rank值，符合前向填充的需求

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