多位置拼接值与reference location dimension映射匹配方案咨询

前置说明 #

• 位置维度表（表名记为dim_location）核心映射关系：
  • LocationID=1 对应城市 Manhattan
  • LocationID=2 对应城市 Yonkers
  • LocationID=3 对应城市 Buffalo
• 交易数据表（表名记为trans_data）的From City、To City字段支持多城市用/拼接的格式
• 映射规则：
  • From City取拼接值的第一个城市匹配LocationID，作为From City ID
  • To City取拼接值的最后一个城市匹配LocationID，作为To City ID
• 输出要求：结果表包含TransID、From City ID、To City ID三个字段

实现思路 #

1. 拆分交易表的城市字段，分别提取From City的第一个值、To City的最后一个值
2. 分别将两个拆分后的城市值与位置维度表关联，匹配对应的LocationID
3. 拼接输出要求的字段即可

代码实现（SQL版） #

MySQL 写法 #

利用SUBSTRING_INDEX函数实现拆分，n为正数取前n个分隔符左侧内容，n为负数取后n个分隔符右侧内容：

SELECT 
  t.TransID,
  d1.LocationID AS `From City ID`,
  d2.LocationID AS `To City ID`
FROM trans_data t
LEFT JOIN dim_location d1 
  ON d1.City = SUBSTRING_INDEX(t.`From City`, '/', 1)
LEFT JOIN dim_location d2 
  ON d2.City = SUBSTRING_INDEX(t.`To City`, '/', -1)

PostgreSQL 写法 #

利用SPLIT_PART和数组长度函数实现拆分：

SELECT 
  t."TransID",
  d1."LocationID" AS "From City ID",
  d2."LocationID" AS "To City ID"
FROM trans_data t
LEFT JOIN dim_location d1 
  ON d1."City" = SPLIT_PART(t."From City", '/', 1)
LEFT JOIN dim_location d2 
  ON d2."City" = SPLIT_PART(t."To City", '/', ARRAY_LENGTH(STRING_TO_ARRAY(t."To City", '/'), 1))

补充说明 #

如果拆分后的城市不存在于位置维度表中，关联后对应的ID会返回NULL，可根据业务需求补充过滤条件或者填充默认值。

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