SQL之开窗函数二——在复杂场景中的实际运用

1.使用开窗函数求移动平局值

大家看股票的时候，经常会看到K线图，里面经常用到的5日、30日移动平均趋势图，那如何使用窗口函数求移动平均值呢

（1）现有一张表Stock，求5日 移动平均值

原表数据结构如下：

 

 

查询5日平均价格语句：

select stockname
,TradingDate
,price
,avg(price) over(partition by stockname 
order by tradingdate rows between 4 preceding and current row) 
as fievedaysavgprice
from Stock

结果如下：

 语法总结：

sum(...A...) over(partition by ...B... order by ...C... rows between ... D1... and ...D2..) 

avg(...A...) over(partition by ...B... order by ...C... rows between ... D1... and ...D2..) 

A：需要被加工的字段名称

B：分组的字段名称

C：排序的字段名称

D：计算的行数范围

rows between unbounded prceding and current row --包括本行和之前所有的行

rows between current row and unbounded following --包括本行和之后所有的行

rows between 3 preceding and current row --包括本行以内和前三行

rows between 3 preceding and 1 following --从前三行到下一行 （5行）

2.分组排序窗口函数实际运用 ntile (n)over（......）

ntile(n) over(partition by ...A... order by ...B...)

n:切片的片数

A:分组的字段名称 

B:排序的字段名称

ntile(n) ：用于将分组数据按照顺序切分成n片，返回当前切片值

如果切片不均匀，默认增加到第一个切片的分布

例子：选出2021年退款金额排名前20%的用户 

原数据表:user_refund

 

查询退款20%的语句：具体分析就是按照用户退款金额总数降序分成5组，取第一组的数据

select * 
from 
(
select [user_name] 
,sum(refund_amount) as refund_amount
,ntile(5) over(order by sum(refund_amount) desc) as level
from user_refund 
where year(refund_time)=2021
group by [user_name]
)
as a where a.level=1