SQL统计前方含2次及以上Threading的Agent行数量

需求说明 #

统计在按Date列排序的情况下，Process为"Agent"的行中，其前方（更早日期）有2条及以上Process为"Threading"的行的数量。
示例结果说明：统计结果为2——第3行的Agent前有2条Threading记录，第7行的Agent前有3条Threading记录，而第9行的Agent前仅有1条Threading记录，不计入统计。

测试数据准备 #

DECLARE @INFO AS TABLE
(
    Process VARCHAR(10) ,
    Content VARCHAR(100) ,
    Date DATETIME
)

INSERT INTO @INFO
(
    Process,
    Content ,
    Date
)
VALUES
('Threading', 'Content A', GETDATE() - 9), ('Threading', 'Content B', GETDATE() - 8), ('Agent', 'Content C', GETDATE() - 7), ('Threading', 'Content D', GETDATE() - 6), ('Threading', 'Content E', GETDATE() - 5), ('Threading', 'Content F', GETDATE() - 4), ('Agent', 'Content G', GETDATE() - 3), ('Threading', 'Content H', GETDATE() - 2), ('Agent', 'Content I', GETDATE() - 1)

实现方案 #

使用窗口函数SUM() OVER()累计统计每行前置的Threading记录数，再筛选符合条件的Agent行并计数：

SELECT COUNT(*) AS QualifiedAgentCount
FROM (
    SELECT 
        Process,
        -- 计算当前行之前（更早日期）的Threading记录总数
        SUM(CASE WHEN Process = 'Threading' THEN 1 ELSE 0 END) OVER(ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS PrecedingThreadingCount
    FROM @INFO
) AS SubQuery
WHERE Process = 'Agent' AND PrecedingThreadingCount >= 2

逻辑说明 #

1. 内层子查询：通过SUM() OVER()窗口函数，按Date升序排序，统计当前行之前所有行中Process为Threading的记录数。ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING明确指定统计范围是从数据集第一行到当前行的前一行。
2. 外层查询：筛选出Process为Agent且前置Threading记录数≥2的行，用COUNT()得到最终符合条件的Agent行数量。

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