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

问题描述

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

部分状态计数一个活跃的 1一个不活跃的 2B 主动 4B 不活动 5

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

Section Active Inactive1 2乙 4 5

这可能吗?

解决方案

安装 附加模块 tablefunc 每个数据库一次，它提供函数 crosstab().从 Postgres 9.1 开始，您可以使用 CREATE EXTENSION 为此:

CREATE EXTENSION IF NOT EXISTS tablefunc;

改进的测试用例

CREATE TABLE tbl (部分文字, 状态文本, ct 整数——计数"；是标准 SQL 中的保留字);插入 tbl 值('A', '活动', 1), ('A', '非活动', 2), ('B', '活动', 4), ('B', '非活动', 5), ('C', '非活动', 7);-- ('C', 'Active') 缺失

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

crosstab(text) with 1 输入参数:

SELECT *从交叉表('选择部分，状态，ct从表ORDER BY 1,2' -- 需要是ORDER BY 1,2"；这里) AS ct(Section"文本，Active"int，Inactive"int)；

返回:

<前>节 |活跃 |不活动---------+--------+----------一个 |1 |2乙 |4 |5C |7 |——！！

• 无需强制转换和重命名.
• 注意C 的错误结果:第一列填充了值7.有时，这种行为是可取的，但不适用于此用例.
• 简单表单也仅限于恰好提供的输入查询中的三列:row_name、category、value.没有空间容纳 额外的列，就像下面的 2 参数替代方案一样.

安全形式

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

SELECT *从交叉表('选择部分，状态，ct从表ORDER BY 1,2'——这里也可以是ORDER BY 1", $$VALUES ('Active'::text), ('Inactive')$$) AS ct("Section" text, "Active" int, "Inactive" int);

返回:

<前>节 |活跃 |不活动---------+--------+----------一个 |1 |2乙 |4 |5C ||7 -- ！！

• 注意 C 的正确结果.

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

   'SELECT DISTINCT 属性 FROM tbl ORDER BY 1'

说明书上有.

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

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

或(不在手册中):

 $$SELECT unnest('{Active,Inactive}'::text[])$$ -- 长列表的简短语法

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

• 您甚至可以使用 crosstab(text, text) 输出具有不同数据类型的列 - 只要文本表示值列是目标类型的有效输入.通过这种方式，您可能有不同种类的属性，并为各个属性输出 text、date、numeric 等.章节末尾有代码示例em>crosstab(text, text)在手册中.

db<>fiddle 这里/p>

输入行过多的影响

多余的输入行的处理方式不同 - 相同(row_name"、category")组合的重复行 - 上面示例中的 (section, status).

1-parameter 表单从左到右填写可用值列.多余的值被丢弃.
较早的输入行胜出.

2-parameter 表单将每个输入值分配给其专用列，覆盖之前的任何分配.
后来的输入行获胜.

通常情况下，您一开始就没有重复项.但是，如果您这样做了，请根据您的要求仔细调整排序顺序 - 并记录正在发生的事情.
或者，如果您不在乎，则可以快速获得任意结果.请注意效果.

高级示例

• 使用 Tablefunc 在多列上透视- 还展示了提到的额外列"

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

crosstabview in psql

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

db=>选择部分，状态，ct FROM tbl crosstabview

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

Daniel Vérité(psql 功能的作者)在 dba.SE 上的相关回答:

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

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

Is this possible?

解决方案

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;

Improved test case

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

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);

Returns:

 Section | Active | Inactive
---------+--------+----------
 A       |      1 |        2
 B       |      4 |        5
 C       |      7 |           -- !!

• 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.

Safe form

crosstab(text, text) with 2 input parameters:

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);

Returns:

 Section | Active | Inactive
---------+--------+----------
 A       |      1 |        2
 B       |      4 |        5
 C       |        |        7  -- !!

• 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'
  

That's in the manual.

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')$$)

Or (not in the manual):

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

• I used dollar quoting to make quoting easier.

• 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<>fiddle here

Effect of excess input rows

Excess input rows are handled differently - duplicate rows for the same ("row_name", "category") combination - (section, status) in the above example.

The 1-parameter form fills in available value columns from left to right. Excess values are discarded.
Earlier input rows win.

The 2-parameter form assigns each input value to its dedicated column, overwriting any previous assignment.
Later input rows win.

Typically, you don't have duplicates to begin with. But if you do, carefully adjust the sort order to your requirements - and document what's happening.
Or get fast arbitrary results if you don't care. Just be aware of the effect.

Advanced examples

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

• Dynamic alternative to pivot with CASE and GROUP BY

crosstabview in psql

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

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.

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

• How do I generate a pivoted CROSS JOIN where the resulting table definition is unknown?

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