sql - 通过聚合函数中其他列的(第一个值)对不同的列值进行排序

Question

我正在尝试根据另一列的值对一些不同的聚合文本的输出顺序进行排序，例如:

string_agg(DISTINCT sometext, ' ' ORDER BY numval)

但是，这会导致错误:

ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list

我明白为什么会这样，因为如果两个重复值的 numval 不同，而另一个值介于两者之间，那么排序将是“不明确的”。

理想情况下，我想按首次出现/最低排序值对它们进行排序，但实际上定义不明确的情况在我的数据中很少见(它主要是顺序重复的值，我想用 DISTINCT)，我最终并不特别关心它们的顺序，并且会对像 MySQL 的 GROUP_CONCAT(DISTINCT sometext ORDER BY numval SEPARATOR ' ') 这样的东西感到满意，尽管它草率。

我预计一些 Postgres 的扭曲是必要的，但我真的不知道最有效/最简洁的方法是什么。

Answer 1

基于DISTINCT ON

SELECT string_agg(sometext, ' ' ORDER BY numval) AS no_dupe
FROM  (
    SELECT DISTINCT ON (1,2) <whatever>, sometext, numval
    FROM   tbl
    ORDER  BY 1,2,3
    ) sub;

这是 @Gordon's query 的更简单等价物.
仅根据您的描述，我会建议 @Clodoaldo's simpler variant .

uniq() 用于整数

对于 integer 值而不是 text，附加模块 intarray有适合您的东西:

uniq(int[])     int[]   remove adjacent duplicates

每个数据库安装一次:

CREATE EXTENSION intarray;

那么查询就是:

SELECT uniq(array_agg(some_int ORDER BY <whatever>, numval)) AS no_dupe
FROM   tbl;

结果是一个数组，用array_to_string()包裹起来如果你需要一个字符串。相关:

• How to create an index for elements of an array in PostgreSQL?
• Compare arrays for equality, ignoring order of elements

事实上，创建一个自定义聚合函数来对 text 做同样的事情并不难......

任何数据类型的自定义聚合函数

如果下一个元素与前一个不同，则只将下一个元素添加到数组中。 (NULL 值已删除!):

CREATE OR REPLACE FUNCTION f_array_append_uniq (anyarray, anyelement)
  RETURNS anyarray
  LANGUAGE sql STRICT IMMUTABLE AS
'SELECT CASE WHEN $1[array_upper($1, 1)] = $2 THEN $1 ELSE $1 || $2 END';

使用 polymorphic types使其适用于任何 标量数据类型。自定义聚合函数:

CREATE AGGREGATE array_agg_uniq(anyelement) (
   SFUNC = f_array_append_uniq
 , STYPE = anyarray
 , INITCOND = '{}'
);

调用:

SELECT array_to_string(
          array_agg_uniq(sometext ORDER BY <whatever>, numval)
        , ' ') AS no_dupe
FROM   tbl;

请注意，聚合本质上是PARALLEL UNSAFE(默认)，即使转换函数可以标记为PARALLEL SAFE。

相关回答:

• Custom PostgreSQL aggregate for circular average