sql - 聚合函数上的 Postgres "group by"

Question

我有一个返回 ID 数组的查询，然后我使用自定义函数对其进行排序，如下所示:-

SELECT array_sort(my_array) as sort FROM table

这将返回:-

{19,21,24,48}
{19,21,24}
{19,21}
{19}
{16,12,13}
{16,12}
...

我想选择第一个元素不同的最长数组，所以从上面的列表中我会得到:-

{19,21,24,48} and {16,12,13}

我怎样才能做到这一点，我试着把第一个元素作为一个单独的项目拉出来，按长度排序并尝试按如下方式分组:-

SELECT DISTINCT (array_sort(path))[1] as first, array_length(path,1) as plen, array_sort(path) as members FROM table GROUP BY first,plen,members ORDER BY plen DESC

这不起作用，只是对列表进行排序

Answer 1

使用distinct on子句:

with cte as (
  select
      array_length(members,1) as plen,
      members[1] as first,
      members
  from (select array_sort(path) as members from table) as a
)
select distinct on (first)
    members
from cte
order by first, plen desc

sql fiddle example