Lakehouse 数据透视与行列转换指南

概述

数据透视（行转列）把明细数据按某个维度的取值展开成多列，例如把“地区 × 月份 × 金额”的明细变成每个月一列的报表宽表；列转行则相反，把宽表中的多个同类列收拢成明细长表，便于统一聚合、关联或绘图。

Lakehouse 提供原生的

PIVOT

UNPIVOT

PIVOT

CASE WHEN

涉及的 SQL 命令

CASE WHEN

PIVOT

EXPLODE

SPLIT

前置准备

全文示例共用以下三张表。

销售明细表

doc_pivot_sales

CREATE TABLE IF NOT EXISTS doc_pivot_sales ( sale_id INT, region STRING, month STRING, product STRING, amount DOUBLE ); INSERT INTO doc_pivot_sales VALUES (1, '华东', '2024-01', '产品A', 1200.00), (2, '华东', '2024-01', '产品B', 800.00), (3, '华东', '2024-02', '产品A', 1500.00), (4, '华东', '2024-02', '产品B', 950.00), (5, '华东', '2024-03', '产品A', 1800.00), (6, '华东', '2024-03', '产品B', 1100.00), (7, '华南', '2024-01', '产品A', 900.00), (8, '华南', '2024-01', '产品B', 600.00), (9, '华南', '2024-02', '产品A', 1100.00), (10, '华南', '2024-02', '产品B', 750.00), (11, '华南', '2024-03', '产品A', 1300.00), (12, '华南', '2024-03', '产品B', 880.00), (13, '华北', '2024-01', '产品A', 700.00), (14, '华北', '2024-01', '产品B', 500.00), (15, '华北', '2024-02', '产品A', 850.00), (16, '华北', '2024-02', '产品B', 620.00), (17, '华北', '2024-03', '产品A', 1000.00), (18, '华北', '2024-03', '产品B', 750.00);

季度宽表

doc_pivot_wide

CREATE TABLE IF NOT EXISTS doc_pivot_wide ( region STRING, q1_amount DOUBLE, q2_amount DOUBLE, q3_amount DOUBLE, q4_amount DOUBLE ); INSERT INTO doc_pivot_wide VALUES ('华东', 2000, 2450, 2900, 3100), ('华南', 1500, 1850, 2180, 2400), ('华北', 1200, 1470, 1750, 1900), ('西南', 800, 950, NULL, NULL);

订单标签表

doc_pivot_tags

tags

CREATE TABLE IF NOT EXISTS doc_pivot_tags ( order_id INT, region STRING, tags STRING ); INSERT INTO doc_pivot_tags VALUES (1, '华东', '产品A,产品B'), (2, '华南', '产品A'), (3, '华北', '产品B,产品C'), (4, '华东', '产品A,产品C'), (5, '华南', '产品B,产品C');

场景一：按月份透视各地区销售额

把月份展开为列，每个地区一行，横向对比各月销售额：

SELECT * FROM ( SELECT region, month, amount FROM doc_pivot_sales ) PIVOT ( sum(amount) FOR month IN ('2024-01' AS jan_amount, '2024-02' AS feb_amount, '2024-03' AS mar_amount) ) ORDER BY region;

amount

2000.0

PIVOT

amount

month

region

FROM doc_pivot_sales PIVOT (...)

sale_id

product

AS jan_amount

2024-01

场景二：多个指标的交叉表

每种产品同时统计金额和订单数

PIVOT

SELECT * FROM ( SELECT region, product, amount FROM doc_pivot_sales ) PIVOT ( sum(amount) AS total, count(*) AS orders FOR product IN ('产品A' AS a, '产品B' AS b) ) ORDER BY region;

列按“透视值优先”排列：先是产品 A 的两个指标，再是产品 B 的两个指标。

月份 × 地区矩阵并附加合计列

透视结果中的列可以在外层查询中继续计算，例如加一列合计：

SELECT month, east, south, north, east + south + north AS total FROM ( SELECT month, region, amount FROM doc_pivot_sales ) PIVOT ( sum(amount) FOR region IN ('华东' AS east, '华南' AS south, '华北' AS north) ) ORDER BY month;

PIVOT

east + south + north

coalesce(east, 0) + coalesce(south, 0) + coalesce(north, 0)

场景三：按多列组合透视

只关心特定的“月份 + 产品”组合，例如对比 1 月产品 A 和 3 月产品 B 的表现。

FOR

IN

