在日常的數據分析工作中，我們可能會經常遇到類似這樣的需求：環比如何計算？怎麼樣得到各部門工資排名前N名員工列表?查找各部門每 人工資占部門總工資的百分比？

對于這樣的需求，使用傳統的SQL實現起來比較困難。這類需求都有一個共同的特點，需要在單表中滿足某些條件的記 錄集内部做一些函數操作，不是簡單的表連接，也不是簡單的聚合可以實現的，通常會讓寫SQL的同學焦頭爛額、絞盡 腦汁，費了大半天時間寫出來一堆長長的晦澀難懂的自連接SQL，且性能低下，難以維護。

要解決此類問題，最方便的就是使用窗口函數。

PART

01

MySQL窗口函數

MySQL從8.0開始支持窗口函數，這個功能在大多商業數據庫和部分開源數據庫中早已支持，有的也叫分析函數。

什麼叫窗口?

窗口的概念非常重要，它可以理解為記錄集合，窗口函數也就是在滿足某種條件的記錄集合上執行的特殊函數。對于每 條記錄都要在此窗口内執行函數，有的函數随着記錄不同，窗口大小都是固定的，這種屬于靜态窗口；有的函數則相反，不同的記錄對應着不同的窗口，這種動态變化的窗口叫滑動窗口。 窗口函數和普通聚合函數也很容易混淆，二者區别如下：

• 聚合函數是将多條記錄聚合為一條；而窗口函數是每條記錄都會執行，有幾條記錄執行完還是幾條。
• 聚合函數也可以用于窗口函數中，這個後面會舉例說明。 下面是一個窗口函數的簡單例子：

--按用戶id分組，消費金額進行降序排select * FROM ( select id, order_money, member_id, create_time, row_number() over(partition by member_id ORDER BY order_money desc) as row_num from dm_order_2018) t;

上面例子中，row_number()over(partition by member_id order by order_money desc)這部分都屬于窗口函數，它的功能是顯示 每個用戶按照訂單金額從大到小排序的序号。

PART

02

窗口函數分類及語法

按照功能劃分，可以把MySQL支持的窗口函數分為如下幾類：

• 序号函數：row_number() / rank() / dense_rank()
• 分布函數：percent_rank() / cume_dist()
• 前後函數：lag() / lead()
• 頭尾函數：first_val() / last_val()
• 其他函數：nth_value() / nfile()窗口函數語法：

window_function_name(expression) OVER ( [partition_defintion] [order_definition] [frame_definition] )PARTITION BY <expression>[{,<expression>...}] 以xx字段作為維度；ORDER BY <expression> [ASC|DESC], [{,<expression>...}] 以xx字段排序，默認升序；[frame_definition] frame_unit {<frame_start>|<frame_between>} 可以認為是更細分的累計。

先指定作為窗口函數的函數名，然後是OVER(…)，就算OVER裡面沒有内容，括号也需要保留。窗口函數的一個概念是當前行，當前行屬于某個窗口，窗口由“[partition_defintion]”，“[order_definition]”，“[frame_definition]“确定。partition_defintion翻譯過來應該是分區，語法是"PARTITION BY < expression>[{,< expression>…}]"，它會根據單個或者多個表達式的計算結果來分區（列名也是一種表達式，它的結果就是列名本身）。在前面的例子中，結果中的每一行都有自己的分區。frame_definition 這裡先講frame_definition，可能應該是叫幀吧。它的作用是在分區裡面再進一步細分窗口。語法是"frame_unit {< frame_start>|< frame_between>}"， frame_unit有兩種，分别是ROWS和RANGE，由ROWS定義的frame是由開始和結束位置的行确定的，由RANGE定義的frame由在某個值區間的行确定。如果隻指定了frame的開始位置，那麼結束位置就默認為當前行。frame_start有三種： UNBOUNDED PRECEDING: 區間的第一行 N PRECEDING: 當前行之前的N行，N可以是數字，也可以是一個能計算出數字的表達式 CURRENT ROW: 當前行frame_between的可以取的值如下： frame_start：如前面所列 UNBOUNDED FOLLOWING：區間的最後一行 N FOLLOWING：當前行之後的N行，N可以是數字，也可以是一個能計算出數字的表達式如果沒顯式指定frame的話，MySQL會認為frame是“ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING”

今天分享下MySQL中的窗口函數，這對分析查詢很有幫助，在工作中，可能會遇到各種百分比、排序、累積求和等需求需要進行窗口查詢。明天舉個具體案例來逐個講解數據分析函數。

歡迎關注微信公衆号，訪問更多精彩：數據之魅。

如需轉載，請聯系授權，謝謝合作。

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!