Presto equivalent to StringTokenizerDelim (Vertica) / Explod

Question

我正在将一个查询从Vertica迁移到Presto(0.246(。该查询包含一个Vertica函数StringTokenizerDelim，它基本上是爆炸和数组(市场列(成行。

这是表格的实物模型：

+----+---------+-------------+
| id | product |   market    |
+----+---------+-------------+
|  1 | Cabinet | AU,GB,FR    |
|  2 | Chair   | US,GB       |
|  3 | Desk    | BE,GB,FR,US |
+----+---------+-------------+

输出应为：

+----+---------+--------+
| id | product | market |
+----+---------+--------+
|  1 | Cabinet | AU     |
|  1 | Cabinet | GB     |
|  1 | Cabinet | FR     |
|  2 | Chair   | US     |
|  2 | Chair   | GB     |
|  3 | Desk    | BE     |
|  3 | Desk    | GB     |
|  3 | Desk    | FR     |
|  3 | Desk    | US     |
+----+---------+--------+

与Presto中的该函数等效的是什么(如果有？在文档中找不到(？

干杯，

Answer 1

split和UNNEST应该做到这一点：

-- sample data
WITH dataset (id, product,   market ) AS (
values (1, 'Cabinet', 'AU,GB,FR'),
(2, 'Chair', 'US,GB'),
(3, 'Desk', 'BE,GB,FR,US')
) 
--query
SELECT id, product, m as market
FROM dataset
CROSS JOIN UNNEST (split(market, ',')) as t(m)

输出：

市场AUBEGBFR[/tr>美国

id	产品
1	机柜
1	机柜	GB
1	机柜	FR
2	主席	美国
2	椅子	GB
3	桌面
3	办公桌
3	桌面
3	桌面