安装附加模块tablefunc once每个数据库,提供以下功能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)
with 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 | -- !!
- 无需铸造和重命名。
- 请注意不正确结果为
C
: 价值7
已填写第一列。有时,这种行为是可取的,但不适合此用例。
- 简单形式也仅限于exactly提供的输入查询中的三列:row_name, category, value。没有空间额外的列就像下面的 2 参数替代方案一样。
安全形式
crosstab(text, text)
with 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 -- !!
这是手册上的。
由于无论如何您都必须拼写出列定义列表中的所有列(预定义的除外)crosstabN()
变体),通常在一个简短的列表中提供一个更有效的方法VALUES
表达式如所示:
$$VALUES ('Active'::text), ('Inactive')$$)
或者(手册中没有):
$$SELECT unnest('{Active,Inactive}'::text[])$$ -- short syntax for long lists
-
I used 美元报价使引用更容易。
-
您甚至可以使用以下方式输出列不同的数据类型 with crosstab(text, text)
- 只要值列的文本表示形式是目标类型的有效输入。这样你可能会有不同种类和输出的属性text
, date
, numeric
等等各自的属性。文末有代码示例chapter crosstab(text, text)在手册中.
数据库小提琴here
过多输入行的影响
多余的输入行的处理方式不同 - 相同(“row_name”,“category”)组合的重复行 -(section, status)
在上面的例子中。
The 1-参数表单从左到右填写可用值列。多余的值将被丢弃。
较早输入的行获胜。
The 2参数form 将每个输入值分配给其专用列,覆盖任何先前的分配。
后来输入的行获胜。
通常,一开始就没有重复项。但如果您这样做,请根据您的要求仔细调整排序顺序 - 并记录发生的情况。
或者如果您不在乎的话,可以快速获得任意结果。只要注意一下效果就可以了。
高级示例
\crosstabview
in psql
Postgres9.6将此元命令添加到其默认交互式终端psql。您可以运行首先使用的查询crosstab()
参数并将其馈送到\crosstabview
(立即或在下一步中)。喜欢:
db=> SELECT section, status, ct FROM tbl \crosstabview
与上面的结果类似,但它是客户端的表示功能只。输入行的处理方式略有不同,因此ORDER BY
不需要。详细信息\crosstabview在手册中。该页面底部有更多代码示例。
Daniel Vérité(psql 功能的作者)在 dba.SE 上的相关回答: