PostgreSQL查询：筛选符合条件的最新连续记录

搞定这个连续记录筛选问题其实不难，我来一步步给你拆解方案。先明确你的需求：从给定的加热状态记录表中，筛选出日期≥2018-02-20且HEATING STATE为ON的最新连续记录（也就是ID7、8、9、10这几条）。先看一下你的示例数据：

排除其他记录的原因 #

• ID1：日期早于2018-02-20，不符合日期条件
• ID2：后续紧接着ID3是OFF状态，属于被中断的ON序列，不是最新的连续段
• ID3、4、6：HEATING STATE为OFF，直接排除
• ID5：虽然是ON，但后续ID6是OFF，同样属于被中断的序列，不是目标段

解决方案1：通用窗口函数法（适用于任意连续序列筛选） #

这个方法用PostgreSQL的窗口函数给连续的ON序列分组，再取最新的分组：

WITH filtered_records AS (
    -- 先筛选符合日期和状态的记录，并给连续ON序列标记分组ID
    SELECT 
        id, 
        heating_state, 
        date,
        -- 当前一条记录不是ON时，生成新分组
        SUM(CASE WHEN LAG(heating_state) OVER (ORDER BY id) = 'ON' THEN 0 ELSE 1 END) 
            OVER (ORDER BY id) AS group_id
    FROM heating_records
    WHERE date >= '2018-02-20' AND heating_state = 'ON'
),
latest_group AS (
    -- 获取最新的连续ON序列的分组ID
    SELECT MAX(group_id) AS max_group
    FROM filtered_records
)
-- 取出最新分组下的所有记录
SELECT fr.id, fr.heating_state, fr.date
FROM filtered_records fr
JOIN latest_group lg ON fr.group_id = lg.max_group
ORDER BY fr.id;

逻辑解释：

1. filtered_records：先过滤出符合条件的ON记录，用LAG()函数查看前一条记录的状态。如果前一条不是ON（包括第一条符合条件的记录），则当前记录是新序列的起点，SUM()函数会累加1，这样同一个连续ON序列的记录会有相同的group_id。
2. latest_group：找到最大的group_id，也就是最后出现的连续ON序列。
3. 最后关联两个CTE，取出该分组的所有记录，就是目标结果。

解决方案2：高效反向查找法（针对最新连续段） #

如果你的数据是按ID递增（即时间顺序），可以用更高效的方式：找到最后一条符合日期的OFF记录，取它之后的所有ON记录：

WITH last_off AS (
    -- 找到日期≥2018-02-20的最后一条OFF记录的ID，没有则返回0
    SELECT COALESCE(MAX(id), 0) AS last_off_id
    FROM heating_records
    WHERE date >= '2018-02-20' AND heating_state = 'OFF'
)
SELECT id, heating_state, date
FROM heating_records
WHERE id > (SELECT last_off_id FROM last_off)
  AND heating_state = 'ON'
  AND date >= '2018-02-20'
ORDER BY id;

逻辑解释：

• last_off：找到符合日期条件的最后一条OFF记录的ID，如果所有符合日期的记录都是ON，COALESCE会把ID设为0，确保能取出所有符合条件的ON记录。
• 最后筛选出ID大于该值的ON记录，就是最新的连续ON段。

这两种方法都能得到你要的ID7-10的结果，根据你的数据量和需求选就行~

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