49 活跃用户

[astak16]

题目

题目连接：活跃用户

写一个 SQL 查询, 找到活跃用户的 id 和 name 。

活跃用户是指那些至少连续 5 天登录账户的用户。

create table accounts (id int, name varchar(10));
insert into accounts (id, name) values
(1, 'Winston'),
(7, 'Jonathan');

create table logins (id int, login_date date);
insert into logins (id, login_date) values
(7, '2020-05-30'),
(1, '2020-05-30'),
(7, '2020-05-31'),
(7, '2020-06-01'),
(7, '2020-06-02'),
(7, '2020-06-02'),
(7, '2020-06-03'),
(1, '2020-06-07'),
(7, '2020-06-10');

SQL：方法一

select
  distinct id, name
from (
  select
	  id,
    login_date,
	  datediff(lead(login_date, 4) over(partition by id order by login_date), login_date) diff
  from logins group by id, login_date
) temp left join accounts using(id) where diff = 4

解析

1. 去重，因为一名用户可能会在一天当中登录多次

   select id, login_date from logins group by id, login_date

2. 使用 lead 窗口函数，向下 4 行

   lead(login_date, 4) over(partition by id order by login_date)
   
   -- 结果
   id  |  login_date  |  lead_date
   1   |  2020-05-30  |  null
   1   |  2020-06-07  |  null
   7   |  2020-05-30  |  2020-06-03
   7   |  2020-05-31  |  2020-06-10
   7   |  2020-06-01  |  null
   7   |  2020-06-02  |  null
   7   ]  2020-06-03  |  null
   7   |  2020-06-10  |  null

3. 使用 datediff 计算 login_date 和 lead_date 之间的时间差

4. 将上面的结果作为临时表 temp

5. 将临时表 temp 和 accounts 左连，筛选出第三步中的时间差为 4 的用户

SQL：方法二

select
	distinct id, name
from accounts join (
  select
    id,
		@rn:=if(@pre_id=id and @pre_login_date = subdate(login_date, interval 1 day), @rn + 1, 1) rn,
		@pre_id:=id,
		@pre_login_date:=login_date
  from (
    select id, login_date from logins group by id, login_date
  ) t1, (select @pre_id:=null, @pre_login_date:=null, @rn:=0) init
) t2 using(id) where t2.rn >= 5 order by accounts.id;

解析

使用变量对将每个用户的登录日期（去重）按照先后顺序进行排名，

• 比较当前数据的 id 和 上一条数据的 id 是否相等
• 用 subdate 比较当前数据的 login_date 和上一条数据的 login_date 是否相等

1. 各变量初始化

   • @pre_id 保存上一条数据的 id， 初始化为 null
   • @pre_login_date 保存上一条数据的 login_date， 初始化为 null
   • @rn 保存媒体数据的排名，初始化为 0

   select @pre_id:=null, @pre_login_date:=null, @rn:=0

2. 使用 if 进行判断

   • 如果 @pre_id (上一条数据的 id) 等于当前这条数据的 id ，并且 @pre_login_date (上一条数据的 login_date)等于当前这条数据的 login_date ，输出@rn + 1 ，否则输出1
     • 运行第一条数据时， @pre_id = null 、 @pre_login_date = null
       • @pre_id = id 是 null = 7 为 false
       • @pre_login_date = subdate(login_date, interval 1 day) 是 null = '2020-05-29' 为 false
       • 输出为 1
       • 将 @pre_id 赋值为当前数据的 id ，结果是 @pre_id:=7 ；@pre_login_date 赋值为当前数据的 login_date，结果是 @pre_login_date:= '2020-05-30'
     • 运行第二条数据时，@pre_id = 7 、@pre_login_date = '2020-05-30'
       • @pre_id = id 是 7 = 7 为 true
       • @pre_login_date = subdate(login_date, interval 1 day) 是 '2020-05-30' = '2020-05-30' 为 true
       • 输出 @rn + 1 为 2
       • 将 @pre_id 赋值为当前数据的 id ，结果是 @pre_id:=7 ；@pre_login_date 赋值为当前数据的 login_date，结果是 @pre_login_date:= '2020-05-31'
     • 以此类推，运行第三条数据，直到运行到最后一条数据

   if(@pre_id=id and @pre_login_date = subdate(login_date, interval 1 day), @rn + 1, 1) rn

