PSQL分组后如何过滤每个分组内的冗余元素

实现方案 #

方案1：先获取全部分组再左关联过滤后的数据 #

完全符合「先定位全部分组，再过滤分组内冗余行」的逻辑，不会丢失任何group_idx分组：

WITH all_groups AS (
    -- 第一步先提取所有存在的分组，保证无遗漏
    SELECT group_idx FROM nexp GROUP BY group_idx
)
SELECT t.* 
FROM all_groups g
LEFT JOIN nexp t 
    ON g.group_idx = t.group_idx 
    AND t.filter_idx <> 'z'; -- 过滤逻辑放在关联条件中，不影响分组保留

如果某个分组下全部都是filter_idx = 'z'的行，返回结果里该分组对应的其他字段会显示为NULL，确保分组不会丢失。 #

方案2：窗口函数实现 #

如果不需要保留全空的分组行，仅需保证所有存在有效数据的分组都不丢失，可以用窗口函数实现：

SELECT id, group_idx, filter_idx
FROM (
    SELECT 
        *,
        COUNT(*) OVER (PARTITION BY group_idx) AS group_total
    FROM nexp
) t
WHERE filter_idx <> 'z' AND group_total > 0;

结果验证 #

针对你提供的样例数据，两种方案返回的有效数据行均为：

id | group_idx | filter_idx
---+-----------+-----------
1  | 1         | x
3  | 3         | x
4  | 2         | x
5  | 1         | x
6  | 3         | x
7  | 2         | x

所有3个group_idx（1、2、3）都完整保留，无分组丢失。

内容的提问来源于stack exchange，提问作者Andriy Van Dam