Coding Interview Prep · 课时

将列反透视为行

Q: 「将列反透视为行」这节课中我会学到什么？

使用 UNPIVOT 或 UNION ALL 将宽表还原。 你通过在浏览器中直接运行的动手代码来练习 Coding Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

使用 UNPIVOT 或 UNION ALL 将宽表还原。

将列反透视为行 是 CoddyKit 上的免费 Coding Interview Prep 课时。这是第 3 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 Coding Interview Prep 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 Coding Interview Prep 课程共包含 4 节课。

反向问题

逆透视是透视的镜像操作：将宽表的列重新转换为行。当数据以电子表格形式到达、但需要规范化以便分析时，面试官会询问这个问题。

例如：每个地区都有 q1, q2, q3, q4 列的表，必须转换为由 (region, quarter, amount) 组成的行。这种长表形式更适合聚合、连接和绘图。

-- Wide input we want to unpivot
region | q1  | q2  | q3  | q4
-------+-----+-----+-----+----
East   | 100 | 150 | 120 | 180
West   | 200 | 250 | 210 | 260

可移植的 UNION ALL 模式

与数据库方言无关的答案是 UNION ALL：针对每个源列编写一个 SELECT，每个查询都输出一个字面标签和该列的值。

请使用 UNION ALL，而不是 UNION，这样无需承担去重开销，并且能够保留每一行，即使两个单元格的值相同也不例外。

SELECT region, 'Q1' AS quarter, q1 AS amount FROM wide_sales
UNION ALL
SELECT region, 'Q2', q2 FROM wide_sales
UNION ALL
SELECT region, 'Q3', q3 FROM wide_sales
UNION ALL
SELECT region, 'Q4', q4 FROM wide_sales;

为什么使用 UNION ALL 而不是 UNION

这是一个经典的面试陷阱。UNION 会删除整个结果中的重复行。如果 East 和 West 在 Q1 都有 100，普通的 UNION 就会合并相同的行，导致数据丢失。

UNION ALL 会在不去重的情况下进行拼接，这正是逆透视所需要的方式。它也更快，因为不需要为了去重而进行排序或哈希处理。

-- UNION would wrongly merge identical (region, quarter, amount) rows
-- UNION ALL keeps every row, always the correct choice here

列类型对齐

UNION ALL 的每个分支都必须按照相同顺序生成数量相同且类型兼容的列。列名取自第一个 SELECT。

如果您的宽表列类型不同（例如一个是 int，另一个是 decimal），数据库引擎会选择一个公共类型。如果它们确实不兼容，请显式进行类型转换，以免合并失败。

SELECT region, 'revenue' AS metric, CAST(revenue AS decimal(12,2)) AS val FROM t
UNION ALL
SELECT region, 'units',   CAST(units   AS decimal(12,2))        FROM t;

SQL Server UNPIVOT

SQL Server 提供专用的 UNPIVOT 运算符，比 UNION ALL 更简洁。您需要指定新的值列、新的标签列，并列出要折叠的源列。

有一个重要行为需要注意：UNPIVOT 会删除值为 NULL 的行。面试官会测试您是否了解这个副作用。

SELECT region, quarter, amount
FROM wide_sales
UNPIVOT (
  amount FOR quarter IN (q1, q2, q3, q4)
) AS u;

UNPIVOT 会删除 NULL

如果某个地区在 q3 中的值为 NULL，SQL Server 的 UNPIVOT 会直接从输出中省略该行。如果无论是否为 NULL 都需要为每一列保留一行，请改用 UNION ALL，因为它会保留这些行。

在面试中请说明这一权衡：原生 UNPIVOT 简洁，但会丢失 NULL 行；UNION ALL 冗长，但数据完整。

-- UNPIVOT: q3 NULL for East -> no (East, Q3) row produced
-- UNION ALL: (East, 'Q3', NULL) row IS produced

PostgreSQL：LATERAL VALUES

