HQL - 查询每天的新增用户数

水善利万物而不争，处众人之所恶，故几于道💦

题目：

从用户登录明细表（user_login_detail）中查询每天的新增用户数，若一个用户在某天登录了，且在这一天之前没登录过，则任务该用户为这一天的新增用户。

with a as(
select 
  user_id,
  substring(login_ts,1,10) login_day --先切出年月日的格式
from user_login_detail
), b as(
select 
  user_id,
  login_day,
  row_number() over(partition by user_id order by login_day) rn --编号，取首次登录的
from a group by user_id,login_day  --然后以用户id和日期分组，去掉同一天登录多次的
), c as(
select * from b where rn=1
)

select login_day login_date_first , count(user_id) user_count from c group by login_day

总结：

1. 先取到日期，精确到日，因为统计的是日新增用户

2. 然后根据日期和用户id分组，去掉每日登录多次的记录；同时编号

3. 取rn=1，即首次登录，也就是这天是新增的用户

4. 然后以日期分组，count(user_id)，结果就是这一天的新增用户

date_format()：需要两个参数，第一个参数是要格式化的日期，第二个参数是指定输出格式的字符串，例如：date_format(‘2021-07-01 12:34:56’, ‘yyyy/MM/dd HH:mm:ss’)

下面是用date_format()切割日期，其他都一样。

with a as (
select
  user_id,
  date_format(login_ts,"yyyy-MM-dd") login_day  --取日期也可以用date_format()函数
from user_login_detail
), b as(
select
  user_id,
  login_day,
  row_number() over(partition by user_id order by login_day) rn
from a group by user_id,login_day
), c as(
select * from b where rn=1
)

select login_day login_date_first, count(user_id) user_count from c group by login_day