按年度52周透视数据并填充缺失值为前值的SQL问题

需求 #

按年度的52周（示例使用13周）对数据进行透视，同时将所有空白值填充为前一个有效值（同年度内，空白周取之前最近有数据的周的值）。

测试数据 #

CREATE TABLE testdata (
    [year] INT,
    id1 VARCHAR(10),
    id2 VARCHAR(10),
    [week] INT,
    date1 DATE,
    date2 DATE
);

INSERT INTO testdata ([year], id1, id2, [week], date1, date2) VALUES
(2023, 'p1', 'p001', 1, '2023-01-01', '2023-01-01'),
(2023, 'p2', 'p002', 4, '2023-01-02', '2023-01-02'),
(2023, 'p3', 'p003', 7, '2023-01-03', '2023-01-03'),
(2023, 'p4', 'p004', 10, '2023-01-04', '2023-01-04'),
(2023, 'p5', 'p005', 13, '2023-01-05', '2023-01-05'),
(2024, 'p1', 'p001', 1, '2024-02-01', '2024-02-01'),
(2024, 'p2', 'p002', 2, '2024-02-02', '2024-02-02'),
(2024, 'p3', 'p003', 7, '2024-02-03', '2024-02-03'),
(2024, 'p4', 'p004', 10, '2024-02-04', '2024-02-04'),
(2024, 'p5', 'p005', 13, '2024-02-05', '2024-02-05');

尝试的代码 #

WITH WeeklyData AS (
  SELECT
    [year],
    [week],
    date1
  FROM testdata
)
, PivotedData AS (
  SELECT
    [year],
    MAX(CASE WHEN [week] = 1 THEN date1 END) AS week_1_date1,
    MAX(CASE WHEN [week] = 2 THEN date1 END) AS week_2_date1,
    MAX(CASE WHEN [week] = 3 THEN date1 END) AS week_3_date1,
    MAX(CASE WHEN [week] = 4 THEN date1 END) AS week_4_date1,
    MAX(CASE WHEN [week] = 5 THEN date1 END) AS week_5_date1,
    MAX(CASE WHEN [week] = 6 THEN date1 END) AS week_6_date1,
    MAX(CASE WHEN [week] = 7 THEN date1 END) AS week_7_date1,
    MAX(CASE WHEN [week] = 8 THEN date1 END) AS week_8_date1,
    MAX(CASE WHEN [week] = 9 THEN date1 END) AS week_9_date1,
    MAX(CASE WHEN [week] = 10 THEN date1 END) AS week_10_date1,
    MAX(CASE WHEN [week] = 11 THEN date1 END) AS week_11_date1,
    MAX(CASE WHEN [week] = 12 THEN date1 END) AS week_12_date1,
    MAX(CASE WHEN [week] = 13 THEN date1 END) AS week_13_date1
  FROM WeeklyData
  GROUP BY [year]
)
SELECT
  [year],
  COALESCE(week_1_date1, LAG(week_1_date1) OVER (ORDER BY [year])) AS week_1_date1,
  COALESCE(week_2_date1, LAG(week_2_date1) OVER (ORDER BY [year])) AS week_2_date1,
  COALESCE(week_3_date1, LAG(week_3_date1) OVER (ORDER BY [year])) AS week_3_date1,
  COALESCE(week_4_date1, LAG(week_4_date1) OVER (ORDER BY [year])) AS week_4_date1,
  COALESCE(week_5_date1, LAG(week_5_date1) OVER (ORDER BY [year])) AS week_5_date1,
  COALESCE(week_6_date1, LAG(week_6_date1) OVER (ORDER BY [year])) AS week_6_date1,
  COALESCE(week_7_date1, LAG(week_7_date1) OVER (ORDER BY [year])) AS week_7_date1,
  COALESCE(week_8_date1, LAG(week_8_date1) OVER (ORDER BY [year])) AS week_8_date1,
  COALESCE(week_9_date1, LAG(week_9_date1) OVER (ORDER BY [year])) AS week_9_date1,
  COALESCE(week_10_date1, LAG(week_10_date1) OVER (ORDER BY [year])) AS week_10_date1,
  COALESCE(week_11_date1, LAG(week_11_date1) OVER (ORDER BY [year])) AS week_11_date1,
  COALESCE(week_12_date1, LAG(week_12_date1) OVER (ORDER BY [year])) AS week_12_date1,
  COALESCE(week_13_date1, LAG(week_13_date1) OVER (ORDER BY [year])) AS week_13_date1
FROM PivotedData
ORDER BY [year];

问题分析 #

