postgresql - 按结果获取一组所有模式的数组聚合

Question

我有一堆 ~600k 行，比如说主人的名字 (varchar) 和宠物类型(也是 varchar)。对于每个主人的名字，我想要一个数组，其中包含他们拥有的最频繁的宠物(如果他们拥有相同数量的相同宠物类型，则为宠物)。

一个例子:

*owner, pet type*
alice, cat
alice, dog
bob, fish
bob, cat
bob, fish
eve, cat
eve, dog
eve, cat
eve, dog

预期输出:

alice, [cat, dog]
bob, [fish]
eve, [cat, dog]

我的感觉是，这是内部查询中的“distinct on”与外部查询中的 array_agg 的某种组合，用于执行数组聚合 - 但我就是做对了。

Answer 1

您可以通过结合窗口函数和分组来做到这一点:

select owner, array_agg(pet order by pet)
from (
  select owner, pet, dense_rank() over (partition by owner order by count(*) desc) as rnk
  from pet
  group by owner, pet
) t
where rnk = 1
group by owner
order by owner;

在线示例:http://rextester.com/MTFIQ24341