PostgreSQL 没有 UNPIVOT，但有一种简洁的惯用写法：对 VALUES 列表使用 CROSS JOIN LATERAL。每个宽表行都会与一个由（标签、值）对组成的小型内联表展开组合。

这种写法比很长的 UNION ALL 更简洁，并且只读取源表一次。

SELECT w.region, v.quarter, v.amount
FROM wide_sales w
CROSS JOIN LATERAL (VALUES
  ('Q1', w.q1),
  ('Q2', w.q2),
  ('Q3', w.q3),
  ('Q4', w.q4)
) AS v(quarter, amount);

只读取一次表

一个值得提及的性能要点是：朴素的 UNION ALL 会针对每个分支扫描一次宽表（四个季度就扫描四次）。LATERAL VALUES 写法和 SQL Server 的 UNPIVOT 都只读取源表一次。

对于大型表，这一点很重要。如果必须使用 UNION ALL，优化器仍可能重复扫描，因此请提到 LATERAL 或 UNPIVOT 是更高效的选择。

筛选空单元格

使用 UNION ALL 或 LATERAL 时，值为 NULL 的行也会被保留。如果问题只要求非空单元格，请添加筛选条件。这会模拟 SQL Server UNPIVOT 自动执行的行为。

是否保留或删除 NULL 值取决于具体判断，因此在编写查询之前，请先与面试官确认需求。

SELECT region, quarter, amount
FROM (
  SELECT region, 'Q1' AS quarter, q1 AS amount FROM wide_sales
  UNION ALL SELECT region, 'Q2', q2 FROM wide_sales
) t
WHERE amount IS NOT NULL;

实例：逆透视后的聚合

一个常见的后续问题是：“从按季度划分的宽格式表中，给出所有季度各地区的总收入。”完成逆透视得到长格式后，聚合就很简单了：按地区分组进行一次 SUM。

这说明了先进行逆透视的真正原因。对四个独立列求和很脆弱，而长格式的 SUM(amount) GROUP BY region 可以扩展到任意数量的季度。

WITH long_sales AS (
  SELECT region, 'Q1' AS quarter, q1 AS amount FROM wide_sales
  UNION ALL SELECT region, 'Q2', q2 FROM wide_sales
  UNION ALL SELECT region, 'Q3', q3 FROM wide_sales
  UNION ALL SELECT region, 'Q4', q4 FROM wide_sales
)
SELECT region, SUM(amount) AS total
FROM long_sales
GROUP BY region;

何时进行逆透视

请在文字题目中识别逆透视信号：

输入包含重复的列，而这些列实际上代表值（月份、年份、指标）。
您需要跨这些值进行聚合、连接或绘图。
您希望在导入时规范化非规范化的电子表格数据。

对于后续的 SQL 操作，长格式几乎总是正确的形状，因此逆透视经常是第一步。

快速检查

请确认您了解最常见的逆透视陷阱。

回顾

逆透视会将列转换为行：

可移植：每列使用一个 SELECT，再通过 UNION ALL 连接（绝不能使用普通的 UNION）。
SQL Server：原生支持 UNPIVOT，写法简洁，但会丢弃 NULL 值。
PostgreSQL：使用 CROSS JOIN LATERAL (VALUES ...)，只扫描一次。
确保各分支的列数和类型一致；如果题目有要求，请筛除 NULL。

常见问题解答

「将列反透视为行」课时是免费的吗？

是的 — 「将列反透视为行」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 Coding Interview Prep 课程的其余内容，请升级到 CoddyKit PRO。 Coding Interview Prep 课程共包含 4 节课。

「将列反透视为行」这节课中我会学到什么？

使用 UNPIVOT 或 UNION ALL 将宽表还原。你通过在浏览器中直接运行的动手代码来练习 Coding Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 Coding Interview Prep 需要有经验吗？

无需任何先前经验。CoddyKit 上的 Coding Interview Prep 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 3 节课，共 4 节。

「将列反透视为行」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 Coding Interview Prep 课中编写并运行代码吗？

能。每节 Coding Interview Prep 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

← 返回 Coding Interview Prep