表: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 | +-----------------+
实现代码:
with t1 as (
select *,
row_number() over(order by id) as id2,
row_number() over (partition by num order by id) as rownum
from logs
)
select distinct num as ConsecutiveNums
from t1
group by id2 + 1 - rownum, num
having count(rownum) >= 3;
思路 & 注意点:
写+看大佬思路花了将近一小时,不过看懂了之后,还是觉得这题在取连续值题中,比较有代表价值。
1. 首先确定大致思路,通过定义id - row_number(),对连续的符合条件的值进行筛选。
原理:假设id为(3,4,5)的num为(4,4,4),对num partition by、 对id order by,row_number生成(1,2,3)。那么id - row_number() 为(2,2,2)。
(为什么代码里不是id而是id2,是因为id可能经过增删改之后并不连续,但是该方法必须用于连续id,因此自建连续 id 命名id2。这个看完思路后便能理解。)
2.对 id - row_number() 、num 进行分组
首先说说对id - row_number() 进行 group by 的原因:
我们可以看到,当num是连续出现时,Id-rownum的值是一致的,利用此特性进行group by,之后对其进行 count >= 3 的约束,便能选出符合要求的数据。
再说说对 num 进行 group by的原因:
可以看到在特殊情况下,num不一致,id-rownum仍然一致。因此,要对num进行group by,把不一样的num排除出去。
3. 为什么要对 id ‘+1’
其实很简单,id可能从0开始,而rownum永远大于0。
id - rownum 等于负数时,会出现 bigint unsigned 报错。
4. id有可能不连续
这个坑很难想到,但是处理起来也简单,思路就是构建一个新列 id2 ,让他连续增长就好了。
但是,有一个点要注意,当我同时在子查询中创建 id2、使用 id2时,会出现
Unknown column 'id2' in 'field list' 报错。
row_number() over(order by id) as id2,
id2 + 1 - row_number() over (partition by num order by id) as rk
这个并不是很能理解,可能SQL不支持这种操作。最后只能把表达式写在group by里,不过含义是一样的。