SQL提取连续负功率区间最后一条记录的方案咨询

要提取连续负功率（Flag=1）区间的最后一条记录，本质是处理SQL中的**间隙岛（Gap-and-Islands）**问题。核心是先把连续的Flag=1记录归为同一分组，再在每个分组中取最后一条记录。

关键前提 #

必须存在一个能确定记录先后顺序的字段（比如自增ID record_id、时间戳 created_at），因为你提到Signal Value是随机值，无法用来判断连续区间的顺序。

方法一：分组ID + 关联查询 #

假设你的表名为power_data，已有flag列（负功率为1，正功率为0），且有排序字段record_id：

WITH grouped_data AS (
    SELECT 
        signal_value,
        power_value,
        flag,
        record_id,
        -- 生成分组ID：每次遇到Flag=0时，分组编号递增，连续Flag=1会被归为同一组
        SUM(CASE WHEN flag = 0 THEN 1 ELSE 0 END) OVER (ORDER BY record_id) AS group_id
    FROM power_data
),
group_last AS (
    -- 找出每个Flag=1分组的最后一条记录ID
    SELECT group_id, MAX(record_id) AS last_record_id
    FROM grouped_data
    WHERE flag = 1
    GROUP BY group_id
)
-- 关联获取目标记录
SELECT 
    gd.signal_value,
    gd.power_value,
    gd.flag
FROM grouped_data gd
JOIN group_last gl ON gd.record_id = gl.last_record_id
ORDER BY gd.record_id;

如果没有提前创建flag列，可以直接在CTE中计算：

WITH flagged_data AS (
    SELECT 
        signal_value,
        power_value,
        record_id,
        CASE WHEN power_value < 0 THEN 1 ELSE 0 END AS flag
    FROM power_data
),
grouped_data AS (
    SELECT 
        *,
        SUM(CASE WHEN flag = 0 THEN 1 ELSE 0 END) OVER (ORDER BY record_id) AS group_id
    FROM flagged_data
),
group_last AS (
    SELECT group_id, MAX(record_id) AS last_record_id
    FROM grouped_data
    WHERE flag = 1
    GROUP BY group_id
)
SELECT 
    fd.signal_value,
    fd.power_value,
    fd.flag
FROM flagged_data fd
JOIN group_last gl ON fd.record_id = gl.last_record_id
ORDER BY fd.record_id;

方法二：ROW_NUMBER简化查询 #

直接在分组内按倒序排序，取行号为1的记录（即每组最后一条）：

WITH flagged_data AS (
    SELECT 
        signal_value,
        power_value,
        record_id,
        CASE WHEN power_value < 0 THEN 1 ELSE 0 END AS flag
    FROM power_data
),
grouped_ranked AS (
    SELECT 
        *,
        -- 生成分组ID
        SUM(CASE WHEN flag = 0 THEN 1 ELSE 0 END) OVER (ORDER BY record_id) AS group_id,
        -- 分组内按记录顺序倒序排，行号1就是最后一条
        ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY record_id DESC) AS rn
    FROM flagged_data
    WHERE flag = 1
)
SELECT signal_value, power_value, flag
FROM grouped_ranked
WHERE rn = 1
ORDER BY record_id;

原理说明 #

• SUM(CASE WHEN flag = 0 THEN 1 ELSE 0 END) OVER (ORDER BY record_id)：这个窗口函数会累加遇到的Flag=0记录数，每次遇到Flag=0就给分组ID加1，这样连续的Flag=1记录会被分到同一个组里。
• 后续通过取分组内排序字段的最大值，或者倒序取行号1，就能拿到每个连续负功率区间的最后一条记录。

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