MySQL8.0版本之后增加了window函数,简化了数据分析中查询语句的编写。本文主要介绍SQL窗口函数的使用,通过实例代码详细介绍,具有一定的参考价值。感兴趣的朋友可以参考一下。
目录
窗口功能有哪些组成部分?1.创建数据分区。2.在分区内排序。3.指定窗口大小。分类窗口功能。1.聚合窗口函数。2.等级窗口函数。3.取值窗口函数。
什么是窗口函数
SQL窗口功能为联机分析处理(OLAP)和商业智能(BI)提供了复杂的分析和报表统计功能,如累计销售统计、产品分类和排名、同比/环比分析等。这些功能通常很难通过聚合函数和分组运算来实现。
窗口函数可以像聚合函数一样分析一组数据并返回结果。它们之间的区别在于,window函数不是将一组数据汇总成一个结果,而是为每一行数据返回一个结果。聚合函数和窗口函数的区别如下图所示。
以SUM函数为例,演示这两个函数的区别。以下语句中的SUM()是一个聚合函数:
选择SUM(salary)作为“所有员工的月工资总额”
来自员工
上面的SUM函数可以用作聚合函数,这意味着将所有员工的数据聚合到一个结果中。因此,该查询返回所有雇员的总月薪:
以下语句中的SUM()是一个窗口函数:
选择emp_name作为“员工姓名”,
总和(工资)超过()作为“所有员工的月工资总额”
来自员工;
其中,关键字OVER表示SUM()是一个窗口函数。空括号表示所有数据将汇总为一组。该查询返回以下结果:
上述查询结果返回了所有雇员的姓名,并且通过聚合函数sum()为每个雇员返回了相同的汇总结果。
从上面的例子可以看出,window函数的语法不同于aggregate函数,因为它包含一个OVER子句。OVER子句用于指定数据分析的窗口。完整的窗口功能定义如下:
其中window_function是窗口函数的名称,expression是可选的分析对象(字段名或表达式),OVER子句包含三个选项:PARTITION BY、ORDER BY和window size (frame _ clause)。
提示:聚合函数将同一分组中的多行数据聚合成一个结果,而窗口函数保留所有原始数据。在某些数据库中,窗口函数也被称为联机分析处理(OLAP)函数或分析函数。
窗口函数组成部分
1.创建数据分区
window OVER子句中的PARTITION BY选项用于定义分区,其作用类似于查询语句中的GROUP BY子句。如果我们指定分区选项,窗口函数将分别分析每个分区。
例如,下面的语句根据不同的部门统计雇员的月工资总额:
选择emp_name为“员工姓名”,salary为“月薪”,dept_id为“部门编号”,
总额(工资)超过(
按部门标识划分
)作为“部门合计”
来自员工;
其中PARTITION BY option表示按部门划分。查询结果如下:
查询中的前三行数据属于同一个部门,因此它们对应的部门合计字段都等于80000(30000 26000 24000)。其他部门的员工用同样的方法进行统计。
提示:在window函数的OVER子句中指定PARTITION BY选项后,我们就可以不使用GROUP BY子句而得到分组统计结果。
如果不指定PARTITION BY选项,则意味着所有数据将作为一个整体进行分析。
2.分区内的排序
window函数的OVER子句中的ORDER BY选项用于指定分区中数据的排序方式,类似于查询语句中的ORDER BY子句。
排序选项通常用于对数据进行分类和排序。例如,以下语句用于分析部门内员工的月薪排名:
选择emp_name为“员工姓名”,salary为“月薪”,dept_id为“部门编号”,
排名()超过(
按部门标识划分
按薪金排序DESC
)为“部门内排名”
来自员工;
其中,RANK函数用于计算数据的排序,按选项划分表示按部门划分,按选项排序表示按部门月薪排序。查询结果如下:
查询中前三行数据属于同一个部门:“刘备”月薪最高,部门排名第一;《关羽》排名第二;“张飞”排第三。其他部门的员工排名也是这样。
提示:window函数的OVER子句中的ORDER BY选项的用法与查询语句中的ORDER BY子句相同。因此,也可以使用nullsfirst或NULLS FIRST选项来指定空值的排序位置。
3.指定窗口大小
函数的window OVER子句中的frame_clause选项用于指定移动分析窗口。窗口总是在分区的范围内,并且是分区的子集。指定分析窗口后,窗口函数不再基于分区,而是基于窗口中的数据。
该窗口可用于实现各种复杂的分析功能,如计算截止到当前日期的总销售额、每月的平均销售额以及前后N个月的平均销售额等。
指定窗口大小的具体选项如下:
ROWS表示以数据线单位计算的窗口偏移量,RANGE表示以数字单位计算的窗口偏移量(如10天、5公里等。).
frame_start选项用于定义窗口的起始位置。您可以指定以下选项之一:
无界前导——表示窗口从分区的第一行开始。表示窗口从当前行之前的第n行开始。当前行——表示窗口从当前行开始。
frame_end选项用于定义窗口的结束位置。您可以指定以下选项之一:
当前行——表示窗口在当前行结束。——后面的m表示窗口在当前行之后的第m行结束。UNBOUNDED FOLLOWING——表示从窗口到分区的最后一行的结尾。
下图说明了这些窗口大小选项的含义。
以下语句指示分析窗口从当前分区的第一行开始,直到当前行的末尾,也就是说,它对应于图表中的前五行记录。
未绑定的前一行和当前行之间的行
窗口函数分类
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窗口函数-值窗口函数
示例表和脚本
-员工信息表
创建员工表
(员工标识号
,emp_name VARCHAR2(50)不为空
,sex VARCHAR2(10)不为空
,部门标识整数不为空
,经理整数
,雇用日期日期不为空
,job_id整数不为空
,薪金数字(8,2)不为空
,奖金数字(8,2)
,电子邮件VARCHAR2(100)不为空
,注释VARCHAR2(500)
,create_by VARCHAR2(50)不为空
,create_ts时间戳不为空
,update_by VARCHAR2(50)
,更新_ts时间戳
) ;
备注表员工是‘员工信息表’;
对员工一栏的评论。EMP _ id是‘员工号,自增主键’;
对列员工的评论;EMP _ name是“员工姓名”;
对列employee.sex的注释是“性别”;
对列员工的评论;dept _ id是“部门编号”;
对员工一栏的评论。经理是‘上级经理’;
对员工一栏的评论。hire _ date是“雇佣日期”;
对列员工的评论;job _ id是“职位号”;
对员工一栏的评论。工资是‘月薪’;
对员工一栏的评论。奖金是‘年终奖’;
对员工一栏的评论。电子邮件是‘电子邮件地址’;
对列员工的评论;comments是“备注”;
对员工一栏的评论。create _ by是‘creator’;
'对员工.创建_ts列的注释是'创建时间;
'对员工. update_by列注释是'修改者;
'对员工.更新_ts列注释为'修改时间;
插入到雇员(雇员标识,雇员姓名,性别,部门标识,经理,雇用日期,工作标识,薪金,奖金,电子邮件,评论,创建者,创建者,更新者,更新者)值(1,' 1刘备', '男,1,空,日期' 2000-01-01 ',1,30000,10000,'刘备@shuguo.com ',空,' Admin ',时间戳2000-01-01 10:00:00 ',NULL,NULL);
插入到雇员(雇员标识,雇员姓名,性别,部门标识,经理,雇用日期,工作标识,薪金,奖金,电子邮件,评论,创建者,创建者,更新者,更新者)值(2,'关羽', '男,1,1,日期' 2000-01-01 ',2,26000,10000,'关羽@shuguo.com ',空,' Admin ',时间戳2000-01-01 10:00:00 ',NULL,NULL);
插入到雇员(雇员标识,雇员姓名,性别,部门标识,经理,雇用日期,工作标识,薪金,奖金,电子邮件,评论,创建者,创建者,更新者,更新者)值(3,'张飞', '男,1,1,日期' 2000-01-01 ',2,24000,10000,'张飞@shuguo.com ',空,' Admin ',时间戳2000-01-01 10:00:00 ',NULL,NULL);
插入到雇员(雇员标识,雇员姓名,性别,部门标识,经理,雇用日期,工作标识,薪金,奖金,电子邮件,评论,创建者,创建者,更新者,更新者)值(4,'诸葛亮', '男,2,1,日期' 2006-03-15 ',3,24000,8000,'诸葛亮@shuguo.com ',空,' Admin ',时间戳2006-03-15 10:00:00 ',空,NULL);
插入到雇员(雇员标识,雇员姓名,性别,部门标识,经理,雇用日期,工作标识,薪金,奖金,电子邮件,评论,创建者,创建者,更新者,更新者)值(5,'黄忠', '男,2,4,日期2008-10-25 ',48000,空,' huangzhong@shuguo.com ',空,'管理',时间戳2008-10-25 10:00:00 ',空,NULL);
插入到雇员(雇员标识,雇员姓名,性别,部门标识,经理,雇用日期,工作标识,薪金,奖金,电子邮件,评论,创建者,创建者,更新者,更新者)值(6,'魏延', '男,2,4,日期' 2007年4月1日',47500,空'韦偃@shuguo.com ',空,' Admin ',时间戳2007-04-01 10:00:00 ',空,NULL);
插入到雇员(雇员标识,雇员姓名,性别,部门标识,经理,雇用日期,工作标识,薪金,奖金,电子邮件,评论,创建者,创建者,更新者,更新者)值(7,'孙尚香', '女,3,1,日期' 2002-08-08 ',5,12000,5000,'孙尚香@shuguo.com ',空,' Admin ',时间戳2002-08-08 10:00:00 ',NULL,NULL);
插入到雇员(雇员标识,雇员姓名,性别,部门标识,经理,雇用日期,工作标识,薪金,奖金,电子邮件,评论,创建者,创建者,更新者,更新者)值(8,'孙丫鬟', '女,3,7,日期' 2002-08-08 ',6,6000,空,' sunyahuan@shuguo.com ',空,'管理',时间戳2002-08-08 10:00:00 ',NULL,NULL);
插入到雇员(雇员标识,雇员姓名,性别,部门标识,经理,雇用日期,工作标识,薪金,奖金,电子邮件,评论,创建者,创建者,更新者,更新者)值(9,'赵云', '男,4,1,日期' 2005-12-19 ',7,15000,6000,'赵云@shuguo.com ',空,'管理',时间戳'管理',时间戳' 2006-12-31 10:00:00 ');
插入到雇员(雇员标识,雇员姓名,性别,部门标识,经理,雇用日期,工作标识,薪金,奖金,电子邮件,评论,创建者,创建者,更新者,更新者)值(10,'廖化', '男,4,9,日期' 2009-02-17 ',86500,空'廖化@shuguo.com ',空,' Admin ',时间戳2009-02-17 10:00:00 ',空,NULL);
插入到雇员(雇员标识,雇员姓名,性别,部门标识,经理,雇用日期,工作标识,薪金,奖金,电子邮件,评论,创建者,创建者,更新者,更新者)值(11,'关平', '男,4,9,日期2011-07-24 ',8,6800,空,' guanping@shuguo.com ',空,'管理',时间戳2011-07-24 10:00:00 ',空,NULL);
插入到雇员(雇员标识,雇员姓名,性别,部门标识,经理,雇用日期,工作标识,薪金,奖金,电子邮件,评论,创建者,创建者,更新者,更新者)值(12,'赵氏', '女,4,9,日期' 2011-11-10 ',86600,空'赵石@shuguo.com ',空,' Admin ',时间戳2011-11-10 10:00:00 ',NULL,NULL);
插入到雇员(雇员标识,雇员姓名,性别,部门标识,经理,雇用日期,工作标识,薪金,奖金,电子邮件,评论,创建者,创建者,更新者,更新者)值(13,'关兴', '男,4,9,日期' 2011-07-30 ',87000,空'关兴@shuguo.com ',空,' Admin ',时间戳2011-07-30 10:00:00 ',空,NULL);
插入到雇员(雇员标识,雇员姓名,性别,部门标识,经理,雇用日期,工作标识,薪金,奖金,电子邮件,评论,创建者,创建者,更新者,更新者)值(14,'张苞', '男,4,9,日期' 2012-05-31 ',86500,空'张苞@shuguo.com ',空,' Admin ',时间戳2012-05-31 10:00:00 ',空,NULL);
插入到雇员(雇员标识,雇员姓名,性别,部门标识,经理,雇用日期,工作标识,薪金,奖金,电子邮件,评论,创建者,创建者,更新者,更新者)值(15,'赵统', '男,4,9,日期2012-05-03 ',8,6000,空,' zhaotong@shuguo.com ',空,'管理',时间戳2012-05-03 10:00:00 ',空,NULL);
插入到雇员(雇员标识,雇员姓名,性别,部门标识,经理,雇用日期,工作标识,薪金,奖金,电子邮件,评论,创建者,创建者,更新者,更新者)值(16,'周仓', '男,4,9,日期' 2010-02-20 ',88000,空'周仓@shuguo.com ',空,' Admin ',时间戳2010-02-20 10:00:00 ',空,NULL);
插入到雇员(雇员标识,雇员姓名,性别,部门标识,经理,雇用日期,工作标识,薪金,奖金,电子邮件,评论,创建者,创建者,更新者,更新者)值(17,'马岱', '男,4,9,日期2014-09-16 ',8,5800,空,' madai@shuguo.com ',空,'管理',时间戳2014-09-16 10:00:00 ',空,NULL);
插入到雇员(雇员标识,雇员姓名,性别,部门标识,经理,雇用日期,工作标识,薪金,奖金,电子邮件,评论,创建者,创建者,更新者,更新者)值(18,'法正', '男,5,2,日期' 2017-04-09 ',9,10000,5000,'法正@shuguo.com ',空,' Admin ',时间戳2017-04-09 10:00:00 ',空,NULL);
插入到雇员(雇员标识,雇员姓名,性别,部门标识,经理,雇用日期,工作标识,薪金,奖金,电子邮件,评论,创建者,创建者,更新者,更新者)值(19,'庞统', '男,5,18,日期' 2017-06-06 ',10,4100,2000,'庞统@shuguo.com ',空,' Admin ',时间戳2017-06-06 10:00:00 ',空,NULL);
插入到雇员(雇员标识,雇员姓名,性别,部门标识,经理,雇用日期,工作标识,薪金,奖金,电子邮件,评论,创建者,创建者,更新者,更新者)值(20,'蒋琬', '男,5,18,日期2018-01-28 ',10,4000,1500,' jiangwan@shuguo.com ',空,'管理',时间戳' 2018-01-28 10:00:00 ',空,NULL);
插入到雇员(雇员标识,雇员姓名,性别,部门标识,经理,雇用日期,工作标识,薪金,奖金,电子邮件,评论,创建者,创建者,更新者,更新者)值(21,'黄权', '男,5,18,日期' 2018-03-14 ',10,4200,空'黄权@shuguo.com ',空,' Admin ',时间戳2018-03-14 10:00:00 ',空,NULL);
插入到雇员(雇员标识,雇员姓名,性别,部门标识,经理,雇用日期,工作标识,薪金,奖金,电子邮件,评论,创建者,创建者,更新者,更新者)值(22,'糜竺', '男,5,18,日期' 2018-03-27 ',10,4300,空'糜竺@shuguo.com ',空,' Admin ',时间戳2018-03-27 10:00:00 ',空,NULL);
插入到雇员(雇员标识,雇员姓名,性别,部门标识,经理,雇用日期,工作标识,薪金,奖金,电子邮件,评论,创建者,创建者,更新者,更新者)值(23,'邓芝', '男,5,18,日期' 2018-11-11 ',104000,空'邓芝@shuguo.com ',空,' Admin ',时间戳2018-11-11 10:00:00 ',空,NULL);
插入到雇员(雇员标识,雇员姓名,性别,部门标识,经理,雇用日期,工作标识,薪金,奖金,电子邮件,评论,创建者,创建者,更新者,更新者)值(24,'简雍', '男,5,18,日期' 2019-05-11 ',104800,空'简雍@shuguo.com ',空,' Admin ',时间戳2019-05-11 10:00:00 ',空,NULL);
插入到雇员(雇员标识,雇员姓名,性别,部门标识,经理,雇用日期,工作标识,工资,奖金,电子邮件,评论,创建者,创建者,更新者,更新者)值(25,'孙淦','男性',5,18,日期' 2018年10月9日',10,4700,空,'孙倩@ shuguo.com ',空,'管理',时间戳' 2
关于SQL窗口函数的使用,本文就讲到这里。有关SQL窗口函数的更多信息,请搜索我们以前的文章或继续浏览下面的相关文章。希望你以后能支持我们!
郑重声明:本文由网友发布,不代表盛行IT的观点,版权归原作者所有,仅为传播更多信息之目的,如有侵权请联系,我们将第一时间修改或删除,多谢。