sql - Postgres对汇总函数进行“分组依据”

我有一个查询，返回一个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

这不起作用，只是命令列表

最佳答案

使用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

关于sql - Postgres对汇总函数进行“分组依据”，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/20422090/

AS

sql - Postgres对汇总函数进行“分组依据”