如何在SQL Server中实现PySpark的collect_set功能？

针对你的需求，由于SQL Server 2019的STRING_AGG不支持直接添加DISTINCT（该特性在SQL Server 2022才引入），且全局提前去重会影响金额总和计算，你可以通过拆分计算逻辑的方式实现目标：分别计算金额总和、生成去重后的old_id聚合列表，再将结果关联。

完整SQL代码 #

假设你的数据表名为your_table，执行以下语句即可得到预期结果：

WITH amount_total_cte AS (
    -- 计算每个new_id的总金额，保留所有原始数据参与求和
    SELECT 
        new_id,
        SUM(amount) AS amount_total
    FROM your_table
    GROUP BY new_id
),
distinct_old_id_list_cte AS (
    -- 先对new_id+old_id去重，再生成无重复的聚合列表
    SELECT 
        new_id,
        STRING_AGG(CONVERT(NVARCHAR(MAX), ISNULL(old_id, 'N/A')), ',') AS old_id_list
    FROM (
        SELECT DISTINCT new_id, old_id
        FROM your_table
    ) AS distinct_records
    GROUP BY new_id
)
-- 关联两个CTE的结果，得到最终输出
SELECT 
    a.new_id,
    d.old_id_list,
    a.amount_total
FROM amount_total_cte a
INNER JOIN distinct_old_id_list_cte d 
    ON a.new_id = d.new_id;

逻辑说明 #

1. 金额总和计算：第一个CTEamount_total_cte直接对原始数据按new_id分组求和，确保所有行的amount都被计入，不会因为去重丢失数据。
2. 去重聚合列表：第二个CTEdistinct_old_id_list_cte先通过子查询获取每个new_id对应的唯一old_id，再用STRING_AGG生成无重复的逗号分隔列表。
3. 结果关联：通过new_id将两个CTE的结果关联，同时得到正确的金额总和和去重后的old_id列表。

执行结果 #

运行上述代码后，你将得到预期输出：

内容的提问来源于stack exchange，提问作者Sagar Moghe