SQL Interview Prep · 课时

使用条件聚合进行透视

Q: 「使用条件聚合进行透视」这节课中我会学到什么？

使用可移植的 CASE 嵌套 SUM 模式，将行转换为列。 你通过在浏览器中直接运行的动手代码来练习 SQL Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

使用可移植的 CASE 嵌套 SUM 模式，将行转换为列。

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

面试场景

最常见的报表相关面试任务之一是：将行转换为列。您有一个类似 sales(region, quarter, amount) 的长表，而面试官希望得到一份宽报表，其中每个季度对应一列。

他们希望听到的可移植、与数据库方言无关的答案是条件聚合：将 CASE 表达式放在 SUM 等聚合函数中。掌握这一点后，您就能在任何数据库中进行透视，即使该数据库没有 PIVOT 关键字。

长表与宽表形式

在进行透视前，先明确两种形态。长表形式每行存储一个事实：每个区域/季度组合各占一行。宽表形式则将一个类别展开到多个列中。

长表：易于插入，但不便于并排阅读。
宽表：非常适合供人阅读的报告。

透视会将长表转换为宽表。面试官喜欢这道题，因为它考查的是您是否理解聚合，而不只是会写语法。

-- Long form (the input)
region | quarter | amount
-------+---------+-------
East   | Q1      | 100
East   | Q2      | 150
West   | Q1      | 200
West   | Q2      | 250

核心模式

关键技巧是：针对每个输出列，编写一个 CASE，当行与该列匹配时返回对应值，否则返回 NULL。再将它放入聚合函数中，使每个分组折叠为键对应的一行。

可以这样理解：将金额求和，但只针对第一季度的行。由于 SUM 会忽略 NULL，不匹配的行不会产生任何贡献。

SELECT
  region,
  SUM(CASE WHEN quarter = 'Q1' THEN amount END) AS q1,
  SUM(CASE WHEN quarter = 'Q2' THEN amount END) AS q2
FROM sales
GROUP BY region;

SUM 为何忽略 NULL

这种模式之所以有效，是因为面试官会追问的一个事实：聚合函数会跳过 NULL。当没有分支匹配时，没有 ELSE 的 CASE 会返回 NULL，因此 SUM(CASE WHEN ... THEN amount END) 只会累加您选中的行。

如果改为 ELSE 0，对于 SUM 也同样有效（加上零不会改变结果），但会破坏 AVG、MIN 和 COUNT 的语义。

-- Both produce the same SUM result:
SUM(CASE WHEN quarter = 'Q1' THEN amount END)
SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END)

实战示例：季度报告

下面是针对示例数据的完整查询。每个地区对应一行，每个季度对应一列。

GROUP BY region 会将四行输入数据压缩为两行输出数据。如果没有它，您将得到每个输入行对应一行的结果，其中大多是 NULL。

SELECT
  region,
  SUM(CASE WHEN quarter = 'Q1' THEN amount END) AS q1,
  SUM(CASE WHEN quarter = 'Q2' THEN amount END) AS q2
FROM sales
GROUP BY region;

-- Result:
-- region | q1  | q2
-- East   | 100 | 150
-- West   | 200 | 250

选择合适的聚合函数

包裹 CASE 的聚合函数必须与问题相匹配：

当每个单元格需要汇总数值时使用 SUM。
当每个地区/季度组合恰好只有一个值，而您只是希望将其显示出来时，使用 MAX 或 MIN。
当每个单元格需要统计匹配行数时使用 COUNT。

面试官经常会询问 COUNT 变体：每个月每种状态有多少个订单？

SELECT
  month,
  COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped,
  COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled
FROM orders
GROUP BY month;

单值单元格使用 MAX

当每个键/类别组合只包含一个值时（真正的交叉表，而不是总计），请使用 MAX 或 MIN。这两个函数都会返回唯一的非 NULL 值，并忽略不匹配分支产生的 NULL。

当您是在重塑属性而不是汇总金额时，这是安全的选择，例如将键/值设置表转换为每个实体一行。

-- Turn key/value rows into one wide row per user
SELECT
  user_id,
  MAX(CASE WHEN attr = 'city'  THEN value END) AS city,
  MAX(CASE WHEN attr = 'plan'  THEN value END) AS plan
FROM user_attributes
GROUP BY user_id;

处理 NULL 输出单元格

如果某个地区没有 Q2 销售额，其 q2 单元格将显示为 NULL。面试官可能会要求您将其显示为 0。请将整个聚合函数包裹在 COALESCE 中。

请将 COALESCE 放在聚合函数外部，而不是放在 CASE 内部，这样只有在整个分组都没有匹配行时才会进行替换。

SELECT
  region,
  COALESCE(SUM(CASE WHEN quarter = 'Q1' THEN amount END), 0) AS q1,
  COALESCE(SUM(CASE WHEN quarter = 'Q2' THEN amount END), 0) AS q2
FROM sales
GROUP BY region;

添加总计列

一个常见的后续问题是：添加一个汇总所有透视列的总计。您不需要按名称逐列相加。对同一分组使用普通的 SUM(amount)，即可得到行总计，因为它完全不会受到 CASE 筛选的影响。

这可以向面试官表明，您理解 SELECT 中的每个聚合函数都会在同一分组上独立计算。

SELECT
  region,
  SUM(CASE WHEN quarter = 'Q1' THEN amount END) AS q1,
  SUM(CASE WHEN quarter = 'Q2' THEN amount END) AS q2,
  SUM(amount) AS total
FROM sales
GROUP BY region;

筛选聚合快捷写法

PostgreSQL 和 SQL 标准支持 FILTER (WHERE ...)，这是一种更简洁的条件聚合写法。它的可读性更好，也避免了 CASE 样板代码。

在面试中提到这一点可以展现您的知识广度，但请注意，MySQL 和 SQL Server 不支持它，因此 CASE 仍然是可移植的答案。

-- Postgres / standard SQL
SELECT
  region,
  SUM(amount) FILTER (WHERE quarter = 'Q1') AS q1,
  SUM(amount) FILTER (WHERE quarter = 'Q2') AS q2
FROM sales
GROUP BY region;

最大限制

条件聚合有一个面试官会重点追问的问题：您必须手动列出每个输出列。如果季度或类别事先未知，这个静态查询就无法适应。

这个问题称为动态透视，需要生成 SQL。不过，对于固定且已知的类别集合，条件聚合仍然是简洁且可移植的最佳选择。

快速检查

检验您对条件聚合模式的掌握程度。

回顾

条件聚合是每位面试官都认可的可移植透视写法：

每个输出列使用一个 CASE，并将其包裹在聚合函数中。
总计使用 SUM，单值单元格使用 MAX/MIN，计数使用 COUNT。
之所以有效，是因为聚合函数会忽略不匹配分支产生的 NULL。
使用 COALESCE 将空单元格转换为 0。
限制：列必须硬编码，这将引出接下来的动态透视。

常见问题解答

「使用条件聚合进行透视」课时是免费的吗？

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

「使用条件聚合进行透视」这节课中我会学到什么？

使用可移植的 CASE 嵌套 SUM 模式，将行转换为列。你通过在浏览器中直接运行的动手代码来练习 SQL Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

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

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

「使用条件聚合进行透视」课时需要多长时间？

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

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

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

此课程中的所有课时

← 返回 SQL Interview Prep