SQL实战练习题(4)-求连续数-leetcode sql 180

目录

• 问题
• 数据SQL
• 答案

问题

数据SQL

create table log01 (
	id int primary key auto_increment,
	num int
)charset=UTF8MB4;

insert into log01(num) values(1), (1), (1), (2), (2), (3), (3), (3), (1), (2), (2);

select id as dt, num as uid  -- 使用id作为日期，num作为用户id（原id无实意，只是表主键，但自增，将其视为日期；原num为次数，数值可重复出现，可视为用户uid；故可翻译成(日志行为)某用户的请求日期，其dt、uid越大，说明近期请求越频繁）
from log01;

答案

SELECT DISTINCT t1.num
FROM logs t1, logs t2, logs t3
WHERE t1.id=t2.id-1 and t2.id=t3.id-1 and t1.num=t2.num and t2.num = t3.num;

---

with
tbl_01 as (
	select num as uid, id as dt -- 别名(翻译)
	from log01
)
,tbl_02 as (  -- 同组内密集无间隔排序
	select uid, dt, row_number() over (partition by uid order by dt) as order_num
	from tbl_01
)
,tbl_03 as ( -- 算间距
	select uid, dt, order_num, (dt - order_num) as margin
	from tbl_02
)
,tbl_04 as (  -- 用户间距分组, having过滤
	select uid, margin, count(1)
	from tbl_03
	group by uid, margin having count(1) >= 3
)
select * from tbl_04;