MERGE语句中使用LAG处理日期时出现数据类型不一致问题

可正常执行的查询语句 #

以下SELECT语句能正常运行，计算每行的前一行日期（首行用自身日期作为默认值）：

select 
    some_date, 
    lag(some_date,1,some_date) 
        over (order by some_date) prev_in_month, 
from test_table
where year_no = 2023 and month_no = 1

查询输出 #

...

注：some_date和prev_in_month列均为DATE类型。

执行失败的MERGE语句 #

尝试用MERGE更新表中prev_in_month列时触发错误：

merge into test_table
using (
  select 
    some_date, 
    lag(some_date,1,some_date) 
        over (order by some_date) prev_in_month, 
  from test_table
  where year_no = 2023 and month_no = 1
) source_table
on (test_table.some_date = source_table.some_date)
when matched then update
set test_table.prev_in_month = source_table.prev_in_month

错误信息 #

ORA-00932: inconsistent datatypes: expected NUMBER got DATE

错误指向LAG函数的第三个参数some_date；若将该参数替换为数字，则会触发反向错误：期望DATE类型却得到NUMBER类型。

临时解决方案 #

将日期转换为字符串类型执行LAG，再转换回DATE类型可正常运行：

merge into test_table
using (
  select 
    some_date, 
    to_date(lag(to_char(some_date,'YYYYMMDD'),1,to_char(some_date,'YYYYMMDD')) 
        over (order by some_date),'YYYYMMDD') prev_in_month, 
  from test_table
  where year_no = 2023 and month_no = 1
) source_table
on (test_table.some_date = source_table.some_date)
when matched then update
set test_table.prev_in_month = source_table.prev_in_month

更简洁的正确解决方案 #

避免使用LAG的第三个参数，改用COALESCE处理首行默认值，绕过类型推断异常：

merge into test_table
using (
  select 
    some_date, 
    coalesce(lag(some_date) over (order by some_date), some_date) prev_in_month
  from test_table
  where year_no = 2023 and month_no = 1
) source_table
on (test_table.some_date = source_table.some_date)
when matched then update
set test_table.prev_in_month = source_table.prev_in_month

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