如何优化填充订单表缺失日期的递归SQL查询？有无替代方案

问题背景 #

需求：填充orders表中的缺失日期值，使日期从表中最小日期到最大日期连续，缺失日期的order_value沿用最近的上一个有效日期的值。

表结构与测试数据 #

create table orders(order_date date, order_value int)

insert into orders values('2022-11-01',100),('2022-11-04 ',200),('2022-11-08',300)

预期输出 #

order_date | order_value
-----------------------
2022-11-01 | 100
2022-11-02 | 100
2022-11-03 | 100
2022-11-04 | 200
2022-11-05 | 200
2022-11-06 | 200
2022-11-07 | 200
2022-11-08 | 300

现有递归查询方案 #

with cte as (
  select min(order_date) [min_date], MAX(order_date) [max_date]
  FROM orders
),  cte2 AS(
  SELECT min_date [date]
  FROM cte
  UNION ALL
  SELECT dateadd(day,1,date) [date]
  FROM cte2
  WHERE date < (SELECT max_date FROM cte)
 ), cte3 as(
  select date [order_date], order_value
  FROM cte2 
  LEFT JOIN orders on date = order_date
 )
 SELECT order_date,
        FIRST_VALUE(order_value) IGNORE NULLS
        OVER(ORDER BY order_date desc ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) [order_value]
 FROM cte3

咨询问题 #

是否有解决该问题的替代方案，或优化上述递归查询的方法？

替代方案与优化方法 #

一、优化现有递归查询 #

1. 避免重复子查询，传递max_date

原递归CTE中每次迭代都会重复查询cte的max_date，可以将max_date直接带入递归CTE，减少不必要的子查询开销：

with cte as (
  select min(order_date) as min_date, max(order_date) as max_date
  from orders
), cte2 as (
  select min_date as date, max_date
  from cte
  union all
  select dateadd(day, 1, date), max_date
  from cte2
  where date < max_date
), cte3 as (
  select cte2.date as order_date, o.order_value
  from cte2
  left join orders o on cte2.date = o.order_date
)
select 
  order_date,
  first_value(order_value) ignore nulls
  over(order by order_date desc rows between current row and unbounded following) as order_value
from cte3
option (maxrecursion 0) -- 日期跨度超过100天时必须添加，解除递归深度限制

2. 合并CTE简化逻辑

可以去掉中间的cte3，将左连接逻辑直接整合到最终查询中，减少CTE层级：

with cte as (
  select min(order_date) as min_date, max(order_date) as max_date
  from orders
), cte2 as (
  select min_date as date, max_date
  from cte
  union all
  select dateadd(day, 1, date), max_date
  from cte2
  where date < max_date
)
select 
  cte2.date as order_date,
  first_value(o.order_value) ignore nulls
  over(order by cte2.date desc rows between current row and unbounded following) as order_value
from cte2
left join orders o on cte2.date = o.order_date
option (maxrecursion 0)

二、非递归替代方案 #

1. 利用日期维度表（推荐生产环境）

如果系统中存在预先构建的日期维度表（包含连续日期），直接关联查询的性能远优于递归：

-- 假设存在日期维度表date_dim，包含date列
select 
  d.date as order_date,
  first_value(o.order_value) ignore nulls
  over(order by d.date desc rows between current row and unbounded following) as order_value
from date_dim d
left join orders o on d.date = o.order_date
where d.date between (select min(order_date) from orders) and (select max(order_date) from orders)
order by d.date

若没有现成的日期维度表，可临时利用系统表生成连续日期（适用于跨度较小的场景）：

with date_range as (
  select top (datediff(day, (select min(order_date) from orders), (select max(order_date) from orders)) + 1)
    dateadd(day, row_number() over(order by (select null)) - 1, (select min(order_date) from orders)) as date
  from sys.all_objects
)
select 
  dr.date as order_date,
  first_value(o.order_value) ignore nulls
  over(order by dr.date desc rows between current row and unbounded following) as order_value
from date_range dr
left join orders o on dr.date = o.order_date
order by dr.date

2. 分组填充法（更直观的填充逻辑）

通过窗口函数标记非空值的分组，再对每个分组取唯一的非空值作为填充值，无需倒序排序，逻辑更直观：

with cte as (
  select min(order_date) as min_date, max(order_date) as max_date
  from orders
), cte2 as (
  select min_date as date, max_date
  from cte
  union all
  select dateadd(day, 1, date), max_date
  from cte2
  where date < max_date
), cte3 as (
  select 
    cte2.date as order_date,
    o.order_value,
    -- 累加非空值数量生成分组，每个分组对应一个有效order_value
    sum(case when o.order_value is not null then 1 else 0 end) over(order by cte2.date) as grp
  from cte2
  left join orders o on cte2.date = o.order_date
)
select 
  order_date,
  max(order_value) over(partition by grp) as order_value
from cte3
order by order_date

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