按客户分组统计大于0数值间的0值记录数

需要从数据源表中提取所有value > 0的记录，并统计每条记录到同一客户（customer_id）下的下一条value>0记录之间，value=0.0000的记录数量。

示例输出 #

数据源表 #

实现代码（SQL） #

使用窗口函数LEAD()定位下一条有效记录，再关联统计中间的0值记录数量：

WITH valid_records AS (
    SELECT 
        value,
        customer_id,
        id,
        -- 获取同客户下的下一条value>0记录的id，无后续则为NULL
        LEAD(id) OVER (PARTITION BY customer_id ORDER BY id) AS next_valid_id
    FROM 
        your_table_name
    WHERE 
        value > 0
),
zero_count_stats AS (
    SELECT 
        v.value,
        v.customer_id,
        -- 统计当前有效记录到下一条有效记录之间的0值记录数
        COUNT(z.id) AS `0.00记录数`
    FROM 
        valid_records v
    LEFT JOIN 
        your_table_name z 
        ON z.customer_id = v.customer_id 
        AND z.id > v.id 
        AND (v.next_valid_id IS NULL OR z.id < v.next_valid_id)
        AND z.value = 0.0000
    GROUP BY 
        v.value, v.customer_id, v.id
)
SELECT 
    value,
    customer_id,
    `0.00记录数`
FROM 
    zero_count_stats
ORDER BY 
    customer_id, id;

逻辑说明 #

1. 筛选有效记录：valid_records CTE先提取所有value>0的记录，并用LEAD()窗口函数获取同一客户下的下一条有效记录ID，用于确定统计范围。
2. 统计0值记录：zero_count_stats CTE将有效记录与同客户、ID在当前有效记录和下一条有效记录之间的0值记录关联，统计数量。
3. 输出结果：最后按客户ID和记录ID排序，得到符合要求的统计结果。

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