时间戳

首页 > 解决方案 > 递归 CTE 有性能问题，需要建议来优化查询

sql - 递归 CTE 有性能问题，需要建议来优化查询

问题描述

我想从日志表中获取前 5 条记录，其中“批准日期”像 NULL 一样更改为值，反之亦然。日期值无关紧要，但顺序很重要。

ApprovedDate            ChangeDate                         changeByUser
NULL                    2019-12-09 06:40:15.437              vaisakh
NULL                    2019-12-09 06:42:31.563             vaisakh
NULL                    2019-12-09 06:42:33.140             vaisakh
NULL                    2019-12-09 07:03:54.660              vaisakh
2019-12-09 07:05:29.800 2019-12-09 07:05:29.817              vaisakh
2019-12-09 07:05:29.800 2019-12-09 07:05:38.707              vaisakh
NULL                    2019-12-09 07:09:33.160               vaisakh
NULL                    2019-12-09 07:09:42.440               vaisakh
NULL                    2019-12-09 09:38:19.757             vaisakh
2019-12-09 09:41:42.977 2019-12-09 09:41:43.243             Raveendran

在这种情况下，我想要第一条记录和第 5 条记录（有人批准了数据，这就是为什么一个值），然后第 7 条记录值为空，有人拒绝了它。

我尝试使用递归 CTE 它正在工作，但对于大型记录巨大的性能问题


DECLARE @today DATETIME = GETDATE();

with RESULT (CIPApprovedDate,ChangeDate,changeByUser,legalId,depth)AS(

  SELECT TOP 1 CIPApprovedDate, ChangeDate, changeByUser, legalId,1
  FROM LegalEntityExtensionLog
  WHERE legalId= 2688518
  ORDER BY ChangeDate ASC

  union ALL

  select 

  L.CIPApprovedDate, L.ChangeDate, L.changeByUser, L.legalId,ct.depth+1
  FROM LegalEntityExtensionLog L INNER JOIN Result CT
 on L.legalId=CT.legalId AND L.changeDate>CT.changeDate

 AND ISNULL(L.CIPApprovedDate,@today) <> ISNULL(CT.CIPApprovedDate,@today) 

)select * from Log  where  ChangeDate in(select MIN(ChangeDate) from Result group by depth)

标签： sqlsql-servertsqlwindow-functionsgaps-and-islands

解决方案

您可以像这样提取转换记录：

select ApprovedDate, ChangeDate, changeByUser
from (
    select 
        l.*,
        lag(ApprovedDate) ver(partition by LegalId order by ChangeDate) lagApprovedDate
    from LegalEntityExtensionLog l
) t
where 
    (lagApprovedDate is null and ApprovedDate is not null)
    or (lagApprovedDate is not null and ApprovedDate is null)

这将显示ApprovedDate从 a 转换null为非null值（或相反）的记录。

推荐阅读