【SQL实用技巧】-- 连续N天登陆问题

连续N天登录问题

以下为用户登陆日期，用一条 SQL 语句查询出连续三天登录的人员姓名。

name	ddate
张三	2021-01-01
张三	2021-01-02
张三	2021-01-03
张三	2021-01-02
李四	2021-01-01
李四	2021-01-02
王五	2021-01-03
王五	2021-01-02
王五	2021-01-02

数据准备

create table game(
    name varchar(10) comment '用户名',
    ddate varchar(20) comment '日期'
);

insert into game values
('张三','2021-01-01'),
('张三','2021-01-02'),
('张三','2021-01-03'),
('张三','2021-01-02'),
('张三','2021-01-07'),
('张三','2021-01-08'),
('张三','2021-01-09'),
('李四','2021-01-01'),
('李四','2021-01-02'),
('王五','2021-01-03'),
('王五','2021-01-02'),
('王五','2021-01-02');

实现

方法一：

with t1 as ( select distinct name,ddate from game),
     t2 as ( select *,
                    row_number() over (partition by name order by ddate) rn
               from t1),
     t3 as ( select *,
                    date_sub(ddate,INTERVAL rn DAY) date2
               from t2 )
  select distinct
         name
    from t3
   group by name,date2
  having count(1)>=3 ;

步骤分解：

1. 去重： select distinct name,ddate from game
2. 使用窗口函数为去重结果按用户名分组对不同日期排序：select * row_number() over (partition by name order by ddate) rn from t1
3. 用原有的日期减去上一步排序的序号得到新的日期列，如果原来日期为连续则该用户的新日期列相同select *, date_sub(ddate,INTERVAL rn DAY) date2 from t2
4. 得到连续登陆3天用户的姓名：select distinct name from t3 group by name, date2 having count(1)>=3 , 此时要做的是统计不同用户在上一步的新日期列出现次数大于等于3次的用户姓名。

上述为此类问题的通用方法步骤：

1-> distinct
2-> row_number  as rn
3-> date_sub(dt,rn) as dt2
4-> group by dt2,name
5-> having count(1)>=N天
6-> distinct name
7-> count(name)

上述步骤中的思想需要熟练掌握来解决此类问题，但是不能死记硬背。下面一起学习第二种方法(此方法只能解决这种狭义的连续N次登陆问题)

方法二

with t1 as (
    select distinct name,ddate
      from game
),
    t2 as (
        select *,
               -- 预期第3天是几号日期？
               date_add(ddate,INTERVAL 2 DAY) as date_expect,
               -- 实际第三次出现是几号日期？
               lead(ddate,2) over(partition by name order by ddate) as date_actual
          from t1
    ),
    t3 as (
        select * from t2 where date_expect=date_actual
    ),
    t4 as (select distinct name from t3)
select * from t4;

步骤分解：

1. 去重，和方法一的第一步相同
2. 对去重的结果构造新的两列日期，第一列是原日期中预期第3天是几号日期(预计登陆日期)，第二列则是实际第三次出现的日期(实际登陆日期)
3. 符合条件的就是 实际登陆日期=预期登陆日期的记录