Databricks中LEFT JOIN结合CASE WHEN语句无法得到正确结果的问题排查

我来帮你拆解下这个问题的核心原因，以及对应的解决思路：

你的代码逻辑回顾 #

你在Databricks中尝试通过LEFT JOIN关联两个数据集，并用CASE WHEN处理ts_primarysecondaryfocus字段，使用的代码如下：

;WITH CTE1 AS (
 SELECT *,ROW_NUMBER()OVER(ORDER BY ts_primarysecondaryfocus)RowNum
 FROM dataverse.accountv41
 ),CTE2 AS (
 SELECT *,ROW_NUMBER()OVER(ORDER BY ts_primarysecondaryfocus)RowNum
 FROM dataverse.optionsetmetadatav3
 )
 SELECT C1.SinkCreatedOn,C1.SinkModifiedOn,C1.statecode,C1.statuscode
 ,CASE WHEN C1.ts_primarysecondaryfocus <> IFNULL(C2.ts_primarysecondaryfocus,'') THEN C2.ts_primarysecondaryfocus ELSE C1.ts_primarysecondaryfocus END AS ts_primarysecondaryfocus
 FROM CTE1 C1
 LEFT JOIN CTE2 C2 ON C1.RowNum = C2.RowNum

问题根源：无意义的关联键 #

你用ROW_NUMBER()OVER(ORDER BY ts_primarysecondaryfocus)生成的RowNum作为关联条件，这是完全错误的逻辑：

• accountv41中ts_primarysecondaryfocus字段绝大多数是空值，排序后生成的RowNum只是空值行的顺序号，没有任何业务关联意义；
• optionsetmetadatav3中该字段有具体有效值，排序后的RowNum是按这些值的字符顺序生成的，和accountv41的RowNum没有任何对应关系；
• 这种关联方式导致LEFT JOIN时，accountv41的空值行根本无法匹配到optionsetmetadatav3的有效值行，最终C2的字段全为空，CASE WHEN自然无法生效。

样本数据验证 #

结合你提供的样本数据来看：

• accountv41前8行的ts_primarysecondaryfocus都是空，生成的RowNum为1-8；
• optionsetmetadatav3中有效值行（donald、TBC、Tier1等）按字符排序后，对应的RowNum是1-4；
• 当用RowNum关联时，accountv41的RowNum1-4会匹配到optionsetmetadatav3的RowNum1-4，但这些行本身是空值，而optionsetmetadatav3的RowNum5也是空值，最终关联后所有行的C2字段还是空，CASE WHEN完全起不到替换作用。

解决方案 #

你需要先明确两个表之间正确的业务关联键，而不是用无意义的行号：

1. 如果optionsetmetadatav3是选项集映射表（存储编码与显示值的对应关系），应该用accountv41中的编码字段（比如假设存在ts_primarysecondaryfocus_code）关联optionsetmetadatav3的对应编码字段，示例代码如下：

   SELECT 
     C1.SinkCreatedOn,
     C1.SinkModifiedOn,
     C1.statecode,
     C1.statuscode,
     CASE 
       WHEN C1.ts_primarysecondaryfocus IS NULL OR C1.ts_primarysecondaryfocus = '' 
       THEN C2.ts_primarysecondaryfocus 
       ELSE C1.ts_primarysecondaryfocus 
     END AS ts_primarysecondaryfocus
   FROM dataverse.accountv41 C1
   LEFT JOIN dataverse.optionsetmetadatav3 C2 
     ON C1.ts_primarysecondaryfocus_code = C2.code -- 替换为实际业务关联键
   

2. 如果你的需求是给accountv41的空值行填充optionsetmetadatav3的有效值（比如按顺序或随机填充），需要先对optionsetmetadatav3的有效值生成序列，再通过窗口函数和交叉连接的方式匹配accountv41的空值行，这一步需要明确具体的业务规则。

总结 #

你的核心问题是使用了没有业务关联的行号作为关联条件，导致匹配逻辑完全失效。先梳理清楚两个表的业务关系，找到正确的关联键，就能得到预期的输出结果。

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