主页 / dba / 问题 / 211501
Accepted
John Frazer
Asked: 2018-07-06 21:34:26 +0800 CST

如何从任何数组中删除重复项并在 PostgreSQL 中保留顺序？

  • 772

我正在寻找一种方法来消除 PostgreSQL 数组中的重复项，同时保留其元素的顺序。我目前拥有的是以下功能：

create function array_unique( anyarray ) 
  returns anyarray immutable strict language sql as $$
  select array( select distinct unnest( $1 ) ); $$;

create function array_unique_sorted( anyarray ) 
  returns anyarray immutable strict language sql as $$
  select array( select distinct unnest( $1 ) order by 1 ); $$;

/* ### TAINT there ought to be a simpler, declarative solution */
create function array_unique_stable( text[] )
  returns text[] immutable strict parallel safe language plpgsql as $$
  declare
    R         text[] = '{}';
    ¶element  text;
  begin
    foreach ¶element in array $1 loop
      if not array[ ¶element ] && R then
        R :=  R || array[ ¶element ];
        end if;
      end loop;
    return R; end; $$;

在上面，array_unique接受任何类型的数组并返回一个删除所有重复项的副本；它们的相对顺序是任意的。 array_unique_sorted是 like array_unique，但元素是相对于彼此排序的；这有时很有用，因为具有相同一组不同元素的所有数组在被此函数标准化后将比较相等。

array_unique_stable已经做了我正在寻找的：给定一个数组（在这个例子中必须是一个text[]数组），它从左到右扫描元素；每当遇到以前看不见的元素时，它就会将该元素添加到结果中。因此，仅保留每个值的第一次出现。

但是，该实现有一些缺点：首先，似乎没有办法编写它，因此它接受了伪类型anyarray。

其次，虽然前两个函数是用 SQL 编写的，但它们可能是内联的， array_unique_stable是用 PL/pgSQL 编写的，因此不能内联。

第三，我无法在纯 SQL 中提出解决方案，这让我很困扰……

postgresql order-by

1 个回答

  1. Best Answer

    这确实可以使用纯 SQL 来完成：

    create function array_unique_stable(p_input anyarray)
  returns anyarray immutable strict parallel safe 
  language sql
as 
$$
select array_agg(t order by x)
from (
  select distinct on (t) t,x
  from unnest(p_input) with ordinality as p(t,x)
  order by t,x
) t2;
$$

    将unnest(p_input) with ordinality返回数组中元素的原始索引，然后用于将它们聚合回外部查询中。

    select array_unique_stable(array['a','x','x','b']) as text_array, 
       array_unique_stable(array[10,1,1,5,8,8]) as int_array

    返回

    text_array | int_array 
-----------+-----------
{a,x,b}    | {10,1,5,8}

    和

    • 11

相关问题