如何让ClickHouse WITH FILL用前值填充非排序列而非0？

方案一：高效计算累计唯一ID并自动填充（推荐） #

这种方式先统计每个ID的首次出现日期，再计算累计唯一数，性能远优于窗口函数中直接使用count(distinct)：

WITH 
-- 获取所有ID的首次出现日期
first_seen_dates AS (
    SELECT id, min(date) AS first_seen FROM test GROUP BY id
),
-- 按日期统计每日新增的唯一ID数量
daily_new_ids AS (
    SELECT first_seen AS date, count(*) AS new_ids FROM first_seen_dates GROUP BY first_seen
)
-- 计算累计唯一ID数，并填充缺失日期
SELECT 
    date,
    sum(new_ids) OVER (ORDER BY date) AS UniqueIdsPresent
FROM daily_new_ids
ORDER BY date 
WITH FILL FROM (SELECT min(date) FROM test) TO (SELECT max(date) FROM test) STEP 1 DAY;

原理说明 #

1. 通过min(date)锁定每个ID的首次出现日期，确保每个ID仅被统计一次；
2. 按日期分组统计每日新增的唯一ID数量；
3. 使用sum() OVER (ORDER BY date)计算累计值，缺失日期被WITH FILL填充后，new_ids自动为0，累加后自然保持之前的累计值，无需额外处理。

方案二：基于原有查询修改填充逻辑 #

如果需要沿用你原来的查询结构，可在WITH FILL后通过窗口函数将0替换为前一行的有效累计值：

WITH base_data AS (
    SELECT date, max(rolling_sum) AS UniqueIdsPresent
    FROM (
        SELECT date, 
            count(distinct id) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rolling_sum 
        FROM test
    )
    GROUP BY date
    ORDER BY date 
    WITH FILL FROM (SELECT min(date) FROM test) TO (SELECT max(date) FROM test) STEP 1 DAY
)
SELECT 
    date,
    last_value(UniqueIdsPresent IGNORE NULLS) OVER (ORDER BY date) AS UniqueIdsPresent
FROM base_data;

原理说明 #

1. 原有查询生成带缺失日期的结果，缺失行的UniqueIdsPresent会被填充为0；
2. 使用last_value(UniqueIdsPresent IGNORE NULLS)窗口函数，取当前行之前最后一个有效累计值，自动覆盖缺失行的0值。

验证结果 #

以你的示例数据为例，两种方案均会返回符合预期的结果：

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