今天我們來聊聊 SQL 窗口函數(shù)。你是不是經(jīng)常對復(fù)雜的報表查詢感到比較頭疼？在網(wǎng)上找到了一些窗口函數(shù)的 demo，但因為對實現(xiàn)原理一知半解，經(jīng)常導(dǎo)致寫的 SQL 查詢的數(shù)據(jù)口徑對不上。如果你有這樣的問題，就靜下心來好好學(xué)習(xí)一下這一講的內(nèi)容。我會給你詳細地介紹 SQL 窗口函數(shù)的原理和使用方法，幫你找到解決問題的方案。

SQL 窗口函數(shù)介紹

首先，我們先對 SQL 窗口函數(shù)的使用場景、所處的執(zhí)行階段以及它的原理做一個簡單的了解。

1. SQL 窗口函數(shù)的使用場景：窗口函數(shù)只能在 select 查詢列表中使用，不能用于 update 和 delete 語句。窗口函數(shù)不影響查詢記錄的數(shù)量，它的作用僅僅是在 select 列表里面新增一個列而已，且多個窗口函數(shù)之間互不影響。
2. SQL 窗口函數(shù)所處的執(zhí)行階段：它只能出現(xiàn)在 select 列表中，晚于 from、where、group by、having 的執(zhí)行。早于 order by、limit、select distinct 的執(zhí)行。
3. SQL 窗口函數(shù)的原理：窗口函數(shù)顧名思義，就是將 SQL 查詢出來的結(jié)果看成一個大窗口。可以對整個窗口進行分區(qū)（partition by）, 每個分區(qū)包含一個滑動窗口（frame）。

圖片

over() 函數(shù)

在我們的 SQL 中通過調(diào)用 over() 函數(shù)，我們可以生成一個窗口（基于 SQL 查詢的全部結(jié)果）。over 函數(shù)內(nèi)部支持如下參數(shù)：

• partition by <分區(qū)列名>；
• order by <列名 [asc|desc] ，用于指定分區(qū)內(nèi)的數(shù)據(jù)的排列先后順序>；
• <range|rows> between <滑動窗口的上邊界> and <滑動窗口的下邊界> 用于指定滑動窗口的大小。

語法解釋

• 當 over 函數(shù)內(nèi)的參數(shù)為空時，整個結(jié)果集就是一個分區(qū)（不指定 partition by）。滑動窗口的大小也是整個結(jié)果集。
• 當 order by 不指定的時候，記錄行（rows）使用默認的順序，也就是從數(shù)據(jù)庫查詢出來的順序。因為窗口函數(shù)在 order by 語句之前執(zhí)行，所以順序只能是默認的順序。在此我強烈建議窗口函數(shù)都帶上 order by 從句，否則結(jié)果列可能會是不確定的值。MySQL 可以支持不帶 order by，但在 Oracle 和 SQL Server 上，指定滑動窗口時，必須帶上 order by，否則就會報錯。
• 當沒有指定滑動窗口大小的參數(shù)時，即沒有指定 range 或者 rows 從句，滑動窗口的默認大小為：上邊界=分區(qū)的第一條記錄，下邊界=當前記錄。

滑動窗口（Frame）語法講解

滑動窗口是基于當前行的，它有一個上邊界和一個下邊界，滑動窗口不能脫離 partition 獨立存在。當指定了 partition by 和 order by，而不指定滑動窗口時，滑動窗口默認的上邊界為 partition 內(nèi)第一條記錄，下邊界為當前記錄。每一行記錄都有一個滑動窗口。

指定滑動窗口的時候，必須是已經(jīng)有了 partition by 從句，否則 SQL 會報錯。雖然 MySQL8 支持，但是不建議你這樣使用。當 over 函數(shù)里面沒有 partition by 從句和滑動窗口從句時，默認的滑動窗口就是整個結(jié)果集。

滑動窗口大小支持兩種模式，range 模式和 rows 模式。

• rows 模式
  rows between N preceding and M following

滑動窗口的構(gòu)成以當前邏輯行為基準點，向上指定 N 行 (邏輯行) 為上邊界，向下指定 M 行 (邏輯行) 為下邊界。

• range 模式（注意：range 模式必須指定 order by 從句）
  range between N preceding and M following

