傳送門：180. 連續出現的數字

題目

表：Logs

±------------±--------+
| Column Name | Type |
±------------±--------+
| id | int |
| num | varchar |
±------------±--------+
在 SQL 中，id 是該表的主鍵。
id 是一個自增列。

找出所有至少連續出現三次的數字。

返回的結果表中的數據可以按 任意順序 排列。

結果格式如下面的例子所示：

示例 1:

輸入：
Logs 表：
±—±----+
| id | num |
±—±----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
±—±----+
輸出：
Result 表：
±----------------+
| ConsecutiveNums |
±----------------+
| 1 |
±----------------+
解釋：1 是唯一連續出現至少三次的數字。

方法一：row_number()

解析

官方題解是死代碼，只能應對連續三次，他們自己都不知道最佳解法，所以本人認為這題可以定級為困難。
判斷元素連續出現次數其實有個經典的公式：全局行號-它的分組排序行號（分組排名），每組中差值相同個數即其連續出現次數，而分組，排序并賦予排名是row_number()的特性，而全局行號數組是有序數組，為方便，這里默認全局行號和row_number()都是按遞增排序的，所以全局行號即表中的id，分組排名即row_number()后的值，全局行號-分組排名中相同數字個數即對應的數字連續出現個數，結果如下表所示：

id	分組字段num	id分組排名	id - id分組排名
1	1	1	0
2	1	2	0
3	1	3	0
4	2	1	3
5	1	4	1
6	2	2	4
7	2	3	4

有了這個表格后，計算過程就很明顯了，先求出id-row_number()按num分組的排名，別名rn，再嵌套一層查詢求組內相同rn的個數>=3的記錄，組內相同rn的數量，即按num和rn分組后的組內數量，所以要按num和rn分組，再篩選組內數量>=3的記錄，坑點來了：按num和rn分組不意味著num是唯一的，只有按num分組才能保證num唯一，所以必須對num去重后再返回num。

代碼（標準SQL）

select distinct num as ConsecutiveNums
from(select num,
id-row_number()over(partition by num order by id) as rn
from Logs)
group by num,rn
having count(*)>=3;

方法二：lead()

解析

學過開窗函數的都知道，lead()是把當前行的后面幾行往上拉，lag()則相反，這題用lag()或lead()都行，本人習慣用lead()，所以這里僅介紹lead()解法。lead()解法和row_number()解法類似，也是求差值，但更簡單，因為原理是：若全局行號-它的分組后n行號=-n，則分組字段至少出現n+1次。和上個方法一樣，全局行號數組必須是有序數組，即id，id后2行用lead(id,2)實現，需要升序排序，結果如下表所示：

id	分組字段num	id分組后2行	id-id分組后2行
1	1	3	-2
2	1	5	-3
3	1	null	null
4	2	7	-3
5	1	null	null
6	2	null	null
7	2	null	null

有了這個表格后，計算過程就很明顯了，先對id求lead()，按num分組，按id升序，再減去id，別名rn，再嵌套一層查詢，求組內rn=2的記錄，即出現3次的num，但它不止一個，所以仍要對num去重后再返回num。

代碼（標準SQL）

select distinct num as ConsecutiveNums
from(select num,
id-lead(id,2)over(partition by num order by id) as rn
from Logs)
where rn=-2;

總結下兩個方法：
假設存在一個有序的全局行號數組和一個分組字段，求至少連續出現n次的分組字段，有兩種方法：
1.全局行號-它的分組排名，每組中差值相同個數即分組字段連續出現次數，其中分組排名用row_number()實現，需要排序。
2.若全局行號-它的分組后n行號=-n，則分組字段至少出現n+1次，其中分組后n行號用lead(全局行號,n)實現，需要排序。
注意：全局行號還包括日期。