SQL统计连续零营收行：仅统计上次有营收后的连续零行

需要统计SQL表中每个ID上次产生营收（T/O>0）后的连续零营收行数，每次产生营收时统计重置。

源数据示例 #

ID         |Date       |  T/O | 
442        |2019-12-31 |    0 |
442        |2020-01-01 |200.00|
442        |2020-01-02 |    0 |
442        |2020-02-06 |    0 |
442        |2020-02-07 |    0 |
442        |2020-02-08 |    0 |
442        |2020-02-09 |150.00|
442        |2020-02-10 |    0 |
442        |2020-02-11 |    0 |
442        |2020-02-15 |    0 |
4500       |2020-01-01 |    0 |

预期结果 #

442        | 3 |
4500       | 1 |

解决方案 #

无需依赖LAG()函数，通过窗口函数标记每个零营收行所属的「最近营收周期」，再筛选最新周期统计行数即可。

兼容大部分SQL方言的实现 #

WITH grouped_data AS (
    SELECT 
        ID,
        `T/O`,
        -- 为每行标记最近一次有营收的日期，从未有营收则为NULL
        MAX(CASE WHEN `T/O` > 0 THEN Date END) OVER (
            PARTITION BY ID 
            ORDER BY Date 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS last_revenue_date
    FROM your_table
),
group_counts AS (
    SELECT 
        ID,
        last_revenue_date,
        COUNT(*) AS zero_days,
        -- 为每个ID的分组按时间排序，最新组排第1
        ROW_NUMBER() OVER (
            PARTITION BY ID 
            ORDER BY COALESCE(last_revenue_date, '1900-01-01') DESC
        ) AS rn
    FROM grouped_data
    WHERE `T/O` = 0
    GROUP BY ID, last_revenue_date
)
SELECT ID, zero_days
FROM group_counts
WHERE rn = 1
ORDER BY ID;

逻辑说明 #

1. grouped_data CTE：按ID分区、日期排序，给每个零营收行打上最近一次有营收的日期标签——同一连续零营收周期的行标签相同；从未有营收的ID，标签为NULL。
2. group_counts CTE：按ID和标签分组统计零行数，同时给每个ID的分组按时间排序（最新的组排第1）。
3. 最后筛选出每个ID的第1组，就是我们需要的「上次营收后的连续零营收行数」。

支持QUALIFY的SQL（如BigQuery、Snowflake）简化版 #

WITH grouped_data AS (
    SELECT 
        ID,
        `T/O`,
        MAX(CASE WHEN `T/O` > 0 THEN Date END) OVER (
            PARTITION BY ID 
            ORDER BY Date 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS last_revenue_date
    FROM your_table
)
SELECT 
    ID,
    COUNT(*) AS zero_days
FROM grouped_data
WHERE `T/O` = 0
GROUP BY ID, last_revenue_date
QUALIFY ROW_NUMBER() OVER (PARTITION BY ID ORDER BY COALESCE(last_revenue_date, '1900-01-01') DESC) = 1
ORDER BY ID;

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