1.窗口函數(shù)簡(jiǎn)介

窗口函數(shù)是SQL中的一項(xiàng)高級(jí)特性，用于在不改變查詢結(jié)果集行數(shù)的情況下，對(duì)每一行執(zhí)行聚合計(jì)算或者其他復(fù)雜的計(jì)算，也就是說窗口函數(shù)可以跨行計(jì)算，可以掃描所有的行，并把結(jié)果填到每一行中。這些函數(shù)通常與OVER()子句一起使用，可以定義窗口或分區(qū)，并在上面執(zhí)行計(jì)算，使用窗口函數(shù)，可以使許多難以處理的棘手問題變得較為容易。
窗口函數(shù)的特點(diǎn)包括：

• 輸入多行（一個(gè)窗口），返回一個(gè)值：窗口函數(shù)為每行數(shù)據(jù)進(jìn)行一次計(jì)算，但不會(huì)改變?cè)疾樵兘Y(jié)果集的行數(shù)
• 計(jì)算方式靈活：可以使用partition by字句將數(shù)據(jù)分區(qū)，并使用order by子句來進(jìn)行排序等一些復(fù)雜運(yùn)算
• 與聚合函數(shù)結(jié)合使用：可以與聚合函數(shù)結(jié)合使用，在不分組的情況下計(jì)算如總和、平均值、最小值、最大值等聚合值。

2.語法結(jié)構(gòu)解析

<窗口函數(shù)> OVER (
    [PARTITION BY <分組列>]
    [ORDER BY <排序列>]
    [ROWS 或 RANGE <窗口框架定義>]
)

其中：

• PARTITION BY 子句用于將數(shù)據(jù)分成不同的分區(qū)，窗口函數(shù)將在每個(gè)分區(qū)內(nèi)執(zhí)行?？梢岳斫鉃?code>group by
• ORDER BY 子句定義了數(shù)據(jù)的排序方式，決定窗口函數(shù)的計(jì)算順序。
• ROWS BETWEEN 子句指定了窗口的范圍，可以是行數(shù)、區(qū)間等。

3.常用的窗口函數(shù)SQL示例

常用的窗口函數(shù)有：

聚合函數(shù)：SUM()、AVG()、COUNT()、MAX()、MIN()等

排名函數(shù)：

• ROW_NUMBER()：為窗口內(nèi)的每一行分配一個(gè)唯一的序號(hào)，序號(hào)連續(xù)且不重復(fù)；
• RANK()：排名函數(shù)，允許有并列的名次，名次后面會(huì)出現(xiàn)空位。
• ENSE_RANK()：排名函數(shù)，允許有并列的名次，名次后面不會(huì)空出位置，即序號(hào)連續(xù)。

分組窗口函數(shù)：

• NTILE()：將窗口內(nèi)的行分為指定數(shù)量的組，每組的行數(shù)盡可能相等。

分布窗口函數(shù)

• PERCENT_RANK()：計(jì)算每一行的相對(duì)排名，返回一個(gè)介于0到1之間的值，表示當(dāng)前行在分區(qū)中的排名百分比。
• CUME_DIST()：計(jì)算小于或等于當(dāng)前行的行數(shù)占窗口總行數(shù)的比例。

取值窗口函數(shù)

• LAG()：訪問當(dāng)前行之前的第n行數(shù)據(jù)。
• LEAD()：訪問當(dāng)前行之后的第n行數(shù)據(jù)。
• FIRST_VALUE()：獲取窗口內(nèi)第一行的值。
• LAST_VALUE()：獲取窗口內(nèi)最后一行的值。
• NTH_VALUE()：獲取窗口內(nèi)第n行的值，如果存在多行則返回第一個(gè)。

這里以employees表為例：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(255),
    department_name VARCHAR(255),
    salary DECIMAL(10, 2)
);
-- 插入數(shù)據(jù)
INSERT INTO employees (employee_id, department_name, name, salary) VALUES
(1, '財(cái)務(wù)部', '張三', 30000),
(2, '財(cái)務(wù)部', '李四', 25000),
(3, '市場(chǎng)部', '王五', 40000),
(4, '市場(chǎng)部', '趙六', 35000),
(5, '市場(chǎng)部', '孫七', 50000),
(6, '技術(shù)部', '周八', 45000),
(7, '技術(shù)部', '錢九', 60000),
(8, '技術(shù)部', '吳十', 55000);

聚合窗口函數(shù)查詢

SELECT
    employee_id,
    name,
    department_name,
    salary,
    SUM(salary) OVER (PARTITION BY department_name) AS total_salary,
    AVG(salary) OVER (PARTITION BY department_name) AS average_salary,
    COUNT(*) OVER (PARTITION BY department_name) AS employee_count,
    MAX(salary) OVER (PARTITION BY department_name) AS max_salary,
    MIN(salary) OVER (PARTITION BY department_name) AS min_salary
