在Synapse数据仓库中拆分分隔列并关联表获取对应ID

需求说明 #

需要处理BTS_Test.Users表中以分号;分隔的用户字段，关联BTS_Test.Student表获取对应学生信息，同时适配百万级数据的高效查询需求。

解决方案脚本 #

1. 拆分用户字段并关联学生表（行级拆分结果） #

该脚本将每行的分隔用户拆分为独立行，并关联获取学生ID：

SELECT 
    u.[Date],
    TRIM(split_val.value) AS UserName,
    st.ID AS StudentID
FROM [BTS_Test].[Users] u
-- 拆分分号分隔的用户字符串
CROSS APPLY STRING_SPLIT(u.Users, ';') split_val
-- 关联学生表，注意去除拆分后字符串的前后空格
JOIN [BTS_Test].[Student] st 
    ON TRIM(split_val.value) = st.StudentName
ORDER BY u.[Date], st.ID;

2. 按日期聚合关联结果（合并为单行） #

如果需要将同一日期的所有学生ID/姓名合并为分隔字符串（匹配常见的预期列效果）：

SELECT 
    u.[Date],
    -- 聚合学生ID为逗号分隔字符串
    STRING_AGG(st.ID, ', ') AS ExpectedStudentIDs,
    -- 聚合学生姓名为分号分隔字符串
    STRING_AGG(st.StudentName, '; ') AS ExpectedUserNames
FROM [BTS_Test].[Users] u
CROSS APPLY STRING_SPLIT(u.Users, ';') split_val
JOIN [BTS_Test].[Student] st 
    ON TRIM(split_val.value) = st.StudentName
GROUP BY u.[Date]
ORDER BY u.[Date];

性能优化说明 #

• 使用Synapse内置的STRING_SPLIT函数，针对分布式架构做了优化，适合百万级数据处理
• Student表采用REPLICATE分布策略，关联时无需跨节点传输数据，大幅提升查询效率
• TRIM函数处理拆分后字符串的前后空格，避免因格式问题导致的关联失败

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