我有这样一张桌子：
Table "public.statistics"
id | integer | not null default nextval('statistics_id_seq'::regclass)
goals | hstore |
项目：
|id |goals | |30059 |"3"=>"123" | |27333 |"3"=>"200","5"=>"10" |
我需要做什么来通过哈希键来聚合所有值？
我想得到这样的结果：
select sum(goals) from statistics
返回
|goals | |"3"=>"323","5"=>"10" |
基于Laurence的回答,这是一种纯sql方法,使用array_agg和hstore(text [],text [])构造函数将总和的键/值对聚合到一个新的hstore中.
http://sqlfiddle.com/#!1/9f1fb/17
SELECT hstore(array_agg(hs_key),array_agg(hs_value::text))
FROM (
SELECT
s.hs_key,sum(s.hs_value::integer)
FROM (
SELECT (each(goals)).* FROM statistics
) as s(hs_key,hs_value)
GROUP BY hs_key
) x(hs_key,hs_value)
我也用简单的强制转换为整数替换了to_number,并简化了键/值迭代.
