PostgreSQL交叉表查询

10 浏览
0 Comments

PostgreSQL交叉表查询

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

例如，我有以下表：

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

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

Section    Active    Inactive
A          1         2
B          4         5

这个可能吗？

admin 更改状态以发布 2023年5月21日
0

2 答案

0 Comments 
SELECT section,
       SUM(CASE status WHEN 'Active' THEN count ELSE 0 END) AS active, --here you pivot each status value as a separate column explicitly
       SUM(CASE status WHEN 'Inactive' THEN count ELSE 0 END) AS inactive --here you pivot each status  value as a separate column explicitly
FROM t
GROUP BY section

：这是一段包含了粗体数字123的段落。

0
0 Comments

一次性在每个数据库中安装附加模块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);

返回：

 Section | Active | Inactive
---------+--------+----------
 A       |      1 |        2
 B       |      4 |        5
 C       |      7 |           -- !!
  • 不需要强制转换和重命名。
  • 请注意C的不正确的结果：值7填充在第一列中。有时，此行为是可取的，但不适用于此用例。
  • 简单形式还限制在所提供的输入查询中仅有三个列：行名称、类别、值。没有多余的列，如下面的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);

返回：

 Section | Active | Inactive
---------+--------+----------
 A       |      1 |        2
 B       |      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

在 这里 使用 db <> fiddle

过多输入行的效果

过多的输入行会被处理得不同 - 会出现相同 ("row_name", "category") 组合 - 在上面的例子中是 (section, status).

1个参数形式从左到右填写可用值列，多余的值将被丢弃。
较早的输入行胜出。

2个参数形式将每个输入值分配给它的专用列，覆盖任何以前的分配。
后面的输入行胜出。

通常，不会有重复的。但如果有，请仔细调整排序顺序以满足您的需求 - 并记录正在发生的事情。
或者，如果您不关心结果，请获得快速的任意结果。只需注意效果即可。

高级示例

在 psql 中使用 \crosstabview

Postgres 9.6 在其默认的交互式终端 psql 添加了这个元命令。您可以运行您将用作第一个 crosstab() 参数的查询，并将其传递给 \crosstabview（立即或在下一步中）。例如：

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

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

psql 功能的作者 Daniel Vérité 在 dba.SE 上的相关答案：

0