PostgreSQL交叉表查询 [英] PostgreSQL Crosstab Query

问题描述

有人知道如何在PostgreSQL中创建交叉表查询吗?
例如，我有下表:

Does any one know how to create crosstab queries in PostgreSQL?
For example I have the following table:

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

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

I would like the query to return the following crosstab:

Section    Active    Inactive
A          1         2
B          4         5

这可能吗?

推荐答案

安装其他模块每个数据库 tablefunc 一次，该数据库提供功能crosstab().从Postgres 9.1开始，您可以为此使用 CREATE EXTENSION :

Install the additional module tablefunc once per database, which provides the function crosstab(). Since Postgres 9.1 you can use CREATE EXTENSION for that:

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

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

输入参数为 1 的

crosstab(text):

Simple form - not fit for missing attributes

crosstab(text) with 1 input parameter:

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参数替代方法一样，多余的列没有空间.
• No need for casting and renaming.
• Note the incorrect result for C: the value 7 is filled in for the first column. Sometimes, this behavior is desirable, but not for this use case.
• The simple form is also limited to exactly three columns in the provided input query: row_name, category, value. There is no room for extra columns like in the 2-parameter alternative below.

crosstab(text, text):

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的正确结果.

  • Note the correct result for C.

    第二个参数可以是任何一个查询，该查询的每个属性返回一个行，该属性与末尾的列定义顺序匹配.通常，您会想从基础表中查询不同的属性，如下所示:

    The second parameter can be any query that returns one row per attribute matching the order of the column definition at the end. Often you will want to query distinct attributes from the underlying table like this:

    'SELECT DISTINCT attribute FROM tbl ORDER BY 1'
    

    在手册中.

    由于您仍然必须拼写出列定义列表中的所有列(预定义的crosstabN()变体除外)，因此在VALUES表达式中提供简短列表的方式通常更为有效，如所示:

    Since you have to spell out all columns in a column definition list anyway (except for pre-defined crosstabN() variants), it is typically more efficient to provide a short list in a VALUES expression like demonstrated:

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

    或者(不在手册中):

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

  • 我使用了 美元报价 使报价更容易.

  • I used dollar quoting to make quoting easier.

    您甚至可以使用crosstab(text, text)输出具有 不同数据类型的列-只要值列的文本表示形式是目标的有效输入类型.这样，您可能具有不同种类的属性，并为各个属性输出text，date，numeric等. 第 crosstab(text, text) .

    You can even output columns with different data types with crosstab(text, text) - as long as the text representation of the value column is valid input for the target type. This way you might have attributes of different kind and output text, date, numeric etc. for respective attributes. There is a code example at the end of the chapter crosstab(text, text) in the manual.

    db<>小提琴此处

    • 使用Tablefunc在多个列上旋转 -还演示了提到的额外的列"

    • Pivot on Multiple Columns using Tablefunc - also demonstrating mentioned "extra columns"

    动态替代枢轴CASE和GROUP BY

    
    

    Postgres 9.6 已将此元命令添加到其默认的交互式终端

    Postgres 9.6 added this meta-command to its default interactive terminal psql. You can run the query you would use as first crosstab() parameter and feed it to \crosstabview (immediately or in the next step). Like:

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

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

    Similar result as above, but it's a representation feature on the client side exclusively. Input rows are treated slightly differently, hence ORDER BY is not required. Details for \crosstabview in the manual. There are more code examples at the bottom of that page.

    DanielVérité(psql功能的作者)对dba.SE的相关回答:

    Related answer on dba.SE by Daniel Vérité (the author of the psql feature):

    • 如何在结果表定义未知的情况下如何生成透视交叉联接?

    
    

    先前接受的答案已过时.

    • 函数crosstab(text, integer)的变体已过时.第二个integer参数将被忽略.我引用了 current 手册:

    • The variant of the function crosstab(text, integer) is outdated. The second integer parameter is ignored. I quote the current manual:

    crosstab(text sql, int N) ...

    crosstab(text)的过时版本.现在将忽略参数N， 因为值列的数量始终由调用查询决定

    Obsolete version of crosstab(text). The parameter N is now ignored, since the number of value columns is always determined by the calling query

  • 不需要强制转换和重命名.

  • Needless casting and renaming.

    如果一行没有所有属性，则失败.请参阅上面具有两个输入参数的安全变量，以正确处理缺少的属性.

    It fails if a row does not have all attributes. See safe variant with two input parameters above to handle missing attributes properly.

    ORDER BY是crosstab()的一参数形式. 手册:

    ORDER BY is required in the one-parameter form of crosstab(). The manual:

    在实践中，SQL查询应始终指定ORDER BY 1,2以确保 输入行的顺序正确

    In practice the SQL query should always specify ORDER BY 1,2 to ensure that the input rows are properly ordered

  这篇关于PostgreSQL交叉表查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！