本篇文章給大家?guī)砹岁P(guān)于SQL的相關(guān)知識，其中主要整理了窗口函數(shù)的相關(guān)問題，SQL窗口函數(shù)為在線分析處理（OLAP）和商業(yè)智能（BI）提供了復(fù)雜分析和報表統(tǒng)計的功能，例如產(chǎn)品的累計銷售額統(tǒng)計、分類排名、同比/環(huán)比分析等，下面一起來看一下，希望對大家有幫助。

推薦學(xué)習(xí)：《SQL教程》

什么是窗口函數(shù)

SQL窗口函數(shù)為在線分析處理（OLAP）和商業(yè)智能（BI）提供了復(fù)雜分析和報表統(tǒng)計的功能，例如產(chǎn)品的累計銷售額統(tǒng)計、分類排名、同比/環(huán)比分析等。這些功能通常很難通過聚合函數(shù)和分組操作來實現(xiàn)。

窗口函數(shù)（Window Function）可以像聚合函數(shù)一樣對一組數(shù)據(jù)進(jìn)行分析并返回結(jié)果，二者的不同之處在于，窗口函數(shù)不是將一組數(shù)據(jù)匯總成單個結(jié)果，而是為每一行數(shù)據(jù)都返回一個結(jié)果。聚合函數(shù)和窗口函數(shù)的區(qū)別如下圖所示。

以SUM函數(shù)為例演示這兩種函數(shù)的差異，以下語句中的SUM()是一個聚合函數(shù)：

SELECT SUM(salary) AS "所有員工月薪總和" FROM employee

以上SUM函數(shù)可作為聚合函數(shù)使用，表示將所有員工的數(shù)據(jù)匯總成一個結(jié)果。因此，查詢返回了所有員工的月薪總和：

以下語句中的SUM()是一個窗口函數(shù)：

SELECT emp_name AS "員工姓名",        SUM(salary) OVER () AS "所有員工月薪總和" FROM employee;

其中，關(guān)鍵字OVER表明SUM()是一個窗口函數(shù)。括號內(nèi)為空，表示將所有數(shù)據(jù)作為一個分組進(jìn)行匯總。該查詢返回的結(jié)果如下：

以上查詢結(jié)果返回了所有的員工姓名，并且通過聚合函數(shù)SUM()為每個員工都返回了相同的匯總結(jié)果。

從以上示例中可以看出，窗口函數(shù)的語法與聚合函數(shù)的不同之處在于，它包含了一個OVER子句。OVER子句用于指定一個數(shù)據(jù)分析的窗口，完整的窗口函數(shù)定義如下：

其中window_function是窗口函數(shù)的名稱，expression是可選的分析對象（字段名或者表達(dá)式），OVER子句包含分區(qū)（PARTITION BY）、排序（ORDER BY）以及窗口大小（frame_clause）3個選項。

提示：聚合函數(shù)將同一個分組內(nèi)的多行數(shù)據(jù)匯總成單個結(jié)果，窗口函數(shù)則保留了所有的原始數(shù)據(jù)。在某些數(shù)據(jù)庫中，窗口函數(shù)也被稱為在線分析處理（OLAP）函數(shù)，或者分析函數(shù)（Analytic Function）。

窗口函數(shù)組成部分

1.創(chuàng)建數(shù)據(jù)分區(qū)

窗口函數(shù)OVER子句中的PARTITION BY選項用于定義分區(qū)，其作用類似于查詢語句中的GROUP BY子句。如果我們指定了分區(qū)選項，窗口函數(shù)將會分別針對每個分區(qū)單獨進(jìn)行分析。

例如，以下語句按照不同部門分別統(tǒng)計員工的月薪合計：

SELECT emp_name AS "員工姓名", salary "月薪", dept_id AS "部門編號",        SUM(salary) OVER (          PARTITION BY dept_id        ) AS "部門合計" FROM employee;

其中，PARTITION BY選項表示按照部門進(jìn)行分區(qū)。查詢返回的結(jié)果如下：

查詢結(jié)果中的前3行數(shù)據(jù)屬于同一個部門，因此它們對應(yīng)的部門合計字段都等于80000（30000+26000+24000）。其他部門的員工采用同樣的方式進(jìn)行統(tǒng)計。

提示：在窗口函數(shù)OVER子句中指定了PARTITION BY選項之后，我們無須使用GROUP BY子句也能獲得分組統(tǒng)計結(jié)果。

如果不指定PARTITION BY選項，表示將全部數(shù)據(jù)作為一個整體進(jìn)行分析。

2.分區(qū)內(nèi)的排序