3. 将步骤一和步骤二的结果作为临时表 t2

4. 查询临时表 t2 筛选出 rn 大于 5 的数据。

SQL：方法三

select
	distinct id, name
from (
  select
	  id,
	  subdate(login_date, rn) first_login_date
  from (
    select
      id,
      login_date,
      row_number() over(partition by id order by login_date) rn
    from (
	    select
        id,
        login_date
      from logins group by id, login_date order by id, login_date
    ) t1
  ) t2 group by id, first_login_date having count(*) >= 5
) t3 left join accounts using(id);

解析

使用窗口函数对将每个用户的登录日期（去重）按照先后顺序进行排名，再用 subdate 计算出第一天登录日期

1. 使用 row_number() ，将每个用户按照登录日期进行排序，因为一个用户可以一天登录多次，所以所以首先要对 logins.login_date 进行去重

   select
     id,
     login_date,
     row_number() over(partition by id order by login_date) rn
   from (
     select id, login_date from logins group by id, login_date
   ) t1

2. 使用 subdate 计算每个日期的第一次登录的日期

   • 计算一段连续的数：用一个数(日期)减去它的排名，会得到一个相同的数

   select
     id,
     subdate(login_date, rn) first_login_date
   from (
     select
       id,
       login_date,
       row_number() over(partition by id order by login_date) rn
     from (
       select id, login_date from logins group by id, login_date
     ) t1
   ) t2

3. 按照 id 和 first_login_date 进行分组，筛选出大于 5 天的用户

4. 连接 accounts ，再对 accounts.id 和 accounts.name 进行去重

   1. 去重是因为，某一个用户可能会有几个连续登录 5 天的区间

SQL：方法四

select
	distinct accounts.*
from accounts
join logins l1 using(id)
join logins l2 on l1.id = l2.id and datediff(l2.login_date, l1.login_date) between 0 and 4
group by accounts.id, accounts.name, l1.login_date
having count(distinct l2.login_date) = 5;

解析

自连接， l1.login_date 作为登录起始日， l2.login_date 用来和 l1.login_date 进行比较，也就是说每个日期都检查之后是否有 4 个连续日期

1. datediff(l2.login_date, l1.login_date) 进行比较，是否在 0 到 4 天之间

   • datediff('2022-01-02', '2022-01-02') // 0
   • datediff('2022-01-03', '2022-01-02') // 1
   • datediff('2022-01-04', '2022-01-02') // 2
   • datediff('2022-01-05', '2022-01-02') // 3
   • datediff('2022-01-06', '2022-01-02') // 4

   那你可能会想到可不可以用 between 1 and 5 ，那我们来看一下 1 ~ 5 的结果是啥

   • datediff('2022-01-03', '2022-01-02') // 1
   • datediff('2022-01-04', '2022-01-02') // 2
   • datediff('2022-01-05', '2022-01-02') // 3
   • datediff('2022-01-06', '2022-01-02') // 4
   • datediff('2022-01-07', '2022-01-02') // 5

   l2.login_date 是 l1.login_date 开始日后（包括开始日）连续登录日期，如果 l2.login_date 不包括开始日，后面处理就麻烦了。

   select
     *
   from logins l1 join logins l2
   on l1.id = l2.id and datediff(l2.login_date, l1.login_date) between 0 and 4;

2. 将 accounts 和 l1 、 l2 连接，并且按照 accounts.id 、accounts.name 、l1.login_date 进行分组。

   • 为什么这里使用 l1.login_date ，因为第一步中计算 l1.login_date 后是否有 4 个连续日期

3. 使用 having 筛选出 l2.login_date 等于 5 数据（需要使用 distinct 去重）

   • 用户可以在一天之内登录多次，所有 l2.login_date 不去重的话，不符合题目要求