SQL窗口函數原理和使用
今天我們來聊聊 SQL 窗口函數。你是不是經常對復雜的報表查詢感到比較頭疼？在網上找到了一些窗口函數的 demo，但因為對實現原理一知半解，經常導致寫的 SQL 查詢的數據口徑對不上。如果你有這樣的問題，就靜下心來好好學習一下這一講的內容。我會給你詳細地介紹 SQL 窗口函數的原理和使用方法，幫你找到解決問題的方案。
SQL 窗口函數介紹
首先，我們先對 SQL 窗口函數的使用場景、所處的執行階段以及它的原理做一個簡單的了解。
- SQL 窗口函數的使用場景：窗口函數只能在 select 查詢列表中使用，不能用于 update 和 delete 語句。窗口函數不影響查詢記錄的數量，它的作用僅僅是在 select 列表里面新增一個列而已，且多個窗口函數之間互不影響。
- SQL 窗口函數所處的執行階段：它只能出現在 select 列表中，晚于 from、where、group by、having 的執行。早于 order by、limit、select distinct 的執行。
- 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 模式下滑動窗口的邊界，以及操作函數的作用范圍和使用方式有了一個清楚的認知。但想要牢固地掌握這些知識，還需要你學以致用，多多練習。
