在數(shù)據(jù)世界中，數(shù)據(jù)的“形狀”往往決定分析的效率。例如：

• 原始數(shù)據(jù)：季度銷售額按行排列，難以直接生成年度對(duì)比報(bào)表。
• 目標(biāo)：將季度（Q1-Q4）轉(zhuǎn)換為列，直觀展示全年趨勢(shì)。

這就是行轉(zhuǎn)列（Pivot）的核心價(jià)值——將冗長的縱向數(shù)據(jù)“壓縮”為橫向結(jié)構(gòu)，讓分析更高效。本文將通過實(shí)戰(zhàn)案例，手把手教你掌握 SQL 中行轉(zhuǎn)列的3 種核心方法，并揭示高手的隱藏技巧。

一、行轉(zhuǎn)列原理：從行到列的數(shù)學(xué)邏輯

問題場(chǎng)景：假設(shè)有一張銷售表 sales：

目標(biāo)：將季度（Q1-Q4）轉(zhuǎn)換為列，輸出：

核心邏輯：

1. 分組聚合：按年份分組，計(jì)算每個(gè)季度的銷售額。
2. 列名固定：將季度值（Q1-Q4）作為新列名。
3. 條件判斷：通過條件表達(dá)式（如CASE WHEN）將行數(shù)據(jù)映射到對(duì)應(yīng)列。

前置 SQL：

CREATE TABLE`sales`  (
`year`varchar(255) CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULLDEFAULTNULL,
`quarter`varchar(255) CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULLDEFAULTNULL,
`revenue`varchar(255) CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULLDEFAULTNULL
) ENGINE = InnoDBCHARACTERSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;


INSERTINTO`sales`VALUES ('2023', 'Q2', '7000');
INSERTINTO`sales`VALUES ('2023', 'Q3', '6000');
INSERTINTO`sales`VALUES ('2023', 'Q4', '8000');
INSERTINTO`sales`VALUES ('2023', 'Q1', '5000');

二、方法詳解：3 種行轉(zhuǎn)列實(shí)戰(zhàn)技巧

方法 1：經(jīng)典 CASE WHEN（通用版）

適用場(chǎng)景：所有 SQL 數(shù)據(jù)庫（MySQL、PostgreSQL、SQL Server 等）。代碼示例：

SELECT
  year,
SUM(CASEWHENquarter = 'Q1'THEN revenue ELSE0END) AS Q1,
SUM(CASEWHENquarter = 'Q2'THEN revenue ELSE0END) AS Q2,
SUM(CASEWHENquarter = 'Q3'THEN revenue ELSE0END) AS Q3,
SUM(CASEWHENquarter = 'Q4'THEN revenue ELSE0END) AS Q4
FROM sales
GROUPBYyear;

關(guān)鍵點(diǎn)：

• CASE WHEN：為每個(gè)季度創(chuàng)建條件判斷，將符合條件的值匯總到對(duì)應(yīng)列。
• SUM()：聚合非目標(biāo)季度的值為 0，確保結(jié)果僅包含目標(biāo)列的值。
• GROUP BY：按年份分組，生成每行的年度匯總。

優(yōu)勢(shì)：

• 兼容性：所有 SQL 數(shù)據(jù)庫支持。
• 可控性：可靈活調(diào)整列名和聚合方式（如MAX()、AVG()）。

方法 2：PIVOT 語句（簡(jiǎn)潔版）

適用場(chǎng)景：SQL Server、Oracle、Snowflake 等支持PIVOT的數(shù)據(jù)庫。代碼示例：

SELECT *
FROM sales
PIVOT (
  SUM(revenue)
  FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4')
) AS pivot_table;

關(guān)鍵點(diǎn)：

• PIVOT：直接指定要轉(zhuǎn)換的列（quarter）和目標(biāo)列值（Q1-Q4）。
• 自動(dòng)聚合：SUM()會(huì)自動(dòng)對(duì)每個(gè)季度的revenue求和。

優(yōu)勢(shì)：

