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

访问量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) -- 加字段 输出max
  from visit b
  where a.uid1 = b.uid1 and a.month1 >= b.month1) visit_max, -- 连接 聚合
  (select sum(visit)
  from visit b
  where 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替换 为了sum
  select
    *,
    if(entry_type = 'enter',1,-1) entry_if
  from 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);