SQL 窗口函数应用

需求1：查询出2019年每月的支付总额和当年累积支付总额

1.查出每年每月支付总额

2. 计算每月累计支付总额

3.筛选2019

select  *,sum(t.m_sum) over (partition by t.y 
                       order by t.m  
                       rows between unbounded preceding and current row) as 累计总支付
from (select year (pay_time) as y,month (pay_time) as m,sum(pay_amount) as m_sum
       from user_trade ut group by year (pay_time),month (pay_time)) as t
where t.y=2019;

 需求2：查询出2018-2019年每月的支付总额和当年累积支付总额

1.在需求1的基础上筛选条件改为在2018和2019

select  *,sum(t.每月支付金额) over (partition by t.y 
                       order by t.m  
                       rows between unbounded preceding and current row) as 累计总支付
from (select year (pay_time) as y,month (pay_time) as m,sum(pay_amount) as 每月支付金额
       from user_trade ut group by year (pay_time),month (pay_time)) as t
where t.y in (2018,2019);

需求3：查询出2019年每个月的近三月移动平均支付金额

1.计算当月和前两个月支付金额的均值

select *,avg(t.每月支付金额) over (partition by t.y order by t.m rows
between 2 preceding and current row) as 平均金额 from (select year (pay_time) as y,month (pay_time) as m,sum(pay_amount) as 每月支付金额
       from user_trade ut where year (pay_time)=2019 group by year (pay_time),month (pay_time)) as t;

需求4：查询出每四个月的最大月总支付金额

1.计算当月和前3个月内最大的支付金额

select *,max(t.每月支付金额) over (partition by t.y order by t.m rows
between 3 preceding and current row) as 最大金额 from (select year (pay_time) as y,month (pay_time) as m,round(sum(pay_amount))  as 每月支付金额
       from user_trade ut  group by year (pay_time),month (pay_time)) as t; 

 需求5：2020年1月，购买商品品类数的用户排名

select user_name ,count(distinct goods_category) as c ,
row_number  () over (order by count(distinct goods_category)) as o1,
rank  () over (order by count(distinct goods_category)) as o2,
dense_rank  () over (order by count(distinct goods_category)) as o3
from user_trade ut 
where year(pay_time)=2020 and month (pay_time)=1 group by user_name order by c;

需求6：查询出将2020年2月的支付用户，按照支付金额分成5组后的结果

1.不需要进行分组

2.对支付金额进行排序

select user_name ,sum(pay_amount) ,
ntile(5) over(order by sum(pay_amount) desc) level
from user_trade ut 
where year(pay_time)=2020 and month (pay_time)=2 group by user_name ;

需求7：查询出2020年支付金额排名前30%的所有用户

1.分组为10，选取分组等于1，2，3

select * from (select user_name ,sum(pay_amount),
ntile(10) over(order by sum(pay_amount) desc) level from user_trade ut 
where year(pay_time)=2020 group by user_name ) t where t.level in (1,2,3);

需求8：查询出King和West的时间偏移(前N行)

select user_name ,pay_time ,
lag (pay_time,1,pay_time) over (partition by user_name order by pay_time) as lag1 ,
lag (pay_time) over (partition by user_name order by pay_time) as lag2 ,
lag (pay_time,2,pay_time) over (partition by user_name order by pay_time) as lag3 ,
lag (pay_time,3,pay_time) over (partition by user_name order by pay_time) as lag4 
from user_trade utc where user_name in ('King','West');

需求9：King和West的时间偏移(后N行)

select  user_name,pay_time,
lead(pay_time,1,pay_time) over(partition by user_name order by pay_time) as lead1,
lead(pay_time) over(partition by user_name order by pay_time) as lead2,
lead(pay_time,2,pay_time) over(partition by user_name order by pay_time) as lead3,
lead(pay_time,3,pay_time) over(partition by user_name order by pay_time) as lead4
from user_trade ut  where user_name in ('King','West');

需求10：查询出支付时间间隔超过100天的用户数

1.计算支付时间差一天

2.筛选差值大于100的不重复用户数

select count(distinct t.user_name)  from (select user_name,pay_time,
lead(pay_time,1,pay_time) over(partition by user_name order by pay_time) as lead1
from user_trade ut) as t where datediff(t.lead1,t.pay_time) >100 and t.lead1 is not null ;

需求11：查询出每年支付时间间隔最长的用户

1.计算支付时间差值为1

2.对支付时间间隔进排序

3.筛选排序等于1

select t2.user_name,t2.y,t2.diff
from (select *, year(t.pay_time) as y,datediff(t.lead1,t.pay_time) as diff,
			rank () over (partition by year(t.pay_time) order by datediff(t.lead1,t.pay_time) desc) as r
		from (select user_name,pay_time,
					lead(pay_time,1,pay_time) over(partition by user_name,year(pay_time)  order by pay_time) as lead1
				from user_trade ut) as t ) as t2 where t2.r=1;

需求12：查出每年每个月支付最大的用户

1.查出每年每月每个用户的支付总额

2.对支付总额进行排序

3.选取排序号为1的

select * 
from
 (select  *,
        rank () over (partition by  t.y,t.m order by t.u_sum desc) as r	
  from (select year(pay_time) as y,
               month(pay_time) as m,
               user_name ,
               sum(pay_amount) as u_sum             
      from user_trade 
       group by year(pay_time),month(pay_time),user_name) t	) as t2 where t2.r=1;

建表语句

create table user_trade
(
user_name varchar(20),
piece int,
price double,
pay_amount double,
goods_category varchar(20),
pay_time date
);

数据：链接：https://pan.baidu.com/s/1Pe-nU-RRT2qAoNHWAITwbQ 
提取码：1234