sql - 在 GROUP BY 中聚合 Postgres 中的 hstore

Question

我有这样的 hstore 数据:

|brand|account|likes|views                 | 
|-----|-------|-----|----------------------|
|Ford |ford_uk|1    |"3"=>"100"            |
|Ford |ford_us|2    |"3"=>"200", "5"=>"10" |
|Jeep |jeep_uk|3    |"3"=>"300"            |
|Jeep |jeep_us|4    |"3"=>"400", "5"=>"20" |

我希望能够按键汇总 hstores，按品牌分组:

|brand|likes|views                 | 
|-----|-----|----------------------|
|Ford |3    |"3"=>"300", "5"=>"10" |
|Jeep |7    |"3"=>"700", "5"=>"20" |

This answer为如何在没有 GROUP BY 的情况下执行此操作提供了一个很好的解决方案。使其适应这种情况会给出如下内容:

SELECT
  sum(likes) AS total_likes,
 (SELECT hstore(array_agg(key), array_agg(value::text))
  FROM (
    SELECT s.key, sum(s.value::integer)
    FROM (
      SELECT((each(views)).*)
    ) AS s(key, value)
    GROUP BY key
  ) x(key, value)) AS total_views
FROM my_table
GROUP BY brand

但是这给出了:

ERROR: subquery uses ungrouped column "my_table.views" from outer query

感谢任何帮助!

Answer 1

这是因为在group by查询中使用了没有聚合函数的views列。
非常快速的解决方法:

with my_table(brand,account,likes,views) as (
  values
    ('Ford', 'ford_uk', 1, '"3"=>"100"'::hstore),
    ('Ford', 'ford_uk', 2, '"3"=>"200", "5"=>"10"'),
    ('Jeep', 'jeep_uk', 3, '"3"=>"300"'::hstore),
    ('Jeep', 'jeep_uk', 4, '"3"=>"400", "5"=>"20"'))
SELECT
  brand,
  sum(likes) AS total_likes,
 (SELECT hstore(array_agg(key), array_agg(value::text))
  FROM (
    SELECT s.key, sum(s.value::integer)
    FROM 
      unnest(array_agg(views)) AS h, --<< aggregate views according to the group by, then unnest it into the table
      each(h) as s(key,value)
    GROUP BY key
  ) x(key, value)) AS total_views
FROM my_table
GROUP BY brand

更新

您还可以创建 aggregate对于这样的任务:

--drop aggregate if exists hstore_sum(hstore);
--drop function if exists hstore_sum_ffunc(hstore[]);
create function hstore_sum_ffunc(hstore[]) returns hstore language sql immutable as $$
  select hstore(array_agg(key), array_agg(value::text))
  from
    (select s.key, sum(s.value::numeric) as value
     from unnest($1) as h, each(h) as s(key, value) group by s.key) as t
$$;
create aggregate hstore_sum(hstore) 
(
    SFUNC = array_append,
    STYPE = hstore[],
    FINALFUNC = hstore_sum_ffunc,
    INITCOND = '{}'
);

之后您的查询将更简单且更“规范”:

select
  brand, 
  sum(likes) as total_likes,
  hstore_sum(views) as total_views
from my_table
group by brand;

更新 2

即使没有 create aggregate 函数 hstore_sum_ffunc 也很有用:

select
  brand, 
  sum(likes) as total_likes,
  hstore_sum_ffunc(array_agg(views)) as total_views
from my_table
group by brand;