ClickHouse中按日计算7天滑动窗口内的唯一用户数

问题核心 #

需要基于用户服务日志表，计算每个日期前7天至当天滑动窗口内的唯一用户数，核心难点是ClickHouse的RANGE窗口不支持INTERVAL语法，需手动转换为秒数，同时要确保结果每天仅返回一行。

错误修正与解决方案 #

1. 窗口函数的关键错误 #

你的现有代码中PARTITION BY day是错误的——滑动窗口需要跨日期统计，按天分区会把不同日期的数据完全隔离，无法实现跨7天的统计逻辑，必须去掉这个分区条件。

2. 7天秒数转换 #

7天对应的秒数为 7*24*3600 = 604800，由于day是date_trunc('day', timestamp)得到的DateTime类型，需要先转换为UInt32类型的时间戳，才能在RANGE窗口中使用秒数范围。

3. 优化后的完整SQL #

WITH cleaned_table AS (
    -- 去重：同一个用户同一天的多条记录仅保留一条，避免重复计数
    SELECT DISTINCT
        user_id,
        date_trunc('day', timestamp) AS day
    FROM table
)
SELECT
    day,
    -- 滑动窗口统计：范围为当前日期前7天到当天
    uniqExact(user_id) OVER (
        ORDER BY toUInt32(day)
        RANGE BETWEEN 604800 PRECEDING AND CURRENT ROW
    ) AS rolling_7d_unique_users
FROM cleaned_table
-- 按天分组，确保每天仅返回一行结果
GROUP BY day
ORDER BY day;

4. 大数据量场景下的高效写法 #

如果日志表数据量极大，建议先生成连续日期序列再关联统计，避免窗口函数的性能瓶颈：

-- 生成目标日期范围（替换为你的实际统计区间）
WITH date_series AS (
    SELECT toDate('2022-06-01') + number AS day
    FROM numbers(30) -- 生成30天的日期，可按需调整数量
),
user_days AS (
    SELECT DISTINCT
        user_id,
        toDate(timestamp) AS day
    FROM table
)
SELECT
    ds.day,
    uniqExact(ud.user_id) AS rolling_7d_unique_users
FROM date_series ds
LEFT JOIN user_days ud 
    ON ud.day BETWEEN ds.day - INTERVAL 6 DAY AND ds.day -- 前6天到当天，共7天范围
GROUP BY ds.day
ORDER BY ds.day;

关键说明 #

• DISTINCT的作用：确保同一个用户每天仅被统计一次，避免单日内多条日志导致的重复计数
• toUInt32(day)：将DateTime类型转换为秒级时间戳，让RANGE窗口能识别秒数范围参数
• 写法选择：窗口函数写法简洁直观，适合中小数据量；预聚合日期的写法性能更优，适合大数据量场景

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