散文網(wǎng) » 生活 »日常 » 22.1 窗口函數(shù)概述

22.1 窗口函數(shù)概述

2020-08-26 22:11 作者:啵嘰玉桂狗勾 0人讀過(guò) | 我要投稿

窗口函數(shù)（Window Function）是 MySQL 8.0 新增的一個(gè)重要的功能，可以為數(shù)據(jù)分析提供強(qiáng)大的支持，例如計(jì)算分組排名、累積求和、同比/環(huán)比增長(zhǎng)率等。本篇我們就來(lái)了解一下 MySQL 中窗口函數(shù)的語(yǔ)法和各種窗口函數(shù)的作用。

另外，這里有一份?SQL 窗口函數(shù)速查表；歡迎下載保存，以便不時(shí)之需。

在第 12 篇中我們學(xué)習(xí)了常見(jiàn)的聚合函數(shù)，包括 AVG、COUNT、MAX、MIN、SUM 以及 GROUP_CONCAT。聚合函數(shù)的作用就是對(duì)一組數(shù)據(jù)行進(jìn)行匯總計(jì)算，并且返回單個(gè)分析結(jié)果。

窗口函數(shù)和聚合函數(shù)類似之處在于它也是對(duì)一組數(shù)據(jù)進(jìn)行分析；但是，窗口函數(shù)不是將一組數(shù)據(jù)匯總為單個(gè)結(jié)果；而是針對(duì)查詢中的每一行數(shù)據(jù)，基于和它相關(guān)的一組數(shù)據(jù)計(jì)算出一個(gè)結(jié)果。下圖演示了聚合函數(shù)和窗口函數(shù)的區(qū)別：

??窗口函數(shù)在其他數(shù)據(jù)庫(kù)中也叫做分析函數(shù)（Analytic Function），或者聯(lián)機(jī)分析處理（OLAP）函數(shù)。

為了便于理解，我們可以比較一下聚合函數(shù)和窗口函數(shù)的結(jié)果。以下示例分別將 COUNT 作為聚合函數(shù)和窗口函數(shù)，計(jì)算員工的人數(shù)：

SELECT count(*)FROM employee;count(*)|--------| ? ? ?25|SELECT emp_id, emp_name, count(*) OVER ()FROM employee;emp_id|emp_name |count(*) OVER ()|------|---------|----------------| ? ?13|關(guān)興 ? ? ?| ? ? ? ? ? ? ?25| ? ?11|關(guān)平 ? ? ?| ? ? ? ? ? ? ?25| ? ? 2|關(guān)羽 ? ? ?| ? ? ? ? ? ? ?25| ? ? 1|劉備 ? ? ?| ? ? ? ? ? ? ?25| ? ?16|周倉(cāng) ? ? ?| ? ? ? ? ? ? ?25| ? ? 8|孫丫鬟 ? ?| ? ? ? ? ? ? ?25|...

聚合函數(shù)（COUNT(*)）通常也可以作為窗口函數(shù)（COUNT(*) OVER()），區(qū)別在于后者包含了OVER關(guān)鍵字；空括號(hào)表示將所有數(shù)據(jù)作為整體進(jìn)行分析，所以得到的數(shù)值和聚合函數(shù)一樣。查詢結(jié)果中，聚合函數(shù)只返回了一個(gè)匯總結(jié)果，而窗口函數(shù)為每一個(gè)員工都返回了一個(gè)結(jié)果。

22.2 窗口函數(shù)的定義

窗口函數(shù)與其他函數(shù)的語(yǔ)法區(qū)別主要在于OVER子句，接下來(lái)我們介紹它的語(yǔ)法。窗口函數(shù)的定義如下：

window_function ( expr ) OVER ( ? ?PARTITION BY ... ? ?ORDER BY ... ? ?frame_clause)

其中，window_function 是窗口函數(shù)的名稱；expr 是參數(shù)，有些函數(shù)不需要參數(shù)；OVER子句包含三個(gè)選項(xiàng)：分區(qū)（PARTITION BY）、排序（ORDER BY）以及窗口大?。?code>frame_clause）。

