SQL Server 有什么方法可以优化递归sql查询吗？

k5ifujac 于 2022-12-03 发布在其他

关注(0)|答案(2)|浏览(181)

我需要在orders表中填充缺少的日期值。DDL：

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

我用下面列出的递归查询解决了mssql中的问题。

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

有没有其他方法可以解决这个问题或者优化递归查询？
先谢谢你。

sql-server

来源：https://stackoverflow.com/questions/74617449/is-there-any-way-to-optimize-the-recursive-sql-query

2条答案

按热度按时间

p1tboqfb1#

正如在注解中提到的，由于您使用的是SQL Server 2022，因此可以在此处使用GENERATE_SERIES，这将比递归公用表表达式（rCTE）的性能高得多。在以前的版本中，您可以使用（内联）计数或日历表。

WITH Dates AS(
    SELECT MIN(order_date) AS MinDate,
           MAX(order_date) AS MaxDate
    FROM dbo.orders),
DateSeries AS(
    SELECT DATEADD(DAY,GS.value,D.MinDate) AS Date
    FROM Dates D
         CROSS APPLY GENERATE_SERIES(0,DATEDIFF(DAY,MinDate,MaxDate),1) GS)
SELECT DS.Date,
       FIRST_VALUE(O.order_value) IGNORE NULLS OVER (ORDER BY DS.Date desc ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS [order_value]
FROM DateSeries DS
     LEFT JOIN dbo.orders O ON DS.Date = O.order_date
ORDER BY DS.Date;

db<>fiddle

赞(0）回复(0）举报 2022-12-03

ngynwnxp2#

日历是最有用的，但另一个选项是临时数字表

示例

Select Order_Date = coalesce(dateadd(DAY,N-1,Order_Date),order_date)
      ,Order_Value
 From ( Select *,NDays = datediff(DAY,order_date,lead(order_date,1) over (order by order_date)) From Orders ) A
 left Join ( Select Top 1000 N=Row_Number() Over (Order By (Select NULL)) From master..spt_values n1, master..spt_values n2 ) B
   on N<=NDays
 Order by Order_Date

结果

赞(0）回复(0）举报 2022-12-03

我来回答

SQL Server 有什么方法可以优化递归sql查询吗？

2条答案

相关问题

热门标签

最新问答