🔍

连续出现的数字

题目

有一个 number 表如下:

idnum
11
21
31
42
51
61
73
83
93
103
114

请找出连续出现 3 次以上的数字。

结果应该是 1 和 3。

解答

方法一 筛选笛卡尔积

SELECT DISTINCT
	l1.num
FROM
	Logs l1,
	Logs l2,
	Logs l3
WHERE
	l1.num = l2.num
	AND l2.num = l3.num
	AND l1.id = l2.id - 1
	AND l2.id = l3.id - 1

我们要保证的是:

由于相同的数字可能连续出现不止三次,所以得到的结果会重复,例如上图中红色线条标注出来的这部分笛卡尔积。我们可以用 distinct 去重。

方法二 使用 lead() 窗口函数

lead()

SELECT
	num,
	LEAD(num, 1) OVER () num1,
	LEAD(num, 2) OVER () num2
FROM
	Logs

得到如下数据:

numnum1num2
111
112
121
211
113
133
333
333
334
34NULL
4NULLNULL

可以看到,第二列相当于把第一列往上挪动了一行,第三列则是网上挪动了两行。由此可以找到重复的数字。即 1 和 3。