如何在ClickHouse中基于日期范围统计每日有效条目数？

原始数据 #

输入的原始数据表如下：

状态生效规则 #

date字段代表条目的状态变更日期：

• 条目从上一条记录的date（首次记录则从该date开始）到当前记录date的前一天，处于上一条的state；
• 从当前date起（直到下一条变更）处于当前state。
  例如：条目A在2022-12-27至2022-12-30期间为blue状态，2022-12-31起为red状态。

期望结果 #

需要统计每个日期的有效条目数量，结果如下：

问题分析 #

你尝试的代码直接按date分组后填充，这种方式只统计了每个变更日期的条目数，没有考虑每个条目状态的时间区间覆盖范围，所以结果不符合预期。

解决方案 #

要实现需求，需要先为每个条目生成状态生效的时间区间，再统计每个日期被多少区间覆盖，同时收集对应的条目名称生成comment。

完整实现SQL #

WITH cleaned_data AS (
    -- 清洗数据，提取有效日期
    SELECT
        item,
        state,
        parseDateTimeBestEffort(date) AS valid_date
    FROM my_table
),
item_intervals AS (
    -- 获取每个条目状态的起止日期节点
    SELECT
        item,
        valid_date AS start_date,
        -- 取下一条状态变更日期，无下一条则用当前日期
        COALESCE(LEAD(valid_date) OVER (PARTITION BY item ORDER BY valid_date), valid_date) AS next_date
    FROM cleaned_data
),
date_ranges AS (
    -- 生成每个状态覆盖的完整日期区间
    SELECT
        item,
        start_date,
        date_sub(next_date, INTERVAL 1 DAY) AS end_date
    FROM item_intervals
    WHERE start_date <= date_sub(next_date, INTERVAL 1 DAY)
    -- 单独处理最后一条状态的生效日期（当天）
    UNION ALL
    SELECT
        item,
        next_date AS start_date,
        next_date AS end_date
    FROM item_intervals
    WHERE LEAD(valid_date) OVER (PARTITION BY item ORDER BY valid_date) IS NULL
),
date_series AS (
    -- 生成需要统计的连续日期序列
    SELECT
        toDate('2022-12-26') + number AS stat_date
    FROM numbers(6) -- 从2022-12-26到2022-12-31共6天
)
-- 关联统计并生成最终结果
SELECT
    ds.stat_date AS date,
    COUNT(DISTINCT dr.item) AS count_item,
    CONCAT('(', arrayStringConcat(groupArray(DISTINCT dr.item), ' & '), ')') AS comment
FROM date_series ds
LEFT JOIN date_ranges dr ON ds.stat_date BETWEEN dr.start_date AND dr.end_date
GROUP BY ds.stat_date
ORDER BY ds.stat_date;

关键步骤说明 #

1. 数据清洗：用parseDateTimeBestEffort自动提取脏数据中的有效日期（比如B的异常日期会被解析为2022-12-31）。
2. 区间生成：通过LEAD窗口函数获取每个条目的下一个状态变更日期，拆分出当前状态的生效起始和结束日期。
3. 日期序列：用numbers函数生成需要统计的连续日期，若不确定起止日期，也可以从原始数据中动态获取最小/最大日期生成。
4. 关联统计：将日期序列与条目生效区间关联，统计每日覆盖的条目数，同时拼接条目名称生成备注。

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