如何从历史表中获得间隔，以便随时间重复组

Question

我有一个历史表，数据如下:

SK ID STATUS EFF_DT EXP_DT 
1 486007909 APP 7/22/2009  8/22/2009
2 486007909 APP 8/22/2009  10/01/2009
3 486007909 CAN 10/01/2009 11/01/2009
4 486007909 CAN 11/02/2009 12/12/2009
5 486007909 APP 12/12/2009 NULL

EXP_DT is null表示行是活动的

我想在每次Status更改时返回一组数据

预期结果如下:

SK ID STATUS EFF_DT EXP_DT                 GAP
1 486007909 APP 7/22/2009  8/22/2009        1
2 486007909 APP 8/22/2009  10/01/2009       1
3 486007909 CAN 10/01/2009 11/01/2009       2
4 486007909 CAN 11/02/2009 12/12/2009       2
5 486007909 APP 12/12/2009 NULL             3

感谢您的帮助!

Answer 1

这是一个典型的缺口和孤岛问题。

我们可以用LAG来检查差异，然后用COUNT窗口来得到GAP的数字。

您可能想要添加一个分区子句，例如PARTITION BY ID

SELECT *,
GAP = COUNT(IsDiff) OVER (ORDER BY EFF_DT ROWS UNBOUNDED PRECEDING)
FROM (
SELECT *,
IsDiff = CASE WHEN LAG(STATUS, 1, '') OVER (ORDER BY EFF_DT) <> STATUS THEN 1 END
FROM YourTable t
) t