找出连续活跃5天的用户
表 Accounts
:
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| name | varchar |
+---------------+---------+
id 是该表主键.
该表包含账户 id 和账户的用户名.
表 Logins
:
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| login_date | date |
+---------------+---------+
该表无主键, 可能包含重复项.
该表包含登录用户的账户 id 和登录日期. 用户也许一天内登录多次.
写一个 SQL 查询, 找到活跃用户的 id 和 name.
活跃用户是指那些至少连续 5 天登录账户的用户.
返回的结果表按照 id 排序.
结果表格式如下例所示:
Accounts 表:
+----+----------+
| id | name |
+----+----------+
| 1 | Winston |
| 7 | Jonathan |
+----+----------+
Logins 表:
+----+------------+
| id | login_date |
+----+------------+
| 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 |
+----+------------+
Result 表:
+----+----------+
| id | name |
+----+----------+
| 7 | Jonathan |
+----+----------+
id = 1 的用户 Winston 仅仅在不同的 2 天内登录了 2 次, 所以, Winston 不是活跃用户.
id = 7 的用户 Jonathon 在不同的 6 天内登录了 7 次, , 6 天中有 5 天是连续的, 所以, Jonathan 是活跃用户.
解:
select distinct a.id, a.name
from Accounts a
join
(
select * ,lead(s.login_date,4) over(partition by s.id order by s.login_date asc) as last_date
from (select distinct id, login_date from Logins) s
)t
on a.id = t.id
where datediff(t.login_date, t.last_date) = -4
order by a.id
#解题思路:
- 把同一天登陆多次的记录去重 :distinct id, login_date
- 根据去重后的数据,用lead(需要偏移位置对列名,偏移的行数-不填则为1,超出边界后对值-不填则为null) over(partition by s.id Order by s.login_date)窗口函数,根据distinct 后的id分组,对其login_date排序(升序)。如果某id的日期偏移行数n=偏移后的日期-开始的日期 n, 则该用户在连续的n天内都有活跃记录。
- 用distinct id, login_date, lead( ) as last_day 为表格形式。