PostgreSQL按优先级合并去重用户组数组的SQL查询方案

针对PostgreSQL 13.11中按优先级合并数组并去重的需求，可以通过拆解数组+窗口函数标记首次出现+二次聚合的方式实现，具体SQL如下：

WITH unnest_data AS (
    SELECT 
        usr_id,
        priority,
        g.group_id,
        g.ordinality AS elem_pos
    FROM user_groups,
         unnest(groups) WITH ORDINALITY AS g(group_id, elem_pos)
),
ranked_groups AS (
    SELECT 
        usr_id,
        group_id,
        priority,
        elem_pos,
        row_number() OVER (PARTITION BY usr_id, group_id ORDER BY priority, elem_pos) AS rn
    FROM unnest_data
)
SELECT 
    usr_id,
    array_agg(group_id ORDER BY priority, elem_pos) AS merged_groups
FROM ranked_groups
WHERE rn = 1
GROUP BY usr_id;

逻辑说明 #

1. 拆解数组并保留顺序：使用unnest(groups) WITH ORDINALITY拆分数组，同时记录每个元素在原数组中的位置elem_pos，确保原数组的元素顺序不会丢失。
2. 标记首次出现的组ID：通过row_number()窗口函数，按usr_id和group_id分组，再按priority升序、elem_pos升序排序，标记每个组ID的首次出现记录（rn=1）——低优先级中已出现的组ID，在高优先级中会被标记为rn>1，后续过滤时会被排除。
3. 聚合去重后的元素：过滤掉rn>1的重复记录，再按priority和elem_pos排序聚合，得到低优先级元素在前、高优先级新元素在后且无重复的合并数组。

验证示例 #

• 当用户ID=1的两条记录为：优先级1的[1,2,3,5]、优先级2的[2,5,10,12]，最终合并结果为[1,2,3,5,10,12]。
• 若优先级调换为：优先级1的[2,5,10,12]、优先级2的[1,2,3,5]，最终合并结果为[2,5,10,12,1,3]。

原方案问题说明 #

PostgreSQL 13不支持array_agg(DISTINCT column ORDER BY ...)的语法组合，因为DISTINCT会打乱ORDER BY指定的顺序逻辑，导致语法错误（错误码42P10）。上述方案通过先去重再聚合的方式规避了这个限制，完全兼容PostgreSQL 13版本。

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