sql - 在 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

我查看了 array functions and operators 的文档但在那个方向找不到任何东西。

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

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