SQL怎样在分组后对字符串进行去重拼接_使用ARRAY_AGG去重

SQL怎样在分组后对字符串进行去重拼接_使用ARRAY_AGG去重

ARRAY_AGG(DISTINCT ...) 是最直接的去重拼接方式

说到在PostgreSQL里分组后对字符串进行去重拼接，ARRAY_AGG(DISTINCT ...)无疑是首选方案。从PostgreSQL 10版本开始，这个功能就得到了原生支持。它的优势非常明显：语法极其简洁，性能表现优秀，最关键的是，它完美契合了“分组、去重、聚合成数组”这一系列连贯需求，无需再借助子查询或数组展开这些“曲线救国”的手段。

不过，直接使用DISTINCT时，有几个细节值得特别注意。一个常见的疏忽是排序问题。比如，你可能会这样写：ARRAY_AGG(DISTINCT tag ORDER BY tag)。看起来没问题，对吧？但如果tag字段里包含了NULL值，默认的排序规则会把NULL排在最前面，这很可能打乱你的业务预期。正确的做法是显式指定NULLS LAST：

• ARRAY_AGG(DISTINCT tag ORDER BY tag NULLS LAST)
• 另外，如果字段是文本类型且可能包含首尾空格，建议先用trim(tag)处理再去重，否则像'VIP'和'VIP '这样的值会被视为两个不同的条目。
• 最后，请务必记住：如果不加ORDER BY子句，聚合结果的顺序是不确定的。即使源数据看起来有序，数据库也不保证ARRAY_AGG的输出顺序。

为什么不用 STRING_AGG(DISTINCT ...) 直接拼接？

你可能会问，STRING_AGG不也支持DISTINCT吗？比如STRING_AGG(DISTINCT tag, ',')，直接输出一个用逗号连接的字符串，岂不是更省事？

这里的关键在于后续处理能力。STRING_AGG的输出是“死”的字符串，一旦生成，就很难再被结构化地处理。而ARRAY_AGG(DISTINCT ...)返回的是鲜活的数组类型，这为后续计算打开了无限可能。你可以立刻调用array_to_string将其转为字符串，用array_length统计元素个数，或者通过下标（如(arr)[1]）直接取出特定位置的元素。

来看几个典型场景：

• 需要同时获得去重后的标签字符串和标签数量：array_to_string(ARRAY_AGG(DISTINCT tag), ',') 和 array_length(ARRAY_AGG(DISTINCT tag), 1)
• 想取优先级最高的那个标签作为主标签：(ARRAY_AGG(DISTINCT tag ORDER BY priority DESC))[1]
• 后续计划在应用层（比如用Python的json.loads）反序列化数据——数组格式比单纯的逗号分隔字符串要结构化得多，处理起来也更方便。

遇到 GROUP BY 字段多、去重逻辑复杂怎么办？

现实情况往往更复杂。当去重规则需要基于多个字段的组合（例如，必须(user_id, tag, source)三者完全相同才算重复），或者需要按照业务优先级进行过滤（比如优先保留source = 'system'的标签，其次才考虑'manual'的）时，简单的DISTINCT关键字就力不从心了。

这时候，正确的思路是把去重逻辑“上推”到输入层。也就是说，先通过CTE（公共表表达式）或子查询，把数据预处理干净。常用的方法包括：

• 使用SELECT DISTINCT ON (user_id, tag) *，按照业务规则为每组重复项只保留一行。
• 或者，利用窗口函数ROW_NUMBER() OVER (PARTITION BY user_id, tag ORDER BY CASE source WHEN 'system' THEN 1 ELSE 2 END)来标记每一行的优先级。

完成这步预处理之后，再对外层的结果集调用ARRAY_AGG(tag)。此时输入数据已经是去重后的状态，聚合函数里自然也就不需要再加DISTINCT了。试图在ARRAY_AGG内部嵌套UNNEST、DISTINCT和ARRAY构造来强行实现复杂去重，是一条弯路，不仅性能差，可读性也低，除非是为了兼容极老的数据库版本，否则不建议采用。

Greenplum 或旧版 PostgreSQL 不支持 DISTINCT 怎么办？

好在，大部分现代版本的Greenplum（6.x及以上）已经支持ARRAY_AGG(DISTINCT ...)了。但如果确实遇到了不支持的环境（比如Greenplum 5.x或非常老的PostgreSQL），也有一条可靠的备用路径：通过子查询先行去重。

具体写法如下：
SELECT user_id, ARRAY_AGG(tag) AS tags FROM (SELECT DISTINCT user_id, tag FROM user_tags) t GROUP BY user_id;

采用这种方法时，有两点需要牢记：

• 子查询的SELECT列表中必须包含外层GROUP BY的所有字段（本例中是user_id），否则外层分组时会报错。
• 如果还需要对结果排序，可以在子查询里加入ORDER BY user_id, tag。但要注意，PostgreSQL并不保证子查询中的排序能传递到外层的聚合函数中。最稳妥的做法，是在外层的ARRAY_AGG中再次明确指定ORDER BY tag。

这种写法的优点是兼容性极佳，几乎能在所有支持数组聚合的数据库上运行。但缺点也很明显：执行计划中多了一层嵌套循环，当数据量很大时，其I/O和内存开销会显著高于原生支持DISTINCT的聚合方式。