按ID分组统计首次出现NO前的YES记录行数

需求：按ID分组，统计每个ID下按month降序排列后，首次出现flag为"NO"之前的所有"YES"记录数量。

原始数据表 #

期望结果 #

SQL实现方案 #

以下两种写法适用于大多数支持窗口函数的数据库（MySQL 8+、PostgreSQL、SQL Server等）：

方法一：标记首次NO的位置

WITH ranked_data AS (
    SELECT 
        ID,
        flag,
        -- 按ID分组，month降序编行号
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY month DESC) AS rn,
        -- 定位每个ID中第一个NO对应的行号
        MIN(CASE WHEN flag = 'NO' THEN ROW_NUMBER() OVER (PARTITION BY ID ORDER BY month DESC) END) OVER (PARTITION BY ID) AS first_no_rn
    FROM your_table_name
)
SELECT 
    ID,
    COUNT(CASE WHEN flag = 'YES' AND rn < first_no_rn THEN 1 END) AS count
FROM ranked_data
GROUP BY ID;

方法二：累积标记是否遇到NO（更简洁）

WITH ordered_data AS (
    SELECT 
        ID,
        flag,
        -- 按ID分组、month降序，累积统计遇到的NO数量，未遇到时为0
        SUM(CASE WHEN flag = 'NO' THEN 1 ELSE 0 END) OVER (PARTITION BY ID ORDER BY month DESC) AS no_encountered
    FROM your_table_name
)
SELECT 
    ID,
    COUNT(CASE WHEN flag = 'YES' AND no_encountered = 0 THEN 1 END) AS count
FROM ordered_data
GROUP BY ID;

思路说明 #

• 方法二逻辑更直观：按month降序排列后，用累积求和标记是否已碰到NO。no_encountered为0时，说明还没到第一个NO的位置，此时统计YES的数量就是目标结果。
• 方法一通过行号定位第一个NO的位置，再统计该行号之前的YES记录，逻辑清晰易懂。

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