通过聚合函数中其他列（的第一个值）对不同的列值进行排序 [英] Ordering distinct column values by (first value of) other column in aggregate function

问题描述

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

I'm trying to order the output order of some distinct aggregated text based on the value of another column with something like:

string_agg(DISTINCT sometext, ' ' ORDER BY numval)

但是，这会导致错误：

However, that results in the error:

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

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

我确实理解为什么会这样，因为如果两个重复值的 numval 不同，则排序将是不确定的，

I do understand why this is, since the ordering would be "ill-defined" if the numval of two repeated values differs, with that of another lying in-between.

理想情况下，我想按首次出现/最低的按值排序，但实际上定义不明确的情况是在我的数据中足够稀有（我想用 DISTINCT 消除的主要是顺序重复的值），我最终并不特别在意它们的顺序，并且会很高兴与类似MySQL的 GROUP_CONCAT （DISTINCT sometext ORDER BY numval SEPARATOR''）尽管它很草率，但仍然可以正常工作。

Ideally, I would like to order them by first appearance / lowest order-by value, but the ill-defined cases are actually rare enough in my data (it's mostly sequentially repeated values that I want to get rid of with the DISTINCT) that I ultimately don't particularly care about their ordering and would be happy with something like MySQL's GROUP_CONCAT(DISTINCT sometext ORDER BY numval SEPARATOR ' ') that simply works despite its sloppiness.

我希望有些Postgres柔和主义是必要的，但是我

I expect some Postgres contortionism will be necessary, but I don't really know what the most efficient/concise way of going about this would be.

推荐答案

如果这是解决方案的一部分，真的不知道这样做的最有效/简洁的方法是什么。如果表达式较大，则在子查询中执行选择非重复可能很不方便。在这种情况下，您可以利用 string_agg（）忽略 NULL 输入值的事实，并执行以下操作：

If this is part of a larger expression, it might be inconvenient to do a select distinct in a subquery. In this case, you can take advantage of the fact that string_agg() ignores NULL input values and do something like:

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>

子查询添加一列，但不需要汇总数据。

The subquery adds a column but does not require aggregating the data.

这篇关于通过聚合函数中其他列（的第一个值）对不同的列值进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！