SELECT * FROM ( SELECT region, month, product, amount FROM doc_pivot_sales ) PIVOT ( sum(amount) FOR (month, product) IN (('2024-01', '产品A') AS jan_a, ('2024-03', '产品B') AS mar_b) ) ORDER BY region;

不在

IN

FOR

场景四：把季度宽表还原为明细（UNPIVOT）

把

q1_amount

q4_amount

quarter

amount

SELECT region, quarter, amount FROM doc_pivot_wide UNPIVOT ( amount FOR quarter IN (q1_amount AS q1, q2_amount AS q2, q3_amount AS q3, q4_amount AS q4) ) WHERE region IN ('华东', '西南') ORDER BY region, quarter;

• amount FOR quarter

  amount FOR quarter
  ：

  amount

  amount
  是新生成的值列，

  quarter

  quarter
  是新生成的名称列，内容为

  IN

  IN
  中的别名。
• 没有出现在

  IN

  IN
  中的列（

  region

  region
  ）原样保留。
• 西南的 Q3、Q4 为 NULL，默认被丢弃，所以只有 2 行。

统计“哪些季度还没有数据”时需要保留 NULL 行，加上

INCLUDE NULLS

SELECT region, quarter, amount FROM doc_pivot_wide UNPIVOT INCLUDE NULLS ( amount FOR quarter IN (q1_amount AS q1, q2_amount AS q2, q3_amount AS q3, q4_amount AS q4) ) WHERE amount IS NULL ORDER BY region, quarter;

场景五：展开多值列

一个单元格里存了多个值（如逗号分隔的标签）时，用

SPLIT

LATERAL VIEW EXPLODE

SELECT order_id, region, tag FROM doc_pivot_tags LATERAL VIEW EXPLODE(SPLIT(tags, ',')) t AS tag ORDER BY order_id, tag;

展开后的行数等于所有订单的标签数之和。展开后可以直接聚合，例如统计每种产品出现在多少个订单中：

SELECT tag AS product, count(*) AS order_count FROM doc_pivot_tags LATERAL VIEW EXPLODE(SPLIT(tags, ',')) t AS tag GROUP BY tag ORDER BY tag;

场景六：PIVOT 无法表达时改用 CASE WHEN

PIVOT

CASE WHEN

SELECT region, sum(CASE WHEN month = '2024-01' AND product = '产品A' THEN amount ELSE 0 END) AS jan_a, sum(amount) AS q1_total, round(sum(CASE WHEN product = '产品B' THEN amount ELSE 0 END) / sum(amount), 4) AS b_ratio FROM doc_pivot_sales GROUP BY region ORDER BY region;

三列分别按“月份 + 产品”“全部数据”“产品”筛选，没有共同的透视列，

PIVOT

两种写法的取舍：

PIVOT

IN

CASE WHEN

CASE WHEN ... ELSE 0

PIVOT

coalesce

清理测试数据

DROP TABLE IF EXISTS doc_pivot_sales; DROP TABLE IF EXISTS doc_pivot_wide; DROP TABLE IF EXISTS doc_pivot_tags;

注意事项

1. 透视值必须是常量：

   PIVOT

   PIVOT
   的

   IN

   IN
   列表不能是子查询。透视值不固定（如月份随时间增加）时，先用

   SELECT DISTINCT

   SELECT DISTINCT
   查出所有取值，再在应用层或调度任务中拼接 SQL。
2. 先裁剪列再透视：

   PIVOT

   PIVOT
   把聚合列和透视列之外的所有列都当作分组列，建议总是用子查询只选出需要的列。
3. PIVOT 的空组合为 NULL：没有数据的组合输出 NULL，参与加减运算前用

   coalesce

   coalesce
   处理。
4. UNPIVOT 默认丢弃 NULL：需要保留空值时写

   UNPIVOT INCLUDE NULLS

   UNPIVOT INCLUDE NULLS
   。

   IN

   IN
   中各列的类型需要兼容。
5. SPLIT 可能产生空字符串：原始数据末尾有多余逗号（如

   '产品A,'

   '产品A,'
   ）时，

   SPLIT

   SPLIT
   会多出一个空字符串元素，可在展开后加

   WHERE tag != ''

   WHERE tag != ''
   过滤。
6. LATERAL VIEW 与 WHERE 的顺序：

   LATERAL VIEW

   LATERAL VIEW
   在

   FROM

   FROM
   阶段执行，

   WHERE

   WHERE
   在其后过滤。要过滤展开前的原始行，把条件写在子查询中。

相关文档

• PIVOT
• UNPIVOT
• GROUP BY
• LATERAL VIEW
• 字符串函数