Redshift. 如何动态地将表格从列转置为行？

Question

如何将 Redshift 表格从列转换为行？

例如，如果我们有一个通用（未知）的表格，如下所示：

source table:

date        id      alfa                beta                gamma   ...                 omega
2018-08-03  1       1                   2                   3                           4
2018-08-03  2       4                   3                   2                           1
...
2018-09-04  1       3                   1                   2                           4
...

我们如何达到以下的结果？

transposed table:

date        id      column_name     column_value
2018-08-03  1       alfa            1
2018-08-03  1       beta            2
...
2018-08-03  2       omega           1
...
2018-09-04  1       gamma           2
...

目标表中的列数（alfa，beta，gamma，...，omega）均为动态的（因此我们正在寻找一种解决方案，不需要每个列的casewhen映射，因为我们想将其应用于几个不同的表）。但是，我们将在所有目标表中都有日期和ID字段（或者至少在所有表中都有主键或候选键）。我们的Redshift版本是：PostgreSQL 8.0.2，Redshift 1.0.3380。我们该如何做到这一点？

- GarouDan

---

我曾经遇到过完全相同的问题，只不过数据源来自API，最终我创建了一个Python脚本来透视或转置数据集。 - demircioglu

嗯，也许可以创建一个Python函数来创建某种动态查询。但我不知道是否可能使用类似于“select * from pivot_function('schema_name', 'table_name', 'table_id_column_name');”的语句来获取结果。 - GarouDan

Redshift UDFs 无法访问表格数据。 - John Rotenstein

Answer 1

你需要在查询中硬编码列名。

CREATE TABLE stack(date TEXT, id BIGINT, alpha INT, beta INT, gamma INT, omega INT);

INSERT INTO STACK VALUES('2018-08-03', 1, 1, 2, 3, 4);
INSERT INTO STACK VALUES('2018-08-03', 2, 4, 3, 2, 1);
INSERT INTO STACK VALUES('2018-08-04', 1, 3, 1, 2, 4);

SELECT
  date,
  id,
  col,
  col_value
FROM
(
SELECT date, id, alpha AS col_value, 'alpha' AS col FROM stack
UNION
SELECT date, id, beta  AS col_value, 'beta'  AS col FROM stack
UNION
SELECT date, id, gamma AS col_value, 'gamma' AS col FROM stack
UNION
SELECT date, id, omega AS col_value, 'omega' AS col FROM stack
) AS data
ORDER BY date, id, col

导致的结果是:

2018-08-03  1   alpha   1
2018-08-03  1   beta    2
2018-08-03  1   gamma   3
2018-08-03  1   omega   4
2018-08-03  2   alpha   4
2018-08-03  2   beta    3
2018-08-03  2   gamma   2
2018-08-03  2   omega   1
2018-08-04  1   alpha   3
2018-08-04  1   beta    1
2018-08-04  1   gamma   2
2018-08-04  1   omega   4

- John Rotenstein

Answer 2

在Redshift中使用unpivot操作

从表名中选择日期、id、列名和col，使用unpivot操作(col for column_name in (alpha、beta、gamma、.....))

- Akash Singh

Answer 3

现在Redshift支持Pivot和Un-pivot操作，您可以使用这些操作来根据您提到的用例获取所需的输出。请查看下面的链接以了解更多详细信息。 https://docs.aws.amazon.com/redshift/latest/dg/r_FROM_clause-pivot-unpivot-examples.html

- Srikant