22.2.1 分區(qū)選項(xiàng)（PARTITION BY）

PARTITION BY選項(xiàng)用于將數(shù)據(jù)行拆分成多個(gè)分區(qū)（組），窗口函數(shù)基于每一行數(shù)據(jù)所在的組進(jìn)行計(jì)算并返回結(jié)果，它的作用類似于GROUP BY分組。如果省略了 PARTITION BY，所有的數(shù)據(jù)作為一個(gè)組進(jìn)行計(jì)算，上文中的示例就是如此。

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

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

其中，OVER 子句中的 PARTITION BY 選項(xiàng)表示按照部門進(jìn)行分區(qū)；因此，SUM 函數(shù)按照部門分別統(tǒng)計(jì)月薪的合計(jì)值。該語(yǔ)句的結(jié)果如下（只顯示了前 3 個(gè)部門的結(jié)果）：

前 3 行數(shù)據(jù)的部門編號(hào)都為 1，因此該部門的月薪合計(jì)為 30000 + 26000 + 24000 = 80000；其他部門的數(shù)據(jù)也采用同樣的方式進(jìn)行計(jì)算。

??SQL 標(biāo)準(zhǔn)要求 PARTITION BY 之后只能使用字段名，不過(guò) MySQL 允許指定表達(dá)式。另外，我們也可以在 PARTITION BY 之后指定多個(gè)分組字段，例如同時(shí)按照部門和性別進(jìn)行分組分析。

22.2.2 排序選項(xiàng)（ORDER BY）

OVER 子句中的ORDER BY選項(xiàng)用于指定分區(qū)內(nèi)的排序方式，與 ORDER BY 子句的作用類似，通常用于數(shù)據(jù)的排名分析。以下示例用于計(jì)算每個(gè)員工在部門內(nèi)的月薪排名：

SELECT emp_name "姓名", salary "月薪", dept_id "部門編號(hào)", ? ? ? rank() OVER ( ? ? ? ? PARTITION BY dept_id ? ? ? ? ORDER BY salary DESC ? ? ? ) AS "部門排名"FROM employee;

其中，PARTITION BY 選項(xiàng)表示按照部門進(jìn)行分區(qū)；ORDER BY 選項(xiàng)指定在分區(qū)內(nèi)按照月薪從高到低進(jìn)行排序；RANK 函數(shù)用于計(jì)算名次，該函數(shù)將會(huì)在下文中進(jìn)行介紹。

該語(yǔ)句的結(jié)果如下（只顯示了前 3 個(gè)部門的結(jié)果）：

前 3 行數(shù)據(jù)的部門編號(hào)都為 1；“劉備”的月薪最高，在部門內(nèi)排名第一；“關(guān)羽”排名第二；“張飛”排名第三。其他部門的數(shù)據(jù)采用同樣的方式進(jìn)行計(jì)算。

??ORDER BY 選項(xiàng)用于指定分區(qū)內(nèi)數(shù)據(jù)的排序，排序字段數(shù)據(jù)相同的行是對(duì)等行（peer）。如果省略 ORDER BY ，分區(qū)內(nèi)的數(shù)據(jù)不進(jìn)行排序，不按照固定順序處理，而且所有數(shù)據(jù)都是對(duì)等行。

22.2.3 窗口選項(xiàng)（frame_clause）

frame_clause選項(xiàng)用于在當(dāng)前分區(qū)內(nèi)指定一個(gè)計(jì)算窗口，也就是一個(gè)與當(dāng)前行相關(guān)的數(shù)據(jù)子集。指定了窗口之后，分析函數(shù)不再基于分區(qū)進(jìn)行計(jì)算，而是基于窗口內(nèi)的數(shù)據(jù)進(jìn)行計(jì)算。窗口會(huì)隨著當(dāng)前處理的數(shù)據(jù)行而移動(dòng)，例如：

