视频篇文档
自连接实现窗口用法
sum() avg() count() over(PARTITION BY)
lag lead
row_number dense_rank rank
....
sum() over()
案例一 :累积消费
create table mytable_0719(
uid int ,
event_date date,
consumption double
);
INSERT INTO mytable_0719 (uid,event_date,consumption) VALUES
(1,'2023-07-01',10.0),
(1,'2023-07-02',10.0),
(1,'2023-07-03',20.0),
(1,'2023-07-04',10.0),
(1,'2023-07-05',10.0),
(2,'2023-07-04',10.0),
(2,'2023-07-05',10.0),
(3,'2023-07-05',10.0);
select uid,event_date,consumption, sum(consumption) over(PARTITION BY uid ORDER BY event_date asc) as csm from mytable_0719
自连接用法
select
t1.uid , t1.event_date,sum(t2.consumption) csm
from
mytable_0719 t1 join mytable_0719 t2
on t1.uid = t2.uid and t1.event_date >= t2.event_date
group by t1.uid , t1.event_date
案例二 : 查询最近一个月（即最大月）之外，剩下每个月的近三个月的累计薪水（不足三个月也要计算）
create table mytable_0720(
uid int ,
month int,
salary double
);
INSERT INTO mytable_0720 (uid,month,salary) VALUES
(1,1,20),
(2,1,20),
(1,2,30),
(2,2,30),
(3,2,40),
(1,3,40),
(3,3,60),
(1,4,60),
(3,4,70);
结果
1 3 90.0
1 2 50.0
1 1 20.0
2 1 20.0
3 3 100.0
3 2 40.0
select
uid,
month,
sum(salary) over(partition by uid
order by
month rows between 2 preceding and current row ) Salary
from
(
select
uid,
month,
salary,
lag(salary, 1, 0) over(partition by uid
order by
month desc) rn
from
mytable_0720
) t1
where
rn != 0
order by
uid,
month desc
select
a.uid as uid,
a.month as month,
SUM(b.salary) as salary
from
mytable_0720 a,
mytable_0720 b
where
a.uid = b.uid
and a.month >= b.month
and a.month < b.month + 3
and (a.uid,
a.month) not in (
select
uid,
MAX(month)
from
mytable_0720
group by
uid)
group by
a.uid,
a.month
order by
a.uid,
a.month desc
TOP_N问题
沿用mytable_0719
create table mytable_0719(
uid int ,
event_date date,
consumption double
);
案例一: 计算每个uid,每天消费金额的消费排名,相同消费金额的并列
select
t1.uid ,t1.event_date,t1.consumption,count(distinct t2.consumption) as t_rank
from
mytable_0719 t1 join mytable_0719 t2
on t1.consumption <= t2.consumption and t1.uid = t2.uid
group by t1.uid ,t1.event_date,t1.consumption
order by t_rank
案例二:取前二
select
t1.uid ,t1.event_date,t1.consumption,count(distinct t2.consumption) as t_rank
from
mytable_0719 t1 join mytable_0719 t2
on t1.consumption <= t2.consumption and t1.uid = t2.uid
group by t1.uid ,t1.event_date,t1.consumption
order by t_rank
havingcount(distinct t2.consumption) <= 2
案例三:取第二
select
t1.uid ,t1.event_date,t1.consumption,count(distinct t2.consumption) as t_rank ,sum(t1.consumption <= t2.consumption)
from
mytable_0719 t1 join mytable_0719 t2
on t1.consumption <= t2.consumption and t1.uid = t2.uid
group by t1.uid ,t1.event_date,t1.consumption
having sum(t1.consumption <= t2.consumption) = 2
order by t_rank
leedcode579
leedcode1159
牛客网23
牛客网74
