SQL Server 2008如何统计boxID变更后的使用次数

SQL Server 2008 支持ROW_NUMBER()排名函数，我们可以用孤岛（Gaps and Islands）算法实现连续相同boxID的分组统计，无需用到LAG函数即可实现需求。

实现逻辑 #

1. 给所有记录按DateTime升序生成全局唯一行号
2. 给每个boxID的内部记录按DateTime升序生成分区行号
3. 连续相同的boxID的两组行号差值固定，我们可以用这个差值作为连续组的唯一标识
4. 统计每个boxID对应不同差值的数量，就是boxID发生变更后的使用次数

完整SQL代码 #

假设你的源表名为box_data，代码如下：

SELECT 
    boxID,
    COUNT(DISTINCT group_id) AS used
FROM (
    SELECT 
        boxID,
        -- 两个行号的差值作为连续相同boxID的组标识
        rn_all - rn_box AS group_id
    FROM (
        SELECT 
            boxID,
            DateTime,
            -- 全局按时间排序的行号
            ROW_NUMBER() OVER(ORDER BY DateTime) AS rn_all,
            -- 每个boxID内部按时间排序的行号
            ROW_NUMBER() OVER(PARTITION BY boxID ORDER BY DateTime) AS rn_box
        FROM box_data
    ) t1
) t2
GROUP BY boxID
ORDER BY boxID

结果验证 #

运行上述代码后返回结果和预期完全一致：

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