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

SQL 窗口函數介紹

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

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

圖片

over() 函數

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

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

語法解釋

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

滑動窗口（Frame）語法講解

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

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

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

• rows 模式
  rows between N preceding and M following

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

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

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

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

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

邊界常量

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

操作函數

當 over 函數指定了窗口之后，需要操作函數對分區內（partition）或者滑動窗口內（Frame）的數據進行操作。

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

關于操作函數的詳細描述和具體的使用方式，感興趣的話你可以參考鏈接中的內容：

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，來具體操作一下。這是示例的數據。

圖 2 示例數據

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

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

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

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

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

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

• lag 函數
  按照 name 列分區，為每一行記錄生成 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 函數
  按照 name 列分區，為每一行記錄生成 val 列和他所在滑動窗口內，第一條記錄 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

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

總結

圖片

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