mysql-sql-练习题-2-窗口函数

窗口函数

访问量max sum
- 建表
- 窗口函数
- 连接
直播间人数第1、3名
- 建表
- 排名+sum

访问量max sum

每个用户截止到每月为止，最大单月访问次数，累计到该月的总访问次数
在这里插入图片描述

建表

create table visit(uid1 varchar(5) comment '用户id',month1 varchar(10) comment '月份',visit integer comment '访问量'
) comment '用户每月访问量';insert into visit
values('A','2000-01',33),('A','2000-02',10),('A','2000-03',38),('B','2000-01',30),('B','2000-02',15),('B','2000-03',44);

窗口函数

select*,max(visit) over(partition by uid1 order by month1) visit_max, -- 加字段 窗口函数sum(visit) over(partition by uid1 order by month1) visit_sum -- 分组 1-当前行
from visit;select*,max(visit) over w visit_max,sum(visit) over w visit_sum
from visit -- 替代重复部分
window w as (partition by uid1 order by month1);

在这里插入图片描述

连接

select*,(select max(visit) -- 加字段 输出maxfrom visit bwhere a.uid1 = b.uid1 and a.month1 >= b.month1) visit_max, -- 连接 聚合(select sum(visit)from visit bwhere a.uid1 = b.uid1 and a.month1 >= b.month1) visit_sum
from visit a;

直播间人数第1、3名

在这里插入图片描述

建表

create table room(live_id varchar(5) comment '直播间id',user_id varchar(3) comment '用户id',date_stamp varchar(17) comment '时间戳',entry_type varchar(6) comment '进入直播间状态：enter登陆 out退出'
) comment '直播间 人数峰值';insert into room
values('1','1','20220101 10:00:00','enter'),('1','2','20220101 10:00:00','enter'),('1','3','20220101 10:00:00','enter'),('1','1','20220101 10:01:00','out'),('1','4','20220101 10:01:01','enter'),('1','5','20220101 10:05:00','enter'),('1','6','20220101 10:05:50','enter'),('1','2','20220101 10:06:00','out'),('1','7','20220101 10:07:40','enter'),('1','3','20220101 10:09:00','out'),('2','12','20220101 10:00:00','enter'),('2','11','20220101 10:00:00','enter'),('2','11','20220101 10:01:00','out'),('2','14','20220101 10:01:01','enter'),('2','15','20220101 10:05:00','enter'),('2','16','20220101 10:05:50','enter'),('2','12','20220101 10:06:00','out'),('2','17','20220101 10:07:40','enter'),('2','18','20220101 10:07:50','out'),('2','19','20220101 10:08:01','enter'),('2','13','20220101 10:09:00','out');

排名+sum

with tmp as(-- 3. if替换 为了sumselect*,if(entry_type = 'enter',1,-1) entry_iffrom room
),tmp1 as(-- 2. 每个时刻人数select*,sum(entry_if) over(partition by live_id order by date_stamp) count_entry -- 分组 1-当前行from tmp),tmp2 as(-- 1. 最值 排名select*,dense_rank() over(partition by live_id order by count_entry desc) dr from tmp1)
select *
from tmp2
where dr in (1,3);