sql - 在选定的 ids postgresql 的其他字段上使用 distinct

Question

我有这个数据

Table
id  weight
1   1000
1   1000
2   2000
2   2000
3   3000
4   3000

我正在尝试查找除 4 之外的不同 ID 的平均权重，我需要这种格式的数据

 id          avg(weight) 
 1,2,3          2000

我试过 distinct 但它给了我平均值，包括所有重复值。

SELECT
    String_agg(distinct id :: text, ', ') AS ids,
    Round(Coalesce(Avg(weight), 0)) AS avg
FROM "table"
where id != 4

我也试过按 id 分组，但它给我的数据格式不同，而且它没有给我正确的平均值。

SELECT
    String_agg(id :: text, ', ') AS ids,
    Round(Coalesce(Avg(weight), 0)) AS avg
FROM "table"
where id != 4
group by id

那么我怎样才能找到这个的平均值呢？

谢谢。

Answer 1

您可以尝试在查找不同记录的子查询上使用您当前的逻辑:

SELECT
    STRING_AGG(id::text, ',' ORDER BY id) AS ids,
    ROUND(COALESCE(AVG(weight), 0)) AS avg
FROM
(
    SELECT DISTINCT id, weight
    FROM "table"
    WHERE id <> 4
) t;

Demo

注意:我在您的 STRING_AGG 调用中添加了一个 ORDER BY 子句，以确保 ID 按您想要的顺序出现。