PostgreSQL如何对查询结果的数组列进行无序去重

针对最多2个元素的text数组、过滤顺序相反的重复行的需求，以下是可行的实现方案：

方案1：通用兼容方案（支持任意长度数组） #

通过对数组元素排序生成唯一标识，搭配DISTINCT ON去重，后续数组元素数量变化也无需修改代码：

SELECT DISTINCT ON (sorted_arr) *
FROM (
    SELECT *, ARRAY(SELECT unnest(array_col) ORDER BY 1) AS sorted_arr
    FROM schema.function($1,$2,$3)
    WHERE conditions...
) t
ORDER BY sorted_arr, id;

逻辑说明：将数组拆分为单个元素排序后重新聚合为新数组，元素相同、顺序不同的数组排序后结果完全一致，DISTINCT ON会取每组排序后数组的第一条数据，上述语句默认保留ID最小的重复行，如需保留ID最大的，将ORDER BY sorted_arr, id改为ORDER BY sorted_arr, id DESC即可。

方案2：最多2元素场景高性能方案 #

因为明确数组最多有2个元素，可以直接通过大小比较构造去重键，避免数组拆分聚合的性能损耗：

SELECT DISTINCT ON (dup_key) *
FROM (
    SELECT *,
        CASE 
            WHEN array_length(array_col, 1) = 1 THEN array_col[1]
            ELSE LEAST(array_col[1], array_col[2]) || '||' || GREATEST(array_col[1], array_col[2])
        END AS dup_key
    FROM schema.function($1,$2,$3)
    WHERE conditions...
) t
ORDER BY dup_key, id;

方案3：窗口函数灵活控制方案 #

如果需要更复杂的重复行保留规则，可以用窗口函数实现：

WITH func_result AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY ARRAY(SELECT unnest(array_col) ORDER BY 1) 
            ORDER BY id -- 这里可以自定义保留规则，比如按其他字段排序
        ) AS rn
    FROM schema.function($1,$2,$3)
    WHERE conditions...
)
SELECT id, array_col -- 按需选择需要返回的字段即可
FROM func_result 
WHERE rn = 1;

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