• 簡(jiǎn)潔性：代碼行數(shù)減少 60%。
• 可讀性：邏輯更直觀。

局限性：

• 不支持動(dòng)態(tài)列：列名必須預(yù)先定義（如 Q1-Q4）。
• 兼容性：部分?jǐn)?shù)據(jù)庫（如 MySQL）不支持PIVOT。

方法 3：動(dòng)態(tài)列生成（進(jìn)階版）

適用場(chǎng)景：列名未知或動(dòng)態(tài)變化（如 quarter ）。實(shí)現(xiàn)思路：

1. 獲取所有唯一列值：從數(shù)據(jù)中提取可能的列名。
2. 動(dòng)態(tài)生成 SQL 語句：通過應(yīng)用程序或存儲(chǔ)過程拼接CASE WHEN條件。

示例（MySql）：

-- 1. 獲取所有列名
SET @cols = NULL;
SELECTGROUP_CONCAT(DISTINCTCONCAT('SUM(CASE WHEN quarter = ''', quarter, ''' THEN revenue END) AS ', quarter))
INTO @cols
FROM sales;

-- 2. 拼接完整SQL語句
SET @sql = CONCAT(
'SELECT year, ', @cols,
' FROM sales
   GROUP BY year'
);

-- 3. 執(zhí)行動(dòng)態(tài)SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATEPREPARE stmt;

優(yōu)勢(shì)：

• 動(dòng)態(tài)適應(yīng)：列名無需硬編碼，適合未知或變化的列。

三、實(shí)戰(zhàn)案例：復(fù)雜場(chǎng)景下的行轉(zhuǎn)列

案例 1：多維度聚合（年+季度+產(chǎn)品）

需求：按年份和產(chǎn)品類型，統(tǒng)計(jì)各季度銷售額。原始數(shù)據(jù)：

SQL 代碼（CASE WHEN）：

SELECT
  year,
  product,
SUM(CASEWHENquarter = 'Q1'THEN revenue ELSE0END) AS Q1,
SUM(CASEWHENquarter = 'Q2'THEN revenue ELSE0END) AS Q2
FROM sales
GROUPBYyear, product;

輸出：

案例 2：非數(shù)值型數(shù)據(jù)轉(zhuǎn)列

需求：統(tǒng)計(jì)不同地區(qū)的用戶注冊(cè)渠道（渠道為文本列）。原始數(shù)據(jù)：

SQL 代碼（PIVOT）：

SELECT region, [線上], [線下]
FROM (
  SELECT region, channel, count
  FROM user_registration
) AS src
PIVOT (
  SUM(count)
  FOR channel IN ([線上], [線下])
) AS pvt;

輸出：

四、性能優(yōu)化與常見問題

1. 性能優(yōu)化技巧

• 索引優(yōu)化：在quarter、year等分組列上建立索引。
• 避免全表掃描：使用WHERE子句過濾無關(guān)數(shù)據(jù)。
• 列數(shù)控制：減少轉(zhuǎn)列的數(shù)量（如僅轉(zhuǎn) Q1-Q4，而非所有月份）。

2. 常見問題解決

• NULL 值處理：

-- 替換NULL為0
COALESCE(SUM(...), 0) AS column_name

• 列名沖突：使用AS關(guān)鍵字重命名列（如AS [Q1_Sales]）。

五、進(jìn)階技巧

結(jié)合窗口函數(shù)

在轉(zhuǎn)列前，先通過窗口函數(shù)計(jì)算累計(jì)值：

SELECT
  year,
SUM(CASEWHENquarter = 'Q1'THEN revenue END) AS Q1,
SUM(CASEWHENquarter = 'Q2'THEN revenue END) AS Q2,
SUM(CASEWHENquarter = 'Q3'THEN revenue END) AS Q3,
SUM(CASEWHENquarter = 'Q4'THEN revenue END) AS Q4,
-- 計(jì)算累計(jì)值
SUM(revenue) OVER (PARTITIONBYyearORDERBYquarter) AS cumulative
FROM sales
GROUPBYyear, quarter;

總結(jié)