結構化查詢語言/視窗函式
本頁面討論的視窗函式是對“傳統”函式的一種特殊且非常強大的擴充套件。它們不是對單個行進行計算，而是對一組行進行計算（類似於聚合函式與GROUP BY 語句一起使用）。這組行 - 關鍵點是 - 在所有由WHERE 語句確定的行上“移動”或“滑動”。這個“滑動視窗”被稱為框架或 - 根據官方 SQL 標準 - “視窗框架”。
以下是一些示例
- 一個簡單的例子是“滑動視窗”，它包含前一行、當前行和下一行。
- 視窗函式的一個典型應用領域是對任意時間序列的評估。如果你有股票市場價格的時間序列，你可以很容易地計算出過去 n 天的移動平均值。
- 視窗函式通常用於資料倉庫和其他OLAP 應用。如果你有關於大量區域內所有產品在大量時期內的銷售資料，你可以計算收入的統計指標。這種評估比簡單的
GROUP BY 語句更強大。
與GROUP BY 語句形成對比的是，GROUP BY 語句每個組只有一行輸出，而視窗函式保留結果集中所有行的標識並顯示它們。
語法
[edit | edit source]視窗函式列在SELECT和FROM這兩個關鍵字之間，與通常的函式和列位於同一位置。它們包含關鍵字OVER。
-- Window functions appear between the key words SELECT and FROM
SELECT ...,
<window_function>,
...
FROM <tablename>
...
;
-- They consist of three main parts:
-- 1. function type (which is the name of the function)
-- 2. key word 'OVER'
-- 3. specification, which rows constitute the 'sliding window' (partition, order and frame)
<window_function> := <window_function_type> OVER <window_specification>
<window_function_type> := ROW_NUMBER() | RANK() | LEAD(<column>) | LAG(<column>) |
FIRST_VALUE(<column>) | LAST_VALUE(<column>) | NTH_VALUE(<column>, <n>) |
SUM(<column>) | MIN(<column>) | MAX(<column>) | AVG(<column> | COUNT(<column>)
<window_specification> := [ <window_partition> ] [ <window_order> ] [ <window_frame> ]
<window_partition> := PARTITION BY <column>
<window_order> := ORDER BY <column>
<window_frame> := see below
總體描述
[edit | edit source]
關於視窗函式，有一些類似的概念。為了區分這些概念，有必要使用精確的術語。這些術語將在接下來的八段中介紹，它們也大致反映了執行順序。前七步的目的是確定實際的框架，而第八步則對其實施操作。
WHERE 語句返回一定數量的行。它們構成結果集。ORDER BY 語句（在語法上位於WHERE 語句之後）將結果集重新排序成特定順序。- 此順序決定了將行傳遞給
SELECT 語句的順序。實際傳遞給SELECT 語句的行被稱為當前行。 WINDOW PARTITION 語句將結果集劃分為視窗分割槽（我們將使用更短的術語分割槽，因為在我們的網站環境中，不會有混淆的風險）。如果沒有WINDOW PARTITION 語句，結果集的所有行都構成一個分割槽。（這些分割槽等同於GROUP BY 語句建立的組）。分割槽彼此獨立：沒有重疊，因為結果集的每一行都只屬於一個分割槽。WINDOW ORDER 語句對每個分割槽的行進行排序（可能與ORDER BY 語句不同）。WINDOW FRAME 語句定義實際分割槽中哪些行屬於實際視窗框架（我們將使用更短的術語框架）。該語句為結果集的每一行定義一個框架。這是透過確定受影響行的上下邊界來實現的。因此，結果集中行數有多少個（通常不同的）框架。上下邊界會隨著結果集的每一行而重新確定！單個行可能屬於多個框架。實際框架是“滑動視窗”的例項化。它的行根據WINDOW ORDER 語句排序。- 如果沒有
WINDOW FRAME 語句，則實際分割槽的行構成框架，具有以下預設邊界：實際分割槽的第一行是它們的低邊界，當前行是它們的上下邊界。如果沒有WINDOW FRAME 語句和WINDOW ORDER 語句，則上下邊界將切換到實際分割槽的最後一行。下面我們將解釋如何更改此預設行為。 - <window_function_type>s 對實際框架的行進行操作。
示例表
[edit | edit source]我們使用下表來演示視窗函式。
CREATE TABLE employee (
-- define columns (name / type / default value / column constraint)
id DECIMAL PRIMARY KEY,
emp_name VARCHAR(20) NOT NULL,
dep_name VARCHAR(20) NOT NULL,
salary DECIMAL(7,2) NOT NULL,
age DECIMAL(3,0) NOT NULL,
-- define table constraints (it's merely an example table)
CONSTRAINT empoyee_uk UNIQUE (emp_name, dep_name)
);
INSERT INTO employee VALUES ( 1, 'Matthew', 'Management', 4500, 55);
INSERT INTO employee VALUES ( 2, 'Olivia', 'Management', 4400, 61);
INSERT INTO employee VALUES ( 3, 'Grace', 'Management', 4000, 42);
INSERT INTO employee VALUES ( 4, 'Jim', 'Production', 3700, 35);
INSERT INTO employee VALUES ( 5, 'Alice', 'Production', 3500, 24);
INSERT INTO employee VALUES ( 6, 'Michael', 'Production', 3600, 28);
INSERT INTO employee VALUES ( 7, 'Tom', 'Production', 3800, 35);
INSERT INTO employee VALUES ( 8, 'Kevin', 'Production', 4000, 52);
INSERT INTO employee VALUES ( 9, 'Elvis', 'Service', 4100, 40);
INSERT INTO employee VALUES (10, 'Sophia', 'Sales', 4300, 36);
INSERT INTO employee VALUES (11, 'Samantha','Sales', 4100, 38);
COMMIT;
第一個查詢
[edit | edit source]此示例演示了邊界如何在結果集中“滑動”。這樣做，它們建立了一個接一個的框架，每個結果集行一個。這些框架是分割槽的一部分，分割槽是結果集的一部分，結果集是表的一部分。
SELECT id,
emp_name,
dep_name,
-- The functions FIRST_VALUE() and LAST_VALUE() explain themselves by their name. They act within the actual frame.
FIRST_VALUE(id) OVER (PARTITION BY dep_name ORDER BY id) AS frame_first_row,
LAST_VALUE(id) OVER (PARTITION BY dep_name ORDER BY id) AS frame_last_row,
COUNT(*) OVER (PARTITION BY dep_name ORDER BY id) AS frame_count,
-- The functions LAG() and LEAD() explain themselves by their name. They act within the actual partition.
LAG(id) OVER (PARTITION BY dep_name ORDER BY id) AS prev_row,
LEAD(id) OVER (PARTITION BY dep_name ORDER BY id) AS next_row
FROM employee;
-- For simplification, we use the same PARTITION and ORDER definitions for all window functions.
-- This is not necessary, you can use divergent definitions.
請注意低邊界（FRAME_FIRST_ROW）和上邊界（FRAME_LAST_ROW）如何隨行而變化。
| ID | EMP_NAME | DEP_NAME | FRAME_FIRST_ROW | FRAME_LAST_ROW | FRAME_COUNT | PREV_ROW | NEXT_ROW |
|---|---|---|---|---|---|---|---|
| 1 | Matthew | Management | 1 | 1 | 1 | - | 2 |
| 2 | Olivia | Management | 1 | 2 | 2 | 1 | 3 |
| 3 | Grace | Management | 1 | 3 | 3 | 2 | - |
| 4 | Jim | Production | 4 | 4 | 1 | - | 5 |
| 5 | Alice | Production | 4 | 5 | 2 | 4 | 6 |
| 6 | Michael | Production | 4 | 6 | 3 | 5 | 7 |
| 7 | Tom | Production | 4 | 7 | 4 | 6 | 8 |
| 8 | Kevin | Production | 4 | 8 | 5 | 7 | - |
| 10 | Sophia | Sales | 10 | 10 | 1 | - | 11 |
| 11 | Samantha | Sales | 10 | 11 | 2 | 10 | - |
| 9 | Elvis | Service | 9 | 9 | 1 | - | - |
該查詢沒有WHERE 語句。因此，表的所有行都是結果集的一部分。根據WINDOW PARTITION 語句，即“PARTITION BY dep_name”，結果集被劃分為 4 個分割槽：“Management”、“Production”、“Sales”和“Service”。框架在這些分割槽內執行。由於沒有WINDOW FRAME 語句，因此框架從實際分割槽的首行開始，一直執行到當前行。
你可以看到框架內的實際行數（FRAME_COUNT 列）從 1 開始增長到分割槽內所有行的總和。當分割槽切換到下一個分割槽時，該數字將從 1 重新開始。
PREV_ROW 和 NEXT_ROW 列顯示實際分割槽內前一行和下一行的 ID。由於首行沒有前驅，因此顯示了空指示器。對於最後一行及其後繼，也相應適用。
基本視窗函式
[edit | edit source]我們介紹一些<window_function_type> 函式及其含義。標準以及大多數實現都包含其他函式和過載變體。
| 簽名 | 範圍 | 含義/返回值 |
|---|---|---|
| FIRST_VALUE(<column>) | 實際框架 | 框架內首行的列值。 |
| LAST_VALUE(<column>) | 實際框架 | 框架內最後一行是列的值。 |
| LAG(<column>) | 實際分割槽 | 前一行（當前行之前的行）的列值。 |
| LAG(<column>, <n>) | 實際分割槽 | 當前行之前第 n 行的列值。 |
| LEAD(<column>) | 實際分割槽 | 後繼行的列值（當前行之後的行）。 |
| LEAD(<column>, <n>) | 實際分割槽 | 當前行之後第 n 行的列值。 |
| ROW_NUMBER() | 實際框架 | 幀內行的數字序列。 |
| RANK() | 實際框架 | 幀內行的數字序列。指定順序中的相同值將評估為相同的數字。 |
| NTH_VALUE(<column>, <n>) | 實際框架 | 幀內第 n 行的列值。 |
| SUM(<column>) MIN(<column>) MAX(<column>) AVG(<column>) COUNT(<column>) |
實際框架 | 如常。 |
以下是一些示例
SELECT id,
emp_name,
dep_name,
ROW_NUMBER() OVER (PARTITION BY dep_name ORDER BY id) AS row_number_in_frame,
NTH_VALUE(emp_name, 2) OVER (PARTITION BY dep_name ORDER BY id) AS second_row_in_frame,
LEAD(emp_name, 2) OVER (PARTITION BY dep_name ORDER BY id) AS two_rows_ahead
FROM employee;
| ID | EMP_NAME | DEP_NAME | ROW_NUMBER_IN_FRAME | SECOND_ROW_IN_FRAME | TWO_ROWS_AHEAD |
|---|---|---|---|---|---|
| 1 | Matthew | Management | 1 | - | Grace |
| 2 | Olivia | Management | 2 | Olivia | - |
| 3 | Grace | Management | 3 | Olivia | - |
| 4 | Jim | Production | 1 | - | Michael |
| 5 | Alice | Production | 2 | Alice | Tom |
| 6 | Michael | Production | 3 | Alice | Kevin |
| 7 | Tom | Production | 4 | Alice | - |
| 8 | Kevin | Production | 5 | Alice | - |
| 10 | Sophia | Sales | 1 | - | - |
| 11 | Samantha | Sales | 2 | Samantha | - |
| 9 | Elvis | Service | 1 | - | - |
這三個例子展示了
- 實際幀內的行號。
- 實際幀內第二行的員工姓名。這並非所有情況下都可行。a) 每個分割槽幀系列中的第一個幀僅包含 1 行。b) 最後一個分割槽及其唯一的幀僅包含一行。
- 領先當前行兩行的員工姓名。與上一列類似，這並非所有情況下都可行。
- 請注意第一行最後兩列的區別。SECOND_ROW_IN_FRAME 列包含 NULL 指示符。與該行關聯的幀僅包含 1 行（從第一行到當前行） - 並且 nth_value() 函式的作用域為“幀”。相反，TWO_ROW_AHEAD 列包含值“Grace”。該值由 lead() 函式評估，該函式的作用域為分割槽！該分割槽包含 3 行：部門“管理”中的所有行。只有在第二行和第三行中，才能無法向前移動 2 行。
如上例所示，WINDOW PARTITION 子句 使用關鍵字 PARTITION BY 來定義分割槽，WINDOW ORDER 子句 使用關鍵字 ORDER BY 來定義分割槽內行的順序。
幀由 WINDOW FRAME 子句 定義，該子句可以選擇跟在 WINDOW PARTITION 子句 和 WINDOW ORDER 子句 之後。
除了作用域為實際分割槽的 lead() 和 lag() 函式之外，所有其他視窗函式都作用於實際幀。因此，一個基本決定是哪些行應構成幀。這是透過建立上下邊界（在 WINDOW ORDER 子句 的意義上）來完成的。這兩個邊界內的所有行都構成實際幀。因此，WINDOW FRAME 子句 主要由兩個邊界的定義組成 - 以四種方式之一
- 在當前行之前和之後定義一定數量的 行。這會導致幀系列中包含恆定的行數 - 有一些例外情況出現在上下邊界附近，以及使用“UNBOUNDED”關鍵字的例外情況。
- 在當前行之前和之後定義一定數量的 組。這些組由之前和之後行的唯一值構建 - 與
SELECT DISTINCT ...或GROUP BY相同。生成的幀涵蓋所有值屬於其中一個組的行。由於每個組都可能由多行（具有相同值）組成，因此每個幀的行數並不恆定。 - 透過指定一個固定的數值來定義某個列的值的 範圍，例如：1.000（對於薪資）或 30 天（對於時間序列）。定義的範圍從當前值與定義值的差值執行到當前值（FOLLOWING 情況構建總和，而不是差值）。分割槽中所有列值落在該範圍內的行都構成幀。相應地，幀內的行數可能因幀而異 - 與 行 技術相反。
- 省略子句並使用預設值。
根據這些不同的策略，有三個關鍵字“ROWS”、“GROUPS”和“RANGE”，它們會導致不同的行為。
WINDOW FRAME 子句 使用一些關鍵字來修改或指定分割槽的有序行在何處視覺化。
Rows in a partition and the related keywords - <-- UNBOUNDED PRECEDING (first row) ... - <-- 2 PRECEDING - <-- 1 PRECEDING - <-- CURRENT ROW - <-- 1 FOLLOWING - <-- 2 FOLLOWING ... - <-- UNBOUNDED FOLLOWING (last row)
術語 UNBOUNDED PRECEDING 表示分割槽中的第一行，而 UNBOUNDED FOLLOWING 表示最後一行。從 CURRENT ROW 開始計算，有 <n> PRECEDING 行和 <n> FOLLOWING 行。顯然，這種 PRECEDING/FOLLOWING 術語僅在存在 WINDOW ORDER 子句 時有效，該子句建立了一個明確的序列。
WINDOW FRAME 子句 的（簡化）語法為
<window_frame> := [ROWS | GROUPS | RANGE ] BETWEEN
[ UNBOUNDED PRECEDING | <n> PRECEDING | CURRENT ROW ] AND
[ UNBOUNDED FOLLOWING | <n> FOLLOWING | CURRENT ROW ]
包含 WINDOW FRAME 子句 的完整視窗函式示例為
...
SUM(salary) OVER (PARTITION BY dep_name ORDER BY salary
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as growing_sum,
...
在這種情況下，WINDOW FRAME 子句 以關鍵字“ROWS”開頭。它將下邊界定義為分割槽的第一個行，並將上邊界定義為實際行。這意味著幀系列從幀到幀增長一行，直到處理完分割槽中的所有行。之後，下一個分割槽從一個 1 行幀開始，並重復增長。
ROWS 語法定義要處理的特定數量的行。
SELECT id, dep_name, salary,
SUM(salary) OVER (PARTITION BY dep_name ORDER BY salary
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS sum_over_1or2or3_rows
FROM employee;
該示例作用於特定數量的行，即當前行之前的兩行（如果存在於分割槽內）和當前行。沒有一種情況是幀記憶體在超過三行的。視窗函式計算這最多三行的薪資總和。
在每個新分割槽中，總和將重置為零，在本例中即為部門。這也適用於 GROUPS 和 RANGE 語法。
當人們對特定數量行的平均值或兩行之間的距離感興趣時，ROWS 語法經常使用。
GROUPS 語法的語義與 ROWS 語法類似 - 只有一個例外：WINDOW ORDER 子句 列中具有相同值的行計為 1 行。GROUPS 語法計算不同值的個數，而不是行的個數。
-- Hint: The syntax 'GROUPS' (Feature T620) is not supported by Oracle 11
SELECT id, dep_name, salary,
SUM(salary) OVER (PARTITION BY dep_name ORDER BY salary
GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_over_groups
FROM employee;
該示例以關鍵字 GROUPS 開頭，並定義它希望對“salary”列的三個不同值進行操作。可能有多於三行滿足這些條件 - 與等效的 ROWS 策略相反。
當人們在審查期間具有變化的行數時，GROUPS 語法是合適的策略，例如：人們每天具有變化的測量值個數，並且對一週或一個月內方差的平均值感興趣。
乍一看，RANGE 語法與 ROWS 和 GROUPS 語法類似。但語義卻大不相同！此語法中給出的數字 <n> 未指定任何計數器。它們指定了當前行中的值與下邊界或上邊界之間的 距離。因此，ORDER BY 列的型別應為 NUMERIC、DATE 或 INTERVAL。
SELECT id, dep_name, salary,
SUM(salary) OVER (PARTITION BY dep_name ORDER BY salary
RANGE BETWEEN 100 PRECEDING AND 50 FOLLOWING) AS sum_over_range
FROM employee;
此定義會導致對所有薪資低於當前行 100 並高於當前行 50 的行進行求和。在我們的示例表中，此條件在某些情況下適用於多於 1 行。
RANGE 策略的典型用例是人們分析寬數值範圍並期望在此範圍內僅遇到幾行的情況，例如：稀疏矩陣。
如果省略了 WINDOW FRAME 子句，則其預設值為：'RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW'。這會導致從分割槽的第一個行到當前行加上所有具有與當前行相同值的行的範圍 - 因為應用了 RANGE 語法。
如果省略了 WINDOW ORDER 子句，則不允許使用 WINDOW FRAME 子句，並且分割槽的全部行都構成幀。
如果省略了 PARTITION BY 子句，則結果集的所有行都構成唯一的分割槽。
儘管 SQL 標準 2003 及其後續版本對視窗函式定義了非常明確的規則，但一些實現並沒有遵循這些規則。一些供應商只實現了標準的一部分——這是他們自己的責任——但其他供應商似乎以一種奇特的方式解釋了標準。
據我們所知，ROWS 語法在實現時符合標準。但似乎 RANGE 語法有時實現了 SQL 標準中 GROUPS 語法所需的功能。（也許這是一個誤解，只是各種實現的公開可用描述沒有反映出細節。）所以：小心，測試你的系統，並給我們反饋在討論頁面上。
顯示 id、emp_name、dep_name、salary 和部門內的平均工資。
--
-- To get the average of the department, every frame must be build by ALL rows of the department.
--
SELECT id, emp_name, dep_name, salary,
AVG(salary) OVER (PARTITION BY dep_name ORDER BY dep_name
-- all rows of partition (=department)
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as avg_salary
FROM employee;
--
-- It's possible to omit the 'window order' clause. Thereby the frames include ALL rows of the actual partition.
-- See: 'Defaults' above.
--
SELECT id, emp_name, dep_name, salary,
AVG(salary) OVER (PARTITION BY dep_name) as avg_salary
FROM employee;
--
-- The following statements lead to different results as the frames are composed of a growing number of rows.
--
SELECT id, emp_name, dep_name, salary,
AVG(salary) OVER (PARTITION BY dep_name ORDER BY salary) as avg_salary
FROM employee;
--
-- It's possible to sort the result set by arbitrary rows (test the emp_name, it's interesting)
--
SELECT id, emp_name, dep_name, salary,
AVG(salary) OVER (PARTITION BY dep_name) as avg_salary
FROM employee
ORDER BY dep_name, salary;
老年人賺的錢比年輕人多嗎？
要給出答案，請顯示 id、emp_name、salary、age 和 3（或 5）個年齡相近的人的平均工資。
SELECT id, emp_name, salary, age,
AVG(salary) OVER ( ORDER BY age ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS mean_over_3,
AVG(salary) OVER ( ORDER BY age ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS mean_over_5
FROM employee;
-- As there is no restriction to any other criterion than the age (department or something else), there is
-- no need for any PARTITION definition. Averages are computed without any interruption.
擴充套件上述問題及其解決方案，以顯示四個部門的結果。
SELECT id, emp_name, salary, age, dep_name,
AVG(salary) OVER (PARTITION BY dep_name ORDER BY age ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS mean_over_3,
AVG(salary) OVER (PARTITION BY dep_name ORDER BY age ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS mean_over_5
FROM employee;
-- Averages are computed WITHIN departments.
顯示 id、emp_name、salary 和與之前人員（按 ID 順序）薪資的差額。
-- For mathematician: This is a very first approximation to first derivate.
SELECT id, emp_name, salary,
salary - LAG(salary) OVER (ORDER BY id) as diff_salary
FROM employee;
-- And the difference of differences:
SELECT id, emp_name, salary,
(LAG(salary) OVER (ORDER BY id) - salary) AS diff_salary_1,
(LAG(salary) OVER (ORDER BY id) - salary) -
(LAG(salary, 2) OVER (ORDER BY id) - LAG(salary) OVER (ORDER BY id)) AS diff_salary_2
FROM employee;
顯示值的“周圍環境”：按 emp_name 排序的所有人員的 id 和 emp_name。在每行中補充前兩個 emp_name 和實際 emp_name 後面的兩個（按通常的字母順序）。
SELECT id,
LAG(emp_name, 2) OVER (ORDER BY emp_name) AS before_prev,
LAG(emp_name) OVER (ORDER BY emp_name) AS prev,
emp_name AS act,
LEAD(emp_name) OVER (ORDER BY emp_name) AS follower,
LEAD(emp_name, 2) OVER (ORDER BY emp_name) AS behind_follower
FROM employee
ORDER BY emp_name;