Oracle,查找所有至少连续出现N次的数字

/ 算法算法 / 169浏览

Oracle,查找所有至少连续出现N次的数字

起因

leetcode180 刷到Leetcode第180题.连续出现的数字

image-20201117182504342

一开始看到题目的时候就觉得有见过类似场景,一下子想不起来,觉得跟我平常工作中的取开仓日很像,思索一会无思路,去看题解,发现大家用的是多表关联和lead聚合函数,无法复用决定研究。

测试数据
Create table  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', '3');
insert into Logs (Id, Num) values ('8', '3');
insert into Logs (Id, Num) values ('9', '3');
insert into Logs (Id, Num) values ('10', '3');
commit;
开始

有个取巧的数学办法,用num来分组 组内排序一个id 叫r_sort

select t.id,
       t.num,
       ROW_NUMBER() over(partition by t.num order by t.id) as r_sort
from Logs t

image-20201117183653748

如果num连续时 r_sort是递增的,id也是递增的 这时候相减就会得出一个相同的值

select t.id,
       t.num,
       ROW_NUMBER() over(partition by t.num order by t.id) as r_sort,
       t.id-ROW_NUMBER() over(partition by t.num order by t.id) as d_sort
from Logs t

image-20201117183740105

当id-r_sort是相同时,说明数字递增,我只需要统计 id-r_sort的个数就可以了。

select t.num from (
select t.id,
       t.num,
       ROW_NUMBER() over(partition by t.num order by t.id) as r_sort,
       t.id-ROW_NUMBER() over(partition by t.num order by t.id) as d_sort
from Logs t) t
group by t.num,d_sort
having count(d_sort)>=3;

image-20201117184030789

当统计连续的n个时,只需要把3改成n就可以了

select t.num from (
select t.id,
       t.num,
       ROW_NUMBER() over(partition by t.num order by t.id) as r_sort,
       t.id-ROW_NUMBER() over(partition by t.num order by t.id) as d_sort
from Logs t) t
group by t.num,d_sort
having count(d_sort)>=N;