postgres hstore aggregation

aggregate.sql

Select (SELECT hstore(array_agg(v), array_agg(c::text)) FROM (
  SELECT v, COUNT(*) as c from unnest(array_agg(country)) v GROUP BY v) t 
 ) countries FROM test;

-- =>  "de"=>"10669", "en"=>"21542", "fr"=>"21573", "it"=>"10783", "us"=>"21774"

array_count.sql

CREATE OR REPLACE FUNCTION array_count(arr anyarray) RETURNS hstore AS $$
  BEGIN
    RETURN
      hstore(array_agg(COALESCE(v::text,'null')), array_agg(c::text)) FROM (
        SELECT v, COUNT(*) as c from unnest(arr) v GROUP BY v
      ) t ;
  END;
 $$ LANGUAGE 'plpgsql' IMMUTABLE;

-- Select array_count(Array['foo','bar','foo','baz'])
-- => "bar"=>"1", "baz"=>"1", "foo"=>"2"

easy_aggregate.sql

Select array_count(array_agg(country)) from test;

hstore_add.sql

CREATE OR REPLACE FUNCTION hstore_add(a hstore, b hstore) RETURNS hstore AS $$
  BEGIN
    RETURN
      hstore(
        array_agg(key),
        array_agg((COALESCE(r.value::integer,0) + COALESCE(l.value::integer,0))::text)
      )
      FROM each(a) l      
      FULL OUTER JOIN each(b) r
      USING (key);
  END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;

-- Select hstore_add('a=>1,b=>2'::hstore ,'a=>1,c=>2'::hstore)
-- => "a"=>"2", "b"=>"2", "c"=>"2"

hstore_sum.sql

CREATE AGGREGATE sum (
  sfunc = hstore_add,
  basetype = hstore,
  stype = hstore,
  initcond = ''
);

table.sql

CREATE TABLE test
(
  country character varying(12),
  created_at timestamp without time zone
);

INSERT INTO test (created_at, country) 
  SELECT time, (Array['de','en','fr','us','it'])[(random()*4+1)::int]  
  FROM generate_series('2012-10-20 00:00'::timestamp, '2012-10-20 23:59', '1 second') time;

CREATE EXTENSION IF NOT EXISTS hstore;

table_by_hour.sql

CREATE TABLE test_by_hour
(
  countries hstore,
  created_at timestamp without time zone
);

INSERT INTO test_by_hour (countries, created_at) 
Select array_count(array_agg(country)), date_trunc('hour',created_at) AS hour 
FROM test GROUP BY date_trunc('hour',created_at);

This gist referes to the blogpost about aggregations with pg hstore