1.基本语法
<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)
2.主要功能
核心要义:建立辅助列去帮助我们做筛选、比较
a.同时具备分组和排序的功能;
b.不同于group by,分组后不减少原表行数;
c.不用表连接就能建立辅助分析列
3.窗口函数分类
-(1)聚合类:聚合函数作为窗口函数,有累计的作用
avg(列名):累计求平均值、sum(列名):累计求和、count(列名):累计计数
max(列名)、min(列名)
-(2)排名类:排名类窗口函数不需要任何参数,生成自增编号列
row_number():按照值排序时产生一个自增编号,不会重复
rank():按照值排序时产生一个自增编号,值相等时会重复,会产生空位
dense_rank():按照值排序时产生一个自增编号,值相等时会重复,不会产生空位
-(3)其他类:如统计窗口内往前/往后的第n行的值,多用于时间序列
lag(列名,往前的第n行数默认为1,default默认为null)
lead(列名,往后的行数默认为1,default默认为null)
ntile(n):用于将分组数据按照顺序切分成n片,返回当前切片值,如果切片不均匀,默认增加第一个切片的分布
4.常见应用
(1)并列排名问题:rank()/dense_rank() over(partition by X order by Y)
(2)TOPN问题:row_number() over(partition by X order by Y)+筛选
(3)TOP20%时间的订单信息:ntile(5) over(order by orderdate DESC)
参考文章:
1.巩固SQL - 窗口函数&变量&数据透视图:https://zhuanlan.zhihu.com/p/147696603
2.LAG()和LEAD() 分析函数详解:https://blog.csdn.net/weixin_38653290/article/details/85042224