【SQL】Lag/Rank/Over視窗函式揭秘，資料分析之旅

七月的夏日，陽光如火，但小悅的心中卻是一片清涼與激情。在資料分析專案組的新崗位上，她彷彿找到了自己新的舞臺，這裡讓她得以將深厚的後端技術實力與資料分析的精髓深度融合。每天，她都沉浸在業務需求的分析與資料驅動的決策之中，與業務、產品等多部門緊密合作，共同揭開資料背後的秘密，為企業的發展貢獻自己的力量。

正當她全身心投入到新環境的學習與探索中時，微信工作群的一則訊息如同夏日裡的一陣清風，為她帶來了新的機遇。逸塵，作為某專案微信群中的甲方代表，透過該群釋出了緊急的資料分析任務，而他當時並未意識到是小悅將會被指定來負責處理這項任務。雖然兩人上週在咖啡館的偶遇只是匆匆一瞥，但那次偶遇似乎為這次合作埋下了一絲伏筆。

面對這份突如其來的挑戰，小悅沒有絲毫的慌亂與猶豫。她深知，無論是熟悉的夥伴還是陌生的合作者，工作中的每一次挑戰都是自我提升與成長的寶貴機會。於是，她迅速調整心態，仔細閱讀任務要求，以專業的態度和嚴謹的精神回覆了逸塵。

---

小悅接到的任務，是根據原始資料表分析每個公司內部不同部門的銷售業績，並計算了每個部門銷售業績的排名、與下一名銷售業績的差距、與本部門最高銷售業績的差距、以及與所有部門銷售平均值的差異。

--根據需求建測試表
create table temp1 (
comp_name varchar(100),
dept_name varchar(100),
sales numeric(18,4)
)
--寫入測試資料：
INSERT INTO temp1  VALUES ('CompanyA', 'Dept 1', 100);
INSERT INTO temp1  VALUES ('CompanyA', 'Dept 3', 80);
INSERT INTO temp1  VALUES ('CompanyA', 'Dept 4', 80);
INSERT INTO temp1  VALUES ('CompanyA', 'Dept 2', 50);
INSERT INTO temp1  VALUES ('CompanyB', 'Dept 4', 120);
INSERT INTO temp1  VALUES ('CompanyB', 'Dept 3', 90);
INSERT INTO temp1  VALUES ('CompanyB', 'Dept 2', 80);
INSERT INTO temp1  VALUES ('CompanyB', 'Dept 1', 70);

---

面對分析公司內部不同部門銷售業績並計算排名、差距及異的需求，小悅的第一反應是使用臨時表，方案一：

-- 建立臨時表來儲存每個公司的最大銷售額和平均銷售額
CREATE TABLE temp_max_avg AS
SELECT comp_name,
       MAX(sales) AS max_sales,
       AVG(sales) AS avg_sales
FROM temp1
GROUP BY comp_name;

-- 建立臨時表來儲存排名和前一個銷售額
CREATE TABLE temp_rank_lag AS
SELECT comp_name,
       dept_name,
       sales,
       ROW_NUMBER() OVER (PARTITION BY comp_name ORDER BY sales DESC) AS rank,
       LAG(sales) OVER (PARTITION BY comp_name ORDER BY sales DESC) AS prev_sales
FROM temp1;

-- 建立最終的臨時表來儲存所有結果
CREATE TABLE temp_final AS
SELECT t1.comp_name,
       t1.dept_name,
       t1.sales,
       t2.rank,
       COALESCE(t2.prev_sales - t1.sales, 0) AS next_behind,
       tma.max_sales - t1.sales AS total_behind,
       t1.sales - tma.avg_sales AS diff_from_avg
FROM temp1 t1
JOIN temp_rank_lag t2 ON t1.comp_name = t2.comp_name AND t1.sales = t2.sales
JOIN temp_max_avg tma ON t1.comp_name = tma.comp_name;

-- 查詢最終結果
SELECT * FROM temp_final
ORDER BY comp_name, rank;

這段SQL查詢主要用於分析每個公司內部不同部門的銷售業績，並計算了每個部門銷售業績的排名、與下一名銷售業績的差距、與本部門最高銷售業績的差距、以及與銷售平均值的差異。以下是對各個欄位的解釋：

• comp_name：公司名稱
• dept_name：部門名稱
• sales：銷售業績
• rank：在公司內部按銷售業績降序排列的排名
• next_behind：與下一名的銷售業績差距（如果是第一名則為0）
• total_behind：與本部門最高銷售業績的差距
• diff_from_avg：與銷售平均值的差異

查詢結果

