我试图在部分更改时创建一个顺序数字标志。
当学生加入某个部分时，标记应为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
是差距和孤岛问题。您可以按如下方式使用分析功能：
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;
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句