PostgreSQL实现分组求和并保留唯一城市及关联几何数据

问题背景 #

现有两张表结构如下：

表A

表B

需求是按城市分组计算sum_value的总和，同时关联对应城市的geom空间字段。

尝试中的问题 #

最初执行以下SQL可得到正确的城市唯一求和结果：

SELECT SUM(tablea.sum_value), tableb.city
FROM tablea, tableb
WHERE tablea.id = tableb.id
GROUP BY tableb.city

结果：

但添加geom字段后，执行以下SQL出现异常：

SELECT SUM(tablea.sum_value), tableb.city, tableb.geom
FROM tablea, tableb
WHERE tablea.id = tableb.id
GROUP BY tableb.city, tableb.geom

得到的结果城市重复，求和失效：

期望得到的正确结果：

解决方法 #

方法1：子查询先求和再关联 #

先对表A按id分组计算总和，再与表B关联获取geom字段，这是最直观的方案：

SELECT a.sum_total, b.city, b.geom
FROM (
    -- 先计算每个id对应的sum_value总和
    SELECT id, SUM(sum_value) AS sum_total
    FROM tablea
    GROUP BY id
) a
INNER JOIN tableb b ON a.id = b.id

方法2：显式关联后按唯一标识分组 #

由于表B中id、city、geom是一一对应的，可直接按表B的id分组（或同时按city、geom），确保分组的唯一性：

SELECT SUM(a.sum_value) AS sum_total, b.city, b.geom
FROM tablea a
INNER JOIN tableb b ON a.id = b.id
GROUP BY b.id, b.city, b.geom

这里选择b.id作为分组依据是因为它是唯一主键，比同时按city和geom更高效，且能避免空间字段分组的性能损耗。

方法3：窗口函数+去重 #

使用窗口函数计算每个分组的总和，再通过DISTINCT去重得到唯一城市记录：

SELECT DISTINCT
    SUM(a.sum_value) OVER (PARTITION BY a.id) AS sum_total,
    b.city,
    b.geom
FROM tablea a
INNER JOIN tableb b ON a.id = b.id

原因说明 #

原SQL错误的核心是先做笛卡尔积再分组：表A中每个城市有多条记录，与表B关联后会生成多条包含相同city和geom的记录，此时按city和geom分组时，每条关联后的记录会被单独视为一组（因为分组是基于所有行的字段组合，而每条行的sum_value不同，导致每组仅包含一条记录，求和结果就是该行的sum_value本身）。通过先求和再关联，或按唯一主键分组，就能避免这个问题。

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