SQL-查询每日新增用户

---

前言

本文主要记录SQL-查询每日新增用户的相关笔记

---

查询每日新增用户

有一张表record，表中有ID与date1两列，计算每日新增用户。

drop table if exists record;
CREATE TABLE record (
id int(4) NOT NULL,
date1 date NOT NULL,
PRIMARY KEY (id));
)
方法1：
select r.date1,ifull(r2.count_num,0) from
record r left join(
select r1.date1,count(*) count_num
from record r1 where r1.date1=
(select min(date1) from record group by id)) r2 on r.date1=r2.date1
group by r1.date1
order by r1.date1
方法2：
select date1,sum(case when date_num=1 then 1 else 0 end) 
from (select date1,
row_number()over(partition by user_id order by date) date_num 
from login)
group by date1
order by date1