PostgreSQL的：得到指定数组元素的出​​现次数 [英] PostgreSQL: get count of occurrences of specified element in array

问题描述

我需要计算数组指定元素出现的次数，是这样的：

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]，D）= 0

是否有可用于解决该问题中的PostgreSQL任何功能？任何帮助是AP preciated。

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

推荐答案

您将需要UNNEST数组再算上出现。

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;

这篇关于PostgreSQL的：得到指定数组元素的出​​现次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！