如何将带GROUP_CONCAT(DISTINCT排序)的MariaDB查询转为PostgreSQL

问题背景 #

在将应用从MariaDB迁移到PostgreSQL时，原MariaDB查询通过GROUP_CONCAT(DISTINCT 字段 ORDER BY 排序字段)实现去重后按指定列排序的聚合，转换为PostgreSQL的STRING_AGG时遇到两个问题：

1. 直接移除ORDER BY会导致聚合结果顺序不符（如sample_info列顺序颠倒）；
2. 在STRING_AGG(DISTINCT ...)中添加ORDER BY会触发错误：in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list

原MariaDB查询：

SELECT
    r.result_id,
    r.sample_id,
    sm.sample_id AS parameter_id,
    GROUP_CONCAT(DISTINCT(sm.metadata_value) ORDER BY smt.display_in_concat SEPARATOR ' ') AS parameter,
    GROUP_CONCAT(DISTINCT(sms.metadata_value) ORDER BY smts.display_in_concat SEPARATOR ' - ') AS sample_info
FROM
    results r
    JOIN samples_metadata sm ON r.parameter_id = sm.sample_id
    JOIN samples_metadata sms ON r.sample_id = sms.sample_id
    JOIN metadata_types smt ON sm.meta_type_id = smt.meta_type_id
    JOIN metadata_types smts ON sms.meta_type_id = smts.meta_type_id
WHERE
    smt.display_in_concat > 0
    AND smts.display_in_concat > 0
    AND r.instance_id = 1625152480
GROUP BY
    r.result_id,
    r.sample_id,
    sm.sample_id;

解决方案：先预处理去重+排序，再聚合 #

PostgreSQL要求STRING_AGG(DISTINCT ...)中的ORDER BY字段必须包含在DISTINCT的参数列表中，无法直接复刻MariaDB的写法。我们可以通过先对聚合数据源做去重和排序预处理，再执行STRING_AGG来实现相同效果：

WITH param_metadata AS (
    -- 预处理参数数据：去重并保留排序字段
    SELECT
        r.result_id,
        r.sample_id,
        sm.sample_id AS parameter_id,
        sm.metadata_value,
        smt.display_in_concat
    FROM results r
    JOIN samples_metadata sm ON r.parameter_id = sm.sample_id
    JOIN metadata_types smt ON sm.meta_type_id = smt.meta_type_id
    WHERE smt.display_in_concat > 0
        AND r.instance_id = 1625152480
    GROUP BY r.result_id, r.sample_id, sm.sample_id, sm.metadata_value, smt.display_in_concat
),
sample_metadata AS (
    -- 预处理样本信息数据：去重并保留排序字段
    SELECT
        r.result_id,
        r.sample_id,
        sms.metadata_value,
        smts.display_in_concat
    FROM results r
    JOIN samples_metadata sms ON r.sample_id = sms.sample_id
    JOIN metadata_types smts ON sms.meta_type_id = smts.meta_type_id
    WHERE smts.display_in_concat > 0
        AND r.instance_id = 1625152480
    GROUP BY r.result_id, r.sample_id, sms.metadata_value, smts.display_in_concat
)
SELECT
    pm.result_id,
    pm.sample_id,
    pm.parameter_id,
    -- 对预处理后的参数数据按display_in_concat排序聚合
    STRING_AGG(pm.metadata_value, ' ' ORDER BY pm.display_in_concat) AS parameter,
    -- 对预处理后的样本信息数据按display_in_concat排序聚合
    STRING_AGG(sm.metadata_value, ' - ' ORDER BY sm.display_in_concat) AS sample_info
FROM param_metadata pm
JOIN sample_metadata sm 
    ON pm.result_id = sm.result_id 
    AND pm.sample_id = sm.sample_id
GROUP BY pm.result_id, pm.sample_id, pm.parameter_id;

原理说明 #

1. 预处理CTE：通过两个CTE分别处理参数和样本信息的数据源，使用GROUP BY完成去重（确保每个metadata_value在分组内唯一），同时保留用于排序的display_in_concat字段。
2. 聚合阶段：在主查询中关联两个预处理后的数据集，使用STRING_AGG时直接按display_in_concat排序，此时无需再使用DISTINCT（CTE已完成去重），最终得到与MariaDB完全一致的排序和聚合结果。

备选写法：关联子查询 #

如果更偏好子查询风格，也可以用嵌套子查询实现相同逻辑：

SELECT
    r.result_id,
    r.sample_id,
    sm.sample_id AS parameter_id,
    -- 子查询预处理参数数据并聚合
    (SELECT STRING_AGG(mv, ' ' ORDER BY display_in_concat)
     FROM (SELECT DISTINCT sm_inner.metadata_value AS mv, smt_inner.display_in_concat
           FROM samples_metadata sm_inner
           JOIN metadata_types smt_inner ON sm_inner.meta_type_id = smt_inner.meta_type_id
           WHERE sm_inner.sample_id = r.parameter_id
             AND smt_inner.display_in_concat > 0) AS param_vals) AS parameter,
    -- 子查询预处理样本信息并聚合
    (SELECT STRING_AGG(mv, ' - ' ORDER BY display_in_concat)
     FROM (SELECT DISTINCT sms_inner.metadata_value AS mv, smts_inner.display_in_concat
           FROM samples_metadata sms_inner
           JOIN metadata_types smts_inner ON sms_inner.meta_type_id = smts_inner.meta_type_id
           WHERE sms_inner.sample_id = r.sample_id
             AND smts_inner.display_in_concat > 0) AS sample_vals) AS sample_info
FROM results r
JOIN samples_metadata sm ON r.parameter_id = sm.sample_id
WHERE r.instance_id = 1625152480
GROUP BY r.result_id, r.sample_id, sm.sample_id;

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