将聚合函数与数组结合使用
-
要在数组中添加值,请使用
SUM
,如以下示例所示。 -
要在数组中聚合多个行,请使用
array_agg
。有关信息,请参阅从子查询创建数组。
注意
从 Athena 引擎版本 2 开始的聚合函数支持 ORDER BY
。
WITH dataset AS ( SELECT ARRAY [ ARRAY[1,2,3,4], ARRAY[5,6,7,8], ARRAY[9,0] ] AS items ), item AS ( SELECT i AS array_items FROM dataset, UNNEST(items) AS t(i) ) SELECT array_items, sum(val) AS total FROM item, UNNEST(array_items) AS t(val) GROUP BY array_items;
在最后一个 SELECT
语句中,您可以使用 reduce()
而不是 sum()
和 UNNEST
来减少处理时间和数据传输,如以下示例所示。
WITH dataset AS ( SELECT ARRAY [ ARRAY[1,2,3,4], ARRAY[5,6,7,8], ARRAY[9,0] ] AS items ), item AS ( SELECT i AS array_items FROM dataset, UNNEST(items) AS t(i) ) SELECT array_items, reduce(array_items, 0 , (s, x) -> s + x, s -> s) AS total FROM item;
任一查询返回以下结果。不保证返回结果的顺序。
+----------------------+
| array_items | total |
+----------------------+
| [1, 2, 3, 4] | 10 |
| [5, 6, 7, 8] | 26 |
| [9, 0] | 9 |
+----------------------+