遇到一个查询需求,需求是 :查询新用户当日登录数,当日之前登陆过的不算新用户。
用户记录表: action表 如下:
name | type | time |
---|---|---|
A | login | 2021-06-08 |
A | login | 2021-06-08 |
B | login | 2021-06-08 |
C | login | 2021-06-08 |
A | login | 2021-06-06 |
F | login | 2021-06-05 |
D | login | 2021-06-04 |
– | – | – |
命令:
select count(DISTINCT(name)) as daily_user_login_sum from action a
inner join (select MIN(time) as t from action group by name) temp on a.time = temp.t
where a.type ='login'
and TO_DAYS(a.time) = TO_DAYS(now())