Clickhouse WITH FILL能否填充指定缺失值？如何生成每日钱包余额

我有一个包含钱包ID、日期、eod_balance的数据集，该数据集仅记录余额变动当日的条目。需要使用Clickhouse编写查询，返回每个钱包每日的余额（余额变动仅在发生当日体现）。

示例数据集 #

期望返回结果 #

可见，每个钱包每日都应有一条记录。我初步考虑使用WITH FILL来补全对应行，但作为Clickhouse新手，不确定是否可行。

Clickhouse的WITH FILL确实可以实现这个需求，结合窗口函数和笛卡尔积生成全量日期范围，就能补全每个钱包的每日余额记录，具体查询如下：

WITH 
-- 提取所有唯一钱包ID
all_wallets AS (SELECT DISTINCT id FROM your_table),
-- 获取数据集的日期边界
date_bounds AS (SELECT min(date) AS start_date, max(date) AS end_date FROM your_table)
SELECT
    id,
    date,
    -- 用窗口函数取每个钱包截至当前日期的最后一次余额值
    last_value(eod_balance) OVER (
        PARTITION BY id 
        ORDER BY date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS eod_balance
FROM
    all_wallets
-- 生成每个钱包在日期范围内的所有日期
CROSS JOIN (
    SELECT toDate(start_date) + number AS date 
    FROM date_bounds, numbers(toUInt64(end_date - start_date + 1))
) AS all_dates
-- 关联原表获取有变动的日期的余额
LEFT JOIN your_table 
    ON all_wallets.id = your_table.id 
    AND all_dates.date = your_table.date
-- 按钱包和日期排序，确保FILL逻辑正确
ORDER BY id, date
-- 按钱包分区补全缺失的日期行，余额自动沿用前值
WITH FILL FROM start_date TO end_date STEP INTERVAL 1 DAY PARTITION BY id

代码说明 #

1. 生成全量基础数据：先提取所有钱包ID和日期范围，通过CROSS JOIN和numbers函数生成每个钱包在日期范围内的所有日期条目。
2. 关联原数据：左关联原表，仅保留有余额变动日期的数值，其他日期为NULL。
3. 填充余额值：使用last_value窗口函数，确保每个日期的余额取该钱包最近一次变动后的余额。
4. 补全行记录：通过WITH FILL按钱包分区补全缺失的日期行，余额会自动沿用前一个非NULL值，最终得到每个钱包每日的余额记录。

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