SQL PostgreSQL 交叉表查询

Question

提问by schone

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

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

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?

这可能吗？

Answer 1

回答by Erwin Brandstetter

Install the additional module tablefunconceper database, which provides the function crosstab(). Since Postgres 9.1 you can use CREATE EXTENSIONfor that:

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

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 1input parameter:

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

Returns:

返回：

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

No need for casting and renaming.
Note the incorrectresult for C: the value 7is filled in for the first column. Sometimes, this behavior is desirable, but not for this use case.
The simple form is also limited to exactlythree columns in the provided input query: row_name, category, value. There is no room for extra columnslike in the 2-parameter alternative below.

无需铸造和重命名。
请注意的错误结果C：7为第一列填充了值。有时，这种行为是可取的，但不适用于此用例。
在简单的形式也被限制到恰好在所提供的输入查询三列：ROW_NAME，类别，值。没有像下面的 2 参数替代方案那样的额外列的空间。

Safe form

安全形式

crosstab(text, text)with 2input parameters:

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

Returns:

返回：

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

Note the correct result for C.
The second parametercan be any query that returns one rowper 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 VALUESexpression like demonstrated:
```
$$VALUES ('Active'::text), ('Inactive')$$)
```
Or (not in the manual):
```
$$SELECT unnest('{Active,Inactive}'::text[])$$  -- short syntax for long lists
```
I used dollar quotingto make quoting easier.
You can even output columns with differentdata typeswith 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, numericetc. for respective attributes. There is a code example at the end of the chapter crosstab(text, text)in the manual.

注意的正确结果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<>fiddle here

db<>在这里摆弄

Advanced examples

高级示例

Pivot on Multiple Columns using Tablefunc- also demonstrating mentioned "extra columns"
Dynamic alternative to pivot with CASE and GROUP BY

使用 Tablefunc 在多列上透视- 还演示了提到的“额外列”
使用 CASE 和 GROUP BY 旋转的动态替代方案

`\crosstabview`in psql

`\crosstabview`在 psql 中

Postgres 9.6added 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:

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

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

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

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

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

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

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

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

The previously accepted answeris outdated.

在以前接受的答案是过时的。

The variant of the function crosstab(text, integer)is outdated. The second integerparameter is ignored. I quote the currentmanual:
crosstab(text sql, int N)...
Obsolete version of crosstab(text). The parameter Nis 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 BYis required in the one-parameter form of crosstab(). The manual:
In practice the SQL query should always specify ORDER BY 1,2to ensure that the input rows are properly ordered

该函数的变体crosstab(text, integer)已过时。第二个integer参数被忽略。我引用当前的手册：
crosstab(text sql, int N)...
的过时版本crosstab(text)。该参数N现在被忽略，因为值列的数量总是由调用查询决定
不必要的铸造和重命名。
如果一行不具有所有属性，则失败。请参阅上面带有两个输入参数的安全变体，以正确处理缺失的属性。
ORDER BY在的单参数形式中是必需的crosstab()。手册：
在实践中，SQL 查询应始终指定ORDER BY 1,2以确保输入行正确排序

Answer 2

回答by Jeremiah Peschka

You can use the crosstab()function of the additional module tablefunc- which you have to install onceper database. Since PostgreSQL 9.1 you can use CREATE EXTENSIONfor that:

您可以使用附加模块 tablefunc的crosstab()功能- 您必须为每个数据库安装一次。从 PostgreSQL 9.1 开始，您可以使用：CREATE EXTENSION

CREATE EXTENSION tablefunc;

In your case, I believe it would look something like this:

在你的情况下，我相信它看起来像这样：

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

Answer 3

回答by araqnid

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

Answer 4

回答by Milos

Solution with JSON aggregation:

使用 JSON 聚合的解决方案：

CREATE TEMP TABLE t (
  section   text
, status    text
, ct        integer  -- don't use "count" as column name.
);

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


