在Databricks Spark SQL中替代SQL Server FOR XML PATH的方案问询

问题背景 #

使用Databricks DBR 10.4 LTS（Spark 3.2.1）迁移SQL Server查询时，因Spark SQL不支持FOR XML PATH函数报错，原查询逻辑为：对每个UserID，将关联的UserStopCountry表中Country字段用, 拼接，去除开头多余分隔符，无数据时返回空字符串。

解决方案 #

Spark SQL可通过collect_list收集分组字段值，搭配concat_ws完成字符串拼接，结合COALESCE处理空值，完全等价原逻辑。

完整替代代码 #

DROP TABLE if exists UserCountry;
CREATE TABLE if not exists UserCountry (
  UserID INT,
  Country VARCHAR(5000)
);
INSERT INTO UserCountry
SELECT 
    L.UserID AS UserID,
    COALESCE(
        concat_ws(', ', collect_list(LC.Country)),
        ''
    ) AS Country
FROM 
    LK_ETLRunUserID L
LEFT JOIN 
    UserStopCountry LC ON L.UserID = LC.UserID
GROUP BY 
    L.UserID

逻辑说明 #

1. LEFT JOIN：保留LK_ETLRunUserID中所有UserID，即使UserStopCountry无对应数据
2. GROUP BY L.UserID：按用户ID分组，匹配原SQL的关联筛选逻辑
3. collect_list(LC.Country)：收集每个分组下的所有Country值生成数组
4. concat_ws(', ', ...)：将数组元素用, 拼接成字符串，自动避免前置分隔符
5. COALESCE(..., '')：无对应Country数据时，将null转为空字符串，与原逻辑一致

结果验证 #

针对给定的UserStopCountry数据：

执行代码后，UserCountry表结果与期望完全一致：

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