FROM employees;

執(zhí)行輸入如下：

排名窗口函數(shù)查詢

ROW_NUMBER()窗口函數(shù)查詢

SELECT
    employee_id,
    name,
    department_name,
    salary,
    ROW_NUMBER() OVER () AS salary_rank
FROM employees;

執(zhí)行輸出如下：

如果想要在部門內(nèi)分區(qū)添加行號(hào)

SELECT
    employee_id,
    name,
    department_name,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_name ORDER BY salary DESC) AS salary_rank
FROM employees;

執(zhí)行結(jié)果如下：

RANK() && DENSE_RANK()

RANK() 和 DENSE_RANK() 函數(shù)的主要區(qū)別在于處理并列名次的方式。RANK() 函數(shù)在遇到并列名次時(shí)會(huì)在下一個(gè)名次處留出空位，而 DENSE_RANK() 函數(shù)則不會(huì)留出空位，名次連續(xù)。
我們需要確保每個(gè)部門至少有兩個(gè)員工的薪資是相同的。我們假如插入的數(shù)據(jù)如下：

INSERT INTO employees (employee_id, name, department_name, salary) VALUES
(1, 'Alice', '財(cái)務(wù)部', 70000),
(2, 'Bob', '財(cái)務(wù)部', 60000),
(3, 'Charlie', '財(cái)務(wù)部', 60000), -- 與Bob薪資相同
(4, 'David', '市場(chǎng)部', 80000),
(5, 'Eve', '市場(chǎng)部', 80000), -- 與David薪資相同
(6, 'Frank', '市場(chǎng)部', 50000),
(7, 'Grace', '技術(shù)部', 90000),
(8, 'Heidi', '技術(shù)部', 75000),
(9, 'Ivan', '技術(shù)部', 75000), -- 與Heidi薪資相同
(10, 'Judy', '財(cái)務(wù)部', 60000), -- 與Bob和Charlie薪資相同
(11, 'Karl', '市場(chǎng)部', 50000), -- 與Frank薪資相同
(12, 'Linda', '技術(shù)部', 90000), -- 與Grace薪資相同
(13, 'Mike', '財(cái)務(wù)部', 50000), -- 新薪資水平
(14, 'Nancy', '市場(chǎng)部', 60000), -- 與Bob和Charlie薪資相同
(15, 'Oliver', '技術(shù)部', 60000); -- 與Bob和Charlie薪資相同

執(zhí)行包含 RANK() 和 DENSE_RANK() 函數(shù)的查詢：

SELECT
    employee_id,
    name,
    department_name,
    salary,
    RANK() OVER (PARTITION BY department_name ORDER BY salary DESC) AS salary_rank,
    DENSE_RANK() OVER (PARTITION BY department_name ORDER BY salary DESC) AS dense_salary_rank
FROM employees
ORDER BY department_name, salary DESC;

執(zhí)行結(jié)果如下：

在這個(gè)結(jié)果中，以財(cái)務(wù)部為例，

• Alice 薪資最高，排名 1。
• Bob、Charlie 和 Judy 薪資相同，使用 RANK() 函數(shù)時(shí)，他們的排名依次為 2、2、2（跳過3、4），使用 DENSE_RANK() 函數(shù)時(shí)，排名連續(xù)為 2、2、2（名次連續(xù)）。

分組窗口函數(shù)查詢

分組窗口函數(shù)NTILE() 將數(shù)據(jù)分為指定數(shù)量的組，每組的行數(shù)盡可能相等。假設(shè)我們要根據(jù)員工的薪資將他們分為四組（例如：高收入、中等收入、較低收入和最低收入），我們可以對(duì)每個(gè)部門使用 NTILE(4) 來實(shí)現(xiàn)：

    SELECT
    employee_id,
    name,
    department_name,
    salary,
    NTILE(4) OVER (PARTITION BY department_name ORDER BY salary DESC) AS salary_quartile
FROM employees;

執(zhí)行結(jié)果如下：

在這個(gè)結(jié)果中，每個(gè)部分都被分為了4組，以市場(chǎng)部為例：
市場(chǎng)部 的 David 和 Eve 位于最高收入組（1），Nancy在中等收入組(2)，F(xiàn)rank分到了較低收入組（3），Karl 被分到了最低收入組（4）注意，由于 NTILE() 函數(shù)的目的是將數(shù)據(jù)分為盡可能相等的組，每個(gè)部門5個(gè)人，分為4組，每個(gè)部門肯定有兩個(gè)人會(huì)分到同一個(gè)組內(nèi)。

分布窗口函數(shù)查詢

SELECT
    employee_id,
    name,
    department_name,
    salary,
    PERCENT_RANK() OVER (PARTITION BY department_name ORDER BY salary DESC) AS salary_percent_rank,
    CUME_DIST() OVER (PARTITION BY department_name ORDER BY salary DESC) AS salary_cume_dist
