时间戳

首页 > 解决方案 > 用于选择组中最佳/第一个元素的 Postgres 聚合函数

postgresql - 用于选择组中最佳/第一个元素的 Postgres 聚合函数

问题描述

我有一个元素表，其中包含许多需要根据优先级进行重复数据删除的项目。以下是一个非常简化但具有代表性的示例：

sophia=> select * from numbers order by value, priority;
 value | priority | label 
-------+----------+-------
     1 |        1 | One
     1 |        2 | Eins
     2 |        1 | Two
     2 |        2 | Zwei
     3 |        2 | Drei
     4 |        1 | Four
     4 |        2 | Vier
(7 rows)

我想将此限制为每个数字仅返回一行。很简单，我可以使用https://wiki.postgresql.org/wiki/First/last_(aggregate)中详述的 first() 聚合函数

sophia=> select value, first(label) from numbers group by value order by value;
 value | first 
-------+-------
     1 | One
     2 | Two
     3 | Drei
     4 | Four
(4 rows)

sophia=>

这样做的问题是顺序没有很好地定义，所以如果数据库行以不同的顺序插入，我可能会得到这个：

sophia=> select value, first(label) from numbers group by value order by value;
 value | first 
-------+-------
     1 | Eins
     2 | Zwei
     3 | Drei
     4 | Vier
(4 rows)

当然，解决方案似乎也很简单，因为我可以通过以下方式下订单：

sophia=> select value, first(label) from (select * from numbers order by priority) foo group by value order by value;
 value | first 
-------+-------
     1 | One
     2 | Two
     3 | Drei
     4 | Four
(4 rows)

sophia=>

然而，这里的问题是查询优化器可以自由地丢弃子查询中的规则顺序，这意味着这并不总是有效并且会在随机的讨厌的地方中断。

我有一个解决方案，我目前在少数几个地方使用依赖于 array_agg 的地方。

sophia=> select value, (array_agg(label order by priority))[1] as best_label from numbers group by value;
 value | best_label 
-------+------------
     1 | One
     2 | Two
     3 | Drei
     4 | Four
(4 rows)

sophia=>

这提供了强大的排序，但涉及在查询期间创建一堆额外的数组，这些数组会被丢弃，因此在较大数据集上的性能相当糟糕。

所以问题是，有没有更好、更清洁、更快的方法来处理这个问题？

标签： postgresqlaggregate-functions

解决方案

您的最后一次尝试包括您的问题的答案，您只是没有意识到：

array_agg(label order by priority)

注意聚合函数中order by的子句。这不是特殊的，而是使用聚合函数的语法的一般部分：array_agg

通常，输入行以未指定的顺序馈送到聚合函数。在许多情况下，这无关紧要。例如，无论 min 以何种顺序接收输入，都会产生相同的结果。但是，某些聚合函数（例如 array_agg 和 string_agg）产生的结果取决于输入行的顺序。当使用这样的聚合时，可选的 order_by_clause 可用于指定所需的排序。order_by_clause 与查询级别的 ORDER BY 子句具有相同的语法，如第 7.5 节所述，除了它的表达式始终只是表达式并且不能是输出列名称或数字。

因此，您的问题的解决方案只是order by在您的first聚合表达式中放置一个：

select value, first(label order by priority) from numbers group by value order by value;

鉴于这是多么优雅，我很惊讶first并且last仍然没有作为内置聚合实现。

推荐阅读