Dense_Rank()over——窗口函數之一

隨著數據量的增加，SQL語句的效率問題越來越引人注目。窗口函數是SQL語句中相對高級的知識點之一，能夠處理複雜的數據統計問題。其中，Dense_Rank()over函數是常用的一種，本文將對其進行詳細闡述。

一、什麼是窗口函數

窗口函數（window function）是SQL標準中的一種高級函數，可以在完成SQL查詢語句的同時對查詢結果進行窗口分析，可以說是「行中行」，是一種特殊的聚合函數。其特點是將窗口內的數據按照指定的順序排序，再進行聚合運算，同時不影響原有的查詢結構。

SELECT column1, column2, SUM(column3) OVER (PARTITION BY column1 ORDER BY column2) AS cum_sum
FROM table_name;

上述代碼中，SUM()函數被稱為聚合函數；OVER()窗口運算函數被稱為窗口函數。它們的作用是先按照column1（PARTITION BY），再按照column2（ORDER BY）排序，最後計算 column3 的累積和。

二、什麼是Dense_Rank()over函數

Dense_Rank()over函數是窗口函數中的一種，與另一種Rank()over函數有所不同。 Dense_Rank()over 不會跳過相同排名的值，它會在有重複值時給每個重複值打上同樣的排名，且排名不會跳過其他排名。這就使 Dense_Rank()over 更加適用於處理數據分組問題。

SELECT column1,column2, Dense_Rank() over (PARTITION BY column1 ORDER BY column2) AS Rank
FROM table_name;

此SQL語句中，Dense_Rank()over函數會先按照 column1 進行分組（PARTITION BY），再按照 column2 進行排序（ORDER BY），最後計算每個組內的排名。

三、怎樣使用Dense_Rank()over函數

1.基礎用法

在傳統SQL中，如果我們想統計每個省份各自的銷售排名，需要使用子查詢，代碼如下：

SELECT Provience,Amount,
(SELECT COUNT(DISTINCT Amount)
FROM Sales S2
WHERE S1.Provience=S2.Provience AND S2.Amount>s1.Amount)+1 AS rank
FROM Sales S1;

但如果我們使用 Dense_Rank()over 函數，就可以將這段代碼轉化為一個簡潔的查詢語句：

SELECT Provience, Amount, Dense_Rank()OVER (PARTITION BY Provience ORDER BY Amount DESC) as Rank
FROM Sales;

上述代碼中，我們使用了密集排名（Dense_Rank）對數據進行分組，按 Amount 的降序排序，再做排名運算。計算在 Provience 分組內的排名。

2.分情況進行排名

在分組內對數據進行排名時，我們可能需要分情況查詢，這時我們可以在 Dense_Rank()over 中添加多個分組。

SELECT Provience, Store, Amount, Dense_Rank()OVER(PARTITION BY Provience, CASE WHEN Store='Store1' THEN 'Store1' ELSE 'Others' END ORDER BY Amount DESC) AS rank
FROM Sales;

上述代碼中，我們在Dense_Rank()over函數中添加了兩個不同的分組：按 Probience 分組、Store 分組。使用 CASE WHEN … END 語句對 Store 進行判斷，分情況進行排名。這裡將 Store 為「Store1」的數據單獨排名，其他的數據分一組進行排名。

3.排名的過濾條件

Dense_Rank()over函數可以使用 WHERE 字句來過濾數據的排名，這種過濾可以分為兩種：行級別過濾和分區級別過濾。

以行級別過濾為例，假設我們需要在查詢時，只返回前 5 名的數據。

SELECT Provience, Store, Amount, Dense_Rank()OVER(ORDER BY Amount DESC) AS rank
FROM Sales
WHERE Dense_Rank()OVER(ORDER BY Amount DESC) <= 5;

上述代碼中，我們通過在WHERE語句中使用 Dense_Rank()over 函數來過濾數據，只返回了排名前5名的數據。

4.分組內的百分比排名

Dense_Rank()over 函數也可以實現分組內的百分比排名計算。

SELECT Probience, Store, Amount, Dense_Rank()OVER(PARTITION BY Store ORDER BY Amount) * 1.0 / COUNT(*) OVER(PARTITION BY Store) AS 'Ranking'
FROM Sales;

上述代碼中，我們使用了Density_rank()over和COUNT()over函數對 Amount 進行了計算，得出了各個 Store 中銷售額排名的百分比。

四、總結

Dense_Rank()over函數是一種靈活和強大的窗口函數，可以在 SQL 查詢語句中實現複雜的數據統計和分組排名。本文詳細介紹了該函數的基本概念、使用方法和常見應用場景。掌握 Dense_Rank()over 的技巧，將有助於優化SQL查詢性能，提高數據處理效率。