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

概述

数据透视与行列转换是数据分析中的常见需求，用于将明细数据重塑为适合报表展示或进一步分析的宽表/长表格式。云器 Lakehouse 通过

CASE WHEN

UNION ALL

LATERAL VIEW EXPLODE

涉及的 SQL 命令

CASE WHEN ... END

GROUP BY

UNION ALL

LATERAL VIEW EXPLODE()

SPLIT()

WITH ... AS (CTE)

SUM() / COUNT()

前置准备

以下示例使用模拟的销售明细表

doc_pivot_sales

-- 创建测试表 CREATE TABLE IF NOT EXISTS doc_pivot_sales ( sale_id INT, region STRING, month STRING, product STRING, amount DOUBLE ); -- 插入测试数据（覆盖华东/华南/华北三个地区、2024年1-3月、两种产品，共 18 行） 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);

场景一：行转列（PIVOT）

将月份作为行维度的明细数据，转换为每个月份对应一列的宽表格式，便于横向对比各月销售额。

-- 按地区汇总各月销售额，将月份行转为列 SELECT region, SUM(CASE WHEN month = '2024-01' THEN amount ELSE 0 END) AS jan_amount, SUM(CASE WHEN month = '2024-02' THEN amount ELSE 0 END) AS feb_amount, SUM(CASE WHEN month = '2024-03' THEN amount ELSE 0 END) AS mar_amount FROM doc_pivot_sales GROUP BY region ORDER BY region;

执行结果：

CASE WHEN month = '2024-01' THEN amount ELSE 0 END

SUM

场景二：列转行（UNPIVOT）

将宽表中的多个季度列（

q1_amount

q2_amount

q3_amount

q4_amount

quarter

amount

首先创建宽表示例数据：

-- 创建宽表（每个地区一行，四个季度各一列） 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);

使用

UNION ALL

-- 列转行：将 q1~q4 四列合并为 quarter + amount 两列 SELECT region, 'Q1' AS quarter, q1_amount AS amount FROM doc_pivot_wide UNION ALL SELECT region, 'Q2', q2_amount FROM doc_pivot_wide UNION ALL SELECT region, 'Q3', q3_amount FROM doc_pivot_wide UNION ALL SELECT region, 'Q4', q4_amount FROM doc_pivot_wide ORDER BY region, quarter;

执行结果：

SELECT

UNION ALL

UNION ALL

场景三：交叉表统计

统计每个月份在各地区的销售额，形成月份 × 地区的二维矩阵，适合制作交叉报表。

-- 月份 × 地区交叉表：每行一个月份，每列一个地区 SELECT month, SUM(CASE WHEN region = '华东' THEN amount ELSE 0 END) AS east_amount, SUM(CASE WHEN region = '华南' THEN amount ELSE 0 END) AS south_amount, SUM(CASE WHEN region = '华北' THEN amount ELSE 0 END) AS north_amount, SUM(amount) AS total_amount FROM doc_pivot_sales GROUP BY month ORDER BY month;

执行结果：

也可以按地区为行、产品为列，统计各地区两种产品的销售额及合计：

-- 地区 × 产品交叉表：每行一个地区，每列一种产品 SELECT region, SUM(CASE WHEN product = '产品A' THEN amount ELSE 0 END) AS product_a_total, SUM(CASE WHEN product = '产品B' THEN amount ELSE 0 END) AS product_b_total, SUM(amount) AS grand_total FROM doc_pivot_sales GROUP BY region ORDER BY region;

执行结果：

CASE WHEN

SELECT DISTINCT

场景四：多值列展开

当一列中存储了逗号分隔的多个值（如标签、产品列表），需要将每个值展开为独立的行，以便进行统计分析。

首先创建包含多值列的示例表：

-- 创建订单标签表（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');

使用

LATERAL VIEW EXPLODE

SPLIT

-- 将 tags 列按逗号拆分，每个标签展开为独立一行 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;

执行结果：

SPLIT(tags, ',')

EXPLODE

LATERAL VIEW

场景五：聚合后转置

先按地区和月份分组聚合，再将聚合结果转置为宽表格式，同时附加合计列。这种两步法适合在聚合逻辑较复杂时保持 SQL 清晰。

-- 第一步：按地区和月份聚合；第二步：将月份列转置为宽表 WITH monthly_summary AS ( SELECT region, month, SUM(amount) AS total_amount FROM doc_pivot_sales GROUP BY region, month ) SELECT region, ROUND(SUM(CASE WHEN month = '2024-01' THEN total_amount ELSE 0 END), 2) AS m1, ROUND(SUM(CASE WHEN month = '2024-02' THEN total_amount ELSE 0 END), 2) AS m2, ROUND(SUM(CASE WHEN month = '2024-03' THEN total_amount ELSE 0 END), 2) AS m3, ROUND(SUM(total_amount), 2) AS total FROM monthly_summary GROUP BY region ORDER BY region;

执行结果：

WITH ... AS

CASE WHEN

清理测试数据

完成数据透视验证后，建议清理测试表：

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

UNDROP TABLE

注意事项

1. 列值需提前枚举：

   CASE WHEN

   CASE WHEN
   行转列要求在写 SQL 时已知所有列值（如月份、地区）。若列值动态变化，需在应用层动态拼接 SQL，或使用

   GROUP_CONCAT

   GROUP_CONCAT
   先查出所有值再构造查询。
2. ELSE 0 vs ELSE NULL：

   CASE WHEN ... ELSE 0 END

   CASE WHEN ... ELSE 0 END
   配合

   SUM

   SUM
   时，无匹配行贡献 0，结果不含 NULL。若改为

   ELSE NULL

   ELSE NULL
   ，

   SUM

   SUM
   会忽略 NULL，结果相同；但

   AVG

   AVG
   会因分母不同而产生差异，需根据业务语义选择。
3. UNION ALL 列数和类型须一致：列转行时，每个

   SELECT

   SELECT
   子句的列数和对应列的数据类型必须相同，否则报错。
4. LATERAL VIEW 与 WHERE 的执行顺序：

   LATERAL VIEW

   LATERAL VIEW
   在

   FROM

   FROM
   阶段执行，

   WHERE

   WHERE
   在其之后过滤。如需过滤展开前的原始行，将条件写在子查询中；如需过滤展开后的结果，直接写在外层

   WHERE

   WHERE
   中。
5. SPLIT 结果含空字符串：若原始数据末尾有多余逗号（如

   "产品A,"

   "产品A,"
   ），

   SPLIT

   SPLIT
   会产生空字符串元素。可在展开后加

   WHERE tag != ''

   WHERE tag != ''
   过滤。
6. 性能考量：行转列的

   CASE WHEN

   CASE WHEN
   数量越多，扫描列越多，对宽表性能影响越大。列数超过 50 时，建议评估是否改用

   MAP_AGG

   MAP_AGG
   或应用层处理。

相关文档

• GROUP BY 子句
• LATERAL VIEW
• 窗口函数
• 字符串函数