题目 : https://leetcode.cn/problems/consecutive-numbers/
数据
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');
需求
查询所有至少连续出现三次的数字
查询结果 :
| ConsecutiveNums |
| 1 |
解决
连续的值 ,有3种情况 ,如:
1, 1, 1, 2, 2, 1, 1
(1 连续性);1, 2, 1, 1, 2, 1, 1
(都不连续性);1, 1, 1, 2, 1, 1, 1
(1 连续性, 去重);
方法1
技术点:
lead()
: 查询当前行向下偏移 n 行的值lag()
: 查询当前行向上偏移 n 行的值
lead/lag(列名, 偏移的offset, 超出窗口的默认值)
over (partition by 分组列 order by 排序列
rows between 开始位置 preceding and 结束位置 following)
查询该行的,下列值,下下列值
select num,
lead(num, 1, 0) over(order by id) as lead_1,
lead(num, 2, 0) over(order by id) as lead_2
from Logs
查询结果:
num|lead_1|lead_2|
---+------+------+
1| 1| 1|
1| 1| 2|
1| 2| 1|
2| 1| 2|
1| 2| 2|
2| 2| 0|
2| 0| 0|
当该行值 = 下列值 = 下下列值就连续相等了
- 注意点:num可能有相同值多次连续,所以要去重
-- 求下列,下下列
with t1 as (
select num,
lead(num, 1, 0) over(order by id) as lead_1,
lead(num, 2, 0) over(order by id) as lead_2
from Logs
)
-- 比较下列,下下列,并去重
select num as ConsecutiveNums
from t1
where num = lead_1 and lead_1 = lead_2
group by num;
缺点: 不够通用,当连续 n 次时,就不好处理了
方法2
思路 :
- 对 num 排序
- 对 num 进行分区排序
- 对俩个值进行差值
- 当差值相同数大于 3 时 , 就说明连续
| num | row_bumber | row_bumber分组 | 差 |
| 1 | 1 | 1 | 0 |
| 1 | 2 | 2 | 0 |
| 1 | 3 | 3 | 0 |
| 2 | 4 | 1 | 3 |
| 2 | 5 | 2 | 3 |
| 1 | 6 | 4 | 2 |
| 1 | 7 | 5 | 2 |
根据id 进行排序 , 避免id不连续情况
select num,
row_number() over(order by id) as row_bumber
from Logs
查询结果:
num|row_bumber|
---+----------+
1| 1|
1| 2|
1| 3|
2| 4|
1| 5|
2| 6|
2| 7|
根据 num 分区窗口,在窗口中根据 id 排序
select num,
row_number() over(partition by num order by id) as row_bumber_partition
from Logs
查询结果:
num|row_bumber_partition|
---+--------------------+
1| 1|
1| 2|
1| 3|
1| 4|
2| 1|
2| 2|
2| 3|
对比差值,查看区别
select num,
row_number() over(order by id) as row_bumber,
row_number() over(partition by num order by id) as row_bumber_partition,
row_number() over(order by id) - row_number() over(partition by num order by id) as diff
from Logs
查询结果:
num|row_bumber|row_bumber_partition|diff|
---+----------+--------------------+----+
1| 1| 1| 0|
1| 2| 2| 0|
1| 3| 3| 0|
2| 4| 1| 3|
1| 5| 4| 1|
2| 6| 2| 4|
2| 7| 3| 4|
解决 :
-- 求差值
with t1 as(
select num,
row_number() over(order by id) - row_number() over(partition by num order by id) as diff
from Logs
),
-- 筛选连续3次的值
t2 as (
select num as ConsecutiveNums
from t1
group by num, diff
having count(*) >= 3
)
-- 去重
select num
from t2
group by num;