comp_name	dept_name	sales	rank	next_behind	total_behind	diff_from_avg
CompanyA	Dept 1	100	1	20	50	15
CompanyA	Dept 3	80	2	0	70	-15
CompanyA	Dept 4	80	2	0	70	-15
CompanyA	Dept 2	50	4	30	100	-45
CompanyB	Dept 4	120	1	30	40	22.5
CompanyB	Dept 2	80	2	10	60	-12.5
CompanyB	Dept 3	90	3	0	50	2.5
CompanyB	Dept 1	70	4	20	70	-27.5

注意：

• rank欄位中，如果兩個部門的銷售業績相同，則它們會共享相同的排名，並且下一個排名的數值會跳過（如上表中的CompanyA的Dept 3和Dept 4）。
• next_behind欄位計算的是與下一名銷售業績的差距，如果沒有下一名（即已經是最後一名），則顯示為0。
• total_behind欄位計算的是與本部門最高銷售業績的差距。
• diff_from_avg欄位計算的是當前部門的銷售業績與該公司所有部門銷售業績平均值的差異。正值表示高於平均值，負值表示低於平均值。

---

考慮到SQL查詢的效率和簡潔性，小悅隨後最佳化方案一，決定採用視窗函式（如RANK(), LEAD(), MAX(), AVG()）來實現。

最佳化後的方案利用視窗函式直接在SELECT查詢中完成所有計算，無需建立臨時表來儲存中間結果。RANK()函式用於計算銷售業績的排名，LEAD()函式（或LAG()，根據需求選擇）用於找出與下一名銷售業績的差距，MAX()和AVG()視窗函式則分別用於計算本部門最高銷售業績和銷售平均值，進而得出與這些值的差異。最佳化後的方案二：

--使用一條語句實現方案一中的臨時表邏輯

--mysql8、oracle10g和mssql2012以上版本
SELECT temp1.*,  
       RANK() OVER (PARTITION BY comp_name ORDER BY sales DESC) AS rank,  
       COALESCE(LAG(sales) OVER (PARTITION BY comp_name ORDER BY sales DESC) - sales, 0) AS next_behind,  
       MAX(sales) OVER (PARTITION BY comp_name) - sales AS total_behind,  
       sales - AVG(sales) OVER (PARTITION BY comp_name) AS diff_from_avg  
FROM temp1  
ORDER BY comp_name, rank;

--PostgreSQL版本
SELECT *,
         RANK()
    OVER w AS rank_by_sales, MAX(sales)
    OVER w - sales AS total_behind, sales - AVG(sales)
    OVER w AS diff_from_avg, COALESCE(LAG(sales)
    OVER w - sales, 0) AS next_behind
FROM temp1 WINDOW w AS (PARTITION BY comp_name
ORDER BY  sales DESC);

方案二與方案一的查詢結果是一樣的。

在方案二的SQL查詢中，使用了幾個視窗函式（也稱為分析函式）來對temp1表中的資料進行分組和排序，並計算每個部門相對於其公司內部其他部門的銷售業績指標。下面是對這些視窗函式的詳細解釋：

1. RANK() OVER (...) AS rank:
   • RANK() 是一個視窗函式，用於為分割槽內的行分配一個唯一的排名。但是，如果兩行或多行具有相同的排序值（在本例中是sales），則這些行將獲得相同的排名，並且下一個排名將跳過相應的數量（即如果有兩行並列第一，則下一行的排名將是第三，而不是第二）。
   • OVER (PARTITION BY comp_name ORDER BY sales DESC) 指定了視窗函式的分割槽和排序方式。這裡，PARTITION BY comp_name 表示根據comp_name欄位將資料分成不同的組（即每個公司的部門被視為一個組），而ORDER BY sales DESC 表示在每個組內，資料將按照sales欄位的降序進行排序。
2. COALESCE(LAG(sales) OVER (...) - sales, 0) AS next_behind:
   • LAG(sales) OVER (...) 是一個視窗函式，用於訪問當前行之前的行的sales值（在本例中是按照sales的降序排列的）。如果沒有前一行（即當前行是分割槽內的第一行），則LAG()函式將返回NULL。
   • COALESCE(expression, 0) 函式用於將其引數列表中的第一個非NULL表示式作為結果返回。如果LAG(sales) - sales的結果是NULL（即當前行是分割槽內的第一行），則COALESCE將返回0。
   • 因此，next_behind列計算的是當前部門的銷售業績與緊隨其後的部門銷售業績之間的差距。如果當前部門是第一名，則差距為0。
3. MAX(sales) OVER (...) - sales AS total_behind:
   • MAX(sales) OVER (...) 是一個視窗函式，用於計算分割槽內sales欄位的最大值。由於這裡使用了相同的分割槽和排序方式（PARTITION BY comp_name），因此它將為每個部門提供該公司內部最高的銷售業績。
   • 然後，從這個最大值中減去當前行的sales值，得到total_behind，即當前部門的銷售業績與該公司內部最高銷售業績之間的差距。