原代码使用LAG函数仅能取上一年同周的数据，无法实现同年度内空白周填充前一个有效周数据的需求（比如2023年周2、3需要填充周1的值，周5、6填充周4的值）。

正确解决方案 #

WITH AllWeeks AS (
    -- 生成1到13的周序列（实际扩展为1-52即可）
    SELECT TOP 13 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS week_num
    FROM sys.columns
),
YearWeekCombination AS (
    -- 生成每个年度的所有周组合，确保无遗漏
    SELECT DISTINCT td.[year], aw.week_num
    FROM testdata td
    CROSS JOIN AllWeeks aw
),
RawDataWithAllWeeks AS (
    -- 关联原始数据，补全年度内所有周的记录
    SELECT
        ywc.[year],
        ywc.week_num,
        td.date1,
        td.date2
    FROM YearWeekCombination ywc
    LEFT JOIN testdata td ON ywc.[year] = td.[year] AND ywc.week_num = td.[week]
),
FilledData AS (
    -- 同年度内填充前一个有效值
    SELECT
        [year],
        week_num,
        -- 取当前周及之前最近的非NULL date1
        LAST_VALUE(date1) OVER (PARTITION BY [year] ORDER BY week_num ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS filled_date1,
        LAST_VALUE(date2) OVER (PARTITION BY [year] ORDER BY week_num ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS filled_date2
    FROM RawDataWithAllWeeks
)
-- 透视生成最终宽表
SELECT
    [year],
    MAX(CASE WHEN week_num = 1 THEN filled_date1 END) AS week_1_date1,
    MAX(CASE WHEN week_num = 2 THEN filled_date1 END) AS week_2_date1,
    MAX(CASE WHEN week_num = 3 THEN filled_date1 END) AS week_3_date1,
    MAX(CASE WHEN week_num = 4 THEN filled_date1 END) AS week_4_date1,
    MAX(CASE WHEN week_num = 5 THEN filled_date1 END) AS week_5_date1,
    MAX(CASE WHEN week_num = 6 THEN filled_date1 END) AS week_6_date1,
    MAX(CASE WHEN week_num = 7 THEN filled_date1 END) AS week_7_date1,
    MAX(CASE WHEN week_num = 8 THEN filled_date1 END) AS week_8_date1,
    MAX(CASE WHEN week_num = 9 THEN filled_date1 END) AS week_9_date1,
    MAX(CASE WHEN week_num = 10 THEN filled_date1 END) AS week_10_date1,
    MAX(CASE WHEN week_num = 11 THEN filled_date1 END) AS week_11_date1,
    MAX(CASE WHEN week_num = 12 THEN filled_date1 END) AS week_12_date1,
    MAX(CASE WHEN week_num = 13 THEN filled_date1 END) AS week_13_date1,
    -- 同理处理date2字段
    MAX(CASE WHEN week_num = 1 THEN filled_date2 END) AS week_1_date2,
    MAX(CASE WHEN week_num = 2 THEN filled_date2 END) AS week_2_date2,
    MAX(CASE WHEN week_num = 3 THEN filled_date2 END) AS week_3_date2,
    MAX(CASE WHEN week_num = 4 THEN filled_date2 END) AS week_4_date2,
    MAX(CASE WHEN week_num = 5 THEN filled_date2 END) AS week_5_date2,
    MAX(CASE WHEN week_num = 6 THEN filled_date2 END) AS week_6_date2,
    MAX(CASE WHEN week_num = 7 THEN filled_date2 END) AS week_7_date2,
    MAX(CASE WHEN week_num = 8 THEN filled_date2 END) AS week_8_date2,
    MAX(CASE WHEN week_num = 9 THEN filled_date2 END) AS week_9_date2,
    MAX(CASE WHEN week_num = 10 THEN filled_date2 END) AS week_10_date2,
    MAX(CASE WHEN week_num = 11 THEN filled_date2 END) AS week_11_date2,
    MAX(CASE WHEN week_num = 12 THEN filled_date2 END) AS week_12_date2,
    MAX(CASE WHEN week_num = 13 THEN filled_date2 END) AS week_13_date2
FROM FilledData
GROUP BY [year]
ORDER BY [year];

步骤说明 #

1. AllWeeks：生成所需的周数序列，示例为1-13，实际可修改为1-52。
2. YearWeekCombination：为每个年度生成所有周的组合，确保没有遗漏的周记录。
3. RawDataWithAllWeeks：关联原始数据，补全所有周的记录，缺失数据的周对应date1/date2为NULL。
4. FilledData：使用LAST_VALUE窗口函数，按年度分组、周数排序，取当前周及之前最近的非NULL值填充空白。
5. 透视：将填充后的数据按年度透视，得到符合需求的宽表格式。

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