有人知道如何在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
这可能吗?
当前回答
您可以使用附加模块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;你会得到这个错误:https://dbfiddle.uk/j8W1CMvI 错误:函数交叉表(未知，整数)不存在 第4行:FROM crosstab('select section::text, status, count::text fro… ＾ 提示:没有函数匹配给定的名称和参数类型。您可能需要添加显式类型强制转换。
其他回答
我设计了一种不同的动态方法，它采用了动态rec. type(一个临时表，通过匿名过程构建)& JSON。这对于不能安装tablefunc/crosstab扩展，但仍然可以创建临时表或运行anon. proc's的最终用户可能很有用。
这个例子假设所有的xtab列都是相同的类型(INTEGER)，但是列的#是数据驱动和可变的。也就是说，JSON聚合函数允许混合数据类型，因此通过使用嵌入式复合(混合)类型有创新的潜力。
如果您想在JSON记录集函数中静态地定义rec.类型(通过发出复合类型的嵌套select)，那么它的真正内容可以减少到一个步骤。
dbfiddle.uk
https://dbfiddle.uk/N1EzugHk
抱歉，这不是完整的，因为我不能在这里测试它，但它可能会让你在正确的方向。我翻译了一些类似的问题:
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;你会得到这个错误:https://dbfiddle.uk/j8W1CMvI 错误:函数交叉表(未知，整数)不存在 第4行:FROM crosstab('select section::text, status, count::text fro… ＾ 提示:没有函数匹配给定的名称和参数类型。您可能需要添加显式类型强制转换。
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
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
