求各CustomerType的最长连续零记录SQL查询方案求助

问题场景 #

原始数据表 #

假设你的数据表名为Table1（从你的SQL语句推测存在表名混淆问题），原始数据如下：

查询需求 #

需要统计每个CustomerType对应的最长连续零记录次数（即MaxZeroRecordsStreak）。

预期输出 #

现有SQL的问题 #

你提供的SQL存在两个核心问题：

• 语法错误：子查询中误用AND代替WHERE，同时表名混淆（Table1和Table2未统一）
• 逻辑错误：仅统计了从最早日期到当前日期的所有零记录总数，完全没有区分「连续」的零记录，无法满足需求

正确的SQL实现 #

要计算连续零记录的最长长度，我们可以利用窗口函数给连续的零记录分组，再统计每组长度取最大值。以下是适配MySQL 8.0+、PostgreSQL、SQL Server等主流数据库的实现：

WITH ZeroStreaks AS (
    SELECT 
        CustomerType,
        Records,
        -- 给非零记录分配递增标识，连续零记录会共享同一GroupId
        SUM(CASE WHEN Records != 0 THEN 1 ELSE 0 END) 
            OVER (PARTITION BY CustomerType ORDER BY Date) AS GroupId
    FROM Table1
)
SELECT 
    CustomerType,
    MAX(CASE WHEN Records = 0 THEN StreakLength ELSE 0 END) AS MaxZeroRecordsStreak
FROM (
    SELECT 
        CustomerType,
        Records,
        COUNT(*) AS StreakLength
    FROM ZeroStreaks
    GROUP BY CustomerType, GroupId, Records
) AS StreakCounts
GROUP BY CustomerType
ORDER BY CustomerType ASC;

代码解释 #

1. CTE ZeroStreaks：

   • 按CustomerType分组、Date排序
   • 通过SUM() OVER()窗口函数，遇到非零记录时累加1，让连续的零记录归为同一个GroupId

2. 中间查询StreakCounts：

   • 按CustomerType、GroupId、Records分组，统计每组的记录数（即连续零/非零的长度）

3. 最终查询：

   • 筛选出零记录的分组长度，取每个CustomerType的最大值，得到最长连续零记录次数

如果你的数据库不支持CTE（比如MySQL 5.x），可以转换成嵌套子查询形式：

SELECT 
    CustomerType,
    MAX(CASE WHEN Records = 0 THEN StreakLength ELSE 0 END) AS MaxZeroRecordsStreak
FROM (
    SELECT 
        CustomerType,
        Records,
        COUNT(*) AS StreakLength
    FROM (
        SELECT 
            CustomerType,
            Records,
            SUM(CASE WHEN Records != 0 THEN 1 ELSE 0 END) 
                OVER (PARTITION BY CustomerType ORDER BY Date) AS GroupId
        FROM Table1
    ) AS ZeroStreaks
    GROUP BY CustomerType, GroupId, Records
) AS StreakCounts
GROUP BY CustomerType
ORDER BY CustomerType ASC;

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