如何查询一组连续出现的数字

leetcode数据库第180题

难度：中等

表：Logs：

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| num         | varchar |
+-------------+---------+
id 是这个表的主键。

来源：力扣（LeetCode）
链接：https://leetcode.cn/problems/consecutive-numbers

编写一个 SQL 查询，查找所有至少连续出现三次的数字。

返回的结果表中的数据可以按 任意顺序 排列。

查询结果格式如下面的例子所示：
示例 1:

输入：
Logs 表：
+----+-----+
| Id | Num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+
输出：
Result 表：
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+
解释：1 是唯一连续出现至少三次的数字。

我的答案

思路是查找一个数字的前一行和后一行的值，应用的是窗口函数lag()和lead().如果这个数字与前后对应的值都相等，则说明该数字出现了至少3次，如果连续出现的次数大于3，则该数字会被重复检索两次，所以在最终的结果中要进行去重处理。

select distinct Num as ConsecutiveNums from 
(select Num, lag(Num) over () as f, lead(Num) over() as b  from Logs) as r
where r.Num = r.f and r.f = r.b

这种方法的缺点是，仅仅适用于连续出现三次的条件，如果面对需要查询连续出现多次的情况则无法处理，所以逻辑上的泛化性不够。

使用row_number()窗口函数

所以另一种思路是对连续出现N次这个概念进行定量描述。应用窗口函数row_number()可以很巧妙的将这个概念设定清楚，那就是当一个数字连续出现时，它的行序号与总排名的差值是不变的。用sql描述就是row_number()-rank()，所以group之后对row_number()-rank()求count()，得到的值就是该数字连续出现的次数，只需要检索出row_number()-rank()大于N的数字，就可以找到连续出现至少N次的数字。解决了查找连续出现任意N次的问题。

select distinct num as ConsecutiveNums
from
    (select 
        id,
        num,
        cast(row_number() over(order by id) as signed) - cast(rank() over(partition by num order by id) as signed) as diff
    from Logs) a
group by num,diff
having count(*) >=3