定義一個(gè)從分區(qū)開(kāi)始到當(dāng)前數(shù)據(jù)行結(jié)束的窗口，可以計(jì)算截止到每一行的累計(jì)總值。
定義一個(gè)從當(dāng)前行之前 N 行數(shù)據(jù)到當(dāng)前行之后 N 行數(shù)據(jù)的窗口，可以計(jì)算移動(dòng)平均值。

具體來(lái)說(shuō)，窗口大小的常用選項(xiàng)如下：

{ ROWS | RANGE } frame_start { ROWS | RANGE } BETWEEN frame_start AND frame_end

其中，ROWS表示以行為單位指定窗口的偏移量，RANGE表示以數(shù)值（例如 30 分鐘）為單位指定窗口的偏移量。frame_start 和 frame_end 分別表示窗口的開(kāi)始行和結(jié)束行，它們的可能取值如下：

CURRENT ROWUNBOUNDED PRECEDINGUNBOUNDED FOLLOWINGexpr PRECEDINGexpr FOLLOWING

frame_start 和 frame_end 的具體意義如下：

CURRENT ROW：對(duì)于 ROWS 方式，代表了當(dāng)前行；對(duì)于 RANGE，代表了當(dāng)前行的所有對(duì)等行。
UNBOUNDED PRECEDING：代表了分區(qū)中的第一行。
UNBOUNDED FOLLOWING：代表了分區(qū)中的最后一行。
expr PRECEDING：對(duì)于 ROWS 方式，代表了當(dāng)前行之前的第 expr 行；對(duì)于 RANGE，代表了等于當(dāng)前行的值減去 expr 的所有行；如果當(dāng)前行的值為 NULL，代表了當(dāng)前行的所有對(duì)等行。
expr FOLLOWING：對(duì)于 ROWS 方式，代表了當(dāng)前行之后的第 expr 行；對(duì)于 RANGE，代表了等于當(dāng)前行的值加上 expr 的所有行；如果當(dāng)前行的值為 NULL，代表了當(dāng)前行的所有對(duì)等行。

如果只有 frame_start，默認(rèn)以當(dāng)前行作為窗口的結(jié)束。如果同時(shí)指定了兩者，frame_start 不能晚于 frame_end，例如 BETWEEN 1 FOLLOWING AND 1 PRECEDING 就是一個(gè)無(wú)效的窗口。下圖可以方便我們理解這些選項(xiàng)的含義：

CURRENT ROW表示當(dāng)前正在處理的行；其他的行可以使用相對(duì)當(dāng)前行的位置表示。需要注意，窗口的大小不會(huì)超出當(dāng)前分區(qū)的范圍。

以下示例按照部門統(tǒng)計(jì)員工的累計(jì)月薪值：

SELECT d.dept_name "部門名稱", e.emp_name "姓名", e.salary "月薪", ? ? ? sum(salary) OVER ( ? ? ? ? PARTITION BY e.dept_id ? ? ? ? ORDER BY e.emp_id ? ? ? ? ROWS UNBOUNDED PRECEDING ? ? ? ) AS "部門累計(jì)月薪"FROM employee eJOIN department d ON (e.dept_id = d.dept_id);

其中，PARTITION BY 選項(xiàng)表示按照部門進(jìn)行分區(qū)；ORDER BY 選項(xiàng)表示按照工號(hào)進(jìn)行排序；窗口子句 ROWS UNBOUNDED PRECEDING 指定窗口從分區(qū)的第一行開(kāi)始，默認(rèn)到當(dāng)前行結(jié)束；因此 SUM 函數(shù)計(jì)算的是部門內(nèi)累計(jì)到當(dāng)前員工為止的月薪合計(jì)。該查詢的結(jié)果如下（只顯示了前 3 個(gè)部門的結(jié)果）：

對(duì)于“行政管理部”，第一個(gè)員工的月薪為 30000，累計(jì)也是 30000；第二個(gè)員工的月薪為 26000，累計(jì)為 30000 + 26000 = 56000；依此類推，直到統(tǒng)計(jì)完該部門的所有員工；然后開(kāi)始統(tǒng)計(jì)下一個(gè)部門的數(shù)據(jù)。

