迁移笔记里写着“最多差 1”，实际差了 2 天

我们把一套 SQL Server 上的报表逻辑搬到了 Databricks，用 dbt 重写。它算的是车间每天要看的几个数：交期、提前期、延误天数、有没有准时。

大部分改动很机械，换个函数名就完事。麻烦的是另一种：能跑通，数字看着也合理，就是跟原来差一点。下面是我们碰到的几处，其中一处是我写这篇时回头核对才发现的。

分层

bronze 由上游的采集任务写入，dbt 从 silver 开始接手。silver 做清洗、去重和字段统一，按业务域分目录（订单、销售、单元、扫描、员工），物化成表；gold 做跨域的 join，也是表；marts 是给看板用的 KPI，物化成视图。

schema 命名改过一处。dbt 默认的规则是 <target schema>_<自定义名>，dev 环境我们照用，每个人的模型落在自己名字开头的 schema 里（比如 jack_silver），谁也不覆盖谁。prod 用改写过的 generate_schema_name，直接叫 silver、gold，marts 按子目录拆开，一个业务域一个 schema，看板那边按域找表。

换个写法就行的

下面这些只是写法不同，结果一样：

SQL Server	Databricks
`GETDATE()`	`current_date()`（后面单独说）
`DATENAME(dw, d)`	`date_format(d, 'EEEE')`
`DATEADD(day, n, d)`	`date_add(d, n)`
`col LIKE '[A-Za-z][0-9]'`	`col RLIKE '[A-Za-z][0-9]'`
`"Column Name"`	`Column Name`

LIKE 那行要小心。SQL Server 的 LIKE 认 [A-Z] 这种字符类，Spark 的不认。原样搬过去不报错，就是永远匹配不上。

DATEDIFF 的参数顺序是反的

sql

-- SQL Server：先写开始，再写结束
DATEDIFF(day, start_date, finish_date)

-- Databricks：先写结束，再写开始
datediff(finish_date, start_date)

原样搬过去也不报错，符号反了而已，提前期全成了负数。这种错好歹一眼就能看出来。

周数差 1，落到工作日上是 2

提前期要扣掉周末，SQL Server 上是这么写的：

sql

DATEDIFF(day, start_date, finish_date) - DATEDIFF(week, start_date, finish_date) * 2

DATEDIFF(week, …) 数的其实是跨过了几个周日（跟 SET DATEFIRST 无关），也就是跨过了几个周末，所以这个式子算出来是准确的工作日数。起止都是工作日、跨度 0 到 14 天的组合，我全部枚举过一遍，没有一个错。

迁移后它变成了：

sql

datediff(finish_date, start_date) - floor(datediff(finish_date, start_date) / 7) * 2

floor(天数 / 7) 数的是完整的七天。迁移笔记里说两者在周边界上最多差 1，行为“接近但不完全一致”。周数确实只差 1，可乘以 2 以后，工作日就差了 2 天。写这篇时我重新算了一遍：

开始	结束	实际工作日	SQL Server 公式	迁移后的公式
周一 9/14	周一 9/21	5	5	5
周三 9/16	周一 9/21	3	3	5
周四 9/17	周一 9/21	2	2	4
周五 9/18	周一 9/21	1	1	3
周五 9/18	周一 9/28	6	6	8

55 种组合里有 20 种多算了 2 天，只要区间跨过一个周末又不满七天就会中。周五开工、周一完工，本来 1 天，算出来 3 天。提前期和延误天数都被高估了。

要在 Spark 里拿到跟 SQL Server 一样的周数，可以找一个固定的周日当锚点，分别算两个日期落在第几周，再相减：

sql

floor(datediff(finish_date, date'2000-01-02') / 7)
  - floor(datediff(start_date, date'2000-01-02') / 7)   -- 2000-01-02 是周日

如果起止一定是工作日，写成 datediff(date_trunc('week', finish_date), date_trunc('week', start_date)) / 7 也行。我拿 4 万组日期跟 SQL Server 的 DATEDIFF(week) 对过：锚点写法全部一致，date_trunc 写法在起止都是工作日时一致。

所以看到“最多差 1”这种说法，得再算一步：落到最后的指标上是多少。

Spark 没有 time 类型

SQL Server 里常用 CAST(ts AS time)，比如判断一次扫描是不是落在某个班次里。Spark SQL 没有 time 类型，我们改成 date_format(ts, 'HH:mm:ss') 再比字符串：

sql

date_format(scan_ts, 'HH:mm:ss') BETWEEN '06:00:00' AND '14:30:00'

这能成立，是因为 HH:mm:ss 定长、补零、24 小时制，字典序刚好就是时间顺序。哪天有人写成 H:mm，或者用了 12 小时制，'9:00' 就会排到 '10:00' 后面，比较结果悄悄就错了。

current_date() 按哪个时区算

is_on_time 这类字段要跟今天比。原来用的 GETDATE() 取数据库服务器的本地时间，Databricks 的 current_date() 按会话时区取。会话时区要是默认的 UTC，在加州，夏令时期间下午五点一过，“今天”就已经是明天了。

另外，这些模型物化成表，今天是哪天，在每次 build 那一刻就定了。看板上的准时率，其实是上次 build 那一刻的准时率。

自定义函数：先做 UDF，后改 macro

原来有个标量函数，从 1000=…;1010=… 这种字符串里按编号取值，有两个模型在用。迁移分了两步：先在 Databricks 上建个 UDF 顶着，后来改写成 dbt macro，展开就是一个正则：

sql

{% macro kv_value(field, key) %}
    nullif(trim(regexp_extract({{ field }}, '(?:^|;){{ key }}[=:]([^;]*)', 1)), '')
{% endmacro %}

改成 macro，就不用在每个环境单独部署、授权一个函数，展开后的 SQL 在 dbt compile 的输出里也看得见。代价是这段正则抄进了每个用它的模型，改一次得把下游全部重新 build。

迁移时就该做的三件事

“最多差 1”能从迁移笔记一路留到现在的模型里，说明光看结果合不合理不够。要是重来，我会在迁移时就做这三件事：

每个改写过的表达式列一张边界输入表：周五到周一、跨月、跨年、午夜前后。两边各跑一遍，逐行比。上面那 55 种组合就是这么比出来的。
按月比汇总：新旧两套各算每月的平均提前期和准时率，差得超过阈值就停下来查。
按主键逐行对照：新旧结果按主键 join，有字段不相等的行全部列出来。