1 需求
从用户登录明细表(user_login_detail)中查询每天的新增用户数,若一个用户在某天登录了,且在这一天之前没登录过,则认为该用户为这一天的新增用户。
- 用户登录明细表:
user_login_detail
user_id(用户id) | ip_address(ip地址) | login_ts(登录时间) | logout_ts(登出时间) |
---|---|---|---|
101 | 180.149.130.161 | 2021-09-21 08:00:00 | 2021-09-27 08:30:00 |
102 | 120.245.11.2 | 2021-09-22 09:00:00 | 2021-09-27 09:30:00 |
103 | 27.184.97.3 | 2021-09-23 10:00:00 | 2021-09-27 10:30:00 |
- 期望结果
login_date_first (日期) | user_count (新增用户数) |
---|---|
2021-09-21 | 1 |
2021-09-22 | 1 |
2021-09-23 | 1 |
2021-09-24 | 1 |
2021-09-25 | 1 |
2021-09-26 | 1 |
2021-09-27 | 1 |
2021-10-04 | 2 |
2021-10-06 | 1 |
2 解答
- 思路
观察用户登录明细表,需要使用到的信息有 用户 id、用户登录时间 这两个字段,这里认为用户都被限制为单点登录,所以登出时间字段用不到。
考虑使用开窗函数中的排名函数对每一个用户按照登录时间进行排名,再对排名进行限定,仅取第一次登录的日期作为新用户首次登录,最后根据日期分组,查看每天存在多少个用户即可。
- 实现
1 开窗,此处使用 row_number 进行编号,over 中指定
partition by user_id
表示针对每一个用户进行编号,指定order by login_ts
表示按照 login_ts 排序之后再编号
SELECT
user_id,
substr(login_ts, 1, 10) dt,
row_number() over (partition by user_id order by login_ts) rn
from user_login_detail
user_id | dt | rn |
---|---|---|
101 | 2021-09-21 | 1 |
101 | 2021-09-27 | 2 |
101 | 2021-09-28 | 3 |
101 | 2021-09-29 | 4 |
101 | 2021-09-30 | 5 |
1010 | 2021-10-09 | 1 |
1010 | 2021-09-27 | 2 |
2 聚合,在第一步的结果上,按照 dt 字段进行聚合,结合
sum(if())
进行统计,最后使用HAVING
进行聚合结果的筛选
select dt login_date_first, sum(if(rn == 1, 1, 0)) user_count from (
SELECT
user_id,
substr(login_ts, 1, 10) dt,
row_number() over (partition by user_id ORDER by login_ts) rn
from user_login_detail
) t1 group by dt HAVING sum(if(rn == 1, 1, 0)) != 0 order by dt;
login_date_first | user_count |
---|---|
2021-09-21 | 1 |
2021-09-22 | 1 |
2021-09-23 | 1 |
2021-09-24 | 1 |
2021-09-25 | 1 |
2021-09-26 | 1 |
2021-09-27 | 1 |
2021-10-04 | 2 |
2021-10-06 | 1 |