如何在值更改后创建新的顺序标志(在SQL中)

Question

我正在尝试在section中创建一个顺序数字标志。

当学生加入一个section时，该标志应该为1，并且在section发生变化之前一直为1。第一次更改的标志应该是2，第二次更改的标志应该是3，以此类推。

由于一个部分可以在更改后重复，我发现创建一个理想的结果是具有挑战性的。

任何帮助都将是非常感激的。

示例数据

create table dbo.cluster_test
(student_id int not null
,record_date date not null
,section varchar(30) null)
insert into cluster_test
(student_id, record_date, section)
values
(123, '2020-02-06', NULL)
,(123, '2020-05-14', 'A')
,(123, '2020-08-12', 'A')
,(123, '2020-09-01', 'B')
,(123, '2020-09-15', 'A')
,(123, '2020-09-29', 'A')
,(123, '2020-11-02', NULL)
,(123, '2020-11-30', NULL)
,(789, '2020-01-12', NULL)
,(789, '2020-04-12', 'A')
,(789, '2020-05-03', NULL)
,(789, '2020-06-13', 'A')
,(789, '2020-06-30', 'B')
,(789, '2020-07-01', 'B')
,(789, '2020-07-22', 'A')

预期的结果

<表类>student_idrecord_date节标记tbody><<tr>1232020-02-06空空1232020-05-1411232020-08-1211232020-09-01B21232020-09-1531232020-09-2931232020-11-02空空1232020-11-30空空7892020-01-12空空7892020-04-1217892020-05-03空空7892020-06-1327892020-06-30B37892020-07-01B37892020-07-224

这是缺口和孤岛问题。解析函数的用法如下:

Select student_id, record_date, section,
Case when section is not null 
Then sum(case when section is not null and (section <> lgs or lgs is null) then 1 end) 
over (partition by student_id order by record_date)
End as flag
From (
Select student_id, record_date, section,
Lag(section) over (partition by student_id order by record_date) as lgs
From cluster_test t
) t
order by student_id, record_date;

,db&lt的在小提琴

您可以访问多个cte并获得如下所示的数据:

with cte_studentSection as (
SELECT student_id, record_Date, section
, lead(section) over(partition by student_id order by record_date) as nextSection
, row_number() over (partition by student_id order by record_date) as rnk
FROM dbo.Cluster_test
where section is not null
), cte_studentSectionFlag as (
SELECT Student_id, record_date, section, rnk, 1 as flag
from cte_studentSection as oc
where record_date = (SELECT MIN(record_Date) from cte_studentSection where student_id = oc.student_id)
union all 
SELECT oc.Student_id, oc.record_date, oc.section,oc.rnk, case when oc.section = cte.section then cte.flag else cte.flag + 1 end
from cte_studentSection as oc
inner join cte_studentSectionFlag as cte on cte.rnk + 1 = oc.rnk and oc.student_id = cte.student_id
)
select student_id, record_date, section, flag
from cte_studentsectionflag 
union all
select student_id, record_date, section, null as flag
from dbo.Cluster_test
where section is null
order by student_id, record_date;

<表类>student_idrecord_date节标记tbody><<tr>1232020-02-06空空1232020-05-1411232020-08-1211232020-09-01B21232020-09-1531232020-09-2931232020-11-02空空1232020-11-30空空7892020-01-12空空7892020-04-1217892020-05-03空空7892020-06-1317892020-06-30B27892020-07-01B27892020-07-223