两种解法:
- 使用count计算每日新用户个数
- 使用case和窗口函数计算每个用户的登陆次数序号,选取第一次登陆的计数
使用count计算每日新用户个数时,要注意不能遗漏新用户个数为0的日期,可以使用union补充上
SELECT l.mdate AS date,COUNT(user_id) AS new FROM ( SELECT user_id,MIN(date) AS mdate FROM login GROUP BY user_id)AS l GROUP BY l.mdate UNION SELECT date,0 AS new FROM login WHERE date NOT IN ( SELECT MIN(date) FROM login GROUP BY user_id) ORDER BY date
使用case和窗口函数计算
SELECT date, SUM(CASE WHEN r=1 THEN 1 ELSE 0 END )AS new FROM ( SELECT user_id,date, DENSE_RANK() OVER (PARTITION BY user_id ORDER BY DATE) AS r FROM login) AS l GROUP BY date ORDER BY date