Postgres如何将Votes表聚合统计数据插入votes_aggregate表

你原有写法错误混合了INSERT和UPDATE的语法逻辑，不需要分「先插入初始0值再更新计数」两步操作，直接通过分组聚合一次性计算所有统计值后插入即可，执行效率更高也无语法问题。

全量初始化聚合表的SQL #

直接一次性扫描Votes表完成所有分组统计，写入votes_aggregate表：

INSERT INTO votes_aggregate (catalog_item_id, listing_id, yes, no)
SELECT
  catalog_item_id,
  listing_id,
  COUNT(*) FILTER (WHERE vote_result = 'y') AS yes,
  COUNT(*) FILTER (WHERE vote_result = 'n') AS no
FROM votes
GROUP BY catalog_item_id, listing_id;

语法说明 #

• 用GROUP BY catalog_item_id, listing_id直接按两个字段组合分组去重，比写DISTINCT后再嵌套子查询关联计数的方式性能高很多，仅需单次扫描Votes表即可完成全部统计
• FILTER是PostgreSQL原生支持的聚合过滤子句，可以在聚合时直接筛选符合条件的记录计数，比多子查询关联的写法更简洁，执行开销更低
• 如果后续需要同步Votes表的新增、修改数据，不需要全表重算，可以给votes_aggregate表的(catalog_item_id, listing_id)字段组合添加唯一约束，使用upsert语法做增量更新：

-- 增量更新聚合数据写法
INSERT INTO votes_aggregate (catalog_item_id, listing_id, yes, no)
SELECT
  catalog_item_id,
  listing_id,
  COUNT(*) FILTER (WHERE vote_result = 'y') AS yes,
  COUNT(*) FILTER (WHERE vote_result = 'n') AS no
FROM votes
GROUP BY catalog_item_id, listing_id
ON CONFLICT (catalog_item_id, listing_id) DO UPDATE
SET yes = EXCLUDED.yes,
    no = EXCLUDED.no;

注意：增量写法必须提前给votes_aggregate表的(catalog_item_id, listing_id)创建唯一约束，否则冲突判断逻辑无法正常生效。

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