sql练习题-用户间隔连续问题

– -- 1 建表 插入数据
drop table if exists interval_continuous_table;
CREATE table if not exists interval_continuous_table(
id STRING COMMENT ‘用户的ID’,
dt DATETIME COMMENT ‘登陆时间’
)COMMENT ‘用户间隔登陆连续表’;
TRUNCATE table interval_continuous_table;
insert into interval_continuous_table values(‘1001’,‘2021-12-12 00:00:00’);
insert into interval_continuous_table values(‘1002’,‘2021-12-12 00:00:00’);
insert into interval_continuous_table values(‘1001’,‘2021-12-13 00:00:00’);
insert into interval_continuous_table values(‘1001’,‘2021-12-14 00:00:00’);
insert into interval_continuous_table values(‘1001’,‘2021-12-16 00:00:00’);
insert into interval_continuous_table values(‘1002’,‘2021-12-16 00:00:00’);
insert into interval_continuous_table values(‘1001’,‘2021-12-19 00:00:00’);
insert into interval_continuous_table values(‘1002’,‘2021-12-17 00:00:00’);
insert into interval_continuous_table values(‘1001’,‘2021-12-20 00:00:00’);
SELECT *
FROM interval_continuous_table
;
– 2解决需求 计算每个用户最大的连续登录天数，可以间隔一天。解释:如果一个用户在 1,3,5,6 登录游戏，则视为连续 6 天登录。
– 0）如果是严格连续，可以用等差数列解决，但有非严格连续，需要嵌套 等差数列，但是题目如果说是间隔三天也算的话那么 怎么办呢？ 分组方法
– 1）此题目非严格连续 也就是说 日期相减 小于等于2 就算 连续
SELECT id
,dt
,lag(dt,1,‘2021-01-01 00:00:00’) OVER(PARTITION BY id ORDER BY dt) lagdt
FROM interval_continuous_table
;
– 2) dt-lagdt 得到dtdiff
SELECT id
,dt
,lagdt
,DATEDIFF(dt,lagdt) dtdiff
FROM (
SELECT id
,dt
,lag(dt,1,‘2021-01-01 00:00:00’) OVER(PARTITION BY id ORDER BY dt) lagdt
FROM Interval_continuous_table
) t1
;
–3)按照用户分组,同时按照时间排序,计算从第一行到当前行大于2的数据的总条数(sum(if(flag>2,1,0)))
SELECT id
,dt
,sum(IF(dtdiff>2,1,0)) OVER(PARTITION BY id ORDER BY dt) flag
FROM (
SELECT id
,dt
,lagdt
,DATEDIFF(dt,lagdt) dtdiff
FROM (
SELECT id
,dt
,lag(dt,1,‘2021-01-01 00:00:00’) OVER(PARTITION BY id ORDER BY dt) lagdt
FROM Interval_continuous_table
) t1
) t2
;
–4) 按照ID flag 分组 求最大日期减去最小日期
SELECT
id,
flag,
datediff(MAX(dt),MIN(dt)) days
FROM
(
SELECT id
,dt
,sum(IF(dtdiff>2,1,0)) OVER(PARTITION BY id ORDER BY dt) flag
FROM (
SELECT id
,dt
,lagdt
,DATEDIFF(dt,lagdt) dtdiff
FROM (
SELECT id
,dt
,lag(dt,1,‘2021-01-01 00:00:00’) OVER(PARTITION BY id ORDER BY dt) lagdt
FROM Interval_continuous_table
) t1
) t2
)t3
group by id,flag;
–5)最大连续登陆天数就是max（days） +1
SELECT
id,
MAX(days) + 1 max_continue_login_days
from
(
SELECT
id,
flag,
datediff(MAX(dt),MIN(dt)) days
FROM
(
SELECT id
,dt
,sum(IF(dtdiff>2,1,0)) OVER(PARTITION BY id ORDER BY dt) flag
FROM (
SELECT id
,dt
,lagdt
,DATEDIFF(dt,lagdt) dtdiff
FROM (
SELECT id
,dt
,lag(dt,1,‘2021-01-01 00:00:00’) OVER(PARTITION BY id ORDER BY dt) lagdt
FROM Interval_continuous_table
) t1
) t2
)t3
group by id,flag
)t4
group by id;