考虑日期序列的SQL组数据

Question

我正在尝试对可能被不同组打断的单独行进行分组。如果它们坏了，我希望它们单独分组。

所以我有：

Col1 | Col2
---------------------
| Y  |01/JAN/2012
| Y  |01/FEB/2012
| N  |01/MAR/2012
| Y  |01/APR/2012
| Y  |01/MAY/2012

我想得到结果：

|col1|col2       |GRP
---------------------
| Y  |01/JAN/2012|1
| Y  |01/FEB/2012|1
| N  |01/MAR/2012|2
| Y  |01/APR/2012|3
| Y  |01/MAY/2012|3

我怎样才能做到这一点？

我目前的尝试是：

select
Col1,
Col2,
dense_rank() over (partition by Col1 order by Col2 asc) as grp  
from
myTABLE
;

但它将所有的"Y"组合在一起，并给我一个序列号，如下所示：

|col1|col2       |GRP
---------------------
| Y  |01/JAN/2012|1
| Y  |01/FEB/2012|2
| N  |01/MAR/2012|1
| Y  |01/APR/2012|3
| Y  |01/MAY/2012|4

Answer 1

这是一种间隙和孤岛问题。我建议使用行号的差异来识别"孤岛"，然后使用row_number():

select t.*, dense_rank() over (order by grp) as grp
from (select t.*,
min(col2) over (partition by col1, seqnum - seqnum_2) as grp
from (select t.*,
row_number() over (order by col2) as seqnum,
row_number() over (partition by col1 order by col2) as seqnum_2
from t
) t
) t
order by col2;

实际上，一种更简单的方法是使用lag()和累积和：

select t.*,
sum(case when col1 = prev_col1 then 0 else 1 end) over (order by col2) as grp
from (select t.*, lag(col1) over (partition by col2) as prev_col1
from t
) t