具有三列的 PostgreSQL 交叉表，其中的值从一列中求和
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8870515/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL cross tab with three columns with values summed from one column
提问by Siraj
I am new to SQL and was trying to do a crosstab in Postgres. I would have done it in Excel, but I have a database of around 3.5 million rows, 20,000 different values for code, 7 categories in cat, and variable values from 1 to 100. A code may only have few of the 7 categories.
我是 SQL 的新手，并试图在 Postgres 中做一个交叉表。我本来可以在 Excel 中完成的，但我有一个包含大约 350 万行、20,000 个不同代码值、cat 中有 7 个类别以及从 1 到 100 的变量值的数据库。一个代码可能只有 7 个类别中的几个。
Excel can't handle the number of rows, so SQL it is.
Excel 不能处理行数，所以它是 SQL。
My data is in the form
我的数据是在表单中
code | cat | value |
--------------------------------
abc123 | 1 | 4 |
abc234 | 2 | 6 |
abc345 | 1 | 1 |
abc123 | 3 | 2 |
abc123 | 6 | 12 |
with code and cat as text, value as integer stored in a Postgres table.
代码和 cat 作为文本，值作为整数存储在 Postgres 表中。
I would like to perform a crosstab on code and cat, with sum of value. I would like it to show zero instead of 'null' in the return, but if 'null' would be simpler query, then that would be fine.
我想对 code 和 cat 执行交叉表，并使用值的总和。我希望它在返回时显示零而不是 'null'，但如果 'null' 是更简单的查询，那么就可以了。
So the output I would like is
所以我想要的输出是
code | 'cat=0' | 'cat=1' | 'cat=2' | 'cat=3' | 'cat=4' | 'cat=5' | 'cat=6'|
abc123 | 25 | 0 | 3 | 500 | 250 | 42 | 0 |
abc234 | 0 | 100 | 0 | 10 | 5 | 0 | 25 |
abc345 | 1000 | 0 | 0 | 0 | 0 | 0 | 0 |
I have searched on Postgres help files and other forums; the closest thing was the SO question PostgreSQL Crosstab Querybut I couldn't figure out how to sum the values from third column.
我在 Postgres 帮助文件和其他论坛上搜索过；最接近的是 SO 问题PostgreSQL Crosstab Query但我无法弄清楚如何对第三列中的值求和。
Any assistance would be greatly appreciated.
任何帮助将不胜感激。
回答by Kristen Hazard
I got this working by updating my code to the following:
我通过将代码更新为以下内容来实现此目的：
select * from crosstab(
'select code, cat, sum(value) as value
from my_table
group by code, cat
order by 1,2'
) as ct(code varchar(255),
cat_0 bigint,
cat_1 bigint,
cat_2 bigint,
cat_3 bigint,
cat_4 bigint,
cat_5 bigint,
cat_6 bigint)
I was able to determine the right data type by running the select statement inside the crosstab and matching my as ct data types to those returned by the query inside the crosstab.
通过在交叉表内运行 select 语句并将我的 as ct 数据类型与交叉表内查询返回的数据类型匹配，我能够确定正确的数据类型。
回答by Siraj
Try:
尝试：
select * from crosstab(
'select code, cat, sum(value) as value
from my_table
group by code, cat
order by 1,2'
) as ct(code text,
cat_0 int,
cat_1 int,
cat_2 int,
cat_3 int,
cat_4 int,
cat_5 int,
cat_6 int)