窗口函數(shù)OVER子句中的ORDER BY選項用于指定分區(qū)內(nèi)數(shù)據(jù)的排序方式，作用類似于查詢語句中的ORDER BY子句。

排序選項通常用于數(shù)據(jù)的分類排名。例如，以下語句用于分析員工在部門內(nèi)的月薪排名：

SELECT emp_name AS "員工姓名", salary "月薪", dept_id AS "部門編號",        RANK() OVER (          PARTITION BY dept_id          ORDER BY salary DESC        ) AS "部門內(nèi)排名" FROM employee;

其中，RANK函數(shù)用于計算數(shù)據(jù)的名次，PARTITION BY選項表示按照部門進(jìn)行分區(qū)，ORDER BY選項表示在部門內(nèi)按照月薪從高到低進(jìn)行排序。查詢返回的結(jié)果如下：

查詢結(jié)果中的前3行數(shù)據(jù)屬于同一個部門：“劉備”的月薪最高，在部門內(nèi)排名第1；“關(guān)羽”排名第2；“張飛”排名第3。其他部門的員工采用同樣的方式進(jìn)行排名。

提示：窗口函數(shù)OVER子句中的ORDER BY選項和查詢語句中的ORDER BY子句的使用方法相同。因此，也可以使用NULLS FIRST或者NULLS LAST選項指定空值的排序位置。

3.指定窗口大小

窗口函數(shù)OVER子句中的frame_clause選項用于指定一個移動的分析窗口，窗口總是位于分區(qū)的范圍之內(nèi)，是分區(qū)的一個子集。在指定了分析窗口之后，窗口函數(shù)不再基于分區(qū)進(jìn)行分析，而是基于窗口內(nèi)的數(shù)據(jù)進(jìn)行分析。

窗口選項可以用于實現(xiàn)各種復(fù)雜的分析功能，例如計算累計到當(dāng)前日期為止的銷售額總和，每個月及其前后各N個月的平均銷售額等。

指定窗口大小的具體選項如下：

其中，ROWS表示以數(shù)據(jù)行為單位計算窗口的偏移量，RANGE表示以數(shù)值（例如10天、5km等）為單位計算窗口的偏移量。

frame_start選項用于定義窗口的起始位置，可以指定以下內(nèi)容之一：
●UNBOUNDED PRECEDING——表示窗口從分區(qū)的第一行開始。
●N PRECEDING——表示窗口從當(dāng)前行之前的第N行開始。
●CURRENT ROW——表示窗口從當(dāng)前行開始。

frame_end選項用于定義窗口的結(jié)束位置，可以指定以下內(nèi)容之一：
●CURRENT ROW——表示窗口到當(dāng)前行結(jié)束。
●M FOLLOWING——表示窗口到當(dāng)前行之后的第M行結(jié)束。
●UNBOUNDED FOLLOWING——表示窗口到分區(qū)的最后一行結(jié)束。

下圖說明了這些窗口大小選項的含義

下面語句表示分析窗口從當(dāng)前分區(qū)的第一行開始，直到當(dāng)前行結(jié)束，即對應(yīng)到圖中前面5行記錄。

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

窗口函數(shù)分類

1.聚合窗口函數(shù)

許多常見的聚合函數(shù)也可以作為窗口函數(shù)使用，包括AVG()、SUM()、COUNT()、MAX()以及MIN()等函數(shù)。

SQL窗口函數(shù)-聚合窗口函數(shù)

2.排名窗口函數(shù)

排名窗口函數(shù)用于對數(shù)據(jù)進(jìn)行分組排名，包括ROW_NUMBER()、RANK()、DENSE_RANK()、PERCENT_RANK()、CUME_DIST()以及NTILE()等函數(shù)。

SQL窗口函數(shù)-排名窗口函數(shù)

3.取值窗口函數(shù)

取值窗口函數(shù)用于返回指定位置上的數(shù)據(jù)行，包括FIRST_VALUE()、LAST_VALUE()、LAG()、LEAD()、NTH_VALUE()等函數(shù)。

SQL窗口函數(shù)-取值窗口函數(shù)

示例表和腳本

--員工信息表 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 '上級經(jīng)理'; 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 '創(chuàng)建者'; COMMENT ON COLUMN employee.create_ts IS '創(chuàng)建時間'; 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,'關(guān)羽', '男', 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,'關(guān)平', '男', 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,'關(guān)興', '男', 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,'趙統(tǒng)', '男', 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,'龐統(tǒng)', '男', 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,'黃權(quán)', '男', 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);

推薦學(xué)習(xí)：《SQL教程》