Window Function

介紹

<視窗函數> OVER(PARTITION BY <用於分組的列名>
ORDER BY <用於排序的列名>
ROWS/RANGE子句<用來定義視窗大小> )

視窗函數

可以放以下兩種函數：

專用視窗函數
Rank：有相同名次，名次以實際個數走，會跳數字。
Dense_Rank：有相同名次，名次不跳數
Row_Number：相同分數依行數排序

分數	Rank	Dense_Rank	Row_Number
100	1	1	1
100	1	1	2
90	3	2	3

聚合函數，如SUM. AVG, COUNT, MAX, MIN, etc.

ROWS & RANGE

ROWS ：依照行的範圍定義 RANGE：依照值的範圍定義

邊界可取值	說明
CURRENT ROW	目前行
n PRECEDING	前 n 行，n 為數字，例如 2 PRECEDING 表示前 2 行
UNBOUNDED PRECEDING	開頭
n FOLLOWING	後 n 行，n 為數字，例如 2 FOLLOWING 表示後 2 行
UNBOUNDED FOLLOWING	結尾

範例

CREATE TABLE Transcript (    
    student VARCHAR(50),
    subject VARCHAR(50),    
    score INT,
    PRIMARY KEY (student, subject)
);

INSERT INTO Transcript (student, subject, score)
VALUES
    ('John', 'English', 60),
    ('John', 'History', 70),
    ('Alice', 'English', 60),
    ('Alice', 'History', 80),
    ('Bob', 'English', 80),
    ('Bob', 'History', 90),
    ('Bob', 'Math', 90);

每個學生個別的科目排名

SELECT *, RANK() OVER(PARTITION BY student ORDER BY score DESC) AS score_rank
FROM Transcript
ORDER BY student ASC;

範例 2

CREATE TABLE Employee_Salary (    
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee VARCHAR(50),
    salary INT    
);

INSERT INTO Employee_Salary (employee, salary)
VALUES
    ('Alice', 10000),
    ('Bob', 20000),
    ('Joe', 30000),
    ('Randy', 40000),
    ('David', 50000);

以 id 累計員工的薪資

SELECT *,
SUM(salary) OVER(ORDER BY id) AS `累積工資`,
SUM(salary) OVER(ORDER BY id ROWS 1 PRECEDING) AS `本行加前一行`,
SUM(salary) OVER(ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS `前一行加到後一行`,
SUM(salary) OVER(ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS `本行加到最後一行`
FROM Employee_Salary;

每位員工的薪資狀況及對應±1萬元及±5千元薪資範圍內的人數

SELECT *,
COUNT(*) OVER(ORDER BY salary RANGE BETWEEN 10000 PRECEDING AND 10000 FOLLOWING) AS `員工人數+-10000`,
COUNT(*) OVER(ORDER BY salary RANGE BETWEEN 5000 PRECEDING AND 5000 FOLLOWING) AS `員工人數+-5000`
FROM Employee_Salary;