0Pricing
Coding Interview Prep · 课时

包含未知列的动态透视

在类别事先未知时生成透视列。

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

困难的透视问题

所有静态透视，无论是 CASE 聚合、SQL Server 的 PIVOT，还是 PostgreSQL 的 crosstab，都有一个共同的限制：编写查询时必须列出输出列。

但是，如果类别未知，例如每周都会变化的产品名称，或者每个活跃月份对应一列，该怎么办？这就是动态透视。它是高级面试题，因为普通 SQL 无法返回列列表在运行时才确定的结果。

为什么仅靠 SQL 无法做到

SQL 在结果集层面采用静态类型：规划器必须在执行前知道列及其类型。单个查询无法表达为您恰好找到的每个值创建一列。

因此，通用方法是分两步生成 SQL 文本：首先查询不重复的类别，然后根据这些类别构建透视查询字符串并执行该字符串。

第 1 步：收集类别

第一步是执行一个普通查询，列出将要变成列的不重复值。通常应先对这些值排序，以保持列布局稳定。

这个结果会提供给字符串构建步骤。在实际系统中，您会先运行该查询、捕获这些行，再根据它们组装下一个查询。

SELECT DISTINCT quarter
FROM sales
ORDER BY quarter;
-- e.g. Q1, Q2, Q3, Q4

第 2 步：构建列列表

接下来，将这些值转换为以逗号分隔的 CASE 表达式列表（或者转换为 PIVOT 所需的方括号名称）。数据库提供了字符串聚合函数，可以直接在 SQL 中完成这一步。

在 PostgreSQL 中是 string_agg；在 MySQL 中是 GROUP_CONCAT；在 SQL Server 中是 STRING_AGG 或较早的 FOR XML PATH 技巧。

-- Postgres: build the SELECT-list fragment
SELECT string_agg(
  format('SUM(CASE WHEN quarter = %L THEN amount END) AS %I',
         quarter, quarter),
  ', '
)
FROM (SELECT DISTINCT quarter FROM sales ORDER BY 1) q;

第 3 步：组装并执行

将生成的片段拼接成完整的查询字符串，然后使用动态执行来运行它：在 PL/pgSQL 中使用 EXECUTE，在 SQL Server 中使用 sp_executesql，或者在 MySQL 中使用 PREPARE/EXECUTE。

这就是动态透视的核心：SQL 编写 SQL，然后执行它。

-- SQL Server pattern
DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(quarter), ',')
FROM (SELECT DISTINCT quarter FROM sales) q;
SET @sql = N'SELECT region, ' + @cols + '
  FROM (SELECT region, quarter, amount FROM sales) s
  PIVOT (SUM(amount) FOR quarter IN (' + @cols + ')) p;';
EXEC sp_executesql @sql;

PostgreSQL 完整示例

在 PostgreSQL 中，您可以将这三个步骤放入 DO 块或函数中。使用 string_agg 构建列列表，将其插入查询，再使用 EXECUTE 运行查询。

由于结果列直到运行时才能确定，因此返回此类结果的函数通常会使用 RETURNS SETOF record，或者将行作为 json 返回，再由调用方展开这些行。

DO $do$
DECLARE
  cols text;
  qry  text;
BEGIN
  SELECT string_agg(
    format('SUM(CASE WHEN quarter=%L THEN amount END) AS %I', quarter, quarter), ', ')
  INTO cols
  FROM (SELECT DISTINCT quarter FROM sales ORDER BY 1) q;
  qry := format('SELECT region, %s FROM sales GROUP BY region', cols);
  EXECUTE qry;
END $do$;

使用预处理语句的 MySQL

MySQL 没有透视运算符，因此动态透视会使用 GROUP_CONCAT 构建条件聚合字符串，然后通过预处理语句运行它。

GROUP_CONCAT 有长度限制（group_concat_max_len）。面试官可能会提到这一点；如果类别很多，请将该限制调高。

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
  CONCAT('SUM(CASE WHEN quarter=''', quarter,
         ''' THEN amount END) AS ', QUOTE(quarter))
) INTO @sql FROM sales;
SET @sql = CONCAT('SELECT region, ', @sql,
                  ' FROM sales GROUP BY region');
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

SQL 注入风险

由于您要将数据值拼接到可执行的 SQL 中，动态透视会带来注入风险。如果类别值包含引号或恶意文本，就可能破坏或劫持生成的查询。

请始终使用数据库引擎提供的安全辅助方法来转义标识符和字面量：在 PostgreSQL 中使用 format('%I', ...) 和 %L，在 SQL Server 中使用 QUOTENAME。绝不要将原始值直接粘贴到字符串中。

-- Safe quoting prevents injection / breakage
-- Postgres: %I identifier, %L literal
format('SUM(CASE WHEN k=%L THEN v END) AS %I', cat, cat)
-- SQL Server: QUOTENAME(cat)

返回未知列

第二个难点是：调用方无法预先知道结果形状。面试官通常接受以下策略：

  • 将行作为 JSON 返回，再由应用层展开各个键。
  • 让过程输出或构建查询，然后作为第二步运行该查询。
  • 类别确定后，在应用代码（数据分析库、BI 工具）中完成最终透视。

没有一种简洁的方法可以通过一次静态调用返回任意列。

实例：按产品进行透视

假设产品会不断上下架，而报表需要为 sales 中当前存在的每个产品提供一个收入列。您无法将列列表硬编码，因此必须生成它。PostgreSQL 让这个过程清晰易懂：使用 string_agg 和安全引用构建 CASE 片段，将其插入查询，然后使用 EXECUTE。

请向面试官逐步说明：发现产品，将每个产品格式化为带引号的列名，完成组装并运行。同样的结构适用于任何数据库引擎；变化的只是辅助方法。

DO $do$
DECLARE cols text; qry text;
BEGIN
  SELECT string_agg(
    format('SUM(CASE WHEN product=%L THEN amount END) AS %I',
           product, product), ', ')
  INTO cols
  FROM (SELECT DISTINCT product FROM sales ORDER BY 1) p;
  qry := format('SELECT region, %s FROM sales GROUP BY region', cols);
  EXECUTE qry;
END $do$;

何时避免动态透视

优秀的候选人知道什么时候不应在 SQL 中这样做。动态 SQL 更难阅读、测试、保护和缓存。通常更好的回答是：

  • 从 SQL 返回长格式数据，再在应用层或报表层进行透视。
  • 如果类别集合很小且变化缓慢，请使用静态透视，并偶尔更新它。

只有在类别集合确实开放且不断变化时，才应使用动态透视。

快速检查

请测试您对动态透视存在的核心原因的理解。

回顾

动态透视用于处理未知的列集合：

  • 静态透视会失败，因为结果列必须在执行前固定。
  • 模式是：查询不重复的类别，构建透视 SQL 字符串，再动态执行它。
  • 使用 string_agg/GROUP_CONCAT/STRING_AGG 构建列列表。
  • 转义值（%I/%L、QUOTENAME）以避免 SQL 注入。
  • 通常更简洁的做法是返回长格式数据，再在应用层进行透视。

常见问题解答

「包含未知列的动态透视」课时是免费的吗？

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

「包含未知列的动态透视」这节课中我会学到什么？

在类别事先未知时生成透视列。 你通过在浏览器中直接运行的动手代码来练习 Coding Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

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

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

「包含未知列的动态透视」课时需要多长时间？

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

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

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

此课程中的所有课时

  1. 使用条件聚合进行透视
  2. 数据库厂商的 PIVOT 与交叉表语法
  3. 将列反透视为行
  4. 包含未知列的动态透视
← 返回 Coding Interview Prep