窗口函数详细解释(面试必备)

窗口函数的应用场景:

(1)用于分区排序
(2)动态Group By
(3)Top N
(4)累计计算
(5)层次查询

窗口函数:

介绍:窗口函数可以进行排序,生成序列号等一般的聚合函数无法实现的高级操作。

窗口函数也称为OLAP函数,意思是对数据库数据进行实时分析处理。窗口函数就是为了实现OLAP而添加的标准SQL功能。

语法:其中[]中的内容可以省略

<窗口函数> over ([partition by <列清单>]
                        order by <排序用列清单>)

FIRST_VALUE: 取分组内排序后,截止到当前行,第一个值
LAST_VALUE: 取分组内排序后,截止到当前行,最后一个值
LEAD(col,n,DEFAULT) : 用于统计窗口内往下第n行值。第一个参数为列名,第二个参数为往下第n行(可选,默认为1),第三个参数为默认值(当往下第n行为NULL时候,取默认值,如不指定,则为NULL)
LAG(col,n,DEFAULT) : 与lead相反,用于统计窗口内往上第n行值。第一个参数为列名,第二个参数为往上第n行(可选,默认为1),第三个参数为默认值(当往上第n行为NULL时候,取默认值,如不指定,则为NULL)
CUME_DIST():返回(小于等于当前行值的行数)/(当前分组内的总行数)
应用场景:比如,统计小于等于当前薪水的人数,所占总人数的比例
PERCENT_RANK():返回(组内当前行的rank值-1)/(分组内做总行数-1)

OVER从句
1、使用标准的聚合函数COUNT、SUM、MIN、MAX、AVG
2、使用PARTITION BY语句,使用一个或者多个原始数据类型的列,例如PARTITION BY [col1,col2…]
3、使用PARTITION BY与ORDER BY语句,使用一个或者多个数据类型的分区或者排序列,例如PARTITION BY [col1,col2…] order by …

窗口函数大体可以分为以下两种:

1.能够作为窗口函数的聚合函数(sum,avg,count,max,min)

2.rank,dense_rank。row_number等专用窗口函数。

语法的基本使用方法:使用rank函数
rank函数是用来计算记录排序的函数。如下:

select product_name, product_type, sale_price,
       rank () over (partition by product_type
	                        order by sale_price) as ranking
from Product;

partition by 能够设定排序的对象范围,类似于group by语句,这里就是以product_type划分排序范围。

order by能够指定哪一列,何种顺序进行排序。也可以通过asc,desc来指定升序降序。

窗口函数兼具分组和排序两种功能。通过partition by分组后的记录集合称为窗口。

然而partition by不是窗口函数所必须的:

select product_name, product_type, sale_price,
       rank () over (order by sale_price) as ranking
from Product;

专用函数的种类:

  1.rank函数:计算排序时,如果存在相同位次的记录,则会跳过之后的位次。

  2.dense_rank函数:同样是计算排序,即使存在相同位次的记录,也不会跳过之后的位次。

  3.row_number函数:赋予唯一的连续位次。

举例说明:

select product_name, product_type, sale_price,
       rank () over (order by sale_price) as ranking,
	   dense_rank () over (order by sale_price) as dense_ranking,
	   row_number () over (order by sale_price) as row_num
from Product;

输出结果如下:
在这里插入图片描述

接下来介绍一下用窗口函数计算移动平均:

窗口函数就是将表以窗口为单位进行分割,并在其中进行排序的函数。其中还包含在窗口中指定更加详细的汇总范围的备选功能,该备选功能中的汇总范围称为框架。

指定最靠近的3行做为汇总对象:

select product_id, product_name, sale_price,
       avg (sale_price) over (order by product_id
	                          rows 2 preceding) as moving_avg
from Product;

指定框架(汇总范围):这里使用的rows(行)和preceding(之前)两个关键字,将框架指定为截止到之前?行,因此rows 2 preceding就是将框架指定为截止到之前2行,也就是将作为汇总对象的记录限定为如下的最靠近3行

解释:

1.自身(当前记录)

2.之前1行的记录

3.之前2行的记录

同理使用关键字following(之后)替换preceding,就可以将框架改为截止到之后?行。

将当前记录的前后行作为汇总对象:

select product_id, product_name, sale_price,
       avg(sale_price) over (order by product_id
	                          rows between 1 preceding and 1 following) as moving_avg--使用between规划范围,语句意思为rows 1 preceding
																				     --到rows 1 following
from Product;

解释:

1.之前1行的记录

2.自身(当前记录)

3.之后1行的记录

  • 5
    点赞
  • 20
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值