SQL窗口函數(shù)怎么使用

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

什么是窗口函數(shù)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

3.指定窗口大小

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

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

指定窗口大小的具體選項(xiàng)如下：

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

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

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

下圖說明了這些窗口大小選項(xiàng)的含義

下面語句表示分析窗口從當(dāng)前分區(qū)的第一行開始，直到當(dāng)前行結(jié)束，即對(duì)應(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ù)用于對(duì)數(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 '員工編號(hào)，自增主鍵';
COMMENT ON COLUMN employee.emp_name IS '員工姓名';
COMMENT ON COLUMN employee.sex IS '性別';
COMMENT ON COLUMN employee.dept_id IS '部門編號(hào)';
COMMENT ON COLUMN employee.manager IS '上級(jí)經(jīng)理';
COMMENT ON COLUMN employee.hire_date IS '入職日期';
COMMENT ON COLUMN employee.job_id IS '職位編號(hào)';
COMMENT ON COLUMN employee.salary IS '月薪';
COMMENT ON COLUMN employee.bonus IS '年終獎(jiǎng)金';
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)建時(shí)間';
COMMENT ON COLUMN employee.update_by IS '修改者';
COMMENT ON COLUMN employee.update_ts IS '修改時(shí)間';
 
 
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);

讀到這里，這篇“SQL窗口函數(shù)怎么使用”文章已經(jīng)介紹完畢，想要掌握這篇文章的知識(shí)點(diǎn)還需要大家自己動(dòng)手實(shí)踐使用過才能領(lǐng)會(huì)，如果想了解更多相關(guān)內(nèi)容的文章，歡迎關(guān)注億速云行業(yè)資訊頻道。