如何在Snowflake中用SQL按id和周将单行拆分为多行

我有一张包含id、week、value字段的表，需要按以下规则生成多行数据：

• 每个id的每条原始记录需生成连续递增周数的行，默认总计5行；
• 若同一id下当前记录之后已有存在的周数记录，则当前记录的生成需截止到后续记录周数的前一周，不再生成满5行。

示例 #

输入表 #

id  week   value
1  2022-W1 200
2  2022-W3 500
2  2022-W6 600

输出表 #

id  week   value
1  2022-W1 200
1  2022-W2 200
1  2022-W3 200
1  2022-W4 200
1  2022-W5 200
2  2022-W3 500
2  2022-W4 500
2  2022-W5 500
2  2022-W6 600
2  2022-W7 600
2  2022-W8 600
2  2022-W9 600
2  2022-W10 600

规则说明 #

• id=1无后续周记录，从2022-W1开始生成5行连续周数据；
• id=2的2022-W3记录，因后续存在2022-W6的记录，仅生成到2022-W5（即后续周的前一周），共3行；2022-W6无后续记录，生成5行连续周数据。

核心思路是先将周格式转为可计算的数值，用窗口函数获取后续周的边界，再生成连续序列并过滤超出范围的行，最后转回原周格式。以下提供两种主流数据库的实现：

PostgreSQL版本 #

WITH processed_data AS (
    SELECT
        id,
        week,
        value,
        -- 将2022-W1转为202201格式，方便数值计算
        CAST(REPLACE(week, 'W', '') AS INT) AS week_num,
        -- 获取同一id下下一条记录的周数，无后续则当前周+4（对应生成5行）
        COALESCE(
            LEAD(CAST(REPLACE(week, 'W', '') AS INT)) OVER (PARTITION BY id ORDER BY week),
            CAST(REPLACE(week, 'W', '') AS INT) + 4
        ) AS next_week_num
    FROM your_table_name
),
generate_series AS (
    -- 生成0-4的偏移量，对应每条原始记录要生成的5行基础序列
    SELECT generate_series(0,4) AS offset
)
SELECT
    pd.id,
    -- 转回YYYY-Wn格式
    CONCAT(
        FLOOR((pd.week_num + gs.offset)/100)::TEXT,
        '-W',
        MOD(pd.week_num + gs.offset, 100)::TEXT
    ) AS week,
    pd.value
FROM processed_data pd
CROSS JOIN generate_series gs
-- 过滤：生成的周数必须小于下一条记录的周数
WHERE (pd.week_num + gs.offset) < pd.next_week_num
ORDER BY pd.id, week;

MySQL版本 #

WITH RECURSIVE generate_series AS (
    -- 递归生成0-4的偏移量
    SELECT 0 AS offset
    UNION ALL
    SELECT offset + 1 FROM generate_series WHERE offset < 4
),
processed_data AS (
    SELECT
        id,
        week,
        value,
        CAST(REPLACE(week, 'W', '') AS UNSIGNED) AS week_num,
        COALESCE(
            LEAD(CAST(REPLACE(week, 'W', '') AS UNSIGNED)) OVER (PARTITION BY id ORDER BY week),
            CAST(REPLACE(week, 'W', '') AS UNSIGNED) + 4
        ) AS next_week_num
    FROM your_table_name
)
SELECT
    pd.id,
    CONCAT(
        FLOOR((pd.week_num + gs.offset)/100),
        '-W',
        MOD(pd.week_num + gs.offset, 100)
    ) AS week,
    pd.value
FROM processed_data pd
CROSS JOIN generate_series gs
WHERE (pd.week_num + gs.offset) < pd.next_week_num
ORDER BY pd.id, week;

• LEAD()窗口函数：精准获取同一id下当前记录的下一条周数，作为生成终止边界；
• 周数格式转换：将字符串格式的周转为数值，解决连续周的计算问题；
• 序列生成：通过generate_series（PostgreSQL）或递归CTE（MySQL）生成基础偏移量，确保每条原始记录最多生成5行；
• 过滤条件：保证生成的周数不会覆盖后续已存在的记录，同时满足无后续记录时生成满5行的需求。

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