sql - 使用 SQL 在重复的 0 和 1 系列中识别和分类中断

Question

我有兴趣根据 CID 列将 PID 分成有序的块。
请注意，OID 用于对每个 PID 子组进行排序
GRP 应该是连续的，如果第一个 CID 以 0 开头，则应该从 0 开始，而如果 GRP 以 1 开头，则 GRP 应该从 1 开始

PID         OID         CID         GRP
----------- ----------- ----------- -----------
1           1           0           NULL
1           2           0           NULL
1           3           1           NULL
1           4           1           NULL
1           5           1           NULL
1           6           0           NULL
1           7           0           NULL
2           1           0           NULL
2           2           0           NULL
2           3           1           NULL
2           4           1           NULL
2           5           0           NULL
2           6           1           NULL
2           7           1           NULL
2           8           0           NULL
3           1           1           NULL
3           2           1           NULL
3           3           1           NULL
3           4           1           NULL
3           5           0           NULL
3           6           1           NULL
3           7           1           NULL
3           8           0           NULL
3           9           0           NULL

下面给出了上表的解决方案，我想使用 T-SQL 解决以下问题，但是，我什至不确定这种任务是否可行。我基本上是在尝试识别列 CID 中的重复中断，一旦确定中断，我想增加 GRP 指标

PID         OID         CID         GRP
----------- ----------- ----------- -----------
1           1           0           0
1           2           0           0
1           3           1           1
1           4           1           1
1           5           1           1
1           6           0           2
1           7           0           2
2           1           0           0
2           2           0           0
2           3           1           1
2           4           1           1
2           5           0           2
2           6           1           3
2           7           1           3
2           8           0           4
3           1           1           1
3           2           1           1
3           3           1           1
3           4           1           1
3           5           0           2
3           6           1           3
3           7           1           3
3           8           0           4
3           9           0           4

以下是创建上表的示例代码:

CREATE TABLE ##SOLUTION (PID INT, OID INT,  CID INT, GRP INT)
insert into ##SOLUTION values 
(1,1,0, 0), 
(1,2,0, 0), 
(1,3,1, 1), 
(1,4,1, 1), 
(1,5,1, 1), 
(1,6,0, 2), 
(1,7,0, 2), 

(2,1,0, 0), 
(2,2,0, 0), 
(2,3,1, 1), 
(2,4,1, 1), 
(2,5,0, 2), 
(2,6,1, 3), 
(2,7,1, 3), 
(2,8,0, 4), 

(3,1,1, 1), 
(3,2,1, 1), 
(3,3,1, 1), 
(3,4,1, 1), 
(3,5,0, 2), 
(3,6,1, 3), 
(3,7,1, 3), 
(3,8,0, 4), 
(3,9,0, 4)

先感谢您

Answer 1

您可以使用 lag()获取先前的值(在每个 pid 内)。然后有条件的累积总和创建您想要的分组:

select s.*,
       sum(case when prev_cid = cid then 0 else 1 end) over (partition by pid order by oid) as grp
from (select s.*,
             lag(cid) over (partition by pid order by oid) as prev_cid
      from solutions s
     ) s

Here是一个db<> fiddle 。
我应该补充一点，SQL Server 使使用可更新的 CTE 更新这些值变得非常容易:

with toupdate as (
      select s.*,
             sum(case when prev_cid = cid then 0 else 1 end) over (partition by pid order by oid) as new_grp
      from (select s.*,
                   lag(cid) over (partition by pid order by oid) as prev_cid
            from solution s
           ) s
     )
update toupdate
     set grp = new_grp;