FROM employees;

執(zhí)行結(jié)果如下：

ERCENT_RANK() 說明：
PERCENT_RANK() 函數(shù)返回一個(gè)介于0到1之間的值，表示當(dāng)前員工薪資在部門中的排名百分比。例如，如果一個(gè)員工的 salary_percent_rank 是0.50，這意味著他的薪資低于或等于部門中50%的員工薪資。

• 在 “財(cái)務(wù)部” 中，Alice 的薪資是最高的，所以她的薪資百分比排名是0.00（即她是最高薪）。Judy、Bob 和 Charlie 的薪資相同，并且低于Alice，所以他們的薪資百分比排名是0.25。
• Mike 的薪資是最低的，所以他的薪資百分比排名是1.00。

CUME_DIST() 說明：

• CUME_DIST() 函數(shù)返回一個(gè)介于0到1之間的值，用于求分區(qū)中大于等于或小于等于當(dāng)前行的數(shù)據(jù)在分區(qū)中的占比。如果是升序排列，則統(tǒng)計(jì)是：小于等于當(dāng)前值的行數(shù)/總行數(shù) ，如果是降序排列，則統(tǒng)計(jì)：大于等于當(dāng)前值的行數(shù)/總行數(shù)。
• 這里按薪水降序排列，表示大于或等于當(dāng)前員工薪資的員工數(shù)量占部門總員工數(shù)量的比例。在 “財(cái)務(wù)部” 中，Alice 的 salary_cume_dist 是0.2，因?yàn)樗男劫Y是最高的。Judy、Bob 和 Charlie 的薪資相同，并且有4個(gè)的員工薪資大于等于他們，所以他們的 salary_cume_dist 是0.80。Mike 是最低薪資，部門所有人都大于等于他，所以他的 salary_cume_dist 是1。

取值窗口函數(shù)查詢

LAG

SELECT
    employee_id,
    name,
    department_name,
    salary,
    LAG(salary, 1, 0) OVER (PARTITION BY department_name ORDER BY salary DESC) AS prev_salary
FROM employees
ORDER BY department_name, salary DESC;

LAG(salary, 1, 0) 說明：

LAG() 函數(shù)返回當(dāng)前員工之前第一個(gè)員工的薪資。如果沒有前一個(gè)員工（即當(dāng)前是部門中薪資最高的員工），則返回指定的默認(rèn)值，這里我們使用0作為默認(rèn)值。

LEAD

SELECT
    employee_id,
    name,
    department_name,
    salary,
    LEAD(salary, 1, 0) OVER (PARTITION BY department_name ORDER BY salary DESC) AS next_salary
FROM employees
ORDER BY department_name, salary DESC;

LEAD(salary, 1, 0) 說明：

LEAD() 函數(shù)返回當(dāng)前員工之后第一個(gè)員工的薪資。如果沒有后一個(gè)員工（即當(dāng)前是部門中薪資最低的員工），則返回指定的默認(rèn)值，這里我們使用0作為默認(rèn)值。

FIRST_VALUE

FIRST_VALUE函數(shù)用于取當(dāng)前行所對(duì)應(yīng)窗口的第一條數(shù)據(jù)的值。

SELECT
    employee_id,
    name,
    department_name,
    salary,
    FIRST_VALUE(salary) OVER (PARTITION BY department_name ORDER BY salary DESC) AS max_salary_in_department
FROM employees
ORDER BY department_name, salary DESC;

執(zhí)行結(jié)果如下：

FIRST_VALUE(salary) 說明：
FIRST_VALUE() 函數(shù)返回部門中薪資是按降序排列的，因此 FIRST_VALUE() 實(shí)際上是返回該部門的最高薪資。

LAST_VALUE

LAST_VALUE函數(shù)用于取當(dāng)前行所對(duì)應(yīng)窗口的最后一條數(shù)據(jù)的值。

SELECT
    employee_id,
    name,
    department_name,
    salary,
    LAST_VALUE(salary) OVER (PARTITION BY department_name ORDER BY salary DESC) AS min_salary_in_department
FROM employees
ORDER BY department_name, salary DESC;

執(zhí)行結(jié)果如下：

NTH_VALUE

獲取窗口內(nèi)第n行的值

SELECT
    employee_id,
    name,
    department_name,
    salary,
    NTH_VALUE(salary, 3) OVER (PARTITION BY department_name ORDER BY salary DESC) AS third_salary_in_department
FROM employees
ORDER BY department_name, salary DESC;

執(zhí)行結(jié)果

