牛客sql练习 - 69

描述

• 牛客每天有很多人登录，请你统计一下牛客每个日期登录新用户个数

drop table if exists login;
CREATE TABLE `login` (
`id` int(4) NOT NULL,
`user_id` int(4) NOT NULL,
`client_id` int(4) NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`id`));

考点

• 分组计数除了用count group by也可以用 sum(case )

# 对满足条件的日期groupby计数，当不满足条件是时不会被计数，即为0的日期会被省略
SELECT a.date, COUNT(a.user_id) new
FROM (SELECT * , MIN(date) OVER(PARTITION BY user_id) first_day FROM login) a
WHERE DATEDIFF(a.date,a.first_day)=0
GROUP BY a.date;

# 对不满足条件的计数为0
SELECT a.date,SUM(CASE WHEN DATEDIFF(date,first_day) = 0 THEN 1 ELSE 0 END)
FROM(
SELECT *,MIN(date) OVER(PARTITION BY user_id) first_day FROM login) a
GROUP BY a.date