sql - 如何在值更改后创建新的顺序标志（在 SQL 中）

Question

我正在尝试在更改部分时创建一个顺序数字标志。

当学生加入某个部分时，该标志应为 1，并继续为 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')

期望的结果

学生卡	记录日期	部分	旗帜
123	2020-02-06	空值	空值
123	2020-05-14	一种	1
123	2020-08-12	一种	1
123	2020-09-01	乙	2
123	2020-09-15	一种	3
123	2020-09-29	一种	3
123	2020-11-02	空值	空值
123	2020-11-30	空值	空值
789	2020-01-12	空值	空值
789	2020-04-12	一种	1
789	2020-05-03	空值	空值
789	2020-06-13	一种	2
789	2020-06-30	乙	3
789	2020-07-01	乙	3
789	2020-07-22	一种	4

试图：

select
 student_id
,record_date
,section
,case when section is not null then row_number() over(partition by student_id, section order by record_date asc)
 end row#
,case when (section is not null) and (lag(section, 1) over(partition by student_id order by record_date asc) is null) then 'start'
      when (lag(section, 1) over(partition by student_id order by record_date asc) is not null) and (section != lag(section, 1) over(partition by student_id order by record_date asc)) then 'change'
 end chk_txt
,case when section is not null then (case when (section is not null) and (lag(section, 1) over(partition by student_id order by record_date asc) is null) then 1
                                          when (lag(section, 1) over(partition by student_id order by record_date asc) is not null) and (section != lag(section, 1) over(partition by student_id order by record_date asc)) then 1
                                          else 0
                                     end)
 end chk_val2
from cluster_test

order by 1, 2

标签： sqlsql-servertsqlgaps-and-islands

Answer 1

这是差距和孤岛问题。您可以使用如下分析函数：

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<>小提琴