2021-09-12

有一张表有3个字段 UID（用户ID）， Date（日期）， Site（网站），有最近1年的多个网站的访问记录。
求：按每个网站，每天输出以下指标

• 新增用户数量
• 当日用户数
• 最近3天登录用户数
  说明：网站新增用户，即此用户第一次访问该网站。
  建表：

create table log(site varchar(10),uid varchar(10),log_date date);
insert into log values('A' , '01' , '2021-06-01');
insert into log values('A' , '01' , '2021-06-03');
insert into log values('A' , '02' , '2021-06-01');
insert into log values('A' , '03' , '2021-06-03');
insert into log values('A' , '01' , '2021-06-05');
insert into log values('A' , '07' , '2021-06-11');
insert into log values('A' , '04' , '2021-06-10');
insert into log values('A' , '05' , '2021-06-10');
insert into log values('A' , '05' , '2021-06-11');
insert into log values('B' , '05' , '2021-06-01');
insert into log values('B' , '06' , '2021-06-03');
insert into log values('B' , '02' , '2021-06-01');

思路：

1. 查询出每个网站第一次访问的所有用户

   使用窗口函数，按照site,uid分组，log_date升序得到每个网站每个用户的访问顺序，rnk = 1即代表是首次访问
   注：窗口函数partition by 可有多个分组字段

2. 按照网站，日期分组统计新增用户数

select site,log_date,count(uid) as add_uid
from
(select *,
       row_number() over(partition by site,uid order by log_date) as rnk
from log) t
where rnk = 1
group by site,log_date
order by site,log_date

select site,log_date,count(distinct uid) as uid_sum
from log
group by site,log_date
order by site,log_date

select site,
       log_date,
       sum(uid_sum) over(partition by site order by log_date rows 2 preceding) as uid3_sum
from
(select site,log_date,count(distinct uid) as uid_sum
from log
group by site,log_date) t