大数据集中的 PostgreSQL 简单交叉表(行到列和列到行)

Question

我有一张看起来像这样的表:

CELL   day1      day2      day3      day4    ......   day365
1      3,7167    0         0         0,1487  ......   0,3256
2      0         0         0,2331    0,1461  ......   1,8765
3      1,431     0,4121    0         1,4321  ......   0
...
...
...
64800

我想转置我的表格，以便我将行作为列，将列作为行。结果如下所示:

DAY    1         2         3       ...... 64800  
day1   3,7167    0         1,431   ...... ......
day2   0         0         0,4121  ...... ......
day3   0         0,2331    0       ...... ......
day4   0,1487    0,1461    1,4321  ...... ......
...
...
...
day365

我最大的问题是表格大小(365 列和 64800 行)。如何编写一个不必在输出中定义所需列的查询。有没有一种方法可以在不定义每一列的情况下创建一个表，你能告诉我我的查询应该是什么样子吗？

我的第二个问题是我什至没有让交叉表在数据子集中工作。以下查询:

SELECT * FROM crosstab(
'SELECT * FROM 1997_subset  ORDER BY 1,2')
AS test("cell" int, "day1" double precision, "day2" double precision, "day3" double precision, "day4" double precision, "day5" double precision)

给我这个错误:

ERROR:  invalid source data SQL statement
DETAIL:  The provided SQL must return 3 columns: rowid, category, and values.

非常感谢您的帮助，非常感谢!

Answer 1

您不能在 PostgreSQL 中执行此操作，因为它在输出中限制为大约 1600 列。否则，tablefunc 模块中的crosstab 函数会帮助您。

您需要使用 COPY 将数据提取到 CSV，然后使用可以转换数据的外部工具。查看 ETL 工具，或者在最坏的情况下，您可能需要编写脚本。

我刚刚检查过，Talend 至少可以进行数据透视(行/列转置、交叉表)。 Pentaho、CloverETL 等可能也可以。