红移.我们如何(动态地)将表从列转置为行?

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映射，因为我们想将其应用于几个不同的表(。

但是我们将在所有目标表中拥有 date 和 id 字段(或者最后在所有表中有一个主键或候选键(。

我们的红移版本是：

PostgreSQL 8.0.2, Redshift 1.0.3380

我们该怎么做呢？

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

Answer 2

在红移中使用取消枢轴操作

选择日期，ID，column_name，列 从table_name unpivot (col for column_name in (alpha，beta，gamma,.....((

Answer 3

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