22.2.4 命名窗口

窗口函數(shù)的 OVER 子句除了直接定義三種選項(xiàng)之外，還可以使用一個(gè)預(yù)定義的窗口變量進(jìn)行定義。窗口變量使用WINDOW子句進(jìn)行定義，語(yǔ)法位于 HAVING 和 ORDER BY 之間。

window_function(expr) OVER window_name WINDOW window_name AS (PARTITION BY ... ORDER BY ... frame_clause)WINDOW window_name AS (other_window_name)

如果查詢中多個(gè)窗口函數(shù)的 OVER 子句相同，利用 WINDOW 子句定義一個(gè)窗口變量，然后在多個(gè) OVER 子句中使用該變量可以簡(jiǎn)化查詢語(yǔ)句。例如：

SELECT d.dept_name "部門名稱", e.emp_name "姓名", e.salary "月薪", ? ? ? sum(salary) OVER w AS "部門累計(jì)月薪", ? ? ? count(*) OVER w AS "部門累計(jì)人數(shù)"FROM employee eJOIN department d ON (e.dept_id = d.dept_id)WINDOW w AS ( ? ? PARTITION BY e.dept_id ? ? ORDER BY e.emp_id ? ? ROWS UNBOUNDED PRECEDING);

接下來(lái)我們介紹一些常見(jiàn)的窗口函數(shù)和示例。

22.3 常用窗口函數(shù)

常見(jiàn)的窗口函數(shù)可以分為以下幾類：聚合窗口函數(shù)、排名窗口函數(shù)以及取值窗口函數(shù)。

??窗口函數(shù)只能出現(xiàn)在 SELECT 列表和 ORDER BY 子句中，查詢語(yǔ)句的處理順序依次為 FROM、WHERE、GROUP BY、聚合函數(shù)、HAVING、窗口函數(shù)、SELECT DISTINCT、ORDER BY、LIMIT。

22.3.1 聚合窗口函數(shù)

常用的聚合函數(shù)，例如 AVG、SUM、COUNT 等，也可以作為窗口函數(shù)使用。上文我們已經(jīng)列舉了一些聚合窗口函數(shù)的示例，再來(lái)看一個(gè)使用 AVG 函數(shù)計(jì)算移動(dòng)平均值的例子：

SELECT d.dept_name "部門名稱", e.emp_name "姓名", e.salary "月薪", ? ? ? avg(salary) OVER ( ? ? ? ? PARTITION BY e.dept_id ? ? ? ? ORDER BY e.salary ? ? ? ? ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ? ? ? ) AS "移動(dòng)平均月薪"FROM employee eJOIN department d ON (e.dept_id = d.dept_id);部門名稱 ?|姓名 ?|月薪 ? ? |移動(dòng)平均月薪 ?|--------|------|--------|------------|行政管理部|張飛 ?|24000.00|25000.000000|行政管理部|關(guān)羽 ?|26000.00|26666.666667|行政管理部|劉備 ?|30000.00|28000.000000|人力資源部|魏延 ?| 8000.00| 8250.000000|人力資源部|黃忠 ?| 8500.00|13833.333333|人力資源部|諸葛亮|25000.00|16750.000000|...

其中，PARTITION BY 選項(xiàng)表示按照部門進(jìn)行分區(qū)；ORDER BY 選項(xiàng)表示按照月薪從低到高進(jìn)行排序；窗口子句 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 指定窗口從當(dāng)前行的前一行開(kāi)始，到當(dāng)前行的下一行結(jié)束；因此該函數(shù)計(jì)算的是每個(gè)部門內(nèi)員工與其前后各一個(gè)員工的平均月薪值。

移動(dòng)平均值通常用于處理時(shí)間序列的數(shù)據(jù)。例如，廠房的溫度檢測(cè)器獲取了每秒鐘的溫度，我們可以使用以下窗口計(jì)算前五分鐘內(nèi)的平均溫度：

avg(temperature) OVER (ORDER BY ts RANGE BETWEEN interval '5 minute' PRECEDING AND CURRENT ROW)

