共计 11223 个字符，预计需要花费 29 分钟才能阅读完成。
本文丸趣 TV 小编为大家详细介绍“SQL 窗口函数怎么使用”，内容详细，步骤清晰，细节处理妥当，希望这篇“SQL 窗口函数怎么使用”文章能帮助大家解决疑惑，下面跟着丸趣 TV 小编的思路慢慢深入，一起来学习新知识吧。
什么是窗口函数
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 窗口函数 - 取值窗口函数
示例表和脚本
-- 员工信息表
CREATE TABLE employee
( emp_id NUMBER
, emp_name VARCHAR2(50) NOT NULL
, sex VARCHAR2(10) NOT NULL
, dept_id INTEGER NOT NULL
, manager INTEGER
, hire_date DATE NOT NULL
, job_id INTEGER NOT NULL
, salary NUMERIC(8,2) NOT NULL
, bonus NUMERIC(8,2)
, email VARCHAR2(100) NOT NULL
, comments VARCHAR2(500)
, create_by VARCHAR2(50) NOT NULL
, create_ts TIMESTAMP NOT NULL
, update_by VARCHAR2(50)
, update_ts TIMESTAMP
) ;
COMMENT ON TABLE employee IS 员工信息表
COMMENT ON COLUMN employee.emp_id IS 员工编号，自增主键
COMMENT ON COLUMN employee.emp_name IS 员工姓名
COMMENT ON COLUMN employee.sex IS 性别
COMMENT ON COLUMN employee.dept_id IS 部门编号
COMMENT ON COLUMN employee.manager IS 上级经理
COMMENT ON COLUMN employee.hire_date IS 入职日期
COMMENT ON COLUMN employee.job_id IS 职位编号
COMMENT ON COLUMN employee.salary IS 月薪
COMMENT ON COLUMN employee.bonus IS 年终奖金
COMMENT ON COLUMN employee.email IS 电子邮箱
COMMENT ON COLUMN employee.comments IS 备注信息
COMMENT ON COLUMN employee.create_by IS 创建者
COMMENT ON COLUMN employee.create_ts IS 创建时间
COMMENT ON COLUMN employee.update_by IS 修改者
COMMENT ON COLUMN employee.update_ts IS 修改时间
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (1, 刘备 , 男 , 1, NULL, DATE 2000-01-01 , 1, 30000, 10000, liubei@shuguo.com , NULL, Admin , TIMESTAMP 2000-01-01 10:00:00 , NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (2, 关羽 , 男 , 1, 1, DATE 2000-01-01 , 2, 26000, 10000, guanyu@shuguo.com , NULL, Admin , TIMESTAMP 2000-01-01 10:00:00 , NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (3, 张飞 , 男 , 1, 1, DATE 2000-01-01 , 2, 24000, 10000, zhangfei@shuguo.com , NULL, Admin , TIMESTAMP 2000-01-01 10:00:00 , NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (4, 诸葛亮 , 男 , 2, 1, DATE 2006-03-15 , 3, 24000, 8000, zhugeliang@shuguo.com , NULL, Admin , TIMESTAMP 2006-03-15 10:00:00 , NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (5, 黄忠 , 男 , 2, 4, DATE 2008-10-25 , 4, 8000, NULL, huangzhong@shuguo.com , NULL, Admin , TIMESTAMP 2008-10-25 10:00:00 , NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (6, 魏延 , 男 , 2, 4, DATE 2007-04-01 , 4, 7500, NULL, weiyan@shuguo.com , NULL, Admin , TIMESTAMP 2007-04-01 10:00:00 , NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (7, 孙尚香 , 女 , 3, 1, DATE 2002-08-08 , 5, 12000, 5000, sunshangxiang@shuguo.com , NULL, Admin , TIMESTAMP 2002-08-08 10:00:00 , NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (8, 孙丫鬟 , 女 , 3, 7, DATE 2002-08-08 , 6, 6000, NULL, sunyahuan@shuguo.com , NULL, Admin , TIMESTAMP 2002-08-08 10:00:00 , NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (9, 赵云 , 男 , 4, 1, DATE 2005-12-19 , 7, 15000, 6000, zhaoyun@shuguo.com , NULL, Admin , TIMESTAMP 2005-12-19 10:00:00 , Admin , TIMESTAMP 2006-12-31 10:00:00
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (10, 廖化 , 男 , 4, 9, DATE 2009-02-17 , 8, 6500, NULL, liaohua@shuguo.com , NULL, Admin , TIMESTAMP 2009-02-17 10:00:00 , NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (11, 关平 , 男 , 4, 9, DATE 2011-07-24 , 8, 6800, NULL, guanping@shuguo.com , NULL, Admin , TIMESTAMP 2011-07-24 10:00:00 , NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (12, 赵氏 , 女 , 4, 9, DATE 2011-11-10 , 8, 6600, NULL, zhaoshi@shuguo.com , NULL, Admin , TIMESTAMP 2011-11-10 10:00:00 , NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (13, 关兴 , 男 , 4, 9, DATE 2011-07-30 , 8, 7000, NULL, guanxing@shuguo.com , NULL, Admin , TIMESTAMP 2011-07-30 10:00:00 , NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (14, 张苞 , 男 , 4, 9, DATE 2012-05-31 , 8, 6500, NULL, zhangbao@shuguo.com , NULL, Admin , TIMESTAMP 2012-05-31 10:00:00 , NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (15, 赵统 , 男 , 4, 9, DATE 2012-05-03 , 8, 6000, NULL, zhaotong@shuguo.com , NULL, Admin , TIMESTAMP 2012-05-03 10:00:00 , NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (16, 周仓 , 男 , 4, 9, DATE 2010-02-20 , 8, 8000, NULL, zhoucang@shuguo.com , NULL, Admin , TIMESTAMP 2010-02-20 10:00:00 , NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (17, 马岱 , 男 , 4, 9, DATE 2014-09-16 , 8, 5800, NULL, madai@shuguo.com , NULL, Admin , TIMESTAMP 2014-09-16 10:00:00 , NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (18, 法正 , 男 , 5, 2, DATE 2017-04-09 , 9, 10000, 5000, fazheng@shuguo.com , NULL, Admin , TIMESTAMP 2017-04-09 10:00:00 , NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (19, 庞统 , 男 , 5, 18, DATE 2017-06-06 , 10, 4100, 2000, pangtong@shuguo.com , NULL, Admin , TIMESTAMP 2017-06-06 10:00:00 , NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (20, 蒋琬 , 男 , 5, 18, DATE 2018-01-28 , 10, 4000, 1500, jiangwan@shuguo.com , NULL, Admin , TIMESTAMP 2018-01-28 10:00:00 , NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (21, 黄权 , 男 , 5, 18, DATE 2018-03-14 , 10, 4200, NULL, huangquan@shuguo.com , NULL, Admin , TIMESTAMP 2018-03-14 10:00:00 , NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (22, 糜竺 , 男 , 5, 18, DATE 2018-03-27 , 10, 4300, NULL, mizhu@shuguo.com , NULL, Admin , TIMESTAMP 2018-03-27 10:00:00 , NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (23, 邓芝 , 男 , 5, 18, DATE 2018-11-11 , 10, 4000, NULL, dengzhi@shuguo.com , NULL, Admin , TIMESTAMP 2018-11-11 10:00:00 , NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (24, 简雍 , 男 , 5, 18, DATE 2019-05-11 , 10, 4800, NULL, jianyong@shuguo.com , NULL, Admin , TIMESTAMP 2019-05-11 10:00:00 , NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (25, 孙乾 , 男 , 5, 18, DATE 2018-10-09 , 10, 4700, NULL, sunqian@shuguo.com , NULL, Admin , TIMESTAMP 2018-10-09 10:00:00 , NULL, NULL);读到这里，这篇“SQL 窗口函数怎么使用”文章已经介绍完毕，想要掌握这篇文章的知识点还需要大家自己动手实践使用过才能领会，如果想了解更多相关内容的文章，欢迎关注丸趣 TV 行业资讯频道。
