Postgres:在int数组中寻找最大值?

问题描述

使用Postgres 9.3 ...

有人可以解释一下为什么我不能直接在未嵌套的数组上使用max函数吗??

Can someone please explain why I can't use a max function directly on an unnested array..?

据我了解，unnest函数会像选择语句一样返回"setof".那么，为什么此查询的简短版本不起作用?(我是在概念上缺少某些东西还是我的问题与语法有关?)

It is my understanding the unnest function returns a "setof" just like the select statement. So why does the short version of this query not work? (Am I missing something conceptually or is my issue a syntax related one?)

table: foo_history:

id | history::smallint
-----------------------------------
1  |  {10,20,30,50,40}

这行不通吗?

Select id, max(unnest(history)) as vMax from foo_history;

...但是这个有...?

...but this one does...?

WITH foo as (
    select id, unnest(history) as history
    from foo_history
)
Select
    id, max(history) as vMax
From foo
Group by id;

推荐答案

如果安装了 intarray 模块，它会提供一些额外的数组运算符，尽管有些效率低下，但它们可以让您编写所需的内容:

If you install the intarray module it provides some extra array operators that'll let you write what you want, albeit somewhat inefficiently:

CREATE EXTENSION intarray;

SELECT id, (sort_desc(history))[1] as vMax
FROM foo_history;

编写将数组添加到 intarray 的最大和最小函数非常容易，代码非常简单.

It would be pretty easy to write greatest and least functions for arrays to add to intarray, the code is pretty simple.

否则，您可以编写一个SQL函数:

Otherwise you can just write an SQL function:

CREATE OR REPLACE FUNCTION array_greatest(anyarray)
RETURNS anyelement
LANGUAGE SQL
AS $$
  SELECT max(elements) FROM unnest($1) elements
$$;

并使用它:

SELECT id, array_greatest(history) as vMax
FROM foo_history;

这篇关于Postgres:在int数组中寻找最大值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！