注意，這里third_salary_in_department會(huì)有null值，實(shí)際上這和分區(qū)內(nèi)默認(rèn)的取值范圍有關(guān)，NTH_VALUE(salary, 3) 將始終返回每個(gè)分區(qū)中的第三行的值，因?yàn)檫@里默認(rèn)取值范圍從排序后的第一個(gè)行（即 ORDER BY 子句中的第一個(gè)值）到當(dāng)前行，所以在當(dāng)前行窗口范圍內(nèi)沒有第三行的值時(shí)，就會(huì)顯示null值。如果我們指定窗口取值范圍如下：

SELECT
    employee_id,
    name,
    department_name,
    salary,
    NTH_VALUE(salary, 3) OVER (PARTITION BY department_name ORDER BY salary desc
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS third_salary_in_department
FROM employees
ORDER BY department_name;

則執(zhí)行結(jié)果如下：

這里使用了ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING來指定窗口的范圍為第一行到最后一行，這意味著窗口包括了分區(qū)內(nèi)的所有行。在這種情況下，NTH_VALUE(salary, 3) 將始終返回每個(gè)分區(qū)中的第三行的值，只要該分區(qū)至少有三行數(shù)據(jù)。

4.窗口的范圍

窗口的范圍，有的資料上面也叫Framing（分幀）,大致可以分為兩種，一種是根據(jù)行（rows between）來劃分，一種是根據(jù)列值（range between）來劃分，它們都可以確定一個(gè)窗口應(yīng)該包含哪些行。窗口的開始和結(jié)束可以使用以下關(guān)鍵字來定義：

• UNBOUNDED PRECEDING：從分區(qū)中的第一行開始（前面所有行）。
• CURRENT ROW：包括當(dāng)前行。
• n PRECEDING：從當(dāng)前行之前的第 n 行開始。
• n FOLLOWING：包括當(dāng)前行之后第 n 行。
• UNBOUNDED FOLLOWING：到分區(qū)中的最后一行結(jié)束（后面所有行）。

基于行劃分：rows between…and…

語法格式如下：

sum(amount) over(order by <column> rows between <start> and <end>)

例如，假設(shè)你有一個(gè)包含銷售數(shù)據(jù)的表，并希望計(jì)算每一行及其前兩行的總和：

SELECT
    sales_date,
    sales_amount,
    SUM(sales_amount) OVER (
        ORDER BY sales_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS running_total
FROM
    sales;

基于列值劃分：range between…and…

語法格式如下：

sum(amount) over(order by <column> range between <start> and <end>)

例如，我們有一個(gè)包含商品數(shù)據(jù)的表，我們希望計(jì)算每個(gè)商品價(jià)格及其前后20單位價(jià)格范圍內(nèi)的商品總和：

SELECT
    product_id,
    product_price,
    SUM(product_price) OVER (
        ORDER BY product_price
        RANGE BETWEEN 20 PRECEDING AND 20 FOLLOWING
    ) AS price_range_total
FROM
    products;

假設(shè)當(dāng)前行product_price值為70，那其窗口范圍是product_price列的值位于50（70-20）到90（70+20）之間的所有的行。

5.窗口函數(shù)的缺省值

over后的窗口函數(shù)劃分語句，包括partition by 、order by 、（row|range）between ...and...這三部分，實(shí)際上這些內(nèi)容也都可以省略不寫。

• partition by省略不寫，表示不分區(qū)。在不進(jìn)行分區(qū)的情況下，將會(huì)把整張表的全部?jī)?nèi)容作為窗口進(jìn)行劃分。
• order by 省略，表示不排序
• （row|range）between …and…省略不寫，則使用其默認(rèn)值，默認(rèn)值分為以下兩種情況：
  • over后面包含order by ,則默認(rèn)值為：range between unbounded preceding and current row
  • over后面不包含order by ,則默認(rèn)值為：rows between unbounded preceding and unbounded following。

小結(jié)：

可以看出，窗口產(chǎn)生的用法很類似聚合函數(shù)，不同的是聚合函數(shù)是將多行數(shù)據(jù)匯總為單個(gè)結(jié)果，窗口函數(shù)的話在同一個(gè)select中我們可以按照不同的列進(jìn)行分區(qū)，而且多個(gè)窗口函數(shù)的列之間不受影響，功能很強(qiáng)大，語法也比較靈活，在進(jìn)行復(fù)雜的數(shù)據(jù)分析或?qū)懸恍﹫?bào)表時(shí)我們就可以考慮用窗口函數(shù)來去實(shí)現(xiàn)。

參考文檔：

https://developer.aliyun.com/article/1541419
https://mysql.net.cn/doc/refman/8.0/en/window-functions.html
https://support.huaweicloud.com/intl/zh-cn/sqlref-spark-dli/dli_08_0069.html
https://www.cnblogs.com/xfeiyun/p/16965394.html

到此這篇關(guān)于SQL中的窗口函數(shù)的文章就介紹到這了,更多相關(guān)SQL 窗口函數(shù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論