PostgreSQL按Region聚合数据，排除跨多区域的ID行

要实现按Region聚合数据，同时排除那些在多个Region中存在的ID对应的所有行，核心思路是先筛选出仅属于单个Region的ID，再基于这些ID过滤原表后进行聚合。以下是两种可行的实现方式：

方法一：子查询筛选符合条件的ID #

通过子查询先找出所有只对应一个Region的ID，再用这些ID过滤原表数据，最后执行聚合：

SELECT region, COUNT(id), SUM(amount)
FROM table1
WHERE id IN (
    -- 筛选出仅属于单个Region的ID
    SELECT id
    FROM table1
    GROUP BY id
    HAVING COUNT(DISTINCT region) = 1
)
GROUP BY region;

方法二：窗口函数标记跨区域ID #

使用窗口函数为每个ID计算其关联的不同Region数量，再过滤掉数量大于1的记录后聚合：

WITH id_region_stats AS (
    SELECT 
        *,
        -- 计算每个ID对应的不同Region数量
        COUNT(DISTINCT region) OVER (PARTITION BY id) AS region_count
    FROM table1
)
SELECT region, COUNT(id), SUM(amount)
FROM id_region_stats
WHERE region_count = 1 -- 仅保留单区域ID的记录
GROUP BY region;

执行结果 #

两种方法都会得到你期望的结果：

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