PostgreSQL交叉表查询

每个数据库安装一次额外的模块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

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

带有一个输入参数的交叉表(文本):

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被填充为第一列。有时，这种行为是可取的，但不适用于此用例。简单表单在提供的输入查询中也仅限于三列:row_name、类别、值。没有多余的列空间，就像下面的2参数选项一样。

安全的形式

带有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 FROM tbl ORDER BY 1

手册上有。

因为无论如何你都必须在列定义列表中拼写出所有的列(除了预定义的crosstabN()变量)，通常在VALUES表达式中提供一个简短的列表更有效，如下所示:

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

或者(不在手册中):

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

我使用美元报价，使报价更容易。您甚至可以使用交叉表(text, text)输出具有不同数据类型的列—只要值列的文本表示形式是目标类型的有效输入。通过这种方式，您可以拥有不同类型的属性，并为各自的属性输出文本、日期、数字等。手册中章节交叉表(文本，文本)的末尾有一个代码示例。

db < >小提琴

输入行过多的影响

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

1参数表单从左到右填充可用值列。多余的值将被丢弃。较早的输入行胜出。

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

通常情况下，你一开始就没有副本。但是如果您这样做了，请仔细调整排序顺序以满足您的需求-并记录正在发生的事情。如果你不关心，也可以快速得到任意结果。只是要注意其影响。

先进的例子

使用Tablefunc对多列进行Pivot -也演示了提到的“额外列” 动态替代的枢轴与CASE和GROUP BY

\十字那时在psql

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

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

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

dba相关回答。SE by Daniel Vérité (psql特性的作者):

我如何生成一个枢轴交叉连接的结果表定义是未知的?

2012-08-01 02:48:06

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

2010-06-09 18:31:53

您可以使用附加模块tablefunc的crosstab()函数—每个数据库必须安装一次。从PostgreSQL 9.1开始，你可以使用CREATE EXTENSION:

CREATE EXTENSION tablefunc;

在你的情况下，我相信它会是这样的:

CREATE TABLE t (Section CHAR(1), Status VARCHAR(10), Count integer);

INSERT INTO t VALUES ('A', 'Active',   1);
INSERT INTO t VALUES ('A', 'Inactive', 2);
INSERT INTO t VALUES ('B', 'Active',   4);
INSERT INTO t VALUES ('B', 'Inactive', 5);

SELECT row_name AS Section,
       category_1::integer AS Active,
       category_2::integer AS Inactive
FROM crosstab('select section::text, status, count::text from t',2)
            AS ct (row_name text, category_1 text, category_2 text);

DB这里:

一切正常:https://dbfiddle.uk/iKCW9Uhh 没有CREATE EXTENSION;你会得到这个错误:https://dbfiddle.uk/j8W1CMvI 错误:函数交叉表(未知，整数)不存在第4行:FROM crosstab('select section::text, status, count::text fro… ＾提示:没有函数匹配给定的名称和参数类型。您可能需要添加显式类型强制转换。

2010-06-09 01:34:00