滑動窗口的構(gòu)成以當前邏輯行為基準點，值是 order by 從句中使用的列的值。

上邊界：當前邏輯行之前 值 >= 當前邏輯行的值 - N 的所有邏輯行

下邊界：當前邏輯行之后 值 <= 當前邏輯行的值 + M 的所有邏輯行

邊界常量

• unbounded preceding：表示分區(qū)內(nèi)第一條記錄 (邏輯行)，不管是否指定 order by 從句。
• unbounded following：表示分區(qū)內(nèi)最后一條記錄 (邏輯行)，不管是否指定 order by 從句。
• current row：字面意思是當前行，在 rows 模式下，表示當前邏輯行。在 range 模式下，表示在當前邏輯行前后，值和當前邏輯行的值相等的所有邏輯行 (range 模式下指定了 order by，值都是有序的)。
• N preceding 和 N following：參考 range 和 rows 模式里面的解釋，分別表示往前 N 行的數(shù)據(jù)和往后 N 行的數(shù)據(jù)。

操作函數(shù)

當 over 函數(shù)指定了窗口之后，需要操作函數(shù)對分區(qū)內(nèi)（partition）或者滑動窗口內(nèi)（Frame）的數(shù)據(jù)進行操作。

窗口函數(shù)分為 聚合函數(shù)和 非聚合函數(shù)。聚合函數(shù)處理數(shù)據(jù)大部分都是基于滑動窗口的。非聚合函數(shù)處理數(shù)據(jù)有基于滑動窗口的，也有基于分區(qū)的。下表是常用的操作函數(shù)，另外不同的數(shù)據(jù)庫還會實現(xiàn)自身特有的操作函數(shù)。

關(guān)于操作函數(shù)的詳細描述和具體的使用方式，感興趣的話你可以參考鏈接中的內(nèi)容：

https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html

https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html

示例

接下來我們通過示例表 names，來具體操作一下。這是示例的數(shù)據(jù)。

圖 2 示例數(shù)據(jù)

• row_number 函數(shù)
  按照 name 列分區(qū)，為每一行記錄生成行號，行號按照 val 列的值倒序生成。

select name,val,
row_number() over(partition by name order by val desc) num
from names

• dense_rank 函數(shù)
  按照 name 列分區(qū)，為每一行記錄按照 val 列的值倒序生成排名。排名不能有間隔。

select name,val,
dense_rank() over(partition by name order by val desc) rnk
from names

• rank 函數(shù)
  按照 name 列分區(qū)，為每一行記錄按照 val 列的值倒序生成排名。排名允許有間隔。

select name,val,
rank() over(partition by name order by val desc) rnk
from names

• lag 函數(shù)
  按照 name 列分區(qū)，為每一行記錄生成 val 列和它前面一條記錄的差值，前面的記錄不存在用 0 表示。

select name,val,
val - lag(val,1,0) over(partition by name order by val desc) diff
from names

其中 lag(val,1,0) 表示，獲取當前行前面 1 行的 val 字段的值，如果前面一行不存在，用 0 表示默認值。

• first_value 函數(shù)
  按照 name 列分區(qū)，為每一行記錄生成 val 列和他所在滑動窗口內(nèi)，第一條記錄 val 列表的差值，按照 val 的值倒序排列。

select name,val,
val - first_value(val) over(partition by name order by val desc) diff
from names

• 混合使用

select name,val,
row_number() over(partitionbynameorderby val desc) num,
val - first_value(val) over(partitionbynameorderby val desc) diff,
dense_rank() over(partitionbynameorderby val desc) rnk
fromnames

因為每個窗口函數(shù)都是獨立的，互不影響，可以在 select 列表里面使用多個窗口函數(shù)生成多列，各個列也互不影響。

總結(jié)

圖片

通過學(xué)習(xí)，相信你對 SQL 窗口函數(shù)已經(jīng)有了一個整體和直觀的認識，對其中的使用細節(jié)也有了一定的理解，特別是對分區(qū)、滑動窗口、range 和 rows 模式下滑動窗口的邊界，以及操作函數(shù)的作用范圍和使用方式有了一個清楚的認知。但想要牢固地掌握這些知識，還需要你學(xué)以致用，多多練習(xí)。