如何在PostgreSQL中结合sum()与distinct()实现去重求和？

现有如下数据表：

需要按state统计每个状态下的文件总数，其中state=2存在重复的file记录，需对file去重。预期结果为：

state(1): count 1, size 10
state(2): count 2, size 40

编写的SQL如下：

select state, count(distinct(file)), sum(size)
from mytable
group by state

该SQL中state=2的count结果正确，但sum()会累加重复file的size，得到不符合预期的结果。如何修正该SQL使sum()不包含重复file的size？数据库为PostgreSQL。

可以先通过子查询对每个state和file的组合进行去重，确保每个file在对应state下只保留一条记录，再基于去重后的结果进行聚合统计：

select 
    state,
    count(file) as count,
    sum(size) as size
from (
    -- 先去重：每个state下的每个file只保留一条记录
    select distinct state, file, size
    from mytable
) t
group by state

说明 #

• 子查询中使用distinct state, file, size过滤掉重复的file记录，确保同一state下的同一个file仅出现一次；
• 外层查询基于去重后的结果分组，此时count(file)直接统计唯一的文件数量，sum(size)也只会计算每个file的size一次，符合预期结果。

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