SQL实现行间值分配：按规则生成new_value列

表结构与测试数据 #

CREATE TABLE table_one( person varchar(55), date_value date, proj varchar(2), value int, time varchar(2) ); 

INSERT INTO table_one VALUES 
('A1','2020-10-01','W',10,'T1'),
('A1','2020-10-01','A',5,'T2'),
('A1','2020-10-01','P',6,'T3'),
('A1','2020-10-01','A',9,'T4'),
('A1','2020-10-01','P',11,'T5'),
('A1','2020-10-01','A',4,'T6'),
('A1','2020-10-01','P',2,'T7'),
('A1','2020-10-01','A',1,'T8'),
('A1','2020-10-01','P',10,'T9'),
('A1','2020-10-01','A',8,'T10');

需求规则 #

• 当某行proj为A且下一行proj为P时，将A行的value赋值给对应P行的new_value；
• 当最后一行proj为A且第一行proj为W时，将最后一行的value赋值给第一行的new_value；
• 当proj为A时，new_value为NULL。

SQL查询语句 #

WITH ranked_data AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY person, date_value ORDER BY time) AS rn,
        COUNT(*) OVER (PARTITION BY person, date_value) AS total_rows,
        LAG(proj) OVER (PARTITION BY person, date_value ORDER BY time) AS prev_proj,
        LAG(value) OVER (PARTITION BY person, date_value ORDER BY time) AS prev_value,
        FIRST_VALUE(proj) OVER (PARTITION BY person, date_value ORDER BY time) AS first_proj,
        LAST_VALUE(value) OVER (PARTITION BY person, date_value ORDER BY time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_a_value
    FROM table_one
)
SELECT 
    person,
    date_value,
    proj,
    value,
    time,
    CASE
        -- 规则3：proj为A时new_value为NULL
        WHEN proj = 'A' THEN NULL
        -- 规则1：P行的前一行是A时，取前一行A的value
        WHEN proj = 'P' AND prev_proj = 'A' THEN prev_value
        -- 规则2：第一行是W且最后一行是A时，给W行赋值最后一行A的value
        WHEN rn = 1 AND first_proj = 'W' AND (SELECT proj FROM ranked_data WHERE rn = total_rows) = 'A' THEN last_a_value
        ELSE NULL
    END AS new_value
FROM ranked_data
ORDER BY rn;

语句说明 #

1. CTE预处理：通过窗口函数给person+date_value分组内的行按time排序编号，同时获取每行的前一行proj、前一行value、分组首行proj、分组末行A的value，为后续规则判断提供数据支撑。
2. CASE分支逻辑：
   • 直接匹配规则3，proj为A时返回NULL；
   • 针对P行，检查前一行是否为A，符合则取前一行A的value，对应规则1；
   • 针对分组首行，若首行是W且末行是A，则将末行A的value赋值给它，对应规则2；
   • 其余情况返回NULL。

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