如何在SQL中使用窗口函數

SQL中的窗口函數啟用跨相關行的計算，而不會崩潰，允許在分區內進行排名，運行總計，移動平均值和行比較。基本語法在（按sort_expression frame_claueser通過partition_expression訂單進行分區的function_name（expression）），其中按組行劃分，在分區內按序列進行訂單，然後frame_clauesh定義了行的窗口。對於排名，rank（），row_number（）和dense_rank（）在組內分配等級，而處理領帶的差異。運行總計使用sum（）與訂單和行無界的前面的訂單，而移動平均值時，將avg（）應用於框架上的框架和當前行之間的框架。 lag（）和Lead（）訪問上一個或下一個行值以進行更改分析。 NTILE() divides data into buckets (eg, quartiles), and PERCENT_RANK() gives relative rank from 0 to 1. Best practices include always using OVER(), applying PARTITION BY for group-level calculations, using ORDER BY for sequence-sensitive logic, choosing ROWS or RANGE frames appropriately, and avoiding window functions in WHERE clauses by using CTEs or subqueries for filtering.這些功能在PostgreSQL，SQL Server，BigQuery，Snowflake，Oracle，Oracle和MySQL 8.0中得到支持，當需要從周圍的行中上下文時，使復雜的分析查詢更加有效，更可讀。

SQL中的窗口函數讓您可以在與當前行相關的一組行中執行計算，而無需將它們折疊成單個輸出行 - 類似於組的匯總函數。它們對於數據分區中的排名，運行總數，移動平均值和比較特別有用。

這是有效使用窗口功能的方法：

1。窗口函數的基本語法

窗口函數的一般結構是：

 function_name（表達式）
    [通過partition_expression分區]
    [通過sort_expression訂購]
    [frame_clause]
）

• function_name ：示例包括ROW_NUMBER() ， RANK() ， SUM() ， AVG() ， LAG() ，等。
• PARTITION BY ：將設置的結果分為分區（如組）。該函數應用於每個分區。
• ORDER BY ：確定每個分區內的行順序。
• frame_clause ：定義當前行周圍的行（例如，“窗框”）的子集（例如， ROWS BETWEEN 3 PRECEDING AND CURRENT ROW ）。

注意：與組不同，使用窗口函數將所有行保存在結果集中。

2。常見用例和示例

在組中排名

使用RANK() ， ROW_NUMBER()或DENSE_RANK()在類別中對項目進行排名。

選擇 
    姓名，
    部門，
    薪水，
    等級（）超過（按工資desc按部門訂單分區）為dept_salary_rank
來自員工；

這在每個部門內按薪水進行排名。

如果您想要唯一的等級（即使有綁帶），則使用ROW_NUMBER() ， RANK()以及DENSE_RANK()沒有差距。

運行總計和移動平均

隨著時間的流逝，計算累積總和或滾動平均值。

選擇 
    日期，
    銷售量，
    sum（sales）超過（按日期行訂購訂單無綁定的上述）作為running_total，
    AVG（銷售）超過（按日期排按日期和當前行之間的日期行）為七日_AVG
來自Daily_sales；

• ROWS UNBOUNED PRECEDING ：從一開始就包括所有行。
• ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ：給出7天的移動平均線。

訪問上一行或下一行

使用LAG()和LEAD()將當前行值與上一個或下一個相比。

選擇 
    日期，
    收入，
    滯後（收入，1）以上（按日期訂購）為prev_day_revenue，
    收入 - 滯後（收入，1）以上（按日期訂購）
來自daily_revenue;

• LAG(revenue, 1)從上排獲得收入。
• 用於計算增長，變化或趨勢。

您也可以使用LEAD()來展望。

百分比和分配分析

使用NTILE()將行分為相對等級的桶或PERCENT_RANK() 。

選擇 
    姓名，
    薪水，
    ntile（4）以上（薪金）為salary_quartile
來自員工；

這將員工分為4個薪水組（四分位數）。

選擇 
    姓名，
    薪水，
    百分比（按薪金訂單）為百分比
來自員工；

給出相對等級為0到1之間的值。

3。關鍵技巧和最佳實踐

• 始終包含OVER() - 無需它，該函數無法用作窗口函數。
• 使用PARTITION BY時，您需要每組單獨的計算（例如每個部門，每個地區）。
• 請謹慎對待ORDER BY ，它會影響排名和基於框架的計算。
• 諸如ROWS或RANGE類的框架子句是可選的，但對於精確控制很重要：
  • ROWS物理行位置上工作。
  • RANGE在邏輯值範圍內工作（通常ORDER BY使用）。
• 避免在直接的子句中使用窗口函數（它們之後發生）。如果需要過濾，將它們包裹在子查詢或CTE中。

CTE的示例：

與排名
    選擇 
        姓名，
        部門，
        薪水，
        等級（）超過（按薪金desc按部門命令分區）為RNK
    來自員工
）
從rnk = 1中選擇 * * rnk; employees;

在每個部門找到最高的僱員。

使用窗口功能使復雜的分析查詢更加可讀性和高效。它們在大多數現代數據庫中都支持，例如PostgreSQL，SQL Server，BigQuery，Snowflake和Oracle（MySQL從版本8.0支持它們）。

基本上，如果您正在使用其他行的上下文進行分析，則窗口功能可能是正確的工具。

以上是如何在SQL中使用窗口函數的詳細內容。更多資訊請關注PHP中文網其他相關文章！