使用开窗函数优化查询的一个案例

早上检查报警邮件时发现又是1000+的报警,于是查找凶手...
最终找到了罪魁祸首,一个ETL查询从晚上10点开始跑到凌晨1点50,好家伙足足跑了3小时50分钟,阻塞了一片一片的JOB:

SELECT 
 ****
FROM 
 A
INNER JOIN
 (
  SELECT XXX,A.END_DATE_REP,MAX(PUBLISH_DATE) AS PUBLISH_DATE
  FROM A
  INNER JOIN 
   (
    SELECT XXX,MAX(END_DATE_REP) AS END_DATE_REP
    FROM A
    GROUP BY XXX
   )B
  ON A.XXX = B.XXX
  AND A.END_DATE_REP=B.END_DATE_REP
  GROUP BY A.XXX,A.END_DATE_REP
 ) C
ON A.XXX = C.XXX
AND A.END_DATE_REP=C.END_DATE_REP
AND A.PUBLISH_DATE=C.PUBLISH_DATE
LEFT JOIN (SELECT A.* 
    FROM B
    JOIN 
  (
   SELECT XXX,FISCAL_PERIOD,MAX(PUBLISH_DATE) AS  PUBLISH_DATE
   FROM  B
   GROUP BY XXX,FISCAL_PERIOD
  ) D
    ON B.XXX = D.XXX
    AND B.PUBLISH_DATE = D.PUBLISH_DATE
    ) F
ON A.XXX = F.XXX
AND A.END_DATE_REP = F.END_DATE_REP

一看到这个查询，瞬间被石化了。典型的使用开窗函数的场景嘛。尝试使用如下的开窗函数写法后，妥妥的10s内解决战斗。

SELECT 
 ****
FROM
(
SELECT
 XXX,RANK() OVER (PARTITION BY XXX ORDER BY END_DATE_REP DESC,PUBLISH_DATE DESC) RAK
FROM A WITH(NOLOCK)
) C
LEFT JOIN
(
SELECT 
 XXX,END_DATE_REP,RANK() OVER (PARTITION BY XXX,END_DATE_REP ORDER BY PUBLISH_DATE DESC) RAK
FROM  B WITH(NOLOCK)
) F
ON C.XXX=F.XXX
AND C.END_DATE_REP = F.END_DATE_REP
AND F.RAK = 1 
WHERE C.RAK = 1