测试数据
drop table if exists login;
CREATE TABLE `login` (
`user_id` int(4) NOT NULL,
`date` date NOT NULL
);
INSERT INTO login VALUES
(2,'2020-10-12'),
(2,'2020-10-12'),
(3,'2020-10-12'),
(1,'2020-10-12'),
(2,'2020-10-13'),
(1,'2020-10-13'),
(3,'2020-10-14'),
(4,'2020-10-14'),
(4,'2020-10-14'),
(4,'2020-10-15');
select * from login ORDER BY user_id;
思路:用户所有登录日期中的最小值=登录的当前日期就是当天的新用户
方法一
去重作为结果temp_0(防止用户一天登录多次)
select DISTINCT user_id,date from login order by user_id
看思路:直接上码
select distinct date,
sum(
case when (user_id,date) in (select user_id,min(date) from login group by user_id) -- 求出用户和他最早的登录日期
then 1 else 0 end
) as new
from (select DISTINCT user_id,date from login order by user_id) temp_0 -- 去重
group by date
order by date
方法二
利用窗口函数rank()
select temp.date,
sum(case when t_rank=1 then 1 else 0 end) new
from
(select user_id,date, row_number() over(partition by user_id order by date) t_rank from login) temp
group by date;