DB2迁移Snowflake：datediff与Days日期差计算结果不一致问题

问题场景 #

从DB2迁移到Snowflake后，执行逻辑近似的查询时，部分数据的日期差统计结果相差1（比如DB2返回619/340，Snowflake返回620/341），多数数据匹配。核心差异点在日期差计算和平均值取整环节：

• DB2用 Days(p2.datepointer) - Days(p1.stopdate) 计算日期差，再取平均值转整数
• Snowflake用 datediff(day, p1.stopdate, p2.datepointer) 计算日期差，同样取平均值转整数

差异根源 #

1. 日期差计算的时间精度处理 #

DB2的Days()函数只提取日期的整数儒略日，完全忽略时间部分——不管两个时间点的时分秒差多大，只要日期相同，差就是0；日期差一天，差就是1。

Snowflake的DATEDIFF(day, start, end)默认会考虑时间部分，但核心逻辑是统计跨越的午夜边界数，这部分其实和DB2的日期差逻辑多数情况一致。真正的关键差异在第二点：

2. 平均值转整数的规则 #

• DB2中，CAST(avg(...) AS INTEGER)会直接截断小数部分（向下取整），比如平均差为619.9时，结果是619。
• Snowflake中，CAST(avg(...) AS INTEGER)会对小数部分四舍五入，比如平均差为619.9时，结果是620。

当某组数据的平均日期差接近整数但存在小数部分时，两种取整规则就会导致结果相差1，正好匹配你看到的数值差异。

修复方案 #

要让Snowflake结果和DB2完全对齐，需要同时调整日期差计算逻辑和取整规则：

步骤1：统一日期差计算，忽略时间部分 #

将Snowflake的日期差计算改为仅基于日期部分，完全忽略时间，和DB2的Days()逻辑对齐：

-- 两种写法效果一致，选其一即可
datediff(day, CAST(p1.stopdate AS DATE), CAST(p2.datepointer AS DATE))
-- 或者
datediff(day, DATE_TRUNC('day', p1.stopdate), DATE_TRUNC('day', p2.datepointer))

步骤2：统一取整规则为截断小数 #

用FLOOR()或TRUNC()函数实现DB2的截断取整逻辑，替代Snowflake默认的四舍五入：

-- 用FLOOR截断
CAST(FLOOR(avg(日期差表达式)) AS INTEGER)
-- 或者用TRUNC
CAST(TRUNC(avg(日期差表达式)) AS INTEGER)

完整修复后的Snowflake查询 #

Select 
'1' as fromPattern, Cast(p1.patternnumber as varchar(20)) as fromCode,
'2' as toPattern, Cast(p2.patternnumber as varchar(20)) as toCode,
Cast(Cast(count(*) as Integer) as varchar(20)) as num,
Cast(Cast(FLOOR(avg(datediff(day, CAST(p1.stopdate AS DATE), CAST(p2.datepointer AS DATE)))) as Integer) as varchar(20)) as avgGap 
from TenMillion_20220731.NS10386_147_20231108_235900_717 p1 
join TenMillion_20220731.NS10386_149_20231108_235900_717 p2 on p1.personid=p2.personid 
group by p1.patternnumber, p2.patternnumber 
order by fromPattern, toPattern, fromCode, toCode 

验证建议 #

抽取差异组的明细数据，手动计算每条记录的日期差和平均值，对比调整后的Snowflake结果与DB2结果，确认是否完全一致。

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