查询客户连续3天TOTAL为0的最早CALENDAR日期及SQL问题排查

先明确你的需求和遇到的问题：

我的数据包含CALENDAR（日期格式如20170801）、CLIENTID、TOTAL三个字段，示例数据如下：
20170801 1700 2；20170801 1800 2；20170801 1900 2；20170801 1990 2；20170801 2000 0；20170801 2090 0；20170802 2090 0；20170803 2090 0
我需要找出连续3天TOTAL为0的客户，对应的最小CALENDAR日期（比如示例中客户2090的结果应该是20170801）。但我自己写的SQL里，a和b的计数逻辑不对，代码如下：

WITH cte AS ( 
  SELECT *,COUNT(1) OVER(PARTITION BY clientid) b 
  FROM ( 
    SELECT tt.* ,(SELECT COUNT(CALENDAR) FROM STATS WHERE total = 0 ) AS a 
    FROM STATS tt WHERE total = 0 
  ) t1 
) 
SELECT * FROM cte WHERE b >= 3

原SQL的问题分析 #

• 子查询里的a是统计了全表所有TOTAL为0的记录数，不是按客户分组计算的，所以每个客户的a值都一样，完全不符合你的需求
• 窗口函数的b是统计每个客户所有TOTAL为0的总记录数，但这只能判断客户有至少3天0值，没法区分是不是连续的3天——比如如果客户有3天0但日期不连续，这个逻辑也会把它筛出来，这显然不是你要的结果

正确的SQL实现方案 #

核心思路是通过日期分组标记来识别连续的日期，具体代码如下：

WITH zero_records AS (
    -- 第一步：筛选所有TOTAL为0的记录，转日期格式并按客户+日期排序
    SELECT 
        CALENDAR,
        CLIENTID,
        STR_TO_DATE(CALENDAR, '%Y%m%d') AS date_val,
        -- 给每个客户的0值记录按日期生成递增序号
        ROW_NUMBER() OVER(PARTITION BY CLIENTID ORDER BY STR_TO_DATE(CALENDAR, '%Y%m%d')) AS rn
    FROM STATS
    WHERE TOTAL = 0
),
continuous_groups AS (
    -- 第二步：生成分组键，连续的日期会被分到同一组
    SELECT 
        CALENDAR,
        CLIENTID,
        date_val,
        -- 日期减去对应的序号，连续日期的这个结果会相同
        DATE_SUB(date_val, INTERVAL rn DAY) AS group_key
    FROM zero_records
)
-- 第三步：筛选出每组记录数>=3的客户，取组内最小日期
SELECT 
    CLIENTID,
    MIN(CALENDAR) AS earliest_continuous_zero_date
FROM continuous_groups
GROUP BY CLIENTID, group_key
HAVING COUNT(*) >= 3;

代码逻辑解释 #

1. zero_records：先把所有TOTAL为0的记录筛出来，同时把字符串格式的日期转成可计算的日期类型，再给每个客户的记录按日期排号，方便后续判断连续性
2. continuous_groups：用DATE_SUB(date_val, INTERVAL rn DAY)生成分组键——比如客户2090的三条记录日期是2017-08-01、2017-08-02、2017-08-03，对应的序号是1、2、3，日期减序号后结果都是2017-07-31，这样就把连续的三天分到了同一个组里
3. 最后按客户和分组键分组，筛选出组内记录数>=3的（也就是连续3天及以上的），取组内最小的CALENDAR就是你要的最早连续0值日期

针对你的示例数据，运行这段SQL后，客户2090的结果就是20170801，完全符合需求。

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