4. sales - AVG(sales) OVER (...) AS diff_from_avg:
   • AVG(sales) OVER (...) 是另一個視窗函式，用於計算分割槽內sales欄位的平均值。同樣，由於使用了PARTITION BY comp_name，這將為每個部門提供該公司內部銷售業績的平均值。
   • 然後，從當前行的sales值中減去這個平均值，得到diff_from_avg，即當前部門的銷售業績與該公司內部平均銷售業績之間的差異。正值表示當前部門的銷售業績高於平均值，負值表示低於平均值。

最後，ORDER BY comp_name, rank 用於對整個查詢結果進行排序，首先按公司名稱（comp_name）升序排序，然後在每個公司內按銷售排名（rank）升序排序。但是，由於rank已經是基於sales降序排列的，所以實際上在每個公司內，rank的排序已經是降序的，但外部的ORDER BY確保了不同公司之間的結果也是有序的。不過，由於rank本身在SQL中可能包含並列的情況，所以實際的排序可能會根據SQL實現的不同而略有差異（例如，在某些資料庫中，並列的排名可能會以它們在表中的原始順序顯示）。

---

這裡介紹一下 各大資料庫中中常見的視窗分析函式 RANK(), OVER(), LAG() 。

1. RANK() 函式:

   • 這個函式最早出現在 IBM DB2 資料庫中,可以追溯到 1993 年。
   • 隨後,Oracle 在 2000 年的 Oracle9i 版本中引入了 RANK() 函式。
   • Microsoft SQL Server 則在 2005 年的 SQL Server 2005 版本中新增了對 RANK() 函式的支援。
   • RANK() 函式的引入,為資料分析師和開發人員提供了一種更加靈活和強大的排序方式,極大地提升了資料分析的效率。

   • RANK() 函式用於給資料集中的行分配一個排名值。
     它會根據指定的列(或表示式)對行進行排序,然後給每一行分配一個排名值。
     如果有多行具有相同的值,它們將獲得相同的排名,後續行的排名值會相應遞增。
     這個函式在需要對資料進行排序並獲取排名資訊時非常有用,比如找出銷售額前 5 名的產品。

2. OVER() 子句:

   • OVER() 子句最早出現在 OLAP (Online Analytical Processing) 領域,可以追溯到 20 世紀 90 年代。
   • Oracle 在 2000 年的 Oracle9i 版本中引入了 OVER() 子句,使得視窗函式的使用更加方便和靈活。
   • Microsoft SQL Server 也在 2005 年的 SQL Server 2005 版本中新增了對 OVER() 子句的支援。
   • OVER() 子句的引入,大大簡化了複雜的資料分析任務,提高了開發人員的工作效率。

   • OVER() 子句用於定義視窗函式的作用範圍。
     它可以指定行組的範圍,從而允許視窗函式在這個範圍內進行計算。
     常見的用法包括:計算移動平均值、計算累計值、計算排名

3. LAG() 和 LEAD() 函式:

   • 這兩個函式最早出現在 IBM DB2 資料庫中,可以追溯到 2001 年。
   • Oracle 在 2006 年的 Oracle 10g 版本中引入了 LAG() 和 LEAD() 函式。
   • Microsoft SQL Server 則在 2008 年的 SQL Server 2008 版本中新增了對這兩個函式的支援。
   • LAG() 和 LEAD() 函式的引入,使得在進行時間序列分析和資料比較時更加方便和高效。

   • LAG() 函式用於獲取當前行前N行的值。
     LEAD() 函式用於獲取當前行後N行的值。
     這兩個函式在進行時間序列分析和資料比較時非常有用,例如:
     計算當前銷售額與上月銷售額的差異
     判斷當前季度的業績是否高於上一季度

除了 Rank、Lag 和 Over 等常見的視窗函式外,SQL 還提供了其他一些有用的視窗函式,包括:

1. Row_Number(): 為每個分割槽中的行分配一個唯一的序號,從 1 開始計數。

2. Partition_By: 可以與其他視窗函式一起使用,用於指定視窗劃分的依據。

3. First_Value() 和 Last_Value(): 分別獲取視窗內第一個值和最後一個值。

4. Ntile(): 將分割槽中的行平均分配到指定數量的組中。

5. Ratio_To_Report(): 計算當前行值佔分割槽總值的比例。

6. Cume_Dist(): 計算當前行小於等於自身值的行數佔分割槽總行數的比例。

7. Percent_Rank(): 計算當前行在分割槽內的百分位rank值。

8. Dense_Rank(): 與 Rank() 類似,但不會出現並列序號的情況。

這些常用的視窗函式都是資料庫技術發展的產物,隨著 OLAP 和資料分析的需求不斷增加,資料庫廠商不斷改進和完善這些功能,使得資料分析工作變得更加簡單和高效。這些視窗函式的引入,極大地推動了資料分析領域的發展。