SQL視窗函數的使用方法

2022-04-21 13:01:58

什麼是視窗函數

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視窗函數的使用方法的文章就介紹到這了,更多相關SQL視窗函數內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！