如何用Oracle 11 SQL查询不符合递增规则的异常计数器

假设你的表名为transaction_table，字段分别为card_no（卡号）、trans_date（交易日期）、counter（计数器），可以通过Oracle的分析函数LAG和LEAD定位异常的计数器值：

SELECT card_no, trans_date, counter
FROM (
    SELECT 
        card_no,
        trans_date,
        counter,
        -- 获取当前行的前一行计数器值
        LAG(counter) OVER (PARTITION BY card_no ORDER BY trans_date) AS prev_cnt,
        -- 获取当前行的后一行计数器值
        LEAD(counter) OVER (PARTITION BY card_no ORDER BY trans_date) AS next_cnt
    FROM transaction_table
) t
WHERE 
    -- 排除没有前序或后序记录的首尾行
    prev_cnt IS NOT NULL 
    AND next_cnt IS NOT NULL
    -- 筛选出不符合正常递增/重置规则的记录
    AND NOT (
        (counter = prev_cnt + 1) 
        OR (prev_cnt = 250 AND counter = 1)
    )
    -- 确认后续记录回到了正常的递增序列，说明当前记录是异常值
    AND next_cnt = prev_cnt + 1;

逻辑说明 #

1. 内层查询：通过LAG和LEAD函数，按卡号分组、交易日期排序，为每一行匹配前一个和后一个交易的计数器值，便于对比当前值在序列中的位置。
2. 外层筛选条件：
   • 排除首尾行（无前后记录的情况）；
   • 先排除计数器正常的场景：要么是连续递增（当前值=前一个值+1），要么是达到阈值后的正常重置（前一个值=250且当前值=1）；
   • 最后筛选出「后一个值回到了前一个值+1的正常序列」的记录——这类记录的当前值明显偏离递增轨迹，后续又回归正常，完全匹配你示例中12、120这类异常的特征。

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