PieCloudDB中查找连续出现至少三次的数字问题求助

问题说明 #

现有如下数据表logs：

使用PieCloudDB Database，需要找出所有连续出现至少三次的数字，但以下查询未得到预期结果：

SELECT num
FROM (
  SELECT num,
         ROW_NUMBER() OVER (ORDER BY id) AS row_number,
         ROW_NUMBER() OVER (PARTITION BY num ORDER BY id) AS number_row_number
  FROM logs
) AS subquery
WHERE row_number - number_row_number >= 2
GROUP BY num
HAVING COUNT(*) >= 3;

问题原因 #

原查询逻辑存在两处错误：

1. WHERE row_number - number_row_number >= 2会过滤掉每个连续相同数字序列的前两条记录，比如num=1的连续3条记录，计算出的差值都是0，不满足条件，导致这组数据完全被排除，最终结果漏掉num=1。
2. 用GROUP BY num统计所有满足差值条件的记录数，而非统计连续序列的长度，逻辑不符合“连续出现”的要求，可能误将非连续的同数字记录算入统计。

正确查询方法 #

方法一：基于窗口函数分组连续序列 #

通过计算分组标识，统计每个连续序列的长度，再筛选符合条件的数字：

SELECT DISTINCT num
FROM (
    SELECT num,
           COUNT(*) OVER (PARTITION BY grp) AS seq_length
    FROM (
        SELECT num,
               -- 计算分组标识，相同连续序列的grp值一致
               ROW_NUMBER() OVER (ORDER BY id) - ROW_NUMBER() OVER (PARTITION BY num ORDER BY id) AS grp
        FROM logs
    ) t
) t2
WHERE seq_length >= 3;

逻辑说明：

• 内层子查询中，grp通过全局行号减去按num分组的行号得到，相同值对应一组连续的相同数字。
• 中间层用窗口函数计算每个连续序列的长度seq_length。
• 外层筛选出序列长度≥3的数字，用DISTINCT去重，确保同一个数字只返回一次。

方法二：用LAG/LEAD函数直接判断连续三次 #

通过判断当前数字与前后两位是否相同，筛选出符合条件的数字：

SELECT DISTINCT num
FROM logs
WHERE 
    -- 当前数字与前两个数字相同
    (num = LAG(num, 1) OVER (ORDER BY id) AND num = LAG(num, 2) OVER (ORDER BY id))
    -- 或当前数字与后两个数字相同（覆盖连续序列的末尾部分）
    OR (num = LEAD(num, 1) OVER (ORDER BY id) AND num = LEAD(num, 2) OVER (ORDER BY id));

逻辑说明：

• 只要数字存在连续三次出现的情况，要么某个位置的数字和前两位相同，要么和后两位相同，通过这两个条件覆盖所有连续三次的场景，最后去重得到结果。

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