如何在窗口聚合时填充空值？解决移动求和的日期间隙问题

现有一段用于计算窗口移动求和的ClickHouse查询语句：

SELECT "Дата",
       "Износ",
       SUM("Сумма") OVER (partition by "Износ" order by "Дата"
           rows between unbounded preceding and current row) AS "Продажи"
FROM (
         SELECT date_trunc('week', period) AS "Дата",
                multiIf(wear_and_tear BETWEEN 1 AND 3, '1-3',
                        wear_and_tear BETWEEN 4 AND 10, '4-10',
                        wear_and_tear BETWEEN 11 AND 20, '11-20',
                        wear_and_tear BETWEEN 21 AND 30, '21-30',
                        wear_and_tear BETWEEN 31 AND 45, '31-45',
                        wear_and_tear BETWEEN 46 AND 100, '46-100',
                        'Новые')           AS "Износ",
                SUM(quantity)              AS "Сумма"
         FROM shinsale_prod.sale_1c sc
                  LEFT JOIN product_1c pc ON sc.product_id = pc.id

         WHERE 1=1
--             AND partner != 'Наше предприятие'
--             AND wear_and_tear = 0
--             AND stock IN ('ShinSale Щитниково', 'ShinSale Строгино', 'ShinSale Кунцево', 'ShinSale Санкт-Петербург', 'Шиномонтаж Подольск')
             AND seasonality = 'з'

--          AND (quantity IN {{quant}} OR quantity  IN -{{quant}})
--          AND stock in {{Склад}}
         GROUP BY "Дата", "Износ"
         HAVING "Дата" BETWEEN '2021-06-01' AND '2022-01-08'
         ORDER BY 'Дата'
)

问题：部分"Износ"分组在2021-12-20至2022-01-03期间无数据，导致图表曲线出现间隙。尝试将子查询与空日期范围右连接，但生成的空行被WHERE条件过滤，最终结果为空或几乎为空。需要用平均值等方式填充这些数据间隙。

核心思路是先生成完整的日期-分组组合，再左连接原有业务数据，最后对缺失值进行填充，再计算移动求和。具体实现如下：

1. 生成完整的日期序列与分组组合 #

先构造目标时间范围内所有按周截断的日期，同时列出所有可能的"Износ"分组值，通过笛卡尔积得到每个分组对应每个日期的完整记录：

WITH 
-- 生成目标时间范围内的所有周日期
(SELECT arrayMap(x -> toDate(x), generateDateRange('2021-06-01', '2022-01-08', INTERVAL 1 week)) AS dates) AS date_list,
-- 定义所有可能的Износ分组
(['1-3', '4-10', '11-20', '21-30', '31-45', '46-100', 'Новые']) AS wear_groups
-- 生成日期和分组的笛卡尔积
SELECT 
    date AS "Дата",
    wear AS "Износ"
FROM date_list
ARRAY JOIN dates AS date, wear_groups AS wear

2. 左连接业务数据并填充缺失值 #

将完整的日期-分组组合左连接原查询的聚合结果，针对缺失的"Сумма"提供两种填充方案：

方案一：用0填充缺失值（适合无销售场景） #

WITH 
(SELECT arrayMap(x -> toDate(x), generateDateRange('2021-06-01', '2022-01-08', INTERVAL 1 week)) AS dates) AS date_list,
(['1-3', '4-10', '11-20', '21-30', '31-45', '46-100', 'Новые']) AS wear_groups,
-- 原有聚合查询逻辑
(
    SELECT date_trunc('week', period) AS "Дата",
           multiIf(wear_and_tear BETWEEN 1 AND 3, '1-3',
                   wear_and_tear BETWEEN 4 AND 10, '4-10',
                   wear_and_tear BETWEEN 11 AND 20, '11-20',
                   wear_and_tear BETWEEN 21 AND 30, '21-30',
                   wear_and_tear BETWEEN 31 AND 45, '31-45',
                   wear_and_tear BETWEEN 46 AND 100, '46-100',
                   'Новые')           AS "Износ",
           SUM(quantity)              AS "Сумма"
    FROM shinsale_prod.sale_1c sc
             LEFT JOIN product_1c pc ON sc.product_id = pc.id
    WHERE seasonality = 'з'
    GROUP BY "Дата", "Износ"
) AS agg_data

