PostgreSQL中如何通过比较两个数组列值实现按数量去重？

要实现从favorite_fruit数组中按数量扣除fruit_eaten数组的元素（即每个元素扣除对应出现次数，剩余数量>0的保留），可以通过数组拆分行统计+重新聚合的方式实现，具体SQL如下：

SELECT 
    idx,
    array_agg(fruit ORDER BY fruit) AS result
FROM (
    SELECT 
        idx,
        fruit,
        COUNT(*) - COALESCE(eaten_count, 0) AS remaining_count
    FROM (
        -- 拆分喜爱水果数组，得到每个元素的单行记录
        SELECT 
            idx,
            unnest(favorite_fruit) AS fruit
        FROM favorite
    ) f
    LEFT JOIN (
        -- 拆分已吃水果数组，统计每个水果的被吃次数
        SELECT 
            idx,
            unnest(fruit_eaten) AS fruit,
            COUNT(*) AS eaten_count
        FROM favorite
        GROUP BY idx, unnest(fruit_eaten)
    ) e ON f.idx = e.idx AND f.fruit = e.fruit
    GROUP BY idx, fruit, eaten_count
    -- 只保留剩余数量大于0的元素
    HAVING COUNT(*) - COALESCE(eaten_count, 0) > 0
) sub
-- 根据剩余数量生成对应行数的元素，用于重新聚合数组
CROSS JOIN LATERAL generate_series(1, remaining_count)
GROUP BY idx
ORDER BY idx;

逻辑说明 #

1. 拆分数组：用unnest()将数组列拆分为单行数据，把数组的批量元素转换为可统计的行记录。
2. 次数统计：对fruit_eaten的拆分结果分组，得到每个idx下各水果的被吃次数；对favorite_fruit的拆分结果直接分组统计出现次数。
3. 计算剩余量：用喜爱水果的总次数减去被吃次数，通过COALESCE处理未出现在fruit_eaten中的元素（默认被吃次数为0）。
4. 过滤与聚合：过滤掉剩余数量≤0的元素，再通过generate_series生成对应次数的元素行，最后用array_agg将元素重新聚合成数组。

测试结果 #

针对你提供的favorite表数据，执行上述SQL后会得到：

完全符合预期结果。

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