Leetcode数据库刷题 180. 连续出现的数字

题目

• 题目链接

SQL架构
表：Logs

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| num         | varchar |
+-------------+---------+
id 是这个表的主键。
 
编写一个 SQL 查询，查找所有至少连续出现三次的数字。
返回的结果表中的数据可以按 任意顺序 排列。

查询结果格式如下面的例子所示：
Logs 表：
+----+-----+
| Id | Num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+

Result 表：
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+
1 是唯一连续出现至少三次的数字。

解法

Create table If Not Exists Logs (Id int, Num int);
Truncate table Logs;
insert into Logs (Id, Num) values ('1', '1');
insert into Logs (Id, Num) values ('2', '1');
insert into Logs (Id, Num) values ('3', '1');
insert into Logs (Id, Num) values ('4', '2');
insert into Logs (Id, Num) values ('5', '1');
insert into Logs (Id, Num) values ('6', '2');
insert into Logs (Id, Num) values ('7', '2');

-- 构造新属性rn，且rownum所实现的需求为分组排序
select id,num,row_number() over(partition by num order by id) as rn
from logs
order by id,num;

+------+------+----+
| id   | num  | rn |
+------+------+----+
|    1 |    1 |  1 |
|    2 |    1 |  2 |
|    3 |    1 |  3 |
|    4 |    2 |  1 |
|    5 |    1 |  4 |
|    6 |    2 |  2 |
|    7 |    2 |  3 |
+------+------+----+

-- 我们可以发现 连续出现的数字的 id - rn 是一样的
select id,num,id - row_number() over(partition by num order by id) as id_rn
from logs
order by id,num;
+------+------+-------+
| id   | num  | id_rn |
+------+------+-------+
|    1 |    1 |     0 |
|    2 |    1 |     0 |
|    3 |    1 |     0 |
|    4 |    2 |     3 |
|    5 |    1 |     1 |
|    6 |    2 |     4 |
|    7 |    2 |     4 |
+------+------+-------+

-- 这里有个小问题 id可能比rn小 所以可以在id上也使用开窗函数 结果和上面的相同
select id,num,(row_number() over(order by id) - row_number() over(partition by num order by id))as id_rn
from logs
order by id,num;

-- 需要在外层进行group by 按照id_rn 和 num分组 进行个数的判断
-- 注意这里num也是分组的条件 
select distinct(num) as ConsecutiveNums
from
(
    select id,num,(row_number() over(order by id) - row_number() over(partition by num order by id))as id_rn
    from logs
)t
group by id_rn,t.num
having count(1) >= 3;

+-----------------+
| ConsecutiveNums |
+-----------------+
|               1 |
+-----------------+