如何将含CROSS APPLY的SQL Server查询转换为Spark SQL

Spark SQL不支持SQL Server的CROSS APPLY和string_split函数，我们可以通过split()+posexplode()的组合实现等价逻辑，同时保证地址拆分后的顺序准确性。以下是修改后的完整Spark SQL代码：

WITH Benef as (
    SELECT DISTINCT 
        IdBeneficiaireSource
        ,Adress
    FROM 
        UPExpBeneficiaryStaging 
)

-------- Split Adress --------

,AdresseBenefTemp1 as (
     SELECT 
         IdBeneficiaireSource
         ,REPLACE(REPLACE(Adress, chr(10), '|'), chr(13), '|') as AdresseV2
     FROM 
        Benef
)

,AdresseBenefTemp2 as (
     SELECT 
        IdBeneficiaireSource
        ,Adresse
        ,pos + 1 as LigneAdresse
     FROM 
         AdresseBenefTemp1
         -- 用posexplode替代CROSS APPLY string_split，同时获取元素位置
         lateral view posexplode(split(AdresseV2, '\\|')) as pos, Adresse
     -- 可选：过滤拆分后为空的地址行，避免无效数据
     WHERE trim(Adresse) != ''
)
  
,AdresseBenefFinal as (
    SELECT DISTINCT
         a.IdBeneficiaireSource
         ,b.Adresse as Adresse_1
         ,c.Adresse as Adresse_2
         ,d.Adresse as Adresse_3
    FROM 
         AdresseBenefTemp2 as a 
         LEFT JOIN AdresseBenefTemp2 as b on b.IdBeneficiaireSource = a.IdBeneficiaireSource AND b.LigneAdresse = 1
         LEFT JOIN AdresseBenefTemp2 as c on c.IdBeneficiaireSource = a.IdBeneficiaireSource AND c.LigneAdresse = 2
         LEFT JOIN AdresseBenefTemp2 as d on d.IdBeneficiaireSource = a.IdBeneficiaireSource AND d.LigneAdresse = 3
)

-------------------------------

SELECT 
    IdBeneficiaireSource
    ,Adresse_1
    ,Adresse_2
    ,Adresse_3
FROM 
    AdresseBenefFinal

关键修改说明 #

• 替换CROSS APPLY string_split：Spark SQL中用lateral view posexplode(split(字符串, 分隔符))实现多行拆分，split将字符串转为数组，posexplode把数组拆分为多行并返回每个元素的位置索引
• 行号生成优化：原SQL用ROW_NUMBER() OVER(...) ORDER BY (SELECT NULL)的排序逻辑不确定，改用pos + 1（pos是元素在数组中的位置，从0开始）可保证行号和地址拆分顺序完全一致
• 特殊字符处理：Spark中用chr(10)和chr(13)对应SQL Server的char(10)和char(13)，也可直接用'\n'和'\r'替代
• 可选过滤空行：添加WHERE trim(Adresse) != ''可过滤拆分后为空的地址条目，避免生成无效的空地址字段

内容的提问来源于stack exchange，提问作者Salah K.