如何高效筛选自增ID递增但datetime列时序异常的行？

针对500万条数据的场景，用累计最大值窗口函数是最优方案，既能解决自连接效率低的问题，也能覆盖任意数量连续异常行的情况。

核心思路 #

因为ID是自增的，数据的顺序由ID决定。我们需要判断每条记录的datetime是否小于等于前面所有记录中的最大datetime值——只要满足这个条件，就说明它没有晚于前序所有记录，属于异常行。

具体SQL代码 #

WITH max_prev_data AS (
    SELECT 
        ID,
        datetime,
        -- 计算当前行之前所有记录的最大datetime
        MAX(datetime) OVER (ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prev_max_dt
    FROM your_table
)
SELECT ID, datetime
FROM max_prev_data
-- 筛选出不符合规则的行（第一行没有前序记录，自动排除）
WHERE datetime <= prev_max_dt;

方案优势 #

1. 效率极高：窗口函数的计算逻辑是线性遍历，时间复杂度为O(n)，500万条数据的处理速度远快于自连接的O(n²)。
2. 覆盖所有异常场景：不管是单条异常还是连续多条异常（比如你提到的ID5、6、7），累计最大值的判断逻辑都能准确识别——哪怕某条异常行的datetime比前一条大，但只要比更早的正常行datetime小，依然会被筛选出来。

补充说明 #

如果你的ID是严格连续无间隙的自增列，也可以用ROW_NUMBER()替代ID排序，但直接用ID排序更贴合数据表的原生逻辑，无需额外计算。

内容的提问来源于stack exchange，提问作者Іван Крічфолуші