六大类型题带你吃透窗口函数

关于窗口函数的基础详细讲解可以看我的第一篇文章：

万字掌握MYSQL常用函数

文章目录

• • 1、连续活跃或登录
  • 2、计算累计指标
  • 3、去除最高值和最低值之后的平均值
  • 4、环比、同比
  • 5、留存率
  • 6、分类TopN

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_daysfrom (select *,date_sub(dt,interval rk-1 day) day_groupfrom (select user_id,date(active_time) dt,ROW_NUMBER() over(partition by user_id order by date(active_time) asc) rkfrom user_activegroup 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_scorefrom (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_moneygroup 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')    ) awhere 日同比 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_idgroup 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 numfrom product_viewwhere productid in (select productidfrom (select productid      ,count(*) as view_num      ,dense_rank() over(order by count(*) desc) rk     from product_viewgroup by productid   # 1、根据产品id分组并对浏览次数进行排序    ) awhere rk <= 3) # 取出浏览次数排名前三的产品id, 次数相同的并列排名输出 group by productid,userid having num > 1order by num desc

• 第一步的结果排名并列输出