PostgreSQL交叉表，带有三列，其中值从一列中汇总。

Question

我是一名SQL新手，正在尝试在Postgres中进行交叉表。我本来会用Excel做这个，但我的数据库有大约350万行数据，20,000个不同的代码值，7个不同的分类，并且变量值从1到100不等。有些代码可能只属于其中几个分类。

由于Excel无法处理如此多的行数据，所以我选择使用SQL。

我的数据格式如下：

code   |  cat        |   value |
--------------------------------
abc123 |   1         |    4    |
abc234 |   2         |    6    |
abc345 |   1         |    1    |
abc123 |   3         |    2    |
abc123 |   6         |    12   |

在一个Postgres表中，存储了以代码和分类为文本，以整数为值的数据。

我想对代码和分类执行交叉表，以显示值的总和。我希望返回结果中显示为零而不是“null”，但如果使用“null”更简单，则也可以。

因此，我想要的输出结果是：

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    |

我在Postgres的帮助文件和其他论坛上搜索过了；最接近的是SO问题PostgreSQL Crosstab Query，但我无法弄清如何对第三列的值求和。

如有任何帮助，将不胜感激。

- Siraj

---

你目前有什么进展？;) - plaes

只是沮丧..尝试了许多网站上的许多示例。即使进行简单的交叉表，我也会得到42601错误..类型不匹配。 - Siraj

你尝试过什么了吗？ - plaes

Answer 1

我通过更新代码来使其正常工作，具体如下：

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)

我能够通过在交叉表内运行select语句并将我的ct数据类型与交叉表内查询返回的数据类型匹配来确定正确的数据类型。

- Kristen Hazard

---

我注意到列名cat_XX可能是随机的，postgres无法匹配它们... :( - Chris

@Chris 如果你需要排序，可以尝试这个 - SELECT * FROM crosstab(' your query ' ,$$VALUES ('cat_xx'::text), ('cat_xx2'::text) $$) - Srinivas Rathikrindi

Answer 2

尝试：

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)

- user359040

---

谢谢。我尝试过了，但是出现了这个错误.... 错误：无效的返回类型 详情：SQL rowid数据类型与返回rowid数据类型不匹配。********** 错误 **********错误：无效的返回类型 SQL状态：42601 详情：SQL rowid数据类型与返回rowid数据类型不匹配。 - Siraj

尝试使用count（value）进行尝试，使用文本而不是int在“cat_0”之后等仍然以相同的错误结束：sql state：42601 - Siraj