1

22.3.2 排名窗口函數(shù)

排名窗口函數(shù)用于對(duì)數(shù)據(jù)進(jìn)行分組排名。常見(jiàn)的排名窗口函數(shù)包括：

ROW_NUMBER，為分區(qū)中的每行數(shù)據(jù)分配一個(gè)序列號(hào)，序列號(hào)從 1 開(kāi)始分配。
RANK，計(jì)算每行數(shù)據(jù)在其分區(qū)中的名次；如果存在名次相同的數(shù)據(jù)，后續(xù)的排名將會(huì)產(chǎn)生跳躍。
DENSE_RANK，計(jì)算每行數(shù)據(jù)在其分區(qū)中的名次；即使存在名次相同的數(shù)據(jù)，后續(xù)的排名也是連續(xù)的值。
PERCENT_RANK，以百分比的形式顯示每行數(shù)據(jù)在其分區(qū)中的名次；如果存在名次相同的數(shù)據(jù)，后續(xù)的排名將會(huì)產(chǎn)生跳躍。
CUME_DIST，計(jì)算每行數(shù)據(jù)在其分區(qū)內(nèi)的累積分布，也就是該行數(shù)據(jù)及其之前的數(shù)據(jù)的比率；取值范圍大于 0 并且小于等于 1。
NTILE，將分區(qū)內(nèi)的數(shù)據(jù)分為 N 等份，為每行數(shù)據(jù)計(jì)算其所在的位置。

排名窗口函數(shù)不支持動(dòng)態(tài)的窗口大?。╢rame_clause），而是以當(dāng)前分區(qū)作為分析的窗口。以下示例按照部門分組，并計(jì)算每個(gè)員工在其部門中的月薪排名，分別使用了 4 個(gè)不同的排名函數(shù)：

SELECT d.dept_name "部門名稱", e.emp_name "姓名", e.salary "月薪", ? ? ? ROW_NUMBER() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS "row_number", ? ? ? RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS "rank", ? ? ? DENSE_RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS "dense_rank", ? ? ? PERCENT_RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS "percent_rank"FROM employee eJOIN department d ON (e.dept_id = d.dept_id);部門名稱 ?|姓名 |月薪 ? ? |row_number|rank|dense_rank|percent_rank ? ? ? |--------|-----|--------|----------|----|----------|-------------------|行政管理部|劉備 |30000.00| ? ? ? ? 1| ? 1| ? ? ? ? 1| ? ? ? ? ? ? ? ?0.0|行政管理部|關(guān)羽 |26000.00| ? ? ? ? 2| ? 2| ? ? ? ? 2| ? ? ? ? ? ? ? ?0.5|行政管理部|張飛 |24000.00| ? ? ? ? 3| ? 3| ? ? ? ? 3| ? ? ? ? ? ? ? ?1.0|...研發(fā)部 ? |趙云 |15000.00| ? ? ? ? 1| ? 1| ? ? ? ? 1| ? ? ? ? ? ? ? ?0.0|研發(fā)部 ? |周倉(cāng) | 8000.00| ? ? ? ? 2| ? 2| ? ? ? ? 2| ? ? ? ? ? ? ?0.125|研發(fā)部 ? |關(guān)興 | 7000.00| ? ? ? ? 3| ? 3| ? ? ? ? 3| ? ? ? ? ? ? ? 0.25|研發(fā)部 ? |關(guān)平 | 6800.00| ? ? ? ? 4| ? 4| ? ? ? ? 4| ? ? ? ? ? ? ?0.375|研發(fā)部 ? |趙氏 | 6600.00| ? ? ? ? 5| ? 5| ? ? ? ? 5| ? ? ? ? ? ? ? ?0.5|研發(fā)部 ? |廖化 | 6500.00| ? ? ? ? 6| ? 6| ? ? ? ? 6| ? ? ? ? ? ? ?0.625|研發(fā)部 ? |張苞 | 6500.00| ? ? ? ? 7| ? 6| ? ? ? ? 6| ? ? ? ? ? ? ?0.625|研發(fā)部 ? |趙統(tǒng) | 6000.00| ? ? ? ? 8| ? 8| ? ? ? ? 7| ? ? ? ? ? ? ?0.875|研發(fā)部 ? |馬岱 | 5800.00| ? ? ? ? 9| ? 9| ? ? ? ? 8| ? ? ? ? ? ? ? ?1.0|...

