0Pricing
SQL Academy · 课时

交叉表模式（PostgreSQL crosstab()）

使用 tablefunc 扩展的 crosstab() 函数生成真正的透视表。

交叉表模式（PostgreSQL crosstab()） 是 CoddyKit 上的免费 SQL Academy 课时。 这是第 4 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 SQL Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL Academy 课程共包含 4 节课。

为什么使用真正的交叉表？

CASE 透视要求您列出每个目标列。对于真正宽的透视表（例如每个产品一列），tablefunc 扩展中的 crosstab() 才是合适的工具。

启用扩展

tablefunc 随 PostgreSQL contrib 一起提供：

CREATE EXTENSION IF NOT EXISTS tablefunc;

基本的 crosstab 函数签名

crosstab 接收一个包含 3 列的 SQL 字符串（row_key、类别、值），并返回 row_key 以及每个类别对应的一列：

SELECT * FROM crosstab(
  $$
    SELECT user_id, status, COUNT(*)::INT
    FROM orders
    GROUP BY user_id, status
    ORDER BY user_id, status
  $$
) AS ct (
  user_id BIGINT,
  paid    INT,
  pending INT,
  cancelled INT
);

为什么要声明输出列

SQL 是静态类型的——规划器需要在解析时知道输出列。因此，您需要在 AS 子句中指定模式，包括数据类型。

双参数 crosstab（带类别集合）

对于稀疏数据，请单独提供类别列表，这样缺失值会变成 NULL，而不会导致列错位：

SELECT * FROM crosstab(
  $$
    SELECT user_id, status, COUNT(*)::INT
    FROM orders GROUP BY user_id, status
    ORDER BY user_id
  $$,
  $$ VALUES ('paid'), ('pending'), ('cancelled') $$
) AS ct (
  user_id BIGINT, paid INT, pending INT, cancelled INT
);

CASE 优于 crosstab 的情况

对于已知且数量较少的类别，CASE/FILTER 更简单——无需扩展，也没有双参数用法的陷阱。在以下情况下使用 crosstab：

  • 类别很多
  • 类别是动态加载的
  • 您要为外部透视工具生成数据

动态透视

对于运行时未知的类别，请在应用程序中生成 SQL，或使用 PL/pgSQL 配合 format() + EXECUTE。

-- Build the SQL dynamically:
SELECT string_agg(format('SUM(CASE WHEN status = %L THEN 1 END) AS %I',
                          status, status), ', ')
FROM (SELECT DISTINCT status FROM orders) s;

为电子表格生成宽格式

面向分析人员的报表通常需要宽格式。您可以在 SQL 中生成宽格式，也可以直接交付长格式，让 BI 工具完成透视。

逆透视：反向操作

要将宽格式转换为长格式，请使用 UNION ALL 或 PostgreSQL 的 jsonb_each_text()：

SELECT id, key AS month, (value)::NUMERIC AS revenue
FROM monthly_wide,
     jsonb_each_text(to_jsonb(monthly_wide) - 'id');

性能

crosstab() 只运行一次内部 SQL，然后在内存中完成透视。瓶颈与普通 GROUP BY 查询相同。

crosstab 的局限性

PostgreSQL 没有原生的 PIVOT 关键字（不同于 Oracle/SQL Server）。crosstab() 是一种替代方案。

回顾

对于大多数透视操作，CASE/FILTER 是简洁的解决方案。当类别很多或事先未知时，crosstab() 是您的工具。

快速检查

哪个扩展提供 PostgreSQL 的 crosstab() 函数？

常见问题解答

「交叉表模式（PostgreSQL crosstab()）」课时是免费的吗？

是的 — 「交叉表模式（PostgreSQL crosstab()）」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 SQL Academy 课程的其余内容，请升级到 CoddyKit PRO。 SQL Academy 课程共包含 4 节课。

「交叉表模式（PostgreSQL crosstab()）」这节课中我会学到什么？

使用 tablefunc 扩展的 crosstab() 函数生成真正的透视表。 你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 SQL Academy 需要有经验吗？

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

「交叉表模式（PostgreSQL crosstab()）」课时需要多长时间？

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

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

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

此课程中的所有课时

  1. UNION、INTERSECT、EXCEPT
  2. UNION ALL 与 UNION（去重成本）
  3. CASE 表达式与透视查询
  4. 交叉表模式（PostgreSQL crosstab()）
← 返回 SQL Academy