【sql复盘】连续登录N天问题

窗口函数

连续登录问题

user_id	login_date
A	2022-09-02
A	2022-09-03
A	2022-09-04
B	2021-11-25
B	2021-12-31
C	2022-01-01
C	2022-04-04
C	2022-09-03
C	2022-09-04
C	2022-09-05
A	2022-09-03
D	2022-10-20
D	2022-10-21
A	2022-10-03
D	2022-10-22
D	2022-10-23
B	2022-01-04
B	2022-01-05
B	2022-11-16
B	2022-11-17

找出连续登录3天的用户

CREATE TABLE SQL_8
(
     user_id 	varchar(2),
     login_date date
);
INSERT INTO SQL_8 (user_id,login_date)
VALUES ('A', '2022-09-02'), ('A', '2022-09-03'), ('A', '2022-09-04'), ('B', '2021-11-25'),
       ('B', '2021-12-31'), ('C', '2022-01-01'), ('C', '2022-04-04'), ('C', '2022-09-03'),
       ('C', '2022-09-05'), ('C', '2022-09-04'), ('A', '2022-09-03'), ('D', '2022-10-20'),
       ('D', '2022-10-21'), ('A', '2022-10-03'), ('D', '2022-10-22'), ('D', '2022-10-23'),
       ('B', '2022-01-04'), ('B', '2022-01-05'), ('B', '2022-11-16'), ('B', '2022-11-17');

法一：利用row_number()+date_sub()函数

• 具体逻辑如下
• 首先获取无重复的记录。distinct
• row_number() over(partition by user_id order by login_date)以user_id为分组固定窗口， order by login_date为浮动窗口，生成新列rn。
• date_sub(login_date ,interval rn day)获取date0
• 再按user_id和date0分组计数
• 按照计数值大于等于3,返回distinct user_id

with t1 as(
    select DISTINCT *
    from sql_8
),
t2 as(
    select *,
        row_number() over(PARTITION BY user_id ORDER BY login_date) as rn
    from t1
),
t3 as(
    select user_id,date_sub(login_date, INTERVAL rn day) as date0
    from t2
),
t4 as(
    select user_id,
        count(1) as n
    from t3
    GROUP BY user_id,date0
)
select user_id
from t4
where n >= 3
ORDER BY user_id;

![结果](https://img-blog.csdnimg.cn/0bbe9a34c544477ba9e68a8814c1cf45.png

法二：利用lead()和date_add()函数。tips：感觉更好用

• 具体逻辑如下
• 获取无重复的记录。distinct *
• 先利用lead(login_date, 2) over( PARTITION BY user_id ORDER BY login_date) as 实际登录日期
• 然后,date_add(login_date, interval 2 day) as 预期登录日期
• 只要预期登录日期=实际登录日期即可筛选出连续3天登录的用户
• 结果还需去重

with t1 as(
        select DISTINCT *
        from sql_8
    ), t2 as(
        select
            user_id,
            lead(login_date, 2) over(PARTITION BY user_id ORDER BYlogin_date) 
            as 实际登录日期,
            date_add(login_date, interval 2 day) as 预期登录日期
        from t1
    )
select DISTINCT user_id
from t2
where 预期登录日期 = 实际登录日期
order by user_id;

拓展：法二找到连续3次进球的球员

思路：首先去重，order by score_time获取浮动窗口，函数lead(player_id,1)获取下一次进球成员，lead(player_id,2)获取下两次进球成员。利用三次进球成员一致进行筛选。
数据：

CREATE TABLE
    SQL_9 (
        player_id varchar(2),
        score int,
        score_time datetime
    );
INSERT INTO
    SQL_9 (player_id, score, score_time)
VALUES ('B3', 1, '2022-09-20 19:00:14'), ('A2', 1, '2022-09-20 19:01:04'), ('A2', 3, '2022-09-20 19:01:16'), ('A2', 3, '2022-09-20 19:02:05'), ('A2', 2, '2022-09-20 19:02:25'), ('B3', 2, '2022-09-20 19:02:54'), ('A4', 3, '2022-09-20 19:03:10'), ('B1', 2, '2022-09-20 19:03:34'), ('B1', 2, '2022-09-20 19:03:58'), ('B1', 3, '2022-09-20 19:04:07'), ('A2', 1, '2022-09-20 19:04:19'), ('B3', 2, '2022-09-20 19:04:31'), ('A1', 2, '2022-09-20 19:04:51'), ('A1', 2, '2022-09-20 19:05:01'), ('B4', 2, '2022-09-20 19:05:06'), ('A1', 2, '2022-09-20 19:05:26'), ('A1', 2, '2022-09-20 19:05:48'), ('B4', 2, '2022-09-20 19:05:58');

with t0 as(
        SELECT DISTINCT *
        from sql_9
    ), t1 as(
        select
            *,
            lead(player_id, 1) over(ORDER BY score_time) 下一个进球,
            lead(player_id, 2) over(ORDER BY score_time) 下两个进球
        from t0
    )
select DISTINCT player_id
from t1
where
    player_id = 下一个进球
    and player_id = 下两个进球
order by player_id;