Count elements in Array
Count elements in Array
Any version
SQL
Nothing
by Emanuel "3manuek"
If you are on PostgreSQL 8.3 or below you will need to add the unnest() function to convert an array to a set of rows.
Function:
CREATE FUNCTION count_elements(text[]) RETURNS text AS $BODY$
select string_agg(i || ':' || c, ' ' ) from (select i, count(*) c FROM (select unnest($1::text[]) i) i group by i order by c desc) foo;
$BODY$
LANGUAGE SQL;
Basic test:
postgres=# select count_elements('{1,2,3,4,2,1,2,3,4,5,3,1,2,3,43,1,2,3}'::text[]);
-[ RECORD 1 ]--+------------------------------
count_elements | 3:5 2:5 1:4 4:2 5:1 43:1
You can improve A LOT the previous function using specific data type for integers or date instead cast to text (which will improve the CPU consumption).
The best of this is that you can combine with array_agg, i.e.:
postgres=# create table da_values as select round(random()*40) val from generate_series(1,100);
SELECT 100
postgres=# select array_agg(val) from da_values ;
{33,25,23,6,9,5,17,18,36,28,23,23,40,21,13,12,23,30,38,30,14,10,27,24,22,13,1,18,0,3,28,33,28,12,40,37,17,17,15,13,5,38,36,4,19,8,16,2,38,14,32,12,24,19,36,6,32,37,24,32,1,12,25,29,24,25,26,1,2,1,13,6,38,9,11,17,18,27,18,16,2,10,28,25,29,24,31,21,21,15,13,22,27,38,11,10,23,37,11,25}
postgres=# select count_elements(array_agg(val::text)) from da_values ;
count_elements
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
25:5 38:5 13:5 23:5 24:5 1:4 12:4 18:4 28:4 17:4 21:3 2:3 37:3 6:3 11:3 10:3 36:3 27:3 32:3 15:2 40:2 16:2 33:2 22:2 5:2 29:2 9:2 14:2 19:2 30:2 26:1 31:1 4:1 3:1 0:1 8:1
(1 row)
If you create a count_elements function to receive int[] instead text[], you won't need to cast the parameter before array_agg-it. This is IMPORTANT becuase this is a basic and example code, but if you are planning to run it seriously you *should* respect the types.
Other stuff you can add is "labels", i.e: "Element:25, Count:5" in the string_agg function inside count_elements.
