SQL 窗口函數簡介

您喜歡討厭的非常強大的功能（但需要知道）

SQL 窗口函數提供了一些極其強大和有用的特性。 但對許多人來說，由於它們對標準 SQL 如此陌生，因此它們難以學習和理解，語法也很奇怪——而且經常被避免使用。

窗口函數可以簡單地解釋為類似於聚合的計算函數，但是在通過GROUP BY子句組合然後隱藏正在聚合的各個行的普通聚合中，窗口函數可以訪問各個行並且可以將這些行中的一些屬性添加到結果集。

在這個 SQL 窗口函數教程中，我將讓您開始使用窗口函數，解釋它們的好處以及何時使用它們，並為您提供真實的示例來幫助您理解這些概念。

了解您的數據的窗口

SQL 中最常用和最重要的特性之一是以特定方式聚合或分組數據行的能力。 但是，在某些情況下，分組可能會變得非常複雜，具體取決於所需的內容。

您是否曾經想要遍歷查詢結果以獲得排名、前 x 列表或類似的？ 您是否有任何分析項目想要為可視化工具準備好數據，但發現這幾乎不可能或太複雜以至於不值得？

窗口函數可以使事情變得更容易。 在獲得查詢結果之後——即，在WHERE子句和任何標準聚合之後，窗口函數將作用於剩餘的行（數據窗口）並為您提供所需的內容。

我們將要研究的一些窗口函數包括：

• OVER
• COUNT()
• SUM()
• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• LEAD()
• LAG()

太過簡單

OVER子句用於指定窗口函數，並且必須始終包含在語句中。 OVER子句中的默認值是整個行集。 例如，讓我們看一下公司數據庫中的員工表，並在每一行顯示員工總數，以及每個員工的信息，包括他們何時加入公司。

 SELECT COUNT(*) OVER() As NumEmployees, firstname, lastname, date_started FROM Employee ORDER BY date_started;

上面的代碼，就像許多窗口函數一樣，也可以用一種更熟悉的非窗口方式編寫——在這個簡單的例子中，這還不算太糟糕：

 SELECT (SELECT COUNT(*) FROM Employee) as NumEmployees, firstname, lastname, date_started FROM Employee ORDER BY date_started;

但是現在，假設我們希望顯示與該行中的員工在同一個月開始工作的員工數量。 我們需要將每行的計數縮小或限制在該月。 這是怎麼做的？ 我們使用 window PARTITION子句，如下所示：

 SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started),YEAR(date_started)) As NumPerMonth, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname FROM Employee ORDER BY date_started;

分區允許您按某個或多個值將窗口過濾為多個部分。 每個部分通常被稱為窗框。

更進一步，假設我們不僅想知道有多少員工在同一個月開始工作，而且我們想顯示他們當月開始的順序。 為此，我們可以使用熟悉的ORDER BY子句。 但是，在窗口函數中， ORDER BY的行為與查詢結束時的行為略有不同。

 SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started), YEAR(date_started) ORDER BY date_started) As NumThisMonth, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname, date_started FROM Employee ORDER BY date_started;

在本例中， ORDER BY修改窗口，使其從分區的開始（在本例中為員工開始的月份和年份）到當前行。 因此，計數在每個分區重新開始。

排名它

窗口函數對於排名目的非常有用。 之前我們看到使用COUNT聚合函數可以讓我們看到員工加入公司的順序。 我們還可以使用窗口排名函數，例如ROW_NUMBER() 、 RANK()和DENSE_RANK() 。

我們在下個月添加新員工並刪除分區後可以看到差異：

 SELECT ROW_NUMBER() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As StartingRank, RANK() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As EmployeeRank, DENSE_RANK() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As DenseRank, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname, date_started FROM Employee ORDER BY date_started;

你可以看到差異。 ROW_NUMBER()給出給定分區內的順序計數（但如果沒有分區，它會遍歷所有行）。 RANK()根據ORDER BY子句給出每行的排名。 它顯示平局，然後跳過下一個排名。 DENSE_RANK也顯示平局，但隨後繼續下一個連續值，就好像沒有平局一樣。

其他排名功能包括：

• CUME_DIST – 計算分區內當前行的相對排名
• NTILE – 盡可能平均地劃分每個窗口分區的行
• PERCENT_RANK – 當前行的百分比排名

另請注意，在此示例中，您可以在單個查詢中擁有多個 Window 函數，並且每個函數的分區和順序都可以不同！

行、範圍和框架，哦，我的

要在OVER()子句中進一步定義或限制您的窗口框架，您可以使用ROWS和RANGE 。 使用ROWS子句，您可以將分區中包含的行指定為當前行之前或之後的行。

 SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal' FROM sales_products;

在此示例中，窗口框架從第一行到當前行減 1，並且每一行的窗口大小繼續增加。

範圍有點不同，我們可能會得到不同的結果。

 SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal' FROM sales_products;

範圍將包括窗口框架中與當前行具有相同ORDER BY值的那些行。 因此，如果ORDER BY不是唯一的，您可能會使用RANGE獲得重複項。

有些人將ROWS描述為物理運算符，而RANGE是邏輯運算符。 在任何情況下， ROWS和RANGE的默認值始終是UNBOUNDED PRECEDING AND CURRENT ROW 。

還有什麼？

大多數標準聚合函數都與 Window 函數一起使用。 我們已經在示例中看到了COUNT 。 其他包括SUM 、 AVG 、 MIN 、 MAX等。

使用窗口函數，您還可以使用LAG和LEAD以及FIRST_VALUE和LAST_VALUE訪問以前的記錄和後續記錄。 例如，假設您想在每一行顯示當月的銷售數字，以及上個月銷售數字之間的差異。 你可能會做這樣的事情：

 SELECT id, OrderMonth, OrderYear, product, sales, sales - LAG(sales,1) OVER (PARTITION BY product ORDER BY OrderYear, OrderMonth) As sales_change FROM sales_products WHERE sale_year = 2019;

基本上，SQL 窗口函數非常強大

雖然這是對 SQL 窗口函數的快速介紹，但希望它會激發您的興趣，了解它們可以做的所有事情。 我們了解到，窗口函數執行的計算類似於聚合函數的執行方式，但額外的好處是它們可以訪問各個行中的數據，這使得它們非常強大。 它們總是包含OVER子句，並且可能包含PARTITION BY 、 ORDER BY和大量聚合（ SUM 、 COUNT等）和其他位置函數（ LEAD 、 LAG ）。 我們還了解了窗口框架以及它們如何封裝數據部分。

請注意，不同風格的 SQL 可能以不同的方式實現窗口函數，並且有些可能沒有實現所有窗口函數或子句。 確保檢查您正在使用的平台的文檔。

如果作為 SQL 開發人員，您對調整 SQL 數據庫性能感興趣，請查看面向開發人員的 SQL 數據庫性能調整。

開窗快樂！

有關具體實現的更多信息，請參閱：

• PostgreSQL 用於 PostgreSQL 實現的 Window Functions 文檔。
• SELECT - Microsoft 的 OVER 子句 (Transact-SQL)文檔。
• SQL Server 中的窗口函數對 SQL Server 實現及其第 2 部分有一個很好的概述。