本文整理自网络,侵删。
目录
- 什么是窗口函数
- 窗口函数组成部分
- 1.创建数据分区
- 2.分区内的排序
- 3.指定窗口大小
- 窗口函数分类
- 1.聚合窗口函数
- 2.排名窗口函数
- 3.取值窗口函数
什么是窗口函数
SQL窗口函数为在线分析处理(OLAP)和商业智能(BI)提供了复杂分析和报表统计的功能,例如产品的累计销售额统计、分类排名、同比/环比分析等。这些功能通常很难通过聚合函数和分组操作来实现。
窗口函数(Window Function)可以像聚合函数一样对一组数据进行分析并返回结果,二者的不同之处在于,窗口函数不是将一组数据汇总成单个结果,而是为每一行数据都返回一个结果。聚合函数和窗口函数的区别如下图所示。
以SUM函数为例演示这两种函数的差异,以下语句中的SUM()是一个聚合函数:
SELECT SUM(salary) AS "所有员工月薪总和" FROM employee
以上SUM函数可作为聚合函数使用,表示将所有员工的数据汇总成一个结果。因此,查询返回了所有员工的月薪总和:
以下语句中的SUM()是一个窗口函数:
SELECT emp_name AS "员工姓名", SUM(salary) OVER () AS "所有员工月薪总和" FROM employee;
其中,关键字OVER表明SUM()是一个窗口函数。括号内为空,表示将所有数据作为一个分组进行汇总。该查询返回的结果如下:
以上查询结果返回了所有的员工姓名,并且通过聚合函数SUM()为每个员工都返回了相同的汇总结果。
从以上示例中可以看出,窗口函数的语法与聚合函数的不同之处在于,它包含了一个OVER子句。OVER子句用于指定一个数据分析的窗口,完整的窗口函数定义如下:
其中window_function是窗口函数的名称,expression是可选的分析对象(字段名或者表达式),OVER子句包含分区(PARTITION BY)、排序(ORDER BY)以及窗口大小(frame_clause)3个选项。
提示:聚合函数将同一个分组内的多行数据汇总成单个结果,窗口函数则保留了所有的原始数据。在某些数据库中,窗口函数也被称为在线分析处理(OLAP)函数,或者分析函数(Analytic Function)。
窗口函数组成部分
1.创建数据分区
窗口函数OVER子句中的PARTITION BY选项用于定义分区,其作用类似于查询语句中的GROUP BY子句。如果我们指定了分区选项,窗口函数将会分别针对每个分区单独进行分析。
例如,以下语句按照不同部门分别统计员工的月薪合计:
SELECT emp_name AS "员工姓名", salary "月薪", dept_id AS "部门编号", SUM(salary) OVER ( PARTITION BY dept_id ) AS "部门合计" FROM employee;
其中,PARTITION BY选项表示按照部门进行分区。查询返回的结果如下:
查询结果中的前3行数据属于同一个部门,因此它们对应的部门合计字段都等于80000(30000+26000+24000)。其他部门的员工采用同样的方式进行统计。
提示:在窗口函数OVER子句中指定了PARTITION BY选项之后,我们无须使用GROUP BY子句也能获得分组统计结果。
如果不指定PARTITION BY选项,表示将全部数据作为一个整体进行分析。
2.分区内的排序
窗口函数OVER子句中的ORDER BY选项用于指定分区内数据的排序方式,作用类似于查询语句中的ORDER BY子句。
排序选项通常用于数据的分类排名。例如,以下语句用于分析员工在部门内的月薪排名:
SELECT emp_name AS "员工姓名", salary "月薪", dept_id AS "部门编号", RANK() OVER ( PARTITION BY dept_id ORDER BY salary DESC ) AS "部门内排名" FROM employee;
其中,RANK函数用于计算数据的名次,PARTITION BY选项表示按照部门进行分区,ORDER BY选项表示在部门内按照月薪从高到低进行排序。查询返回的结果如下:
查询结果中的前3行数据属于同一个部门:“刘备”的月薪最高,在部门内排名第1;“关羽”排名第2;“张飞”排名第3。其他部门的员工采用同样的方式进行排名。
提示:窗口函数OVER子句中的ORDER BY选项和查询语句中的ORDER BY子句的使用方法相同。因此,也可以使用NULLS FIRST或者NULLS LAST选项指定空值的排序位置。
3.指定窗口大小
窗口函数OVER子句中的frame_clause选项用于指定一个移动的分析窗口,窗口总是位于分区的范围之内,是分区的一个子集。在指定了分析窗口之后,窗口函数不再基于分区进行分析,而是基于窗口内的数据进行分析。
窗口选项可以用于实现各种复杂的分析功能,例如计算累计到当前日期为止的销售额总和,每个月及其前后各N个月的平均销售额等。
指定窗口大小的具体选项如下:
其中,ROWS表示以数据行为单位计算窗口的偏移量,RANGE表示以数值(例如10天、5km等)为单位计算窗口的偏移量。
frame_start选项用于定义窗口的起始位置,可以指定以下内容之一:
- UNBOUNDED PRECEDING——表示窗口从分区的第一行开始。
- N PRECEDING——表示窗口从当前行之前的第N行开始。
- CURRENT ROW——表示窗口从当前行开始。
frame_end选项用于定义窗口的结束位置,可以指定以下内容之一:
- CURRENT ROW——表示窗口到当前行结束。
- M FOLLOWING——表示窗口到当前行之后的第M行结束。
- UNBOUNDED FOLLOWING——表示窗口到分区的最后一行结束。
下图说明了这些窗口大小选项的含义
下面语句表示分析窗口从当前分区的第一行开始,直到当前行结束,即对应到图中前面5行记录。
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
窗口函数分类
1.聚合窗口函数
许多常见的聚合函数也可以作为窗口函数使用,包括AVG()、SUM()、COUNT()、MAX()以及MIN()等函数。
SQL窗口函数-聚合窗口函数
2.排名窗口函数
排名窗口函数用于对数据进行分组排名,包括ROW_NUMBER()、RANK()、DENSE_RANK()、PERCENT_RANK()、CUME_DIST()以及NTILE()等函数。
SQL窗口函数-排名窗口函数
3.取值窗口函数
取值窗口函数用于返回指定位置上的数据行,包括FIRST_VALUE()、LAST_VALUE()、LAG()、LEAD()、NTH_VALUE()等函数。
SQL窗口函数-取值窗口函数
相关阅读 >>
更多相关阅读请进入《sql》频道 >>

数据库系统概念 第6版
机械工业出版社
本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。
转载请注明出处:木庄网络博客 » SQL窗口函数的使用方法