Oracle 行轉列函數 PIVOT

一、概述

Oracle 行轉列函數 PIVOT 是一種非常方便的操作，在處理跨行轉換數據的時候非常有效，能夠將行數據轉為列數據，方便數據的查詢和分析。Oracle 行轉列函數 PIVOT 主要是通過將數據行轉化為數據列，類似於 Excel 的數據透視表功能，通過將聚合函數應用於數據列，實現數據的交叉匯總。

二、PIVOT 的基本語法

SELECT <非聚合列>,       [pivot_clause][
       [subquery]       { FOR <column>          IN <value1>, <value2>, ... <value_n> ]       }      ]
FROM <table>[PIVOT XML] 

其中，pivot_clause 包括 PIVOT 操作的聚合列、行列轉換所需的列名和列轉換對應值的映射。使用 PIVOT XML 可以讓結果以 XML 形式返回。

三、PIVOT 的具體應用

a. 按列分組匯總數據

實際運用中有很多這樣的場景：需求是按照特定的列進行分組，同時還需要對其他列進行計數、求和這樣的聚合計算，所產生的數據結構如下：

| ID | NAME | AGE | GENDER | SCORE || 1  | Tom  | 18  | M      | 90    || 2  | Mary | 19  | F      | 88    || 1  | Tom  | 18  | M      | 95    || 2  | Mary | 19  | F      | 92    || 3  | Jack | 18  | M      | 80    |

可以使用下列 SQL 語句:

SELECT ID, NAME, GENDER,        SUM(SCORE) AS TOTAL_SCORE,        AVG(SCORE) AS AVG_SCORE,        COUNT(*) AS COUNTERFROM SCORES GROUP BY ID, NAME, GENDER;

結果如下表:

| ID | NAME | GENDER | TOTAL_SCORE | AVG_SCORE | COUNTER || 1  | Tom  | M      | 185        | 92.5      | 2       || 2  | Mary | F      | 180        | 90.0      | 2       || 3  | Jack | M      | 80         | 80.0      | 1       |

b. 行列轉換

PIVOT 函數可以將行值轉換為列值，這個功能在處理某些業務場景時很有用。例如，上面的分組表格，我們可能還需要將結果按照性別進一步劃分，得到類似於下面這個表：

| ID | NAME | MALE_SCORE | FEMALE_SCORE || 1  | Tom  | 185        | NULL         || 2  | Mary | NULL         | 180         || 3  | Jack | 80         | NULL         |

可以使用下列 SQL 語句:

SELECT ID, NAME,        MAX(CASE WHEN GENDER = 'M' THEN SCORE ELSE NULL END) AS MALE_SCORE,       MAX(CASE WHEN GENDER = 'F' THEN SCORE ELSE NULL  END) AS FEMALE_SCOREFROM SCORES GROUP BY ID, NAME;

結果如下表:

| ID | NAME | MALE_SCORE | FEMALE_SCORE || 1  | Tom  | 185        | NULL         || 2  | Mary | NULL         | 180         || 3  | Jack | 80         | NULL         |

c. 使用子查詢和PIVOT子句完成複雜轉換

在實際情況下，PIVOT 有時需要使用子查詢來進行更複雜的轉換操作，下面來一起看一個 SQL 語句實戰：

SELECT *FROM (  SELECT ID, NAME, GENDER, SCORE   FROM SCORES ) PIVOT (  AVG(SCORE) for GENDER IN ('M', 'F') )

這個 SQL 查詢操作完成了一個聚合操作，同時將平均值分別按照性別轉換為了不同的列，從而完成了數據的透視表聚合。這個應用主要是針對複雜的、跨度廣的數據結構，能夠方便的將數據轉為透視表形式。

四、PIVOT 的注意事項

a. 請避免混用 SUM 和 COUNT 聚合函數

在使用 PIVOT 的時候，如果混用 SUM 和 COUNT 這樣的聚合函數，在生成的結果中會出現錯誤，因此需要避免使用這樣的混用寫法。

b. 請注意對缺失記錄的處理

在使用 PIVOT 函數的時候，我們還需要注意對於缺失的記錄需要進行處理，否則會導致 PIVOT 函數中出現空值 null，這樣會影響到後續的計算。我們需要在寫 PIVOT 函數之前，針對記錄的缺失做好充分的預處理工作。

c. 請根據實際情況進行動態拼接

在實際情況中，針對不同的數據表格，需要使用不同的動態拼接操作，才能夠最終得到想要的結果，而不是只使用一種固定的規則。因此，我們需要在實際操作中多進行嘗試，積累一定的經驗。

五、總結

PIVOT 是一種強大的 SQL 函數，它能夠將普通的表格數據快速轉換為透視表格形式，方便進行數據的查詢和匯總，尤其是針對複雜的數據結構，PIVOT 會發揮出非常重要的作用。在實際工作中，針對具體的數據結構，我們需要對 PIVOT 函數進行不斷的嘗試和探索，多進行數據的拼接和轉換，才能夠得到更好的結果。