如何在AWS Athena中使用LISTAGG？

Question

我希望在Amazon Athena中使用LISTAGG进行查询。有没有将数据聚合成列表或字符串的方法？

根据Amazon Athena用户指南，grouping_expressions元素可以是任何函数（如SUM、AVG、COUNT等）。

- Shin Kim

Answer 1

选项1：数组

with t(i) as (select 1 union all select 2 union all select 3) 
select  array_agg(i) as result 
from    t
;

  result
-----------
 [3, 2, 1]

选项2：字符串

with t(i) as (select 1 union all select 2 union all select 3) 
select  array_join(array_agg(i),',') as result 
from    t
;

 result
--------
 1,3,2

- David דודו Markovitz

Answer 2

尝试这个link。

语法。

LISTAGG( [DISTINCT] aggregate_expression [, 'delimiter' ] ) 
[ WITHIN GROUP (ORDER BY order_list) ]   

例子

SELECT LISTAGG(sellerid, ', ') 
WITHIN GROUP (ORDER BY sellerid) 
FROM sales WHERE eventid = 4337;

- Iyad Bacdounes