ClickHouse 22.8版本：如何用前后非零值填充价格字段零值？

现有如下结构的数据表：

该表由以下查询生成：

SELECT a.date AS date,
       a.id AS id,
       p.price AS price
  FROM articles a
  LEFT JOIN (SELECT date,
                    id,
                    price
               FROM prices) p
    ON a.date = p.date
   AND a.id = p.id

由于prices表缺少部分日期数据，导致部分price字段值为0。需在ClickHouse 22.8.9.24版本中，将这些零值替换为对应ID的前一个或后一个非零价格值。

1. 前向填充（取前一个非零值） #

通过窗口函数last_value结合IGNORE NULLS参数，先将0转为NULL，再按ID分组、日期排序，取当前行之前最近的非NULL价格：

WITH base_data AS (
    SELECT 
        a.date AS date,
        a.id AS id,
        CASE WHEN p.price = 0 THEN NULL ELSE p.price END AS price
    FROM articles a
    LEFT JOIN prices p ON a.date = p.date AND a.id = p.id
)
SELECT 
    date,
    id,
    COALESCE(price, 
             last_value(price) OVER (PARTITION BY id ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW IGNORE NULLS)
            ) AS 填充后的前值
FROM base_data
ORDER BY id, date;

2. 后向填充（取后一个非零值） #

使用first_value结合反向日期排序，按ID分组后取当前行之后最近的非NULL价格：

WITH base_data AS (
    SELECT 
        a.date AS date,
        a.id AS id,
        CASE WHEN p.price = 0 THEN NULL ELSE p.price END AS price
    FROM articles a
    LEFT JOIN prices p ON a.date = p.date AND a.id = p.id
)
SELECT 
    date,
    id,
    COALESCE(price, 
             first_value(price) OVER (PARTITION BY id ORDER BY date DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW IGNORE NULLS)
            ) AS 填充后的后值
FROM base_data
ORDER BY id, date;

3. 同时获取前向/后向填充结果 #

将两种逻辑合并，在同一张表中返回两种填充值：

WITH base_data AS (
    SELECT 
        a.date AS date,
        a.id AS id,
        CASE WHEN p.price = 0 THEN NULL ELSE p.price END AS price
    FROM articles a
    LEFT JOIN prices p ON a.date = p.date AND a.id = p.id
)
SELECT 
    date,
    id,
    COALESCE(price, 
             last_value(price) OVER (PARTITION BY id ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW IGNORE NULLS)
            ) AS 期望的前值,
    COALESCE(price, 
             first_value(price) OVER (PARTITION BY id ORDER BY date DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW IGNORE NULLS)
            ) AS 期望的后值
FROM base_data
ORDER BY id, date;

• NULL转换：通过CASE将0转为NULL，让窗口函数可以识别并跳过需要替换的值；
• 分区与排序：PARTITION BY id确保仅在同一ID范围内查找前后值，ORDER BY date保证按时间顺序匹配；
• IGNORE NULLS：ClickHouse 21.8+版本支持该参数，可让窗口函数直接跳过NULL值，取最近的非NULL数据。

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