PostgreSQL交叉表查询

小开

抱歉，这不是完整的，因为我不能在这里测试它，但它可能会让你在正确的方向。我翻译了一些类似的问题:

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;

它将返回一个类型id，最高出价和最低要价以及两者之间的差值(正差值意味着可以以低于出售的价格购买某样东西)。

小开

你可以使用附加模块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 tablefunc;，你会得到这个错误:https://dbfiddle.uk/j8W1CMvI

ERROR:  function crosstab(unknown, integer) does not exist
LINE 4: FROM crosstab('select section::text, status, count::text fro...
^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

小开

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

小开

最佳答案

为每个数据库安装附加模块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 <强> < / >强输入参数的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的正确结果。
第二个参数可以是任何查询，每个属性返回一个行，匹配最后的列定义顺序。通常你会想从底层表中查询不同的属性，就像这样:
```
  '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 .

输入行过多的影响

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

1参数表单从左到右填充可用值列。多余的值将被丢弃。

出现形式将每个输入值赋给它的专用列，覆盖之前的任何赋值。

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

先进的例子

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

`\crosstabview`在psql

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

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

与上面的结果类似，但它是客户端的表示特性独占。输入行处理略有不同，因此不需要ORDER BY。在该页的底部有更多的代码示例。

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

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

小开

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

小开

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

创建扩展__abc0;

你可以使用下面的代码来创建数据透视表:

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)

小开

有一个不同的动态方法，我已经设计，一个采用动态rec.类型(一个临时表，通过匿名过程构建)&JSON。这对于不能安装tablefunc/crosstab扩展，但仍然可以创建临时表或运行anon. proc's的最终用户可能很有用。

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

如果您想在JSON记录集函数中静态地定义rec.类型(通过发出复合类型的嵌套select)，那么它的真正内容可以减少到一个步骤。

< a href = " https://dbfiddle。> db小提琴.uk .uk /N1EzugHk" rel="nofollow noreferrer"> db小提琴.uk .uk

< a href = " https://dbfiddle。uk/N1EzugHk" rel="nofollow noreferrer">https://dbfiddle.uk/N1EzugHk