如何在SQL中对按ITEM和CITY分组的数据集进行周度重采样

原始数据集 #

ITEM    CITY        START_Y   START_W   FIRST_USE_Y   FIRST_USE_W   VALUE
A       NEW YORK    2023      30             2023             32    15000
A       LONDON      2024       2             2024              2    12000
A       LONDON      2024       2             2024              5    50000
B       NEW YORK    2023      49             2024              1    19540
B       MADRID      2023      10             2023             11    15444

需求说明 #

• 按ITEM和CITY的组合分组
• 每组生成最多5个周度数据点
• 当FIRST_USE_Y与FIRST_USE_W的组合无对应原始数据时，VALUE填充为0
• 注：START_W和FIRST_USE_W是年周数，取值范围1-52

尝试过的SQL代码 #

WITH RECURSIVE weekly_intervals AS (
    SELECT MIN(start_w) AS start_w, MAX(start_w) AS end_w
    FROM citywise_values
    UNION ALL
    SELECT start_w + INTERVAL 1 WEEK, end_w
    FROM weekly_intervals
    WHERE start_w + INTERVAL 1 WEEK <= end_w
),
filled_values AS (
    SELECT 
        w.item,
        w.city,
        w.start_y,
        w.start_w,
        COALESCE(cv.value, 0) AS value
    FROM 
        (SELECT 
            item,
            city,
            start_y,
            start_w
        FROM 
            citywise_values
        GROUP BY 
            item, city) w
    LEFT JOIN 
        citywise_values cv ON w.item = cv.item
                             AND w.city = cv.city
                             AND w.start_y = cv.start_y
                             AND w.start_w = cv.start_w
)
SELECT 
    item,
    city,
    start_y,
    start_w,
    COALESCE(value, LAG(value) OVER (PARTITION BY item, city, start_y ORDER BY start_w)) AS value
FROM 
    filled_values
RIGHT JOIN
    weekly_intervals
ON
    filled_values.start_w = weekly_intervals.start_w
ORDER BY
    item, city, start_y, start_w

期望输出 #

ITEM    CITY        START_Y     START_W     FIRST_USE_Y     FIRST_USE_W     VALUE
A       NEW YORK    2023        30                2023              30      0
A       NEW YORK    2023        30                2023              31      0
A       NEW YORK    2023        30                2023              32      15000
A       NEW YORK    2023        30                2023              33      0
A       NEW YORK    2023        30                2023              34      0
A       LONDON      2024        2                 2024              2       12000
A       LONDON      2024        2                 2024              3       0
A       LONDON      2024        2                 2024              4       0
A       LONDON      2024        2                 2024              5       50000
A       LONDON      2024        2                 2024              6       0
B       NEW YORK    2023        49                2023              49      0
B       NEW YORK    2023        49                2023              50      0
B       NEW YORK    2023        49                2023              51      0
B       NEW YORK    2023        49                2023              52      0
B       NEW YORK    2023        49                2024              1       19540
B       MADRID      2023        10                2023              10      0
B       MADRID      2023        10                2023              11      15444
B       MADRID      2023        10                2023              12      0
B       MADRID      2023        10                2023              13      0
B       MADRID      2023        10                2023              14      0

以下是适配需求的SQL代码，兼容PostgreSQL、MySQL 8.0+等主流数据库：

WITH RECURSIVE group_base AS (
    -- 提取每个ITEM+CITY组的起始年周，作为生成周度数据的基准
    SELECT 
        item,
        city,
        start_y,
        start_w,
        start_y AS base_y,
        start_w AS base_w,
        1 AS seq
    FROM citywise_values
    GROUP BY item, city, start_y, start_w
    UNION ALL
    -- 递归生成后续4个周的数据，自动处理跨年周边界
    SELECT 
        gb.item,
        gb.city,
        gb.start_y,
        gb.start_w,
        CASE 
            WHEN gb.base_w = 52 THEN gb.base_y + 1
            ELSE gb.base_y
        END,
        CASE 
            WHEN gb.base_w = 52 THEN 1
            ELSE gb.base_w + 1
        END,
        gb.seq + 1
    FROM group_base gb
    WHERE gb.seq < 5 -- 限制最多生成5个周度数据点
),
original_data AS (
    -- 提取原始数据中用于匹配的关键字段
    SELECT 
        item,
        city,
        start_y,
        start_w,
        first_use_y,
        first_use_w,
        value
    FROM citywise_values
)
-- 关联递归生成的周度数据与原始数据，填充缺失值为0
SELECT 
    gb.item,
    gb.city,
    gb.start_y,
    gb.start_w,
    gb.base_y AS first_use_y,
    gb.base_w AS first_use_w,
    COALESCE(od.value, 0) AS value
FROM group_base gb
LEFT JOIN original_data od 
    ON gb.item = od.item
    AND gb.city = od.city
    AND gb.base_y = od.first_use_y
    AND gb.base_w = od.first_use_w
ORDER BY gb.item, gb.city, gb.start_y, gb.start_w, gb.seq;

关键逻辑说明 #

1. group_base递归CTE：

   • 先获取每个ITEM+CITY组的起始年周，作为生成周序列的基准
   • 通过递归生成后续4个周的数据，自动处理跨年场景（如2023年第52周后直接跳转到2024年第1周）
   • 用seq字段严格控制生成的周数不超过5个

2. original_dataCTE：

   • 简化原始数据结构，只保留后续关联需要的字段，提升查询效率

3. 最终关联查询：

   • 将递归生成的周度数据与原始数据按ITEM+CITY+年周关联
   • 用COALESCE函数将未匹配到原始数据的VALUE填充为0
   • 按需求排序输出结果

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