MySQL視窗函式彙總

郭慕荣發表於2024-09-27
MySql函式

1.視窗函式概述
視窗函式是一種SQL函式，非常適合於資料分析，其最大的特點就是：輸入值是從SELECT語句的結果集中的一行或者多行的"視窗"中獲取的，也可以理解為視窗有大有小（行數有多有少）。
透過OVER子句，視窗函式與其他的SQL函式有所區別，如果函式具有OVER子句，則它是視窗函式。如果它缺少了OVER子句，則他就是個普通的聚合函式。
視窗函式可以簡單地解釋為類似於聚合函式的計算函式，但是透過GROUP BY子句組合的常規聚合會隱去正在聚合的各個行，最終輸出稱為一行。但是視窗函式聚合完之後還可以訪問當前行的其他資料，並且可以將這些行的某些屬性新增到結果當中去。
下面可以透過兩個圖來區分普通的聚合函式和視窗函式

首先讓我們先新增測試資料，並檢視錶。

CREATE DATABASE IF NOT EXISTS EmployeeDB;
USE EmployeeDB;
CREATE TABLE Employees (
    EmployeeID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100),
    DepartmentID INT,
    Salary DECIMAL(10, 2)
);
 
INSERT INTO Employees (Name, DepartmentID, Salary) VALUES
('Alice', 1, 50000),
('Bob', 1, 55000),
('Charlie', 2, 60000),
('David', 1, 50000),
('Eve', 2, 65000),
('Frank', 3, 45000),
('Grace', 3, 47000),
('Hannah', 3, 48000),
('Ian', 2, 70000),
('Jack', 1, 52000);

select DepartmentID, sum(salary) as total
from employees
group by DepartmentID;

我們可以看的出來，常規聚合函式把id進行分組然後把每組的薪資綜合計算出來放在最後面。

我們可以透過這兩個例子看出來，聚合函式和視窗聚合函式的區別。就是視窗函式會進行分組，但不會把行進行合併。對於每一組視窗函式返回出來的結果都會重複的放在最後面。

2.視窗函式的語法
Function(arg1,..., argn) OVER ([PARTITION BY <...>] [ORDER BY <....>] [<window_expression>])
對於以上的視窗函式的語法[ ]中的語法是可以根據自己的需求進行選擇（非必須寫入語法），並且此語法嚴格按照上面的順序來規定。
Function(arg1,..., argn)是表示函式的分類，可以是下面分類中的任何一組。
----------聚合函式，例如sum,min,avg,count等函式（常用）
----------排序函式，例如rank row_number dense_rank()等函式（常用）
----------跨行函式，lag lead 函式
OVER [PARTITION BY <...>] 類似於group by 用於指定分組
--每個分組你可以把它叫做視窗
--不分組的情況可以寫成partition by null 或者直接不寫partition by，所有列為一個大組
--分組的情況下，partition by 後面可以跟多個列，例如partition by cid,cname
[ORDER BY <....>] 用於指定每個分組內的資料排序規則 支援ASC、DESC
[<window_expression>] 用於指定每個視窗中 操作的資料範圍 預設是視窗中所有行
1.視窗函式語法解釋-Function(arg1,..., argn)
通常和partition by分組使用。當然也可以不分組使用，但也不分組使用通常沒有意義。
----------聚合函式，例如sum,min,avg,count等函式（常用）
----------排序函式，例如rank row_number dense_rank()等函式（常用）
----------跨行函式，lag lead 函式
1.聚合函式
我們還透過上文的測試資料進行演示。我們就演示2個函式，其他的聚合類函式都是相同的用法。
sum函式：求和

select *,
       sum(Salary) over (partition by DepartmentID) total
from employees

min函式 ：最小值

select *,
       min(Salary) over (partition by DepartmentID) total
from employees;

其他的聚合函式都是同樣的用法。

2.排序函式
rank row_number dense_rank()等函式，通常與order by函式一起使用。
row_number()函式：對分組之後按照某些規則從高到低或者從低到高進行排序(order by),然後打上序號,不考慮並列的情況。

rank()函式：對分組之後按照某些規則從高到低或者從低到高進行排序(order by),然後打上序號，考慮並列情況並且跳躍排名，對此我們需要增添一組資料。

INSERT INTO Employees (Name, DepartmentID, Salary) VALUES('css',1,45000);

相關文章