在STRING_AGG函数中使用DISTINCT和ORDER BY。

Question

我试图使用string_agg函数聚合一列数据，同时按照该列数据排序并只显示唯一值。考虑以下示例。是否存在语法问题或者使用的方法不可行？

SELECT STRING_AGG(DISTINCT foo.a::TEXT,',' ORDER BY foo.a DESC)
              FROM (
                       SELECT 1 As a
                       UNION ALL
                       SELECT 1
                       UNION ALL
                       SELECT 1
                       UNION ALL
                       SELECT 2
                   ) AS foo

[2019-11-22 13:29:32] [42P10] 错误：在带有DISTINCT的聚合函数中，ORDER BY表达式必须出现在参数列表中 [2019-11-22 13:29:32] 位置：53

该错误意味着，在使用DISTINCT的聚合函数时，ORDER BY表达式必须包含在函数的参数列表中。

- Daniel L. VanDenBosch

Answer 1

错误消息非常清晰。在 ORDER BY 子句中使用的表达式也必须出现在聚合部分中。

你可以这样做:

SELECT STRING_AGG(DISTINCT foo.a::TEXT, ',' ORDER BY foo.a::TEXT DESC)
FROM (
    SELECT 1 As a
    UNION ALL SELECT 1
    UNION ALL SELECT 1
    UNION ALL SELECT 2
) AS foo

DB Fiddle上的演示

虽然这个方法可以工作，但问题在于它会将数字按照字符串顺序排序，而字符串的排序规则并不相同。在字符串的情况下，10小于2。

另一种选择是使用数组：首先，可以使用ARRAY_AGG()聚合数字（具有正确的数字排序），然后使用ARRAY_TO_STRING()将其转换为逗号分隔的字符串列表。

SELECT ARRAY_TO_STRING(ARRAY_AGG(DISTINCT a ORDER BY a DESC), ',')
FROM (
    SELECT 1 As a
    UNION ALL SELECT 1
    UNION ALL SELECT 1
    UNION ALL SELECT 2
) AS foo

DB Fiddle演示

- GMB