MySQL 行列轉換實戰：CASE WHEN 實現動態列透視

業務場景

原始表記錄每日各事件（下載點擊、成功點擊、安裝成功）的 UV 值，需轉為「一行一日期，多列事件指標」的寬表，供報表直連查詢。

源數據（結果集 A）

biz_date    event            uv
2023-01-01  downClick        100
2023-01-01  successClick     80
2023-01-01  installSuccess   60
2023-01-02  downClick        120
...

目標數據（結果集 B）

biz_date    downClick    successClick    installSuccess
2023-01-01  100          80              60
2023-01-02  120          90              70
...

核心 SQL：條件聚合

SELECT
    a.biz_date,
    SUM(CASE WHEN a.`event` = 'downClick'       THEN a.uv END) AS downClick,
    SUM(CASE WHEN a.`event` = 'successClick'    THEN a.uv END) AS successClick,
    SUM(CASE WHEN a.`event` = 'installSuccess'  THEN a.uv END) AS installSuccess
FROM a
GROUP BY a.biz_date
ORDER BY a.biz_date;

關鍵點解析

• CASE WHEN ... THEN ... END：非匹配行返回 NULL，SUM() 自動忽略 NULL，等同於條件求和。
• 不需 ELSE 0，SUM(NULL) 為 NULL，外層可用 COALESCE(...,0) 顯示 0。
• GROUP BY biz_date 將多行壓縮為單行，實現行→列轉換。

進階變體

1. 動態事件類型（未知列數）

MySQL 不支持動態列輸出，需兩步：

-- 步驟 1：獲取所有事件類型
SELECT GROUP_CONCAT(DISTINCT CONCAT('SUM(CASE WHEN `event`=', QUOTE(`event`), ' THEN uv END) AS ', `event`))
FROM a INTO @sql;

-- 步驟 2：構建並執行動態 SQL
SET @sql = CONCAT('SELECT biz_date, ', @sql, ' FROM a GROUP BY biz_date ORDER BY biz_date');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

2. 多指標同時透視（UV + PV + 時長）

SELECT
    biz_date,
    SUM(CASE WHEN `event`='downClick' THEN uv END) AS downClick_uv,
    SUM(CASE WHEN `event`='downClick' THEN pv END) AS downClick_pv,
    SUM(CASE WHEN `event`='downClick' THEN duration END) AS downClick_dur,
    ...
FROM a
GROUP BY biz_date;

3. 配合窗口函數（MySQL 8.0+）計算環比

WITH daily AS (
  SELECT
    biz_date,
    SUM(CASE WHEN `event`='downClick' THEN uv END) AS downClick
  FROM a GROUP BY biz_date
)
SELECT
  biz_date,
  downClick,
  LAG(downClick) OVER (ORDER BY biz_date) AS prev_day,
  ROUND(100.0 * (downClick - LAG(downClick) OVER (ORDER BY biz_date)) / NULLIF(LAG(downClick) OVER (ORDER BY biz_date),0), 2) AS mom_pct
FROM daily;

性能優化建議

1. 源表建立組合索引 (biz_date, `event`)，加速分組掃描。
2. 大數據量考慮物化視圖或 Summary Table 定時刷新。
3. 避免 SELECT *，只讀取所需列（覆蓋索引）。

對比：PIVOT / CROSSTAB

MySQL 無原生 PIVOT（SQL Server）或 CROSSTAB（PostgreSQL/Oracle），條件聚合是標準、可移植、高性能的通用方案。

掌握此模式，靈活應對各類報表透視需求，無需依賴應用層二次處理。