PostgreSQL:获取数组中指定元素的出现次数。

I need to calculate the count of occurrences of specified element in array, something like:

我需要计算数组中指定元素出现的次数，如:

elem_occurrences_count(ARRAY[a,b,c,a,a], a) = 3

elem_occurrences_count(ARRAY[a,b,c,a,a],a)= 3

elem_occurrences_count(ARRAY[a,b,c], d) = 0

elem_occurrences_count(数组(a,b,c),d)= 0

Is there any function in PostgreSQL that can be used to solve the problem? Any help is appreciated.

PostgreSQL中有什么函数可以用来解决这个问题吗?任何帮助都是感激。

4 个解决方案

#1

You will need to unnest the array and then count the occurrences.

您将需要取消数组的嵌套，然后计算出现的次数。

with elements (element) as (
   select unnest(ARRAY['a','b','c','a','a'])
)
select count(*)
from elements
where element = 'a';

This can easily be embedded into a function:

这可以很容易地嵌入到一个函数中:

create or replace function count_elements(elements text[], to_find text)
  returns bigint
as
$body$
  select count(*) 
  from unnest(elements) element 
  where element =  to_find;
$body$
language sql;

Update

更新

Since Postgres 9.5 this can also be done using array_positions() which returns an array of positions where an element was found. The length of that array is the number of occurrences:

由于postgres9.5也可以使用array_positions()，它返回找到元素的位置数组。该数组的长度是出现的次数:

select cardinality(array_positions(ARRAY['a','b','c','a','a'], 'a'));

#2

9.5+

There is an easier method now

现在有一种更简单的方法

SELECT
  sArray,
  c,
  coalesce(array_length( array_positions(sArray, c), 1 ),0) AS count
FROM ( VALUES
  (ARRAY['a','b','c','a','a'], 'a'),
  (ARRAY['a','b','c'], 'd')
) AS t(sArray,c);

   sarray    | c | count 
-------------+---+-------
 {a,b,c,a,a} | a |     3
 {a,b,c}     | d |     0
(2 rows)

#3

More generic function is here;

这里有更一般的函数;

CREATE FUNCTION count_array_elements (
  i_elements pg_catalog.anyarray,
  i_element_to_find pg_catalog.anyelement,
  out count bigint
)
RETURNS bigint AS
$body$
BEGIN
  SELECT count(*) INTO count
  FROM unnest(i_elements) u
  WHERE u = i_element_to_find;
END;
$body$
LANGUAGE 'plpgsql'
IMMUTABLE
RETURNS NULL ON NULL INPUT;

With this way, we can query like this one below;

通过这种方式，我们可以像下面这样查询;

SELECT * FROM count_array_elements(array [ TRUE, TRUE, FALSE, FALSE, FALSE ], TRUE);

#4

Thanks to all contributors here, I learnt a few things. I am building on work of others in this thread and others in *.

感谢这里所有的贡献者，我学到了一些东西。我正在构建这个线程中的其他人和*中的其他人的工作。

I tried to create a function that will count for all the unique elements in the array.

我尝试创建一个函数来计算数组中所有唯一的元素。

I was targeting returning a json but it seems you can only return as SETOF.

我的目标是返回json，但似乎只能返回SETOF。

result of count_element_3

由于count_element_3

CREATE OR REPLACE FUNCTION count_element_3(str_array text[])
RETURNS setof text
AS
$$
DECLARE
    unique_element_array text[];
    cardinality_array int[];
    retArray text[];
BEGIN
-- Find unique items first
    unique_element_array := array(select distinct unnest(str_array));
    FOR I IN array_lower(unique_element_array, 1)..array_upper(unique_element_array, 1) 
    LOOP
    cardinality_array[I] := (select cardinality(array_positions(str_array, unique_element_array[I])));
    retArray[I] := concat(unique_element_array[I],':',cardinality_array[I]);
    END LOOP;

RETURN QUERY SELECT(retArray::text);
END;
$$
LANGUAGE plpgsql 
VOLATILE 
RETURNS NULL ON NULL INPUT;

with t1 as (SELECT
  sArray,
  c,
  coalesce(array_length( array_positions(sArray, c), 1 ),0) AS count
FROM ( VALUES
  (ARRAY['a','b','c','a','a'], 'a'),
  (ARRAY['a','b','c'], 'd')
) AS t(sArray,c)
)

select sarray, count_element_3(sarray) from t1

    sarray          count_element_3 
    text[]          text
-------------------------------------
    "{a,b,c,a,a}"   "{c:1,a:3,b:1}"
    "{a,b,c}"       "{c:1,a:1,b:1}"