一、题目描述
表:Logs
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| num | varchar |
+-------------+---------+
id 是这个表的主键。
编写一个 SQL 查询,查找所有至少连续出现三次的数字。
返回的结果表中的数据可以按 任意顺序 排列。
查询结果格式如下面的例子所示:
示例 1
输入:
Logs 表:
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
输出:
Result 表:
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
解释:1 是唯一连续出现至少三次的数字。
二、代码
代码如下:
SELECT DISTINCT Num AS ConsecutiveNums # 用DISTINCT保证输出结果不重复
FROM (
SELECT
Num,
LAG(Num, 1) OVER (ORDER BY id) AS prev_nums, # 获取当前行的前一行的值
LAG(Num, 2) OVER (ORDER BY id) AS prev_two_nums # 获取当前行前两行的值
FROM Logs
) subquery
WHERE Num = prev_nums AND Num = prev_two_nums; # 如果当前行=前一行=前两行,则返回
三、解题思路
本题主要考验列表的遍历操作,需要能够找到连续的几行对应的值并对其进行判断。本题解题思路为:使用LAG(Num, N)
窗口函数,查询前N
行的值并记录,如果当前行的值=前一行的值=前两行的值,则表示连续出现了三次同样的数字,返回该结果即可。
其中,LAG
窗口函数的用法如下:
id | Num |
---|---|
1 | 10 |
2 | 20 |
3 | 30 |
4 | 40 |
id | Num | LAG(Num, 1) OVER (ORDER BY id) |
---|---|---|
1 | 10 | NULL |
2 | 20 | 10 |
3 | 30 | 20 |
4 | 40 | 30 |
id | Num | LAG(Num, 2) OVER (ORDER BY id) |
---|---|---|
1 | 10 | NULL |
2 | 20 | NULL |
3 | 30 | 10 |
4 | 40 | 20 |
从表中可以看出,如果当前行的前N
行不存在,则输出结果为NULL
,对于本题结果来说并不影响。
另外,需要注意的是,这里在输出结果时使用了DISTINCT
来保证输出结果的数字不重复,原因在于,如果使用上述方法去判断连续出现4次或4次以上的数字时,则会重复输出该重复的数字。