如何在SQL中按条件从列提取最新关联值生成新列

需求描述 #

需要在SQL中根据特定条件，在新列中获取指定的最新值。

样本数据 #

UID GID OMD  Prev_OMD Date
1    g1  bb   null    Jan 1
1    g2  bb   bb      jan 2
1    g3  bb   bb      jan 2
1    g4  tc   bb      jan 5
1    g5  of   tc      jan 10
1    g6  bb   of      jan 10
2    g1  tc   null    jan 11
2    g2  bb   tc      jan 14

当前已编写的查询语句 #

select uid, gid, omd, lag(omd,1) over (partition by uid order by date) as prev_omd , date
from table1

具体规则条件 #

• 若特定UID的omd列值从bb变为其他值，则当前行的新列需填入最后一次出现bb的日期，且该值保持至再次出现omd=bb的行。
• 若UID变更后的首行omd值不为bb，则新列填null；若为bb则填该行的日期。

期望结果 #

UID GID OMD  Prev_OMD Date    New Date
1    g1  bb   null    Jan 1   Jan 1
1    g2  bb   bb      Jan 2   Jan 2
1    g3  bb   bb      Jan 2   Jan 2
1    g4  tc   bb      Jan 5   Jan 2
1    g5  of   tc      Jan 10  Jan 2
1    g6  bb   of      Jan 10  Jan 10
2    g1  tc   null    Jan 11  Null
2    g2  bb   tc      Jan 14  Jan 14

解决方案 #

可以通过窗口函数标记bb的连续区间，再结合聚合窗口函数获取目标日期：

WITH grouped_data AS (
    SELECT 
        uid, gid, omd, prev_omd, date,
        -- 按UID分组、日期排序，每遇到bb就累加分组编号
        SUM(CASE WHEN omd = 'bb' THEN 1 ELSE 0 END) OVER (PARTITION BY uid ORDER BY date) AS bb_group
    FROM (
        -- 保留原查询的prev_omd计算逻辑
        SELECT 
            uid, gid, omd, 
            lag(omd,1) over (partition by uid order by date) as prev_omd, 
            date
        FROM table1
    ) t
)
SELECT 
    uid, gid, omd, prev_omd, date,
    -- 每个分组内取最后一次出现bb的日期，无bb则返回null
    MAX(CASE WHEN omd = 'bb' THEN date END) OVER (PARTITION BY uid, bb_group) AS "New Date"
FROM grouped_data
ORDER BY uid, date;

逻辑说明 #

1. 先执行原查询算出prev_omd，保留所有基础数据。
2. 给每个UID下的行按日期排序，每碰到omd=bb就给分组编号加1——这样两次bb之间的非bb行会归到同一个分组，对应上一次的bb区间。
3. 在每个UID的分组里，提取该组内最后一次出现bb的日期作为New Date；如果分组里没有bb（比如UID首行非bb），就返回null。

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