SQL中row_number()状态切换时未重置计数的问题咨询

期望实现：同一id下，当present值在1和0之间切换（或反之），以及id变更时，重置计数，对连续相同present值的日期段进行编号。但当前SQL执行后，id=2、日期2024-03-13、present=1的记录，RowNumberPresent为2，不符合预期（期望是1）。

现有SQL代码：

;with RankEmpolyeeAttendance
as (select id,
date,present,
row_number() over (partition by id, present order by date) as RowNumberPresent
from dbo.attendance
)
SELECT r.id,
r.date,
r.present,
r.RowNumberPresent FROM RankEmpolyeeAttendance as r
order by r.id,
r.date

执行结果：

当前SQL使用partition by id, present，会把同一id下所有present=1的记录归为同一个分区，完全忽略这些记录是否在日期上连续。所以id=2的两条present=1记录（2024-03-11和2024-03-13）会被分到同一个分区，row_number()按日期递增计数，导致2024-03-13的记录得到2，而非预期的1。

要实现连续相同present值的分段计数，需要先识别出每个连续段的分组标识，再在分组内进行计数。可以用LAG()函数获取上一条记录的id和present值，判断是否发生变化，生成分组标识，最后基于分组标识执行row_number()。

修改后的SQL：

;with AttendanceWithGroup
as (
    select 
        id,
        date,
        present,
        -- 当id变化、当前present与上一条不同时，生成新的分组标识
        SUM(CASE WHEN prev_present IS NULL OR prev_present != present OR prev_id != id THEN 1 ELSE 0 END) 
            OVER (PARTITION BY id ORDER BY date) as GroupId
    from (
        select 
            id,
            date,
            present,
            LAG(present) OVER (PARTITION BY id ORDER BY date) as prev_present,
            LAG(id) OVER (ORDER BY id, date) as prev_id
        from dbo.attendance
    ) t
),
RankEmpolyeeAttendance
as (
    select 
        id,
        date,
        present,
        row_number() over (partition by id, GroupId order by date) as RowNumberPresent
    from AttendanceWithGroup
)
SELECT 
    id,
    date,
    present,
    RowNumberPresent 
FROM RankEmpolyeeAttendance 
order by id, date

修改后执行结果如下，符合预期：

内容的提问来源于stack exchange，提问作者PeterH