Databricks SQL中datetime转字符串及表关联返回空结果问题

核心问题分析 #

1. 时间类型不匹配：datetime类型的aa.column1直接和字符串类型的cc.start_time/cc.end_time比较，Databricks无法正确识别时间关系，导致匹配失败。
2. Left Join后被过滤：原SQL中left join table3 cc后使用默认的inner join关联table2 bb，若cc无匹配行，cc.start_time/cc.end_time会为null，between null and null的条件会直接过滤掉这些行，相当于把left join变成了inner join效果。
3. 时间格式不兼容：转换时若未指定正确的字符串格式，转换后的时间会变为null，同样导致匹配失效。

具体修改方案 #

方案1：保留原Left Join意图（保留table1所有行）

将时间字符串转为datetime类型，同时把table2的关联改为left join，避免过滤cc未匹配的行：

select distinct 
    aa.column1,
    aa.column2,
    cc.column3
from table1 aa
left join table3 cc 
    on cc.shift_original_name = aa.shift
left join table2 bb 
    -- 替换为你的cc时间字符串实际格式，比如'yyyy/MM/dd HH:mm'
    on aa.column1 between to_timestamp(cc.start_time, 'yyyy-MM-dd HH:mm:ss') 
                      and to_timestamp(cc.end_time, 'yyyy-MM-dd HH:mm:ss')

方案2：只保留匹配到cc的行（用Inner Join）

若不需要table1的无匹配行，直接用inner join，同时转换时间类型：

select distinct 
    aa.column1,
    aa.column2,
    cc.column3
from table1 aa
inner join table3 cc 
    on cc.shift_original_name = aa.shift
inner join table2 bb 
    on aa.column1 between to_timestamp(cc.start_time, 'yyyy-MM-dd HH:mm:ss') 
                      and to_timestamp(cc.end_time, 'yyyy-MM-dd HH:mm:ss')

额外排查步骤 #

• 先确认aa.shift和cc.shift_original_name是否存在匹配值，执行以下SQL验证：

  select aa.shift, cc.shift_original_name 
  from table1 aa 
  left join table3 cc on cc.shift_original_name = aa.shift
  

• 检查cc.start_time的字符串格式，比如是'2024-05-20 10:00:00'还是'05/20/2024 10:00'，必须和to_timestamp的第二个参数完全一致，否则转换会返回null。
• 取一条aa.column1的具体值，手动在table3中查询对应shift的start/end时间是否包含该值，验证时间范围逻辑是否正确。

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