方法)
一开始是这样写的
SELECT date, COUNT(user_id) FROM login WHERE (date, user_id) IN( SELECT MIN(date), user_id FROM login GROUP BY user_id ) GROUP BY date;
但没办法通过,因为无法输出没有新用户登录的日期
- 因此要外连结一个完整的login作为主表
SELECT l.date, IFNULL(a.new_num, 0) FROM login as l LEFT JOIN( SELECT date, COUNT(user_id) AS new_num FROM login WHERE (date, user_id) IN( SELECT MIN(date), user_id FROM login GROUP BY user_id) GROUP BY date)a ON a.date = l.date GROUP BY l.date ORDER BY l.date;
- 借鉴大神的窗口函数解法
select a.date, sum(case when t_rank=1 then 1 else 0 end) new from (select date, row_number() over(partition by user_id order by date) t_rank from login) a group by date;