如何基于条件SQL从新列中的列中获取最后相关的值

Question

我正试图根据新列中的某些条件获得最后一个特定值。

样本数据：

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

我在以下条件下编写sql查询时遇到了问题。

已尝试查询，直到达到上述条件

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

以下基于条件的结果：

如果特定uid的omd列值从bb to any other text更改，则获取current omd行的最后一个bb日期但如果uid changed和row doesn't start与omd = bb在一起，则new date列中的add null为同一行日期

在这里的第4行，omd value is changed to tc，然后在新的日期中，最后一个bb日期应该到来，并且应该是omd = bb comes again的所有行。

预期数据：

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

Answer 1

SELECT 
t1.UID,
t1.GID,
t1.OMD,
LAG(t1.OMD, 1) OVER (PARTITION BY t1.UID ORDER BY t1.Date) AS Prev_OMD,
t1.Date,
CASE 
WHEN t1.OMD = 'bb' THEN t1.Date
WHEN LAG(t1.OMD, 1) OVER (PARTITION BY t1.UID ORDER BY t1.Date) = 'bb' THEN 
MAX(CASE WHEN t2.OMD = 'bb' THEN t2.Date END) OVER (PARTITION BY t1.UID, grp ORDER BY t1.Date)
ELSE NULL
END AS New_Date
FROM 
table1 t1
LEFT JOIN (
SELECT 
UID,
GID,
OMD,
Date,
SUM(CASE WHEN OMD = 'bb' THEN 1 ELSE 0 END) OVER (PARTITION BY UID ORDER BY Date) AS grp
FROM 
table1
) t2 ON t1.UID = t2.UID AND t1.GID = t2.GID AND t1.Date >= t2.Date