Hive SQL 面试题 - 统计每日新增用户

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