如何在SQL Server中实现Conteo列的条件重置计数

问题描述 #

需要计算Conteo列，规则为：

• 当Gestion = 1时，Conteo取值为0
• 当Gestion IS NULL时，计数从1开始递增，直到再次遇到Gestion = 1时重置计数

现有原始数据：

核心思路 #

要实现重置计数，关键是为每个客户的行划分重置区间：每次遇到Gestion=1时，开启一个新的区间。通过累计统计每个客户的Gestion=1出现次数，即可得到区间分组ID，再在每个区间内对null行进行递增计数。

实现代码 #

SELECT
    Fecha,
    Cliente,
    Gestion,
    CASE
        WHEN Gestion = 1 THEN 0
        ELSE ROW_NUMBER() OVER (
            PARTITION BY Cliente, GroupId
            ORDER BY Fecha
        )
    END AS Conteo
FROM (
    SELECT
        *,
        -- 累计统计每个客户的Gestion=1次数，作为区间分组ID
        SUM(CASE WHEN Gestion = 1 THEN 1 ELSE 0 END) OVER (
            PARTITION BY Cliente
            ORDER BY Fecha
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS GroupId
    FROM Example
) t
ORDER BY Fecha, Cliente;

代码解释 #

1. 生成区间分组ID：
   使用SUM() OVER()窗口函数，按客户分组、日期排序，累计计算当前行及之前所有行中Gestion=1的次数。每次遇到Gestion=1，GroupId会加1，后续的null行都会属于同一个分组，直到下一个Gestion=1出现。

2. 计算Conteo列：

   • 当Gestion=1时直接返回0
   • 对null行，按客户和分组ID分区，用ROW_NUMBER()生成从1开始的递增计数，实现区间内的连续计数。

测试验证 #

执行上述查询后，将得到期望结果：

测试数据 #

CREATE TABLE Example
(
    Fecha date NOT NULL,
    Cliente varchar(5) NOT NULL,
    Gestion int NULL,
    Conteo int NULL
);

INSERT INTO Example (Fecha, Cliente, Gestion, Conteo)
VALUES ('2023-05-01', 'X1', 1, 0),
       ('2023-05-01', 'X2', 1, 0),
       ('2023-05-02', 'X1', null, 1),
       ('2023-05-02', 'X2', null, 1),
       ('2023-05-03', 'X1', null, 2),
       ('2023-05-03', 'X2', 1, 0),
       ('2023-05-04', 'X1', 1, 0),
       ('2023-05-04', 'X2', null, 1);

内容的提问来源于stack exchange，提问作者Andrés Rengifo