如何聚合数组去重Ids且保证Distance求和结果正确？

原始数据 #

期望输出 #

现有代码问题 #

你当前的查询通过UNNEST(ids)将每行拆分为多行，导致Distance被重复计算——比如第一行的331会被计算3次，第二行的200被计算3次，最终求和结果为331*3 + 200*3 = 1593，而非预期的531。

正确查询方案 #

方案一：用CTE拆分独立聚合（通用型） #

这种方式将距离求和、ID去重聚合、区域去重拆分为独立的子查询，再通过Owner关联合并，逻辑清晰且兼容多数SQL引擎：

WITH total_distance AS (
    SELECT 
        owner,
        SUM(distance) AS distance
    FROM table
    GROUP BY owner
),
unique_ids AS (
    SELECT 
        owner,
        ARRAY_AGG(DISTINCT id) AS ids
    FROM table, UNNEST(ids) AS id
    GROUP BY owner
),
unique_zones AS (
    SELECT 
        owner,
        ARRAY_AGG(DISTINCT zone) AS zone
    FROM table
    GROUP BY owner
)
SELECT 
    td.distance,
    ui.ids,
    uz.zone,
    td.owner
FROM total_distance td
JOIN unique_ids ui ON td.owner = ui.owner
JOIN unique_zones uz ON td.owner = uz.owner;

方案二：子查询内嵌聚合（简洁型） #

如果不想用CTE，可以直接在主查询中用子查询处理ID的去重聚合，同时直接对原始表求和距离：

SELECT
    SUM(distance) AS distance,
    (SELECT ARRAY_AGG(DISTINCT id) FROM table t2, UNNEST(t2.ids) id WHERE t2.owner = t1.owner) AS ids,
    ARRAY_AGG(DISTINCT zone) AS zone,
    owner
FROM table t1
GROUP BY owner;

方案三：PostgreSQL专属优化（依赖行唯一标识） #

如果你的表有主键（比如row_id），可以利用PostgreSQL的复合类型特性，确保每个原始行的距离只被计算一次，同时完成ID聚合：

SELECT
    SUM((DISTINCT row_id, distance))::int AS distance,
    ARRAY_AGG(DISTINCT id) AS ids,
    ARRAY_AGG(DISTINCT zone) AS zone,
    owner
FROM table, UNNEST(ids) AS id
GROUP BY owner;

注：该方案仅适用于PostgreSQL，且必须有唯一标识每一行的字段。

核心逻辑 #

要解决问题，必须将距离求和与ID数组拆分两个操作解耦：

1. 距离求和直接基于原始行计算，确保每个行的距离只被累加一次；
2. ID数组需要先拆分为单个元素，再通过DISTINCT去重后重新聚合为数组；
3. 区域字段直接通过ARRAY_AGG(DISTINCT zone)完成去重聚合。

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