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

173次閱讀

共計(jì) 11223 個(gè)字符，預(yù)計(jì)需要花費(fèi) 29 分鐘才能閱讀完成。

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

什么是窗口函數(shù)

SQL 窗口函數(shù)為在線分析處理（OLAP）和商業(yè)智能（BI）提供了復(fù)雜分析和報(bào)表統(tǒng)計(jì)的功能，例如產(chǎn)品的累計(jì)銷售額統(tǒng)計(jì)、分類排名、同比 / 環(huán)比分析等。這些功能通常很難通過(guò)聚合函數(shù)和分組操作來(lái)實(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ù)的差異，以下語(yǔ)句中的 SUM() 是一個(gè)聚合函數(shù)：

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

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

以下語(yǔ)句中的 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é)果返回了所有的員工姓名，并且通過(guò)聚合函數(shù) SUM() 為每個(gè)員工都返回了相同的匯總結(jié)果。

從以上示例中可以看出，窗口函數(shù)的語(yǔ)法與聚合函數(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ù)庫(kù)中，窗口函數(shù)也被稱為在線分析處理（OLAP）函數(shù)，或者分析函數(shù)（Analytic Function）。

窗口函數(shù)組成部分 1. 創(chuàng)建數(shù)據(jù)分區(qū)

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

例如，以下語(yǔ)句按照不同部門分別統(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)之后，我們無(wú)須使用 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ù)的排序方式，作用類似于查詢語(yǔ)句中的 ORDER BY 子句。

排序選項(xiàng)通常用于數(shù)據(jù)的分類排名。例如，以下語(yǔ)句用于分析員工在部門內(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)和查詢語(yǔ)句中的 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é)束。

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

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

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

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

許多常見(jiàn)的聚合函數(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, 周倉(cāng) ,  男 , 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, 簡(jiǎn)雍 ,  男 , 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í)踐使用過(guò)才能領(lǐng)會(huì)，如果想了解更多相關(guān)內(nèi)容的文章，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道。

正文完