PostgreSQL中如何去除查询结果数组的重复值？

你当前的写法是分别对两个来源的经验值去重后再拼接，但两个数组之间可能存在重复元素，所以最终结果还是会有重复。以下是几种可行的解决办法：

方法一：使用array_distinct对拼接后的数组去重 #

PostgreSQL 9.3及以上版本支持array_distinct函数，可以直接移除数组中的重复元素。修改后的查询语句如下：

SELECT
    consultant_profiles.id,
    array_distinct(
      array_remove(array_agg(DISTINCT function_experiences.experience), NULL) ||
      array_remove(array_agg(DISTINCT sellable_project_function_experiences.experience), NULL)
    ) AS function_experiences
FROM
    consultant_profiles
LEFT OUTER JOIN
    consultant_backgrounds ON consultant_backgrounds.consultant_profile_id = consultant_profiles.id
LEFT OUTER JOIN
    function_experiences ON function_experiences.consultant_background_id = consultant_backgrounds.id
LEFT OUTER JOIN
    sellable_projects ON sellable_projects.consultant_profile_id = consultant_profiles.id
LEFT OUTER JOIN
    sellable_project_function_experiences ON sellable_project_function_experiences.sellable_project_id = sellable_projects.id
GROUP BY
    consultant_profiles.id;

方法二：先合并数据源再聚合去重 #

这种方式通过子查询将两个来源的经验数据合并，再统一进行聚合，既能避免多表连接产生的冗余行，也能确保最终数组无重复元素：

SELECT
    cp.id,
    array_remove(array_agg(DISTINCT fe.experience), NULL) AS function_experiences
FROM
    consultant_profiles cp
LEFT JOIN (
    -- 从顾问背景获取经验数据
    SELECT cb.consultant_profile_id, fe.experience
    FROM consultant_backgrounds cb
    LEFT JOIN function_experiences fe ON fe.consultant_background_id = cb.id
    UNION ALL
    -- 从可售项目获取经验数据
    SELECT sp.consultant_profile_id, spfe.experience
    FROM sellable_projects sp
    LEFT JOIN sellable_project_function_experiences spfe ON spfe.sellable_project_id = sp.id
) fe ON fe.consultant_profile_id = cp.id
GROUP BY cp.id;

兼容低版本PostgreSQL的方案（低于9.3） #

如果你的PostgreSQL版本不支持array_distinct，可以用unnest展开数组，去重后再重新聚合：

SELECT
    cp.id,
    array_agg(DISTINCT exp.experience) AS function_experiences
FROM
    consultant_profiles cp
LEFT OUTER JOIN
    consultant_backgrounds cb ON cb.consultant_profile_id = cp.id
LEFT OUTER JOIN
    function_experiences fe ON fe.consultant_background_id = cb.id
LEFT OUTER JOIN
    sellable_projects sp ON sp.consultant_profile_id = cp.id
LEFT OUTER JOIN
    sellable_project_function_experiences spfe ON spfe.sellable_project_id = sp.id,
    unnest(
      array_remove(array_agg(DISTINCT fe.experience) OVER (PARTITION BY cp.id), NULL) ||
      array_remove(array_agg(DISTINCT spfe.experience) OVER (PARTITION BY cp.id), NULL)
    ) AS exp(experience)
GROUP BY cp.id;

不过这种写法效率不如前两种，优先推荐前两种方案。

内容的提问来源于stack exchange，提问作者Mateusz Urbański