PostgreSQL 交叉表查询

Question

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

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

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

Section    Active    Inactive
A          1         2
B          4         5

Answer 1

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

创建扩展tablefunc；

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

create table test_Crosstab( section text,
status text,
count numeric)

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

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

Answer 2

安装附加模块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。有时，这种行为是可取的，但不适合此用例。
• 简单形式还限制为所提供的输入查询中的恰好三列：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);

crosstab(text, text)：返回：

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

• 注意 C.

  


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

   '从 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 此处

过多输入行的影响

多余的输入行的处理方式不同 - 相同（“row_name”，“category”）组合的重复行 - 上例中的 (section, status)。

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

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

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

高级示例

• 使用 Tablefunc 对多个列进行透视 -还演示了提到的“额外列”

• 透视的动态替代方案CASE 和 GROUP BY

\crosstabview 在 psql

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

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

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

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

• How do I generated aivory CROSS JOIN where the结果表定义未知？

Answer 3

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

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

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 Fiddle here：

• 一切正常： https: //dbfiddle.uk/iKCW9Uhh
• 如果没有 CREATE EXTENSION tablefunc;，您会收到此错误： https://dbfiddle.uk/j8W1CMvI

  错误：函数交叉表（未知，整数）不存在
  第 4 行：FROM crosstab('选择部分::文本，状态，计数::文本来自...
               ^
  提示：没有函数与给定的名称和参数类型匹配。您可能需要添加显式类型转换。
  

Answer 5

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 6

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

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;

我正在使用的代码是：

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;

它将返回 typeID、最高出价和最低要价以及两者之间的差异（正差异意味着某物的购买价格可能低于其出售价格）。

Answer 7

我设计了一种不同的动态方法，它采用动态记录。类型（临时表，通过匿名过程构建）& JSON。这对于无法安装 tablefunc/crosstab 扩展但仍可以创建临时表或运行的最终用户可能很有用。过程的。

该示例假设所有 xtab 列都是相同类型 (INTEGER)，但列数是数据驱动的 &可变参数。也就是说，JSON 聚合函数确实允许混合数据类型，因此通过使用嵌入式复合（混合）类型有创新的潜力。

如果您想静态定义rec，则其真正的内容可以减少到一步。 JSON 记录集函数内的类型（通过发出复合类型的嵌套 SELECT）。

dbfiddle.uk

https://dbfiddle.uk/N1EzugHk