PostgreSQL中如何对GROUP BY后的array_agg结果统计去重地点数量

看来你是想把每个uid对应的不同地点的出现次数，整理成像{hk:2,jp:1}这种键值对格式的结果对吧？之前用array_agg只能得到带重复值的地点数组，结合count()没搞定，那是因为得先做一层分组统计，再聚合为键值对结构。

直接给你可以运行的SQL语句：

SELECT 
  t.uid,
  json_object_agg(t.place, cnt) AS place_count
FROM (
  -- 先统计每个用户每个地点的出现次数
  SELECT 
    uid,
    place,
    COUNT(*) AS cnt
  FROM tour_tracking
  WHERE orderon::time BETWEEN '18:00:00' AND '20:00:00'
  GROUP BY uid, place
) t
-- 再把每个用户的地点-次数对聚合为JSON对象
GROUP BY t.uid;

简单说下逻辑： #

• 内层子查询：先按uid和place做分组，算出每个用户每个地点出现的次数，这一步是把重复的地点合并并计数。
• 外层查询：用PostgreSQL的json_object_agg函数，把每个用户的（地点，次数）键值对聚合为一个JSON对象，正好就是你想要的格式。

如果你偏好使用PostgreSQL的hstore类型（一种键值对存储类型），也可以用hstore_agg来实现，不过需要先确保hstore扩展已经安装（执行CREATE EXTENSION IF NOT EXISTS hstore;），语句如下：

SELECT 
  t.uid,
  hstore_agg(t.place || '=>' || cnt) AS place_count
FROM (
  SELECT 
    uid,
    place,
    COUNT(*) AS cnt
  FROM tour_tracking
  WHERE orderon::time BETWEEN '18:00:00' AND '20:00:00'
  GROUP BY uid, place
) t
GROUP BY t.uid;

一般来说JSON格式兼容性更好，推荐第一种方案。

内容的提问来源于stack exchange，提问作者yuc