Count elements in Array

From PostgreSQL wiki

Revision as of 11:40, 10 October 2012 by 3manuek (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

Snippets

Count elements in Array

Works with PostgreSQL

Any version

Written in

SQL

Depends on

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.

See also

Array Range Array Median

Personal tools