其中，4 個(gè)函數(shù)的 OVER 子句完全相同；PARTITION BY 表示按照部門進(jìn)行分區(qū)；ORDER BY 表示按照月薪從高到低進(jìn)行排序。我們以結(jié)果中的“研發(fā)部”為例進(jìn)行分析：

ROW_NUMBER 函數(shù)為每個(gè)員工分配了一個(gè)連續(xù)的數(shù)字編號(hào)，可以看作是一種排名。其中“廖化”和“張苞”的月薪相同，但是編號(hào)不同；
RANK 函數(shù)為每個(gè)員工指定了一個(gè)名次，其中“廖化”和“張苞”的名次都是 6。在他們之后的“趙統(tǒng)”的名次為 8，產(chǎn)生了跳躍；
DENSE_RANK 函數(shù)為每個(gè)員工指定了一個(gè)名次，其中“廖化”和“張苞”的名次都是 6。在他們之后的“趙統(tǒng)”的名次為 7，名次是連續(xù)值；
PERCENT_RANK 函數(shù)按照百分比指定名次，取值位于 0 到 1 之間。其中“趙統(tǒng)”的百分比排名為 0.875，也產(chǎn)生了跳躍。

以下語(yǔ)句演示了 CUME_DIST 和 NTILE 函數(shù)的作用：

SELECT emp_name AS "姓名", salary AS "月薪", ? ? ? CUME_DIST() OVER (ORDER BY salary DESC) AS "累積占比", ? ? ? NTILE(5) OVER (ORDER BY salary DESC) AS "相對(duì)位置"FROM employee;姓名 ?|月薪 ? ?|累積占比|相對(duì)位置|-----|--------|------|----|劉備 ?|30000.00| ?0.04| ? 1|關(guān)羽 ?|26000.00| ?0.08| ? 1|諸葛亮|25000.00| ?0.12| ? 1|張飛 ?|24000.00| ?0.16| ? 1|趙云 ?|15000.00| ? 0.2| ? 1|孫尚香|12000.00| ?0.24| ? 2|...糜竺 ?| 4300.00| ?0.84| ? 5|黃權(quán) ?| 4200.00| ?0.88| ? 5|龐統(tǒng) ?| 4100.00| ?0.92| ? 5|蔣琬 ?| 4000.00| ? 1.0| ? 5|鄧芝 ?| 4000.00| ? 1.0| ? 5|

其中，OVER 子句沒(méi)有指定分區(qū)選項(xiàng)，因此所有的員工作為一個(gè)整體進(jìn)行分析；ORDER BY 按照月薪從高到低進(jìn)行排序。從結(jié)果可以看出，20% 的員工月薪大于等于 15000；或者說(shuō)，月薪 15000 意味著在公司中的排名屬于最高的 20%。NTILE 將員工按照月薪從高到低分成了 5 個(gè)組，相對(duì)位置為 1 的員工是月薪最高的 20% 員工。

22.3.3 取值窗口函數(shù)

取值窗口函數(shù)用于返回指定位置上的數(shù)據(jù)。常見(jiàn)的取值窗口函數(shù)包括：

FIRST_VALUE，返回窗口內(nèi)第一行的數(shù)據(jù)。
LAST_VALUE，返回窗口內(nèi)最后一行的數(shù)據(jù)。
NTH_VALUE，返回窗口內(nèi)第 N 行的數(shù)據(jù)。
LAG，返回分區(qū)中當(dāng)前行之前的第 N 行的數(shù)據(jù)。
LEAD，返回分區(qū)中當(dāng)前行之后第 N 行的數(shù)據(jù)。

