PostgreSQL中如何统计字符串聚合内各字符串的数量？

当然可以实现你想要的目标输出！你的核心需求是按postcode和geom分组后，不仅聚合字符串，还要带上每个字符串在分组内的出现次数，对吧？我来帮你调整SQL逻辑：

正确的查询语句 #

CREATE TABLE test AS
SELECT 
    postcode,
    geom,
    STRING_AGG(cnt::text || ' ' || col, ', ' ORDER BY cnt DESC, col) AS aggregated_column
FROM (
    -- 第一步：先统计每个分组下各字符串的出现次数
    SELECT 
        col,
        COUNT(*) AS cnt,
        postcode,
        geom
    FROM tablewithdata
    GROUP BY col, postcode, geom
) AS grouped_counts
-- 第二步：按postcode和geom聚合，拼接带计数的字符串
GROUP BY postcode, geom;

逻辑解释 #

1. 子查询grouped_counts：先按col、postcode、geom三重分组，算出每个字符串在对应地理分组内的出现次数cnt。比如你给出的示例数据，这一步会得到：

   col	cnt	postcode	geom
   Banana	3	xxx	yyy
   Monkey	2	xxx	yyy
   Thailand	1	xxx	yyy

2. 外层聚合：基于子查询的结果，再按postcode和geom分组，用STRING_AGG把cnt::text || ' ' || col拼接成你想要的格式。ORDER BY cnt DESC, col可以让出现次数多的字符串排在前面，也可以根据需求改成仅按col排序。

你原查询的问题 #

你之前的查询错误地将原表a和子查询x做了无关联条件的连接，这会导致笛卡尔积（结果行数爆炸），而且完全没必要关联原表——我们只需要基于子查询的统计结果再聚合就足够了。

用上面的SQL，你就能得到类似3 Banana, 2 Monkey, 1 Thailand的聚合结果啦！

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