Snowflake关联带生效日期表时旧记录返回NULL的解决方法咨询

原SQL错误原因 #

原有逻辑是先对每个商品取全局最新的生效记录，再关联发票日期<=该生效日期的记录，会触发两个问题：

• 2021-02-01的发票日期早于商品100的全局最新生效日期2021-09-01，错误匹配到数量5
• 2021-09-10的发票日期晚于全局最新生效日期2021-09-01，无法匹配出现NULL值

修复方案 #

方案1：通用SQL写法（兼容所有支持窗口函数的数据库） #

将行号过滤逻辑放在关联之后，按发票日期+商品分组，取小于等于发票日期的最大生效日期对应的记录即可：

SELECT * FROM REPORT t1
LEFT JOIN ITEM t2 
  ON t1.ITEM = t2.ITEM 
  AND t2.EFFECTIVE_DATE <= t1.INVOICE_DATE
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY t1.INVOICE_DATE, t1.ITEM 
  ORDER BY t2.EFFECTIVE_DATE DESC
) = 1;

方案2：Snowflake专属ASOF JOIN写法（性能更优） #

Snowflake原生支持ASOF JOIN用于时间维度的近似匹配，自动匹配小于等于目标值的最大时间记录，代码更简洁：

SELECT * 
FROM REPORT t1
ASOF LEFT JOIN ITEM t2
  ON t1.ITEM = t2.ITEM
  AND t2.EFFECTIVE_DATE <= t1.INVOICE_DATE;

两种写法执行后都可以得到期望的结果：

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