其中，LAG 和 LEAD 函數(shù)不支持動(dòng)態(tài)的窗口大小（frame_clause），而是以當(dāng)前分區(qū)作為分析的窗口。

以下語(yǔ)句使用 FIRST_VALUE、LAST_VALUE 以及 NTH 函數(shù)分別獲取每個(gè)部門內(nèi)部月薪最高、月薪最低以及月薪第二高的員工：

SELECT d.dept_name "部門名稱", e.emp_name "姓名", e.salary "月薪", ? ? ? first_value(salary) OVER w "最高月薪", ? ? ? last_value(salary) OVER w "最低月薪", ? ? ? nth_value(salary, 2) OVER w "第二高月薪"FROM employee eJOIN department d ON (e.dept_id = d.dept_id)WINDOW w AS ( ? ? PARTITION BY e.dept_id ? ? ORDER BY salary DESC ? ? ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)ORDER BY e.dept_id, salary DESC;部門名稱 |姓名 ?|月薪 ? ? |最高月薪 |最低月薪 |第二高月薪|--------|-----|--------|--------|--------|--------|行政管理部|劉備 |30000.00|30000.00|24000.00|26000.00|行政管理部|關(guān)羽 |26000.00|30000.00|24000.00|26000.00|行政管理部|張飛 |24000.00|30000.00|24000.00|26000.00|人力資源部|諸葛亮|25000.00|25000.00| 8000.00| 8500.00|人力資源部|黃忠 | 8500.00|25000.00| 8000.00| 8500.00|人力資源部|魏延 | 8000.00|25000.00| 8000.00| 8500.00|...

以上三個(gè)函數(shù)的默認(rèn)窗口是從當(dāng)前分區(qū)的第一行到當(dāng)前行，所以我們?cè)?code>OVER子句中將窗口設(shè)置為整個(gè)分區(qū)。

LAG 和 LEAD 函數(shù)可以用于計(jì)算銷量數(shù)據(jù)的環(huán)比/同比增長(zhǎng)。我們創(chuàng)建一個(gè)新的數(shù)據(jù)表：sales_monthly，它記錄了不同產(chǎn)品按月統(tǒng)計(jì)的銷售金額。以下語(yǔ)句統(tǒng)計(jì)不同產(chǎn)品每個(gè)月的環(huán)比增長(zhǎng)率：

SELECT product AS "產(chǎn)品", ym "年月", amount "銷量", ? ? ? (amount - LAG(amount, 1) OVER (PARTITION BY product ORDER BY ym))/ ? ? ? LAG(amount, 1) OVER (PARTITION BY product ORDER BY ym) * 100 AS "環(huán)比增長(zhǎng)率（%）"FROM sales_monthlyORDER BY product, ym;產(chǎn)品|年月 ? |銷量 ? ?|環(huán)比增長(zhǎng)率（%）|---|------|--------|---------|桔子|201801|10154.00| ? ? ? ? |桔子|201802|10183.00| 0.285602|桔子|201803|10245.00| 0.608858|桔子|201804|10325.00| 0.780869|桔子|201805|10465.00| 1.355932|桔子|201806|10505.00| 0.382226|桔子|201807|10578.00| 0.694907|桔子|201808|10680.00| 0.964265|桔子|201809|10788.00| 1.011236|桔子|201810|10838.00| 0.463478|桔子|201811|10942.00| 0.959587|桔子|201812|10988.00| 0.420398|桔子|201901|11099.00| 1.010193|桔子|201902|11181.00| 0.738805|桔子|201903|11302.00| 1.082193|桔子|201904|11327.00| 0.221200|桔子|201905|11423.00| 0.847532|桔子|201906|11524.00| 0.884181|...

其中，LAG(amount, 1) 表示獲取上一期的銷量；PARTITION BY 表示按照產(chǎn)品分區(qū)；ORDER BY 表示按照月份進(jìn)行排序；當(dāng)前月份的銷量減去上個(gè)月的銷量，再除以上個(gè)月的銷量，就是環(huán)比增長(zhǎng)率。

標(biāo)簽：