自我加入需要时间



我有以下查询,它根据某些条件选择AAD_00TO30列的SUM。

当我删除以下条件时,查询将在1秒内执行,但当包含相同条件时,它需要一分钟以上的时间。

有人能建议我修改查询以获得更好的性能吗。

AND A.AAD_DATE >= (SELECT MAX(B.AAD_DATE) 
FROM MST_AR_AS_ON_DATE B 
WHERE MONTH(B.AAD_DATE) = MONTH(A.AAD_DATE) AND YEAR(B.AAD_DATE) = YEAR(A.AAD_DATE))

查询:

SELECT '00-30 #66ff66',SUM(A.AAD_00TO30)  FROM MST_AR_AS_ON_DATE A 
WHERE MONTH(A.AAD_DATE) = MONTH(DATEADD(MM,-1,GETDATE()))  
AND YEAR(A.AAD_DATE) = YEAR(DATEADD(MM,-1,GETDATE())) 
AND A.AAD_RESP_NOW = 4 
AND A.AAD_DATE >= (SELECT MAX(B.AAD_DATE) 
FROM MST_AR_AS_ON_DATE B 
WHERE MONTH(B.AAD_DATE) = MONTH(A.AAD_DATE) AND YEAR(B.AAD_DATE) = YEAR(A.AAD_DATE))

尝试使用RANK()标记符合月份最后日期条件的行。然后消除没有获胜等级的行:

WITH 
MST_AR_AS_ON_DATE_RANKED AS (
SELECT
*,
RANK() OVER (
PARTITION BY
YEAR(AAD_DATE),
MONTH(AAD_DATE)
ORDER BY
AAD_DATE DESC -- last day of month ranked highest
) AS AAD_DATE_RANK
FROM
MST_AR_AS_ON_DATE
)
SELECT 
'00-30 #66ff66',
SUM(AAD_00TO30)
FROM 
MST_AR_AS_ON_DATE_RANKED
WHERE 
MONTH(AAD_DATE) = MONTH(DATEADD(MM,-1,GETDATE()))  
AND YEAR(AAD_DATE) = YEAR(DATEADD(MM,-1,GETDATE())) 
AND AAD_RESP_NOW = 4
AND AAD_DATE_RANK = 1 
;

相关内容

  • 没有找到相关文章

最新更新