如何用SQL实现遇0重置的客户订单连续计数列？

我有一张包含日期(date)、客户(customer)、**订单数(orders)**的表格，需要生成一列匹配「期望结果(desired result)」的数据，规则如下：

• 订单数为0时，计数置为0
• 连续非0订单数时，按顺序递增计数
• 遇到0后，后续非0订单的计数重新从1开始

我尝试过以下SQL语句，但无法得到预期结果：

row_number() over(partition by customer_id,desired_result order by work_date)

当前结果与期望结果对比如下：

期望结果示例 #

当前错误结果 #

这属于连续序列分组计数场景，核心是先给每个连续非0的订单段分配唯一标识，再在段内计数。以下是兼容MySQL 8+、PostgreSQL、SQL Server等主流数据库的实现：

WITH grouped_data AS (
    SELECT 
        date,
        customer,
        orders,
        -- 生成分组ID：订单数为0时不递增，非0时递增，实现连续非0段的分组
        SUM(CASE WHEN orders = 0 THEN 0 ELSE 1 END) OVER (
            PARTITION BY customer 
            ORDER BY date 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS group_id
    FROM your_table_name
)
SELECT 
    date,
    customer,
    orders,
    -- 订单数为0返回0，否则在客户+分组ID的分区内按日期计数
    CASE 
        WHEN orders = 0 THEN 0 
        ELSE ROW_NUMBER() OVER (PARTITION BY customer, group_id ORDER BY date) 
    END AS desired_result
FROM grouped_data
ORDER BY customer, date;

代码说明 #

1. CTE grouped_data：
   通过SUM() OVER()窗口函数，为每个客户的连续非0订单生成唯一group_id。订单数为0时，CASE返回0，分组ID保持不变；非0时返回1，分组ID递增，这样每个连续非0的订单段会拥有相同的group_id，遇到0后后续非0订单的分组ID会重新开始计数。

2. 最终查询：
   用CASE处理0订单的情况，直接返回0；非0订单则在「客户+分组ID」的分区内，用ROW_NUMBER()按日期生成递增计数，实现遇0重置的效果。

运行上述SQL后，将得到与期望结果完全一致的输出。

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