在RedShift中将值拆分成多行

Question

如何将一个字段（例如CSV字符串）拆分为多行的问题已经有了答案：Split values over multiple rows。但是，该问题涉及MSSQL，而回答中使用了各种RedShift没有对应功能的特性。为了完整起见，这里举个例子：当前数据：

| Key | Data     |
+-----+----------+
| 1   | 18,20,22 |
| 2   | 17,19    |

所需数据：

| Key | Data     |
+-----+----------+
| 1   | 18       |
| 1   | 20       |
| 1   | 22       |
| 2   | 17       |
| 2   | 19       |

现在，我可以提供一种解决CSV字段中小且有界元素数量的情况的方法：使用split_part并在所有可能的数组位置上进行union操作，如下所示：

SELECT Key, split_part(Data, ',', 1) 
FROM mytable
WHERE split_part(Data, ',', 1) != ""
    UNION
SELECT Key, split_part(Data, ',', 2) 
FROM mytable
WHERE split_part(Data, ',', 2) != ""
-- etc. etc.

然而，这显然非常低效，并且不适用于更长的列表。

有更好的想法吗？

编辑：

关于乘以行数的问题也有一个类似的问题：在Redshift中拆分行。然而，我不知道如何将此方法应用于此处。

编辑2：

可能是重复问题：Redshift. 将逗号分隔的值转换为行。但没有什么新的 - @Masashi Miyazaki的答案与我上面的建议类似，并且存在相同的问题。

- etov

---

请查看以下链接：https://dev59.com/gV8f5IYBdhLWcg3wB-3E#31998832 - Bob Baxley

请在此处查看此问题的可行答案。https://dev59.com/Cqbja4cB1Zd3GeqPZwNv#46785509 - Jon Scott

@JonScott，看起来不错。你能把它作为答案吗，这样我就可以解决了吗？ - etov

@etov 我已经添加了针对你问题的答案。 - Jon Scott

Answer 1

以下是Redshift的答案，它可以处理每行高达10,000个值。

设置测试数据

create table test_data (key varchar(50),data varchar(max));
insert into test_data
    values
      (1,'18,20,22'),
      (2,'17,19')
;

代码

with ten_numbers as (select 1 as num union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0)
  , generted_numbers AS
(
    SELECT (1000 * t1.num) + (100 * t2.num) + (10 * t3.num) + t4.num AS gen_num
    FROM ten_numbers AS t1
      JOIN ten_numbers AS t2 ON 1 = 1
      JOIN ten_numbers AS t3 ON 1 = 1
      JOIN ten_numbers AS t4 ON 1 = 1
)
  , splitter AS
(
    SELECT *
    FROM generted_numbers
    WHERE gen_num BETWEEN 1 AND (SELECT max(REGEXP_COUNT(data, '\\,') + 1)
                                 FROM test_data)
)
  , expanded_input AS
(
    SELECT
      key,
      split_part(data, ',', s.gen_num) AS data
    FROM test_data AS td
      JOIN splitter AS s ON 1 = 1
    WHERE split_part(data, ',', s.gen_num) <> ''
)
SELECT * FROM expanded_input
order by key,data;

- Jon Scott

---

我有一个类似的问题在这里，与分裂有关。我想知道你能不能帮我解决？链接 - AndyP

Answer 2

欢迎使用RDS PostgreSql实例并创建一个到RedShift的dblink。然后，您可以像在普通的PostgreSQL数据库上一样操作结果集，并甚至通过同一dblink将结果放回到RedShift中。

- Yuri Levinsky

---

数组类型不受Redshift支持：http://docs.aws.amazon.com/redshift/latest/dg/c_unsupported-postgresql-datatypes.html，因此这不是一个有效的答案... - etov

非常愿意，但是我得到了这个错误："Amazon Invalid operation: Specified types or functions (one per INFO message) not supported on Redshift tables.;警告： Function "string_to_array(text,text)" not supported" (query: select string_to_array(csv_field, ',') from mytable;)。 - etov

集群版本为1.0.901，支持版本升级... 您是否有关于此功能的文档（例如在RedShift的发布说明中）？ - etov

抱歉，我的错误。我提议使用的函数只能按照我提出的方式在RedShift上工作，并且不能用于所请求的表数据。唯一的方法是：获取RDS PostgreSql实例并创建到RedShift的dblink。然后，您可以像在普通的PostgreSQL DB上一样操作结果集，甚至通过相同的dblink将结果放回RedShift。 - Yuri Levinsky

感谢@Yuri。这确实是一个可行的解决方法，但它背离了使用Redshift的原始目的（例如在大量数据上进行快速查询）。在这方面，如果可能的话，问题中提出的方法可能更好。看起来我们将不得不等待亚马逊添加此功能... - etov