Redshift - 将多列转换为行（反转）

Question

在Redshift中：

我有一个包含30个维度字段和150多个度量字段的表格。
为了在可视化工具(Tableau)中充分利用这些数据，我需要将度量列转换为一个度量和一个维度进行分类。

简短示例：

   Date         Country    Order     Banana  Apple  Orange  Kiwi Lemon

    1-10-2018    Belgium    XYZ789    14       0     10      16    7
    1-10-2018    Germany    ABC123    10      15      3      15    3
    2-10-2018    Belgium    KLM456     9       9      7       1    7

结果：

   Date         Country    Order     Measure_Name   Measure_Value
    1-10-2018    Belgium    XYZ789    Banana         14
    1-10-2018    Belgium    XYZ789    Apple           0
    1-10-2018    Belgium    XYZ789    Orange         10
    1-10-2018    Belgium    XYZ789    Kiwi           16
    1-10-2018    Belgium    XYZ789    Lemon           7
    1-10-2018    Germany    ABC123    Banana         10
    1-10-2018    Germany    ABC123    Apple          15
    1-10-2018    Germany    ABC123    Orange          3
    1-10-2018    Germany    ABC123    Kiwi           15
    1-10-2018    Germany    ABC123    Lemon           3
    2-10-2018    Belgium    KLM456    Banana          9
    2-10-2018    Belgium    KLM456    Apple           9
    2-10-2018    Belgium    KLM456    Orange          7
    2-10-2018    Belgium    KLM456    Kiwi            1
    2-10-2018    Belgium    KLM456    Lemon           7

我知道并尝试了“UNION ALL”解决方案，但我的表有数百万行，并且要展开的列超过150个，对于这个解决方案来说太大了。（即使SQL代码超过8k行）

你有什么想法可以帮助我吗？

非常感谢。

- Thomas Olivier

---

你能回顾一下那张表格的来源并在那个阶段更改转换吗？ - Jon Scott

可能是一个重复的问题：将带有UNPIVOT的sql代码改写成Redshift的代码。 - Nathan Griffiths

Answer 1

当以'命令式'方式编写此代码时，您可能希望使用像flatMap（或其它等效的编程语言）之类的东西将一个生成更多行。要在SQL中生成行，必须使用JOIN。

这个问题可以通过将表与另一个具有与unpivot相同数量的列的表进行(CROSS)JOIN。您需要添加一些条件魔法，然后就完成了。

CREATE TABLE t (
  "Date" date, 
  "Country" varchar, 
  "Order" varchar, 
  "Banana" varchar, 
  "Apple" varchar, 
  "Orange" varchar, 
  "Kiwi" varchar, 
  "Lemon" varchar
);

INSERT INTO t VALUES ('1-10-2018', 'Belgium', 'XYZ789', '14', '0', '10', '16', '7');
INSERT INTO t VALUES ('1-10-2018', 'Germany', 'ABC123', '10', '15', '3', '15', '3');
INSERT INTO t VALUES ('2-10-2018', 'Belgium', 'KLM456', '9', '9', '7', '1', '7');

WITH 
    cols as (
      select 'Banana' as c
      union all 
      select 'Apple' as c
      union all 
      select 'Orange' as c
      union all 
      select 'Kiwi' as c
      union all 
      select 'Lemon' as c
      )
select 
    "Date", 
    "Country", 
    "Order",
    c "Fruit Type",
    CASE c 
        WHEN 'Banana' THEN "Banana" 
        WHEN 'Apple' THEN "Apple"
        WHEN 'Orange' THEN "Orange"
        WHEN 'Kiwi' THEN "Kiwi"
        WHEN 'Lemon' THEN "Lemon"
        ELSE NULL
    END as "Amount Ordered"

from t cross join cols;

https://www.db-fiddle.com/f/kojuPAjpS5twCKXSPVqYyP/3

- botchniaque

Answer 2

考虑到您需要转置150列的情况，我认为使用SQL不可行。我曾经遇到过几乎完全相同的情况，并使用Python解决了它。伪代码和说明在这个问题中。

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

- demircioglu