SQL 复杂行列转换的终极对决：CASE WHEN、PIVOT 与 LATERAL VIEW EXPLODE

SQL 复杂行列转换的终极对决CASE WHEN、PIVOT 与 LATERAL VIEW EXPLODE在日常商业数据分析与报表出数中数据的行转列Pivot / 长表转宽表与列转行Unpivot / 宽表转长表是出现频次最高、但也是最考验 SQL 功底的核心场景。在不同的业务场景和数据库引擎中行列转换的需求形态千差万别场景 A行转列将每个用户的多门课程考试成绩数学 90、语文 85、英语 92从多行记录展开为单行宽表的一门课一列场景 B列转行将一张包含了q1_sales、q2_sales、q3_sales、q4_sales四个季度列的宽表折叠还原为(quarter, sales)的标准长窄表场景 C数组炸裂将用户画像表中的标签数组[高消费, 数码控, 奶爸]炸裂拆分为独立的 3 行记录。面对这些需求很多工程师在不同数据库MySQL, Hive, Spark, ClickHouse之间切换时经常为该用CASE WHEN还是PIVOT、该用UNION ALL还是LATERAL VIEW EXPLODE感到困惑。今天我们系统拆解 SQL 行列转换的三大核心技术流派奉上全场景的极致实现模板与性能优劣横评。三大流派物理拓扑全景大纲---------------------------------------------------------------------------------------------------- | 【第一流派长表转宽表 (行转列 / Row-to-Column Pivoting)】 | | 1. 经典通用派: MAX(CASE WHEN ...) / SUM(CASE WHEN ...) (所有 SQL 引擎 100% 通用支持) | | 2. 现代语法糖派: PIVOT 算子 (Oracle, SQL Server, Snowflake, Spark 2.4 原生支持) | ---------------------------------------------------------------------------------------------------- vs ---------------------------------------------------------------------------------------------------- | 【第二流派宽表转长表 (列转行 / Column-to-Row Unpivoting)】 | | 1. 传统物理拆分派: 多分支 UNION ALL (简单直观但扫描多次底层表) | | 2. 现代交叉炸裂派: LATERAL VIEW EXPLODE(ARRAY(...)) / UNPIVOT (单次扫描性能极佳) | ---------------------------------------------------------------------------------------------------- vs ---------------------------------------------------------------------------------------------------- | 【第三流派集合与数组炸裂 (Array Exploding)】 | | 1. Hive / Spark: LATERAL VIEW EXPLODE(array_col) | | 2. ClickHouse: arrayJoin(array_col) | | 3. Presto / Trino: CROSS JOIN UNNEST(array_col) | ----------------------------------------------------------------------------------------------------流派一行转列实战MAX(CASE WHEN)vs 原生PIVOT假设我们有一张用户月度消费明细表user_monthly_spend(user_id, month, amount)。1. 通用之王MAX(CASE WHEN ...)全数据库通用SELECT user_id, -- 核心条件聚合命中月份保留数值未命中填充 0 COALESCE(MAX(CASE WHEN month 2026-07 THEN amount END), 0) AS spend_202607, COALESCE(MAX(CASE WHEN month 2026-08 THEN amount END), 0) AS spend_202608, COALESCE(MAX(CASE WHEN month 2026-09 THEN amount END), 0) AS spend_202609 FROM dw_prod.user_monthly_spend GROUP BY user_id;2. 现代原生写法Spark SQL 原生PIVOT算子-- 在 Spark 2.4 / Snowflake 中原生支持优雅的 PIVOT 语法 SELECT * FROM ( SELECT user_id, month, amount FROM dw_prod.user_monthly_spend ) PIVOT ( SUM(amount) FOR month IN (2026-07 AS spend_202607, 2026-08 AS spend_202608, 2026-09 AS spend_202609) );流派二列转行实战UNION ALLvsLATERAL VIEW EXPLODE假设有一张季度业绩宽表store_quarter_sales(store_id, q1_amt, q2_amt, q3_amt, q4_amt)。❌ 传统低效写法4 次 UNION ALL 导致 4 次全表扫描SELECT store_id, Q1 AS quarter, q1_amt AS amount FROM store_quarter_sales UNION ALL SELECT store_id, Q2 AS quarter, q2_amt AS amount FROM store_quarter_sales UNION ALL SELECT store_id, Q3 AS quarter, q3_amt AS amount FROM store_quarter_sales UNION ALL SELECT store_id, Q4 AS quarter, q4_amt AS amount FROM store_quarter_sales;✅ 现代极致性能写法利用ARRAY构造 LATERAL VIEW单次扫描-- 在 Hive / Spark SQL 中仅需扫描 1 次底层表即可完成优雅列转行 SELECT store_id, quarter_info.quarter_name, quarter_info.quarter_amount FROM store_quarter_sales LATERAL VIEW EXPLODE(ARRAY( NAMED_STRUCT(quarter_name, Q1, quarter_amount, q1_amt), NAMED_STRUCT(quarter_name, Q2, quarter_amount, q2_amt), NAMED_STRUCT(quarter_name, Q3, quarter_amount, q3_amt), NAMED_STRUCT(quarter_name, Q4, quarter_amount, q4_amt) )) tf AS quarter_info;性能对比在 1000 万行大宽表上实测LATERAL VIEW相比 4 次UNION ALL磁盘 I/O 减少 75%执行耗时从 1 分 20 秒骤降至 18 秒流派三数组与多值集合炸裂跨主流引擎语法速查业务表user_tags(user_id, tags)其中tags包含数组[A, B, C]-- 1. Hive / Spark SQL 语法: SELECT user_id, single_tag FROM user_tags LATERAL VIEW EXPLODE(tags) t AS single_tag; -- 2. ClickHouse 语法 (极致简洁): SELECT user_id, arrayJoin(tags) AS single_tag FROM user_tags; -- 3. Presto / Trino 语法: SELECT user_id, single_tag FROM user_tags CROSS JOIN UNNEST(tags) AS t(single_tag);终极选型总结行转列长转宽首选MAX(CASE WHEN)不仅执行计划最稳健透明而且对条件判断支持度最灵活如支持在CASE WHEN内部写复合逻辑。列转行宽转长坚决废弃UNION ALL采用EXPLODE(ARRAY(...))语法糖最大化发挥单次扫描的 I/O 压缩优势。炸裂空数组保留主键使用EXPLODE_OUTER如果某用户的tags数组为空或NULL普通EXPLODE会将该用户直接过滤丢弃使用EXPLODE_OUTER可以保留该用户行并将标签填充为NULL防止数据静默丢失。