Athena中聚合列中的项目数

Question

我有一些列如下所列：

水果Sean DoeSean Doe

Answer 1

您的查询包含一个额外的分组-按Fruits。删除它：

-- sample data
WITH dataset(FirstName, Fruits, Date) AS (
VALUES ('Sean Doe'  , 'Oranges' ,'2022-06-03'),
('Jon Doe'  , 'Apples'  ,'2022-06-02'),
('Jon Doe'  , 'Mangoes' ,'2022-06-02'),
('Sean Doe' , 'Peaches' ,'2022-06-02'),
('Jon Doe'  , 'Strawberries'    ,'2022-06-02'),
('Sean Doe' , 'Blueberries' ,'2022-06-02'),
('Jon Doe'  , 'Blueberries' ,'2022-06-01')
)
-- query
Select
FirstName,
Date,
array_join(array_agg(Fruits), ',') AS aggregated_fruits , 
count(Fruits) AS count_of_fruits_on_one_date 
FROM dataset
GROUP BY FirstName, Date 
ORDER BY count(Fruits) desc

输出：

aggregated_fruitscount_of_ffruits_on_one_Date苹果、芒果、草莓21Jon Doe

FirstName	日期
Jon Doe	2022-06-02	3
Sean Doe	2022-06-02	桃子、蓝莓
Sean Doe	2022-06-03	橙子