PostgreSQL 交叉表查询

Question

有谁知道如何在 PostgreSQL 中创建交叉表查询？

例如我有下表：

 Section    Status    Count
A          Active    1
A          Inactive  2
B          Active    4
B          Inactive  5

我希望查询返回以下交叉表：

 Section    Active    Inactive
A          1         2
B          4         5

这可能吗？

原文由 user151419 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

每个数据库安装 一次 附加模块 tablefunc ，它提供功能 crosstab() 。从 Postgres 9.1 开始，您可以使用 CREATE EXTENSION ：

 CREATE EXTENSION IF NOT EXISTS tablefunc;

改进的测试用例

CREATE TABLE tbl (
   section   text
 , status    text
 , ct        integer  -- "count" is a reserved word in standard SQL
);

INSERT INTO tbl VALUES
  ('A', 'Active', 1), ('A', 'Inactive', 2)
, ('B', 'Active', 4), ('B', 'Inactive', 5)
                    , ('C', 'Inactive', 7);  -- ('C', 'Active') is missing

简单的形式 - 不适合缺少的属性

crosstab(text) 带 1个 输入参数：

 SELECT *
FROM   crosstab(
   'SELECT section, status, ct
    FROM   tbl
    ORDER  BY 1,2'  -- needs to be "ORDER BY 1,2" here
   ) AS ct ("Section" text, "Active" int, "Inactive" int);

回报：

 部分 |活跃 |不活跃
---------+--------+----------
 一个 | 1 | 2
 乙| 4 | 5
 C | 7 | ——！！

• 无需铸造和重命名。
• 请注意 C 的 错误 结果：值 7 填写在第一列。有时，这种行为是可取的，但不适用于此用例。
• 简单形式也仅限于提供的输入查询 _中的三列： rowname 、 category 、 value 。没有像下面的 2 参数替代方案那样的 额外列 的空间。

安全形式

crosstab(text, text) 有 2个 输入参数：

 SELECT *
FROM   crosstab(
   'SELECT section, status, ct
    FROM   tbl
    ORDER  BY 1,2'  -- could also just be "ORDER BY 1" here

  , $$VALUES ('Active'::text), ('Inactive')$$
   ) AS ct ("Section" text, "Active" int, "Inactive" int);

回报：

 部分 |活跃 |不活跃
---------+--------+----------
 一个 | 1 | 2
 乙| 4 | 5
 C | | 7—— ！！

• 注意 C 的正确结果。

• 第二个参数 可以是任何查询，每个属性返回 _一行_，匹配最后列定义的顺序。通常，您会希望从基础表中查询不同的属性，如下所示：

     'SELECT DISTINCT attribute FROM tbl ORDER BY 1'

手册上是这样的。

由于无论如何您都必须拼出列定义列表中的所有列（预定义的 crosstabN() 变体除外），因此在 VALUES 表达式中提供短列表通常更有效如演示：

     $$VALUES ('Active'::text), ('Inactive')$$)

或者（不在手册中）：

     $$SELECT unnest('{Active,Inactive}'::text[])$$  -- short syntax for long lists

• 我使用 美元报价 使报价更容易。

• 您甚至可以使用 crosstab(text, text) 输出具有 不同 数据类型 的列 - 只要值列的文本表示是目标类型的有效输入。这样，您可能具有不同类型的属性并输出 text ， date ， numeric 等。 手册中的章节 crosstab(text, text) 末尾有一个代码示例。

_db<> 在这里 摆弄_

多余输入行的影响

多余的输入行的处理方式不同 - 相同（“row_name”，“category”）组合的重复行 - (section, status) 在上面的例子中。

1 参数 表单从左到右填写可用值列。多余的值被丢弃。

较早的输入行获胜。

2 参数 形式将每个输入值分配给其专用列，覆盖任何先前的分配。

稍后输入行获胜。

通常，您一开始就没有重复项。但如果您这样做，请根据您的要求仔细调整排序顺序 - 并记录发生的情况。

如果您不在乎，也可以快速获得任意结果。请注意效果。

高级示例

• 使用 Tablefunc 旋转多列- 还演示了提到的“额外列”

• 使用 CASE 和 GROUP BY 的动态替代方案

\crosstabview 在 psql

Postgres 9.6 将此元命令添加到其默认交互式终端 psql 中。您可以运行将用作第一个 crosstab() 参数的查询并将其提供给 \crosstabview （立即或在下一步中）。喜欢：

 db=> SELECT section, status, ct FROM tbl \crosstabview

与上面类似的结果，但它 _是客户端专有的表示功能_。输入行的处理方式略有不同，因此不需要 ORDER BY 。 手册中 \crosstabview 的详细信息。 该页面底部有更多代码示例。

Daniel Vérité（psql 功能的作者）对 dba.SE 的相关回答：

• 如何在结果表定义未知的情况下生成旋转的 CROSS JOIN？

原文由 Erwin Brandstetter 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

我设计了一种不同的动态方法，一种采用动态记录的方法。类型（临时表，通过匿名过程构建）和 JSON。这对于无法安装 tablefunc/crosstab 扩展但仍可以创建临时表或运行 anon 的最终用户可能很有用。 proc的。

该示例假定所有 xtab 列都是相同类型 (INTEGER)，但列数是数据驱动和可变参数。也就是说，JSON 聚合函数确实允许混合数据类型，因此可以通过使用嵌入式复合（混合）类型进行创新。

如果你想静态定义rec，它的真正内容可以减少到一步。在 JSON 记录集函数中键入（通过发出复合类型的嵌套 SELECT）。

dbfiddle.uk

https://dbfiddle.uk/N1EzugHk

原文由 L. Rodgers 发布，翻译遵循 CC BY-SA 4.0 许可协议