如何查询Logs表中连续出现超过两次的num字段值？

现有Logs日志表，ID列为标识列，num列存储可重复数字。需求是仅输出num列中连续出现超过两次的值——比如示例里1连续出现3次，所以输出1；后续的1和2都没连续超过2次，不纳入结果。

示例数据 #

Logs表的原始数据：

预期输出 #

错误尝试 #

我写了以下查询，但它把1的总出现次数统计成了4次，没识别到“连续重复”的特性，不符合需求：

with cte as(
 select logno,grp = sum(case when logno = repeat_val then 0 else 1 end) over (order by sno) from
 (
 select sno,logno,
 lag(logno) over(order by logno) as repeat_val
 from 
 logprac l) s)

 select logno as ConsecutiveNums , count(*) 
 from cte
 group by grp,logno
having count(*)>2
 order by grp;

正确解法 #

核心思路是给连续相同的num分配同一个分组ID，统计每组的行数后筛选出行数>2的num，最后去重避免重复输出。

方法一：用LAG函数标记分组 #

WITH numbered_logs AS (
    SELECT 
        num,
        -- 当前num和前一个相同时，不增加分组ID；不同时开启新分组
        SUM(CASE WHEN num = LAG(num) OVER(ORDER BY id) THEN 0 ELSE 1 END) OVER(ORDER BY id) AS group_id
    FROM Logs
),
group_counts AS (
    SELECT 
        num,
        COUNT(*) AS consecutive_count
    FROM numbered_logs
    GROUP BY group_id, num
    HAVING COUNT(*) > 2
)
SELECT DISTINCT num AS ConsecutiveNums
FROM group_counts;

方法二：用ROW_NUMBER()计算分组ID #

通过行号的差值来标记连续相同的num，同一连续组的差值会一致：

WITH grouped_logs AS (
    SELECT 
        num,
        -- 全局行号减去按num分组的行号，连续相同num的差值相同
        ROW_NUMBER() OVER(ORDER BY id) - ROW_NUMBER() OVER(PARTITION BY num ORDER BY id) AS group_id
    FROM Logs
),
group_counts AS (
    SELECT 
        num,
        COUNT(*) AS consecutive_count
    FROM grouped_logs
    GROUP BY group_id, num
    HAVING COUNT(*) > 2
)
SELECT DISTINCT num AS ConsecutiveNums
FROM group_counts;

说明 #

两种方法都是先把连续重复的num归为同一组，再统计每组的长度。筛选出长度超过2的组后，用DISTINCT确保同一个num即使多次连续出现，也只输出一次。

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