在 Presto 中按数组值分组

Question

像这样的电影表：

genre               revenue
array<string>           int
---------------------------
[Drama]               10000
[Drama, Fiction]       2000
[Fiction]               300
[Comedy]               5000
[Comedy, Fiction]       500

如何按包含的流派对电影进行分组并执行聚合，例如：

genre       count_movies    sum_revenue
---------------------------------------
Drama                  2          12000
Fiction                3           2800
Comedy                 2           5500

我已经查看了数组函数和运算符的文档，但找不到这方面的任何内容。

负责此输出的查询可能如下所示：

SELECT
COUNT(*) AS count_movies,
SUM(revenue) AS sum_revenue
FROM movies
GROUP BY ARRAY_EXPLODE(genre) -- this is fake, just to illustrate the point

Answer 1

您可以使用UNNEST：

-- pseudocode
SELECT t.genre,
COUNT(*) AS count_movies,
SUM(revenue) AS sum_revenue
FROM movies
CROSS JOIN UNNEST(genre) AS t(genre)
GROUP BY t.genre