数据缺失值填充：补全年份内缺失Period的虚拟行

实现思路 #

先生成1到13的完整Period序列，再将其与原始数据表按Year和Period做左连接，最后对缺失字段填充默认值即可。

示例SQL代码（以MySQL为例） #

WITH all_periods AS (
    -- 生成1到13的完整Period序列
    SELECT 1 AS period UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5 UNION ALL
    SELECT 6 UNION ALL
    SELECT 7 UNION ALL
    SELECT 8 UNION ALL
    SELECT 9 UNION ALL
    SELECT 10 UNION ALL
    SELECT 11 UNION ALL
    SELECT 12 UNION ALL
    SELECT 13
)
SELECT
    2022 AS Year,
    ap.period AS Period,
    -- 原始数据有值则用原始Week，否则默认1
    COALESCE(t.Week, 1) AS Week,
    -- 原始数据有值则用原始PeriodXweek，否则拼接Period和1
    COALESCE(t.PeriodXweek, CONCAT(ap.period, 'X', 1)) AS PeriodXweek,
    -- 原始数据有值则用原始amount，否则填充0
    COALESCE(t.amount, 0) AS amount
FROM all_periods ap
LEFT JOIN your_table t ON ap.period = t.Period AND t.Year = 2022
ORDER BY ap.period;

代码说明 #

• all_periods CTE：生成1到13的完整Period列表，确保不会遗漏任何需要的Period值。
• 左连接：将完整Period序列与原始表关联，保留所有Period，缺失的原始数据行会显示为NULL。
• COALESCE函数：对NULL值进行替换，Week默认设为1，PeriodXweek按PeriodX1格式生成，amount填充为0。
• 排序：按Period升序排列，保证结果顺序符合预期。

适配多年份场景 #

如果需要处理所有年份（2020、2021、2023等），可以先提取所有唯一年份，再和Period序列做交叉连接，再左连原始表：

WITH all_years AS (
    SELECT DISTINCT Year FROM your_table
),
all_periods AS (
    SELECT 1 AS period UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5 UNION ALL
    SELECT 6 UNION ALL
    SELECT 7 UNION ALL
    SELECT 8 UNION ALL
    SELECT 9 UNION ALL
    SELECT 10 UNION ALL
    SELECT 11 UNION ALL
    SELECT 12 UNION ALL
    SELECT 13
),
year_periods AS (
    SELECT ay.Year, ap.period FROM all_years ay CROSS JOIN all_periods ap
)
SELECT
    yp.Year,
    yp.period AS Period,
    COALESCE(t.Week, 1) AS Week,
    COALESCE(t.PeriodXweek, CONCAT(yp.period, 'X', 1)) AS PeriodXweek,
    COALESCE(t.amount, 0) AS amount
FROM year_periods yp
LEFT JOIN your_table t ON yp.Year = t.Year AND yp.period = t.Period
ORDER BY yp.Year, yp.period;

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