SQL Server 2012+高效实现State为0时Expected列递增的方法

嘿，这个问题我之前也碰到过！你的需求是仅通过Id和State列生成Expected列——按Id排序时，每次遇到State=0就让Expected值递增，示例数据如下：

你当前实现的SQL在大数据集下效率极差，代码如下：

WITH Groups AS (
    SELECT Id, ROW_NUMBER() OVER (ORDER BY Id) AS GroupId
    FROM tblState WHERE State=0
)
SELECT S.Id, S.[State], S.Expected, G.GroupId
FROM tblState S
OUTER APPLY (SELECT TOP 1 GroupId FROM Groups WHERE Groups.Id <= S.Id ORDER BY Id DESC) G

更简洁高效的实现方式 #

你可以用SQL Server 2012及以上版本支持的累计窗口函数来解决，这个写法只需要对表做一次扫描，完全避免了原写法中大量的嵌套查找操作，性能提升非常明显：

SELECT 
    Id,
    State,
    SUM(CASE WHEN State = 0 THEN 1 ELSE 0 END) OVER (ORDER BY Id) AS Expected
FROM tblState
ORDER BY Id;

原理说明

这个写法的核心逻辑是：

• 对每一行，判断State是否为0，是则标记为1，否则为0
• 通过SUM() OVER (ORDER BY Id)对这些标记值做累计求和（默认就是从第一行到当前行的范围）
• 每次遇到State=0时，累计值就加1，正好对应你需要的Expected分组序号

如果你喜欢更简洁的写法，还可以用IIF函数或者隐式类型转换：

-- 用IIF简化
SELECT 
    Id,
    State,
    SUM(IIF(State = 0, 1, 0)) OVER (ORDER BY Id) AS Expected
FROM tblState
ORDER BY Id;

-- 用布尔值转INT的隐式转换
SELECT 
    Id,
    State,
    SUM(CAST(State = 0 AS INT)) OVER (ORDER BY Id) AS Expected
FROM tblState
ORDER BY Id;

为什么这个写法更高效 #

原写法中的OUTER APPLY + TOP 1会对每一行都执行一次子查询，去查找最近的State=0的行，在大数据集下会触发大量的嵌套循环查找，IO和CPU消耗都会飙升。而窗口函数的写法只需要扫描表一次，所有计算都在内存中完成，执行计划更高效，在百万级以上的数据量下，执行时间会差几个数量级。

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