string_agg去重如何实现？

Question

在使用 `STRING_AGG` 函数进行字符串拼接时，常遇到重复值被多次合并的问题。由于标准语法不支持直接去重，如何在 `STRING_AGG` 中实现去重成为常见技术难点。典型场景如：按部门分组拼接员工姓名时，因数据冗余导致姓名重复。虽然 `DISTINCT` 关键字在部分数据库（如 PostgreSQL）中可直接用于 `STRING_AGG(DISTINCT name, ',')`，但某些数据库（如 SQL Server）的 `STRING_AGG` 不支持 `DISTINCT`，需借助子查询或 `GROUP BY` 预先去重。因此，跨数据库平台实现 `STRING_AGG` 去重需结合 CTE、派生表或窗口函数等技巧，确保结果既去重又正确排序。

Answer 1

STRING_AGG 去重技术全解析：从基础到跨平台实践

1. 问题背景与典型场景

在现代数据库开发中，STRING_AGG 函数被广泛用于将多行数据聚合成单个字符串。然而，当源数据存在冗余或关联不当时，常导致拼接结果中出现重复值。

例如，在人力资源系统中，按部门分组拼接员工姓名时：

SELECT 
    dept_id,
    STRING_AGG(employee_name, ',') AS employees
FROM employee_dept_view
GROUP BY dept_id;

若视图 employee_dept_view 因历史数据变更或权限设计导致同一员工在某部门下出现多条记录，则其姓名将在结果中重复出现，影响数据可读性与下游处理逻辑。

2. 不同数据库对 DISTINCT 的支持差异

目前主流数据库对 STRING_AGG 是否支持 DISTINCT 存在明显分歧：

数据库	STRING_AGG 支持 DISTINCT?	示例语法
PostgreSQL	✅ 是	STRING_AGG(DISTINCT name, ',')
SQL Server (2017+)	❌ 否	需预处理去重
Oracle (12c+)	✅ 是（通过 LISTAGG + DISTINCT）	LISTAGG(DISTINCT name, ',')
MySQL	❌ 不支持（使用 GROUP_CONCAT(DISTINCT ...)）	非标准函数

3. 解决方案一：使用派生表预先去重

适用于 SQL Server 等不支持 DISTINCT 的平台。核心思路是先通过子查询或 CTE 实现唯一组合。

WITH Deduplicated AS (
    SELECT DISTINCT dept_id, employee_name
    FROM employee_dept_view
)
SELECT 
    dept_id,
    STRING_AGG(employee_name, ',') WITHIN GROUP (ORDER BY employee_name) AS employees
FROM Deduplicated
GROUP BY dept_id;

此方法确保每个“部门-员工”组合仅保留一行，从根本上避免重复。

4. 解决方案二：结合窗口函数进行精细化控制

当需要基于业务规则判断“有效”记录时（如取最新入职时间），可借助 ROW_NUMBER() 进行筛选：

WITH RankedEmployees AS (
    SELECT 
        dept_id,
        employee_name,
        ROW_NUMBER() OVER (
            PARTITION BY dept_id, employee_name 
            ORDER BY hire_date DESC
        ) as rn
    FROM employee_history
)
SELECT 
    dept_id,
    STRING_AGG(employee_name, ',') WITHIN GROUP (ORDER BY employee_name) AS employees
FROM RankedEmployees
WHERE rn = 1
GROUP BY dept_id;

该方式不仅去重，还能保证选取最具代表性的记录。

5. 跨平台兼容性设计模式

为实现 SQL 脚本在多种数据库间迁移，建议抽象去重逻辑为通用结构：

• 始终优先使用 CTE 或派生表完成去重
• 避免依赖特定方言的 DISTINCT 扩展
• 统一使用 WITHIN GROUP (ORDER BY ...) 显式定义排序
• 封装为视图或内联表值函数以提升复用性

6. 性能优化建议

大规模数据下，去重操作可能成为瓶颈。以下是关键调优点：

1. 在 DISTINCT 或 PARTITION BY 字段上建立复合索引
2. 限制参与聚合的数据范围（如按时间分区过滤）
3. 避免在高基数列上执行无限制的字符串拼接
4. 考虑异步物化汇总表替代实时计算
5. 监控执行计划，防止临时排序溢出至磁盘

7. 可视化流程：STRING_AGG 去重处理路径

graph TD
            A[原始数据集] --> B{是否含重复?}
            B -- 是 --> C[应用去重机制]
            C --> D[CTE/Derived Table]
            C --> E[Window Function Filtering]
            C --> F[GROUP BY 预聚合]
            D --> G[STRING_AGG 拼接]
            E --> G
            F --> G
            B -- 否 --> G
            G --> H[返回去重后字符串]

8. 实战案例：复杂业务去重逻辑

假设员工可能因岗位调动在多个部门兼职，但只允许在主职部门显示：

WITH PrimaryAssignments AS (
    SELECT 
        edv.dept_id,
        e.name AS employee_name,
        e.preferred_name,
        RANK() OVER (
            PARTITION BY e.employee_id 
            ORDER BY CASE WHEN edv.is_primary = 1 THEN 0 ELSE 1 END
        ) as primary_rank
    FROM employee_dept_view edv
    JOIN employees e ON edv.emp_id = e.id
    WHERE edv.status = 'ACTIVE'
)
SELECT 
    dept_id,
    STRING_AGG(
        COALESCE(preferred_name, employee_name), 
        ' | '
    ) WITHIN GROUP (ORDER BY employee_name) AS display_names
FROM PrimaryAssignments
WHERE primary_rank = 1
GROUP BY dept_id;

此查询融合了状态过滤、主岗识别和别名优先级，体现真实场景下的综合处理能力。