SELECT section,
       (obj ->> 'Active')::int AS active,
       (obj ->> 'Inactive')::int AS inactive
FROM (SELECT section, json_object_agg(status,ct) AS obj
      FROM t
      GROUP BY section
     )X

Answer 5

回答by LanceH

Sorry this isn't complete because I can't test it here, but it may get you off in the right direction. I'm translating from something I use that makes a similar query:

抱歉，这不完整，因为我无法在此处进行测试，但它可能会让您朝着正确的方向前进。我正在翻译我使用的类似查询的东西：

select mt.section, mt1.count as Active, mt2.count as Inactive
from mytable mt
left join (select section, count from mytable where status='Active')mt1
on mt.section = mt1.section
left join (select section, count from mytable where status='Inactive')mt2
on mt.section = mt2.section
group by mt.section,
         mt1.count,
         mt2.count
order by mt.section asc;

The code I'm working from is:

我正在使用的代码是：

select m.typeID, m1.highBid, m2.lowAsk, m1.highBid - m2.lowAsk as diff, 100*(m1.highBid - m2.lowAsk)/m2.lowAsk as diffPercent
from mktTrades m
   left join (select typeID,MAX(price) as highBid from mktTrades where bid=1 group by typeID)m1
   on m.typeID = m1.typeID
   left join (select typeID,MIN(price) as lowAsk  from mktTrades where bid=0 group by typeID)m2
   on m1.typeID = m2.typeID
group by m.typeID, 
         m1.highBid, 
         m2.lowAsk
order by diffPercent desc;

which will return a typeID, the highest price bid and the lowest price asked and the difference between the two (a positive difference would mean something could be bought for less than it can be sold).

这将返回一个类型 ID、最高出价和最低出价以及两者之间的差异（正差异意味着可以以低于可出售的价格购买某物）。

Answer 6

回答by Lekshmi Kurup

Crosstabfunction is available under the tablefuncextension. You'll have to create this extension one time for the database.

Crosstab功能在tablefunc扩展下可用。您必须为数据库创建一次此扩展。

CREATE EXTENSION tablefunc;

创建扩展tablefunc；

You can use the below code to create pivot table using cross tab:

您可以使用以下代码使用交叉表创建数据透视表：

create table test_Crosstab( section text,
<br/>status text,
<br/>count numeric)

<br/>insert into test_Crosstab values ( 'A','Active',1)
                <br/>,( 'A','Inactive',2)
                <br/>,( 'B','Active',4)
                <br/>,( 'B','Inactive',5)

select * from crosstab(
<br/>'select section
    <br/>,status
    <br/>,count
    <br/>from test_crosstab'
    <br/>)as ctab ("Section" text,"Active" numeric,"Inactive" numeric)

SQL PostgreSQL 交叉表查询

提问by schone

回答by Erwin Brandstetter

Improved test case

改进的测试用例

Simple form - not fit for missing attributes

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

Safe form

安全形式

Advanced examples

高级示例

`\crosstabview`in psql

`\crosstabview`在 psql 中

回答by Jeremiah Peschka

回答by araqnid

回答by Milos

回答by LanceH

回答by Lekshmi Kurup

相关推荐

最近更新

标签

SQL PostgreSQL 交叉表查询

提问by schone

回答by Erwin Brandstetter

Improved test case

改进的测试用例

Simple form - not fit for missing attributes

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

Safe form

安全形式

Advanced examples

高级示例

\crosstabviewin psql

\crosstabview在 psql 中

回答by Jeremiah Peschka

回答by araqnid

回答by Milos

回答by LanceH

回答by Lekshmi Kurup

相关推荐

T-SQL - 左外连接 - where 子句与 on 子句中的过滤器

SQL 如何比较 sqlite TIMESTAMP 值

SQL 链接服务器 SQLNCLI 问题。“没有交易是活跃的”

从 SQL Server 中的十进制中删除尾随零

相关推荐

最近更新

标签

`\crosstabview`in psql

`\crosstabview`在 psql 中