02. SQLboy自连接实现窗口用法

视频篇文档

自连接实现窗口用法

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