时间戳

首页 > 解决方案 > 过滤子查询性能问题

sql - 过滤子查询性能问题

问题描述

我正在尝试检查每天的里程值是否必须大于前一天。在表中有很多 vleets 所以我尝试为每一行创建一个数字，并按每个 vloot 的日期对其进行排序，并将里程数与临时表进行比较。

这是我的查询，但需要很长时间才能处理并且没有工作。有人可以帮忙吗？

+----+-----------+-----------+------+
| Fleet|   Date   |   Mileage| Distance|
+----+-----------+-----------+------+
| 3143 | 4/12/2016 | 5/18/2016 | XX   |
| 3254 | 4/1/2016  | 4/4/2016  | YY   |
| 2548 | 5/23/2016 | 5/28/2016 | YY   |
| 3254 | 9/21/2016 | 9/26/2016 | XX   |
| 2548 | 8/7/2016  | 8/9/2016  | YY   |
| 3143 | 8/2/2015  | 8/12/2015 | YY   |
| 2548 | 4/12/2015 | 4/18/2015 | YY   |
+----+-----------+-----------+------+


Create table #temp (
    rownumber INT,
    vloot nvarchar(20),
    datum DATETIMEOFFSET,
    km decimal(21,2),
    distance decimal(21,2)
)



 
Insert into #temp
Select ROW_NUMBER() OVER(PARTITION BY  [EqmmspvEqmId]
                            ORDER BY MIN(DATEDIFF(DD, [EqmmspvDate],GETDATE()))   DESC)  as Rownumber,
                             [EqmmspvEqmId],
                             [EqmmspvDate],
                             [EqmmspvValue],
                            [EqmmspvDifference]
                             

  FROM [ULTIMO].[dba].[EquipmentMspValue]
       WHERE  EqmmspvRecStatus = 2  
     AND [EqmmspvMspId] = 'DEFAULT'
  Group by [EqmmspvEqmId],EqmmspvDate, [EqmmspvValue], [EqmmspvDifference]
  
 
  
  Select ROW_NUMBER() OVER(PARTITION BY  [EqmmspvEqmId]
                            ORDER BY MIN(DATEDIFF(DD, [EqmmspvDate],GETDATE()))   DESC) + 1   as  Rownumber,
                             [EqmmspvEqmId],
                             [EqmmspvDate],
                             [EqmmspvValue],
                            [EqmmspvDifference] 
                             

  FROM [ULTIMO].[dba].[EquipmentMspValue] as e 
  inner join #temp as t on t.vloot = e.[EqmmspvEqmId]   
  --and Rownumber   = t.rownumber  
   
  WHERE 
  
  e.[EqmmspvValue]  >  ALL (Select t.km FROM #temp as t
                              WHERE Rownumber   = t.rownumber  
) 

AND 
      e.[EqmmspvEqmId] = '00000000000079'
AND
      e.EqmmspvRecStatus = 2  
AND 
      e.[EqmmspvMspId] = 'DEFAULT'
 
    
    GROUP BY [EqmmspvEqmId],EqmmspvDate, [EqmmspvValue], [EqmmspvDifference]

标签： sqlsql-server

解决方案

我找到了解决方案，

DROP TABLE #temp
CREATE TABLE #temp (
    rownumber INT,
    vloot nvarchar(20),
    datum DATETIMEOFFSET,
    km int,
    distance decimal(21,2)
)

Insert into #temp
Select ROW_NUMBER() OVER(PARTITION BY  [EqmmspvEqmId]
                            ORDER BY MIN(DATEDIFF(DD, [EqmmspvDate],GETDATE()))   DESC)  as Rownumber,
                             [EqmmspvEqmId],
                             [EqmmspvDate],
                             [EqmmspvValue],
                            [EqmmspvDifference]
                             

  FROM [ULTIMO].[dba].[EquipmentMspValue]
       WHERE  EqmmspvRecStatus = 2  
     AND [EqmmspvMspId] = 'DEFAULT'
Group by [EqmmspvEqmId],EqmmspvDate, [EqmmspvValue], [EqmmspvDifference]
  
 ------------------------------------------------------------------------------------------------------
SELECT
e.[EqmmspvEqmId], 
e.[EqmmspvDate],
e.[EqmmspvValue] ,
e.[EqmmspvDifference],
e.Rownumber

FROM
(
  
 
 SELECT 
 
 ROW_NUMBER() OVER(PARTITION BY  [EqmmspvEqmId]
                            ORDER BY MIN(DATEDIFF(DD, [EqmmspvDate],GETDATE()))   DESC)     as  Rownumber,
                             [EqmmspvEqmId],
                             [EqmmspvDate],
                          --LAG(EqmmspvValue, 1, 0) OVER(ORDER BY MIN(DATEDIFF(DD, [EqmmspvDate],GETDATE())) DESC)   as previuos_kilometerage,
              LEAD(EqmmspvValue, 1, 0) OVER(ORDER BY MIN(DATEDIFF(DD, [EqmmspvDate],GETDATE())) DESC)   as next_kilometerage,
                    [EqmmspvValue],
                            [EqmmspvDifference] 
                            
                         

  FROM [ULTIMO].[dba].[EquipmentMspValue] as e  
  INNER JOIN #temp as t on t.vloot = e.[EqmmspvEqmId] 
   
    WHERE e.EqmmspvRecStatus = 2  
AND       e.[EqmmspvMspId] = 'DEFAULT'

     
    GROUP BY [EqmmspvEqmId],EqmmspvDate, [EqmmspvValue], [EqmmspvDifference] 

 ) as e  INNER JOIN #temp as te on te.rownumber = e.Rownumber 
 
WHERE  e.EqmmspvValue < e.next_kilometerage

GROUP BY
e.[EqmmspvEqmId], 
e.[EqmmspvDate],
e.EqmmspvValue,
e.[EqmmspvDifference],
e.Rownumber

ORDER BY e.Rownumber ASC

推荐阅读