sql窗口函数的使用方法是,sql窗口函数的使用方法是什么

sql窗口函数的使用方法是,sql窗口函数的使用方法是什么,SQL窗口函数的使用方法

MySQL8.0版本之后增加了window函数，简化了数据分析中查询语句的编写。本文主要介绍SQL窗口函数的使用，通过实例代码详细介绍，具有一定的参考价值。感兴趣的朋友可以参考一下。

什么是窗口函数

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的观点，版权归原作者所有，仅为传播更多信息之目的，如有侵权请联系，我们将第一时间修改或删除，多谢。

sql窗口函数的使用方法是,sql窗口函数的使用方法是什么

目录

什么是窗口函数

窗口函数组成部分

1.创建数据分区

2.分区内的排序

3.指定窗口大小

窗口函数分类

1.聚合窗口函数

2.排名窗口函数

3.取值窗口函数

相关文章阅读