SELECT 
    full."Дата",
    full."Износ",
    -- 用0填充缺失的销量数据
    COALESCE(agg."Сумма", 0) AS "Сумма",
    -- 计算移动求和
    SUM(COALESCE(agg."Сумма", 0)) OVER (PARTITION BY full."Износ" ORDER BY full."Дата" 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "Продажи"
FROM 
    (SELECT date AS "Дата", wear AS "Износ" FROM date_list ARRAY JOIN dates AS date, wear_groups AS wear) AS full
LEFT JOIN agg_data AS agg ON full."Дата" = agg."Дата" AND full."Износ" = agg."Износ"
ORDER BY full."Дата", full."Износ"

方案二：用分组历史平均值填充（适合平滑曲线场景） #

先计算每个"Износ"分组的平均销量，再用该值填充缺失数据：

WITH 
(SELECT arrayMap(x -> toDate(x), generateDateRange('2021-06-01', '2022-01-08', INTERVAL 1 week)) AS dates) AS date_list,
(['1-3', '4-10', '11-20', '21-30', '31-45', '46-100', 'Новые']) AS wear_groups,
-- 原有聚合查询逻辑
(
    SELECT date_trunc('week', period) AS "Дата",
           multiIf(wear_and_tear BETWEEN 1 AND 3, '1-3',
                   wear_and_tear BETWEEN 4 AND 10, '4-10',
                   wear_and_tear BETWEEN 11 AND 20, '11-20',
                   wear_and_tear BETWEEN 21 AND 30, '21-30',
                   wear_and_tear BETWEEN 31 AND 45, '31-45',
                   wear_and_tear BETWEEN 46 AND 100, '46-100',
                   'Новые')           AS "Износ",
           SUM(quantity)              AS "Сумма"
    FROM shinsale_prod.sale_1c sc
             LEFT JOIN product_1c pc ON sc.product_id = pc.id
    WHERE seasonality = 'з'
    GROUP BY "Дата", "Износ"
) AS agg_data,
-- 计算每个分组的平均销量
(
    SELECT "Износ", AVG("Сумма") AS avg_sum
    FROM agg_data
    GROUP BY "Износ"
) AS avg_data

SELECT 
    full."Дата",
    full."Износ",
    -- 优先用实际销量，无数据则用分组平均值，无历史数据则用0
    COALESCE(agg."Сумма", avg.avg_sum, 0) AS "Сумма",
    -- 计算移动求和
    SUM(COALESCE(agg."Сумма", avg.avg_sum, 0)) OVER (PARTITION BY full."Износ" ORDER BY full."Дата" 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "Продажи"
FROM 
    (SELECT date AS "Дата", wear AS "Износ" FROM date_list ARRAY JOIN dates AS date, wear_groups AS wear) AS full
LEFT JOIN agg_data AS agg ON full."Дата" = agg."Дата" AND full."Износ" = agg."Износ"
LEFT JOIN avg_data AS avg ON full."Износ" = avg."Износ"
ORDER BY full."Дата", full."Износ"

关键说明 #

• 原查询中的HAVING "Дата" BETWEEN ...被替换为生成日期序列时的范围控制，避免过滤掉完整组合中的空日期行。
• 使用generateDateRange生成连续周日期，确保时间序列无间隙。
• 笛卡尔积生成完整的日期-分组组合，保证每个分组在每个日期都有记录。
• 填充方式可根据业务需求选择：0填充适合无销售场景，平均值填充适合需要平滑曲线的场景。

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