红移.我们如何才能将表从列自动换行(动态)? [英] Redshift. How can we transpose (dynamically) a table from columns to rows?

问题描述

我们如何将Redshift表从列转换为行?

How can we transpose a Redshift table from columns to rows?

例如，如果我们有一个通用的(未知的)表，如下所示:

For example, if we have a generic (not already known) table like the following:

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
...

我们如何获得以下结果?

How we can achieve the following result?

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)都是动态的(因此，我们正在寻找一种解决方案，即每列都不存在case when映射因为我们想将此应用到几个不同的表).

Where the target table, the number of columns (alfa, beta, gamma, ..., omega) are all dynamic (so We're looking for a solution that no case when mapping for each column is needed, since We'd like to apply this to several different tables).

但是我们将在所有目标表中(以及最后在所有表中都有主键或候选键)中具有date和id字段.

But we will have and date and id fields in all target tables (or at last a primary key or a candidate key in all tables).

我们的Redshift版本是:

Our Redshift version is:

PostgreSQL 8.0.2, Redshift 1.0.3380

我们该怎么做?

推荐答案

您需要将列名称硬编码到查询中.

You would need to hard-code the column names into the query.

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

这篇关于红移.我们如何才能将表从列自动换行(动态)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！