SQL需求：统计每日首个False出现前的连续True行数

数据表结构与数据 #

需求 #

按日期分组，每组数据按hour降序排列，统计每组中首个False出现之前连续的True的行数。备注：hour降序排列可能存在间隔，仍按行数统计。

预期结果 #

SQL解决方案 #

思路 #

核心是按日期分组后，先对每组按hour降序排序，标记出首个False之前的所有True行，最后统计数量。可以通过窗口函数的累积特性或行号定位实现。

方法一：行号定位法 #

WITH ranked_data AS (
    SELECT 
        date,
        flag,
        -- 按日期分组，hour降序生成行号
        ROW_NUMBER() OVER (PARTITION BY date ORDER BY hour DESC) AS rn,
        -- 找到每组中首个False的行号
        MIN(CASE WHEN flag = FALSE THEN ROW_NUMBER() OVER (PARTITION BY date ORDER BY hour DESC) END) OVER (PARTITION BY date) AS first_false_rn
    FROM your_table_name
)
SELECT 
    date,
    -- 统计行号小于首个False位置的True行数，无符合条件则为0
    COUNT(CASE WHEN rn < first_false_rn AND flag = TRUE THEN 1 END) AS count
FROM ranked_data
GROUP BY date
ORDER BY date DESC;

方法二：累积标记法（更简洁） #

WITH ordered_data AS (
    SELECT 
        date,
        flag,
        -- 累积求和，遇到False后标记为1，未遇到时为0
        SUM(CASE WHEN flag = FALSE THEN 1 ELSE 0 END) OVER (PARTITION BY date ORDER BY hour DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS has_encountered_false
    FROM your_table_name
)
SELECT 
    date,
    -- 统计未遇到False前的True行数
    COUNT(CASE WHEN has_encountered_false = 0 AND flag = TRUE THEN 1 END) AS count
FROM ordered_data
GROUP BY date
ORDER BY date DESC;

解释 #

• 方法一：先给每组数据按hour降序生成行号，定位出首个False的位置，再统计该位置之前的True行数。如果首行就是False，自然没有符合条件的行，count为0。
• 方法二：用累积求和标记是否已遇到False，首个False之前的行标记为0，之后为1，直接统计标记为0的True行数即可。

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