按聚合函数中其他列的(第一个值)对不同列的值排序

Question

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

string_agg(DISTINCT sometext, ' ' ORDER BY numval)

但是，这会导致错误:

错误:在有DISTINCT的聚合中，ORDER BY表达式必须出现在参数列表

我确实理解为什么会这样，因为如果两个重复值的numval不同，而另一个位于两者之间，那么排序将是"不明确的"。

理想情况下，我想按第一次出现/最低顺序按值排序，但在我的数据中，定义不清的情况实际上是非常罕见的(它大多是顺序重复的值，我想用DISTINCT来摆脱)，我最终并不特别关心它们的排序，并且会很高兴像MySQL的GROUP_CONCAT(DISTINCT sometext ORDER BY numval SEPARATOR ' ')这样的东西，尽管它很马马粗略，但它只是工作。

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

Answer 1

Building on 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查询的简单等效。
仅从你的描述来看，我就会建议使用@Clodoaldo的更简单的变体。

uniq() for integer

对于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()中。相关:

• 如何在PostgreSQL创建数组元素的索引?
• 比较数组是否相等，忽略元素的顺序

事实上，用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';

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

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。

相关回答:

• 自定义PostgreSQL aggregate for circular average

Answer 2

通过预聚合

消除进行区分的需要

select string_agg(sometext, ' ' order by numval)
from (
    select sometext, min(numval) as numval
    from t
    group by sometext
) s

@Gordon的回答提出了一个很好的观点。前提是还有其他需要的列。在这种情况下，建议使用distinct on

select x, string_agg(sometext, ' ' order by numval)
from (
    select distinct on (sometext) *
    from t
    order by sometext, numval
) s
group by x

Answer 3

我最终做的是完全避免使用DISTINCT，而是选择使用正则表达式替换来删除顺序重复的条目(这是我的主要目标)，如下所示:

regexp_replace(string_agg(sometext, ' ' ORDER BY numval), 
               '(yw+y)(?:s+1)+', '1', 'g')

如果外部排序导致它们之间出现另一个条目，则不会删除重复，但这对我来说有效，可能更好。它可能比其他选项慢一点，但我发现它的速度足以满足我的目的。

Answer 4

如果这是较大表达式的一部分，则可能不方便在子查询中执行select distinct。在这种情况下，您可以利用string_agg()忽略NULL输入值的事实，并执行如下操作:

select string_agg( (case when seqnum = 1 then sometext end) order by numval)
from (select sometext, row_number() over (partition by <whatever>, sometext order by numval) as seqnum
      from t
     ) t
group by <whatever>

子查询添加一个列，但不需要聚合数据。