PostgreSQL关联查询计算time列平均值遇重复行问题求助

你遇到的核心问题是关联消息表后，主表的记录被重复返回，导致time列的平均值计算时，同一个主记录的time被多次计入，最终结果不符合你预期的4个唯一值的平均结果。下面给你几个适配PostgreSQL 9.4的解决方案，核心思路都是先拿到唯一的主记录time值，再计算平均值：

方案1：用DISTINCT去重主记录 #

先通过子查询筛选出所有符合条件的唯一主记录（每个主记录仅保留一条），再对这些time值求平均：

SELECT AVG(time) AS avg_time
FROM (
    -- 筛选符合关联/过滤条件的唯一主记录的time
    SELECT DISTINCT m.id, m.time
    FROM main_table m
    JOIN messages msg ON m.id = msg.main_id
    -- 在这里添加你的筛选条件，比如 WHERE m.status = 'valid' 之类的
) AS unique_main_records;

方案2：用GROUP BY按主记录主键分组 #

如果主表的id是唯一主键，通过分组可以确保每个主记录只被统计一次：

SELECT AVG(unique_time) AS avg_time
FROM (
    SELECT m.time AS unique_time
    FROM main_table m
    JOIN messages msg ON m.id = msg.main_id
    -- 你的筛选条件
    GROUP BY m.id, m.time -- 按主键+time分组，确保每个主记录仅出现一次
) AS unique_main_records;

方案3：用PostgreSQL专属的DISTINCT ON（更灵活） #

PostgreSQL的DISTINCT ON可以指定按某列去重，确保每个id只返回第一条记录（需要配合ORDER BY使用）：

SELECT AVG(time) AS avg_time
FROM (
    SELECT DISTINCT ON (m.id) m.time
    FROM main_table m
    JOIN messages msg ON m.id = msg.main_id
    -- 你的筛选条件
    ORDER BY m.id -- DISTINCT ON要求必须先按去重列排序
) AS unique_main_records;

为什么原来的查询会出错？ #

举个直观的例子：如果某个主记录关联了2条消息，直接JOIN后这个主记录会被返回2次，它的time值会被计算2次。比如你期望的4个值里，若有一个值被重复计入一次，总和就变成81250 + 63457 + 31592 + 33987 + 33987 = 244273，除以5得到的结果就和你想要的52571差很多了。上面的方案都是先把重复的主记录去重，确保每个主记录的time只被统计一次，这样就能得到你预期的平均值。

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