SQL窗口函數怎么使用

本文小編為大家詳細介紹“SQL窗口函數怎么使用”，內容詳細，步驟清晰，細節處理妥當，希望這篇“SQL窗口函數怎么使用”文章能幫助大家解決疑惑，下面跟著小編的思路慢慢深入，一起來學習新知識吧。

什么是窗口函數

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窗口函數怎么使用”文章已經介紹完畢，想要掌握這篇文章的知識點還需要大家自己動手實踐使用過才能領會，如果想了解更多相關內容的文章，歡迎關注億速云行業資訊頻道。