如何用SQL窗口函数计算连续同值时长并删除超长零速度记录

最优实现方案 #

针对车速连续次数统计的经典孤岛问题，仅需2层嵌套窗口函数即可实现，代码简洁且执行效率更高：

WITH speed_grp AS (
    SELECT 
        *,
        -- 标记当前行与同车上一行车速是否发生变化
        SUM(CASE WHEN speed = LAG(speed, 1) OVER (PARTITION BY car ORDER BY time) THEN 0 ELSE 1 END) 
            OVER (PARTITION BY car ORDER BY time) AS grp_id
    FROM demo
)
SELECT 
    id, car, speed, time,
    -- 同一车、同一连续车速分组的总行数就是连续出现次数
    COUNT(*) OVER (PARTITION BY car, grp_id) AS lasting
FROM speed_grp
ORDER BY id;

逻辑说明 #

1. 内层CTE speed_grp 完成连续分组标记：
   • 用LAG(speed,1)取同车的上一行车速，和当前车速对比，车速变化则标记1，不变标记0
   • 对标记值做累加求和，得到同车下的连续分组ID，所有连续相同车速的行会归属到同一个grp_id
2. 外层查询直接按car + grp_id分区统计总行数，就是该段车速的连续出现次数，和你需要的lasting字段逻辑完全一致。

原有代码问题说明 #

你之前的写法有两个核心错误导致结果不符合预期：

• LAG(speed, 1, 0)的默认值设置错误：如果车的第一条记录车速不是0，会错误判定第一条记录和上一行（虚拟的0值）车速不同，分组逻辑出错
• 额外新增的grp字段完全冗余，PARTITION BY car后直接按time排序即可，不需要额外的行号做排序依据

过滤规则验证 #

执行上述代码得到带lasting的结果后，直接用你之前设想的过滤规则即可删除符合要求的行：

DELETE FROM demo 
WHERE id IN (
    SELECT id FROM (
        WITH speed_grp AS (
            SELECT 
                *,
                SUM(CASE WHEN speed = LAG(speed, 1) OVER (PARTITION BY car ORDER BY time) THEN 0 ELSE 1 END) 
                    OVER (PARTITION BY car ORDER BY time) AS grp_id
            FROM demo
        )
        SELECT id FROM speed_grp
        WHERE speed = 0 AND COUNT(*) OVER (PARTITION BY car, grp_id) > 2
    ) t
)

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