SQL Server中SUM窗口函数计算连续分组Ranker值不符预期咨询

测试表结构与预置数据 #

现有如下测试表结构及预置数据：

CREATE TABLE Table1
(
    [Classroom] int,
    [CourseName] varchar(8),
    [Lesson] varchar(9),
    [StartTime] char(4),
    [EndTime] char(4)
);
    
INSERT INTO Table1
    ([Classroom], [CourseName], [Lesson], [StartTime], [EndTime])
VALUES
    (1001, 'Course 1', 'Lesson 1', '0800', '0900'),
    (1001, 'Course 1', 'Lesson 2', '0900', '1000'),
    (1001, 'Course 1', 'Lesson 3', '1000', '1100'),
    (1001, 'Course 1', 'Lesson 6', '1100', '1200'),
    (1001, 'Course 2', 'Lesson 10', '1100', '1200'),
    (1001, 'Course 2', 'Lesson 11', '1200', '1300'),
    (1001, 'Course 1', 'Lesson 4', '1300', '1400'),
    (1001, 'Course 1', 'Lesson 5', '1400', '1500');

待排查查询语句 #

用于实现连续课程分组标识计算的查询语句如下：

WITH A AS 
(
    SELECT 
        ClassRoom,
        CourseName,
        StartTime,
        EndTime,
        PrevCourse = LAG(CourseName, 1, CourseName) OVER (ORDER BY StartTime)
    FROM   
        Table1
), B AS 
(
    SELECT 
        ClassRoom,
        CourseName,
        StartTime, EndTime,
        Ranker = SUM(CASE WHEN CourseName = PrevCourse THEN 0 ELSE 1 END) 
                    OVER (ORDER BY StartTime, CourseName)
    FROM   
        A
)
SELECT B.* 
FROM B;

执行结果与预期偏差 #

语句执行后返回结果如下：

ClassRoom CourseName StartTime EndTime Ranker
---------------------------------------------
1001      Course 1   0800      0900    0
1001      Course 1   0900      1000    0
1001      Course 1   1000      1100    0
1001      Course 1   1100      1200    0
1001      Course 2   1100      1200    1
1001      Course 2   1200      1300    1
1001      Course 1   1300      1400    2
1001      Course 1   1400      1500    2

预期Ranker分组结果应为(0,0,0,0)、(1,1)、(1,1)，实际返回为(0,0,0,0)、(1,1)、(2,2)，核心疑问：

• 为何最后连续两行Course 1对应的Ranker值为2？
• 是否对窗口函数的计算逻辑理解存在疏漏？

你对SUM窗口函数逐行累加的规则理解没出错，问题出在对分组计数逻辑的预期错了。
你写的SUM(CASE WHEN ... END) OVER (ORDER BY ...)，统计的是从结果集第一行到当前行，累计出现了多少次「当前行课程和上一行课程不一致」的切换事件，不是给不同课程名分配固定编号。
逐行算一遍累加过程就能明白：

• 第1行（0800 Course1）：LAG取的默认值就是自身，课程一致，标记0，累计和0 → Ranker=0
• 第2-4行（0900/1000/1100的Course1）：每一行和上一行都是Course1，标记都是0，累计和一直保持0 → Ranker=0
• 第5行（1100 Course2）：上一行是Course1，课程变化，标记1，累计和0+1=1 → Ranker=1
• 第6行（1200 Course2）：上一行是Course2，无变化，标记0，累计和还是1 → Ranker=1
• 第7行（1300 Course1）：上一行是Course2，再次变化，标记1，累计和1+1=2 → Ranker=2
• 第8行（1400 Course1）：上一行是Course1，无变化，标记0，累计和保持2 → Ranker=2

你预期最后一段Course1的Ranker是1，相当于想让相同课程名不管中间隔了多少其他课程，都用同一个编号。但你这套写法本来就是用来计算**连续课程块（也就是SQL常见的孤岛问题）**的：每切换一次课程，不管切回的是不是之前出现过的课程，都算一个新的连续块，编号自然要加1。举个例子，如果1500之后还有一节Course2，那这节课的Ranker会是3，不会跳回1，这才是连续块编号的正确逻辑——编号只对应一段连在一起没中断的同课程段，和课程名本身无关，只看相邻行是否一致。
要是你真的需要相同课程名不管是否连续都用同一个编号，换成DENSE_RANK() OVER (ORDER BY CourseName)就行，但从你一开始的写法来看，你本来要做的就是连续块分组，那现在返回的2才是正确结果，之前的预期是把「连续段编号」和「课程名固定编号」搞混了。

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