文章目录
问题
如何通过SQL获取每个月连续登陆超过5天的用户？
例如，某个用户3月的登录数据如下（一个用户在一天之内可能会登录多次）：
| user_id | login_time |
|---|---|
| 0001 | 2021-03-01 09:20:36 |
| 0001 | 2021-03-01 13:45:36 |
| 0001 | 2021-03-02 10:20:36 |
| 0001 | 2021-03-03 07:20:36 |
| 0001 | 2021-03-09 09:20:36 |
| 0001 | 2021-03-10 09:20:36 |
| 0001 | 2021-03-11 09:20:36 |
| 0001 | 2021-03-11 20:20:36 |
| 0001 | 2021-03-12 09:20:36 |
| 0001 | 2021-03-13 09:20:36 |
| 0001 | 2021-03-14 09:20:36 |
| 0001 | … |
实现方式
1. 实现SQL
先上实现SQL语句：
select
distinct month, user_id
from (
select
user_id,
date_format(new_login_time, "yyyy-MM") month,
row_number over(partition by user_id, new_login_time order by new_login_time asc) as rn_1
from (
select
user_id,
date_sub(login_time, rn) new_login_time
from (
select
user_id,
login_time,
row_number over(partition by user_id order by login_time asc) as rn
from (
select
distinct user_id,
date_format(login_time, "yyyy-MM-dd") login_time
from t
) t1
) t2
) t3
) t4
where rn_1 >= 5
2. 实现步骤
- 由于一天之内同一个用户会登录多次，我们将login_time格式化成时分秒（yyyy-MM-dd）的格式，并对整个数据进行去重，输出数据如下：
| user_id | login_time |
|---|---|
| 0001 | 2021-03-01 |
| 0001 | 2021-03-02 |
| 0001 | 2021-03-03 |
| 0001 | 2021-03-09 |
| 0001 | 2021-03-10 |
| 0001 | 2021-03-11 |
| 0001 | 2021-03-12 |
| 0001 | 2021-03-13 |
| 0001 | 2021-03-14 |
| 0001 | … |
- 使用窗口函数对步骤1输出的数据进行组内排序，SQL如下：
//rn为行号标记
row_number over(partition by user_id order by login_time asc) as rn
排序之后的数据如下：
| user_id | login_time | rn |
|---|---|---|
| 0001 | 2021-03-01 | 1 |
| 0001 | 2021-03-02 | 2 |
| 0001 | 2021-03-03 | 3 |
| 0001 | 2021-03-09 | 4 |
| 0001 | 2021-03-10 | 5 |
| 0001 | 2021-03-11 | 6 |
| 0001 | 2021-03-12 | 7 |
| 0001 | 2021-03-13 | 8 |
| 0001 | 2021-03-14 | 9 |
| 0001 | … | … |
- 我们将login_time列减去rn列，输出以下数据：
| user_id | login_time | new_login_time |
|---|---|---|
| 0001 | 2021-03-01 | 2021-02-28 |
| 0001 | 2021-03-02 | 2021-02-28 |
| 0001 | 2021-03-03 | 2021-02-28 |
| 0001 | 2021-03-09 | 2021-03-05 |
| 0001 | 2021-03-10 | 2021-03-05 |
| 0001 | 2021-03-11 | 2021-03-05 |
| 0001 | 2021-03-12 | 2021-03-05 |
| 0001 | 2021-03-13 | 2021-03-05 |
| 0001 | 2021-03-14 | 2021-03-05 |
| 0001 | … | … |
- 对步骤3输出的数据进行组内排序，SQL如下：
//rn_1为行号标记
row_number over(partition by user_id, new_login_time order by new_login_time asc) as rn_1
排序之后输出数据如下：
| user_id | new_login_time | rn_1 |
|---|---|---|
| 0001 | 2021-02-28 | 1 |
| 0001 | 2021-02-28 | 2 |
| 0001 | 2021-02-28 | 3 |
| 0001 | 2021-03-05 | 1 |
| 0001 | 2021-03-05 | 2 |
| 0001 | 2021-03-05 | 3 |
| 0001 | 2021-03-05 | 4 |
| 0001 | 2021-03-05 | 5 |
| 0001 | 2021-03-05 | 6 |
| 0001 | … | … |
- 过滤出那些rn_1大于等于5的记录，并对user_id进行去重，就能得出每月连续登陆大于等于5天的用户了。