关于窗口函数的基础详细讲解可以看我的第一篇文章:
1、连续活跃或登录
现有用户活跃时间信息表user_active:user_id(用户id), active_time(活跃时间)
需求:计算连续活跃2天及以上的用户。
create table user_active(
user_id int ,
active_time datetime
);
insert into user_active values
(10001,'2021-01-01 07:00:00'),
(10001,'2021-01-01 07:06:40'),
(10001,'2021-01-03 15:40:20'),
(10001,'2021-01-04 17:30:30'),
(10001,'2021-01-03 05:12:30'),
(10002,'2021-01-02 09:48:40'),
(10002,'2021-01-02 08:37:50'),
(10002,'2021-01-03 15:26:20'),
(10002,'2021-01-04 16:14:10'),
(10002,'2021-01-04 19:43:50');
"""
1、提取用户日期并去重(记录的是时间,而我们只需要知道用户是否在某天登陆过即可),按用户id分组,日期升序排序
2、用日期—排名号-1得到新的日期,新日期相同的便是连续登录或活跃
3、按照用户和新日期分组计数,>=2的就是连续活跃2次以上
"""
select user_id, day_group, count(*) as continue_days
from (
select *
,date_sub(dt,interval rk-1 day) day_group
from (
select user_id
,date(active_time) dt
,ROW_NUMBER() over(partition by user_id order by date(active_time) asc) rk
from user_active
group by user_id, date(active_time)
) a # 1、提取日期并排名
) b # 3、得到新日期组
group by user_id, day_group # 3、按照用户和新日期分组计数,过滤>=2
having count(*) >= 2
- 从结果可以看出,用户10002从2021-01-02这天开始连续登录了3天;10001连续登录了2天。
2、计算累计指标
商品每天销售量的表sale_info: product_id(产品id),sale_date(销售日期),quantity(销售量)
需求:使用SQL统计出每个商品截至每天的累积销量和总销售量。
create table sale_info(
product_id varchar(20),
sale_date date,
quantity int
);
insert into sale_info values
('A','2021-12-01',23),
('A','2021-12-02',34),
('A','2021-12-03',56),
('B','2021-12-01',32),
('B','2021-12-02',34),
('B','2021-12-03',78),
('C','2021-12-01',76),
('C','2021-12-02',89),
('C','2021-12-03',99);
select product_id
,sale_date
,quantity
,sum(quantity) over(partition by product_id order by sale_date asc) as cum_quantity # 根据产品分区,日期排序,输出截至当前日的累计销量
,sum(quantity) over(partition by product_id) as sum_quantity # 只分区,不排序,输出总销量
from sale_info
- (只贴出了部分结果)
3、去除最高值和最低值之后的平均值
学生分数记录表stu_score4:name(姓名),subject(学科),score(分数)
需求:求各科的平均分(去除最高分和最低分)
create table stu_score4(
name VARCHAR(20) not null,
subject VARCHAR(20) not null,
score int
);
insert into stu_score4 values
('安柏','语文',89),('安柏','数学',85),('安柏','英语',89),
('凯亚','语文',88),('凯亚','数学',67),('凯亚','英语',96),
('诺艾尔','语文',84),('诺艾尔','数学',77),('诺艾尔','英语',86),
('梨月','语文',83),('梨月','数学',67),('梨月','英语',66),
('钟离','语文',82),('钟离','数学',47),('钟离','英语',96);
"""
1、既然要去除最高分和最低分,所以需要窗口函数对学科分区、分数排序;升序排除第一名,降序排除第一名。
2、根据学科进行分组,计算平均分
"""
select subject
,cast(avg(score) as decimal(10,2) ) as avg_score
from (
select name
, subject
, score
, dense_rank() over(partition by subject order by score) as rk1 # 按学科分数升序排序,rk1=1的就是最低分 用dens_rank()原因:有并列最高低分都舍掉 。rank()也可以
, dense_rank() over(partition by subject order by score desc) as rk2 # 按学科分数降序排序 rk2=1的就是最高分
from stu_score4
) a
where rk1 != 1 and rk2 != 1 # 去除最高分和最低分
group by subject
order by avg_score desc
4、环比、同比
产品销售金额表sale_money:product_id(产品id),(date)日期,(money)金额。
记录了产品2020年和2021年元旦期间(1.1-1.3)销售金额。
需求:求各产品的2021年与2020年销售额相比的年环比和日同比。
create table sale_money(
product_id varchar(20),
date date,
money decimal
);
insert into sale_money values
('A','2020-01-01',23),('A','2020-01-02',44),('A','2020-01-03',55),
('A','2021-01-01',45),('A','2021-01-02',66),('A','2021-01-03',76),
('B','2020-01-01',56),('B','2020-01-02',77),('B','2020-01-03',78),
('B','2021-01-01',65),('B','2021-01-02',87),('B','2021-01-03',98);
# 1、年环比
"""
1、根据产品id和年份分组,对金额求和即得到当年的总金额;
2、年环比 = (今年/去年) - 1 所以需要向后移动函数lag()将年总金额按年份升序,向前移动一年;在表格中是向下移动一行;
"""
select product_id
,year(date) year
,sum(money) as sum_money
,lag(sum(money)) over(partition by product_id order by year(date) asc) as last_year_money # 得到去年的总销售额
,round((sum(money)/(lag(sum(money)) over(partition by product_id order by year(date) asc))-1)*100,2) as 环比
from sale_money
group by product_id,year(date)
# 2、日同比
"""
1、根据产品id和日期分组,日同比 = (今年某日/去年同日) - 1
"""
select *
from (
select product_id
,year(date) year
,date_format(date,'%m-%d') as day
,money
,lag(money) over(partition by product_id,date_format(date,'%m-%d') order by year(date) asc) as last_money # 得到去年同日的销售额 lag()函数向后移动函数 即把某列数据向下移动一行
,concat(round((money/lag(money) over(partition by product_id,date_format(date,'%m-%d') order by year(date) asc)-1)*100,2),'%') as 日同比
from sale_money
group by product_id, year(date),date_format(date,'%m-%d')
) a
where 日同比 is not null # 只保留有同比的数据
- 如果只想保留2021年的数据,可以where 筛选以下
5、留存率
给出如下某个App的
用户注册表user_info:user_id(用户id)、reg_time(注册时间)
登录日志表login_log:user_id(用户id)、login_time(登陆时间)
需求:计算该App的次日留存率、三日、7日留存率。
从用户注册当天作为第一天,这里的次日、三日、七日指的是 第二天、第三天、第七天,日期差分别为1,2,6。
根据留存天数的定义不同,输出结果也不同,具体怎么算日期,看公司内部定义。
-- 用户注册表
create table user_info(user_id varchar(10) primary key,reg_time datetime);
insert into user_info values
('u_01','2020-01-01 09:15:00'),('u_02','2020-01-01 00:04:00'),('u_03','2020-01-01 22:16:00'),
('u_04','2020-01-01 20:32:00'),('u_05','2020-01-01 13:59:00'),('u_06','2020-01-01 21:28:00'),
('u_07','2020-01-01 14:03:00'),('u_08','2020-01-01 11:00:00'),('u_09','2020-01-01 23:57:00'),
('u_10','2020-01-01 04:46:00'),('u_11','2020-01-02 14:21:00'),('u_12','2020-01-02 11:15:00'),
('u_13','2020-01-02 07:26:00'),('u_14','2020-01-02 10:34:00'),('u_15','2020-01-02 08:22:00'),
('u_16','2020-01-02 14:23:00'),('u_17','2020-01-03 09:20:00'),('u_18','2020-01-03 11:21:00'),
('u_19','2020-01-03 12:17:00'),('u_20','2020-01-03 15:26:00');
-- 登陆日志表
create table login_log(user_id varchar(10),login_time datetime,primary key(user_id,login_time));
insert into login_log values
('u_02','2020-01-02 00:14:00'),('u_10','2020-01-02 08:32:00'),('u_03','2020-01-02 09:20:00'),
('u_08','2020-01-02 10:07:00'),('u_04','2020-01-02 10:29:00'),('u_09','2020-01-02 11:45:00'),
('u_05','2020-01-02 12:19:00'),('u_01','2020-01-02 14:29:00'),('u_15','2020-01-03 00:26:00'),
('u_14','2020-01-03 11:18:00'),('u_11','2020-01-03 13:18:00'),('u_16','2020-01-03 14:33:00'),
('u_06','2020-01-04 07:51:00'),('u_18','2020-01-04 08:11:00'),('u_07','2020-01-04 09:27:00'),
('u_10','2020-01-04 10:59:00'),('u_20','2020-01-04 11:51:00'),('u_03','2020-01-04 12:37:00'),
('u_17','2020-01-04 15:07:00'),('u_08','2020-01-04 16:35:00'),('u_01','2020-01-04 19:29:00'),
('u_14','2020-01-05 08:03:00'),('u_12','2020-01-05 10:27:00'),('u_15','2020-01-05 16:33:00'),
('u_19','2020-01-06 09:03:00'),('u_20','2020-01-06 15:26:00'),('u_04','2020-01-08 11:03:00'),
('u_05','2020-01-08 12:54:00'),('u_06','2020-01-08 19:22:00'),('u_13','2020-01-09 10:20:00'),
('u_15','2020-01-09 16:40:00'),('u_18','2020-01-10 21:34:00');
"""
次日留存率 = 第一天注册了且第二天留下的人 / 第一天注册人数
三日留存率 = 第一天注册了且第三天留下的人 / 第一天注册人数
七日留存率 = 第一天注册了且第七天留下的人 / 第一天注册人数
1、既然要算留存率,肯定要将注册日和登陆日做日期差,所以首先将两张表根据用户id连接
2、根据注册日期分组,对用户id直接去重计数(用户可能一天登录了多次)得到当天的注册人数;
3、利用日期差函数datediff(end,start)计算注册日和登陆日的日期差,
若datediff() = 1 则是次日留存用户;若datediff() = 2 则是3日留存用户; 若datediff() = 6 则是七日留存用户;
4、对n日留存用户id去重计数 / 当天注册人数 即n日留存率
"""
select date(u.reg_time) as dt
,count(DISTINCT u.user_id) as 当日活跃人数
,count(DISTINCT case when datediff(l.login_time,u.reg_time)=1 then u.user_id end) as 次日留存人数
,round(count(DISTINCT case when datediff(l.login_time,u.reg_time)=1 then u.user_id end)/count(DISTINCT u.user_id)*100,2) as 次日留存率
,count(DISTINCT case when datediff(l.login_time,u.reg_time)=3 then u.user_id end) as 三日留存人数
,round(count(DISTINCT case when datediff(l.login_time,u.reg_time)=3 then u.user_id end)/count(DISTINCT u.user_id)*100,2) as 三日留存率
,count(DISTINCT case when datediff(l.login_time,u.reg_time)=7 then u.user_id end) as 七日留存人数
,round(count(DISTINCT case when datediff(l.login_time,u.reg_time)=7 then u.user_id end)/count(DISTINCT u.user_id)*100,2) as 七日留存率
from user_info u left join login_log l on u.user_id = l.user_id
group by date(u.reg_time)
### 留存率好像没用到窗口函数唉
有个疑问:留存率随着日期变化一定是越来越低吗?
6、分类TopN
假设有一个网店,上线了100多个商品,每个顾客浏览任何一个商品时都会产生一条浏览记录,
浏览记录存储的表名为product_view:访客的用户id为userid,浏览的商品名称是productid。
需求:输出商品浏览次数top3的所有用户信息,输出商品id、用户id、浏览次数(>1),按浏览次数降序排序
create table product_view(
userid varchar(20),
productid varchar(20)
);
insert into product_view values('10001','A'),
('10002','A'),('10001','D'),('10007','A'),('10005','A'),
('10003','B'),('10002','A'),('10008','B'),('10006','C'),
('10004','C'),('10004','B'),('10003','A'),('10004','A'),
('10003','A'),('10004','A'),('10003','B'),('10003','C'),
('10002','D'),('10006','C'),('10003','A'),('10002','A');
# 2、
select productid
,userid
,count(*) as num
from product_view
where productid in
(
select productid
from (
select productid
,count(*) as view_num
,dense_rank() over(order by count(*) desc) rk
from product_view
group by productid # 1、根据产品id分组并对浏览次数进行排序
) a
where rk <= 3) # 取出浏览次数排名前三的产品id, 次数相同的并列排名输出
group by productid,userid having num > 1
order by num desc
- 第一步的结果排名并列输出