sql - 在 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] ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list [2019-11-22 13:29:32] Position: 53

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

Demo on 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

Demo on DB Fiddle