在PostgreSQL中对array_agg进行子选择 [英] Subselect on array_agg in postgresql

问题描述

在Postgresql 9.2+中的hading子句中是否可以使用聚合函数中的值？

Is there a way to use a value from an aggregate function in a having clause in Postgresql 9.2+?

例如，我想获取每个 monkey_id 的第二高数字> 123，第二高的数字。在下面的示例中，我想获取（monkey_id 1，数字222）。

For example, I would like to get each monkey_id with a 2nd highest number > 123, as well as the second highest number. In the example below, I'd like to get (monkey_id 1, number 222).

monkey_id | number
------------------
1         | 222
1         | 333
2         | 0
2         | 444

SELECT 
  monkey_id, 
  (array_agg(number ORDER BY number desc))[2] as second 
FROM monkey_numbers 
GROUP BY monkey_id
HAVING second > 123

我得到列 second不存在。

推荐答案

您必须将其放置在Haveing子句中

You will have to place that in the having clause

SELECT 
  monkey_id
FROM monkey_numbers 
GROUP BY monkey_id
HAVING array_agg(number ORDER BY number desc)[2] > 123

原因是 在 select 之前执行，因此 second 当时还不存在。

The explanation is that the having will be executed before the select so second still doesn't exist at that time.

这篇关于在PostgreSQL中对array_agg进行子选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！