SQL 遇非零重置的零值递增计数器查询优化咨询

你当前的实现存在性能问题的核心原因是关联子查询的使用：对每一条n=0的记录，都需要遍历全表查找早于当前日期的最大非0值日期，时间复杂度为O(n²)，数据量较大时计算开销会呈指数级上升。

我们可以通过两次窗口函数实现O(n)时间复杂度的计算，仅需对表进行一次扫描，无需自关联操作，性能提升非常明显。优化后的SQL如下：

DECLARE @test TABLE
( 
    d DATE, 
    n INT
)

INSERT INTO @test
VALUES
    ('2021-01-01', 0),
    ('2021-01-02', 0),
    ('2021-01-03', 0),
    ('2021-01-04', 5),
    ('2021-01-05', 0),
    ('2021-01-06', 0),
    ('2021-01-07', 10),
    ('2021-01-08', 10),
    ('2021-01-09', 0),
    ('2021-01-10', 0),
    ('2021-01-11', 9),
    ('2021-01-12', 0),
    ('2021-01-13', 0)

WITH group_cte AS (
    SELECT 
        d,
        n,
        -- 每遇到一个非0的n，分组编号+1，相同分组内的记录会共享同一个编号
        SUM(CASE WHEN n <> 0 THEN 1 ELSE 0 END) OVER(ORDER BY d ROWS UNBOUNDED PRECEDING) AS group_id
    FROM @test
)
SELECT 
    d,
    n,
    -- 每个分组内按日期排序生成行号，即为需求的计数器
    ROW_NUMBER() OVER(PARTITION BY group_id ORDER BY d ASC) AS counter
FROM group_cte
ORDER BY d

• 如果你的业务表数据量较大，建议给日期字段d建立有序索引，窗口函数可以直接利用索引的排序特性，避免额外的排序开销，性能会进一步提升。
• 该逻辑兼容所有支持标准窗口函数的数据库（SQL Server 2012+、MySQL 8.0+、PostgreSQL、Oracle等）。

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