Redshift SQL需求：生成同ID直至grade=pk的递进记录

需求说明 #

为数据集中的每个ID生成递进记录，直到grade字段变为pk，各字段遵循以下递进规则：

• grade_id从当前值递减至1
• year随grade_id递减逐年递减（每降1个grade_id，年份减1）
• Diff随grade_id递减逐年递减（每降1个grade_id，Diff减1）
• grade与grade_id的对应关系为：7→5、6→4、5→3、4→2、3→1、2→k、1→pk

原始数据集 #

期望输出示例（以ID=101为例） #

Redshift SQL代码 #

WITH grade_mapping AS (
    -- 定义grade_id与grade的固定映射关系
    SELECT 
        gs.grade_id,
        CASE gs.grade_id
            WHEN 7 THEN '5'
            WHEN 6 THEN '4'
            WHEN 5 THEN '3'
            WHEN 4 THEN '2'
            WHEN 3 THEN '1'
            WHEN 2 THEN 'k'
            WHEN 1 THEN 'pk'
        END AS grade
    FROM generate_series(1,7) gs(grade_id)
),
original_data AS (
    -- 替换为你的实际表名
    SELECT 
        id, grade, grade_id, year, Diff
    FROM your_table_name
)
SELECT 
    od.id,
    gm.grade,
    gm.grade_id,
    od.year - (od.grade_id - gm.grade_id) AS year,
    od.Diff - (od.grade_id - gm.grade_id) AS Diff
FROM original_data od
-- 关联映射表，生成从当前grade_id到1的所有递进记录
JOIN grade_mapping gm 
    ON gm.grade_id <= od.grade_id
ORDER BY od.id, gm.grade_id DESC;

注意事项 #

1. 将代码中的your_table_name替换为你实际使用的表名称
2. 如果grade_id的取值范围超出1-7，需同步调整generate_series(1,7)的参数，并更新CASE语句中的映射规则
3. 对于已经是pk（grade_id=1）的ID，只会保留原始记录，不会生成额外行

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