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

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

SELECT  ****FROM  AINNER 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 ) CON A.XXX = C.XXXAND A.END_DATE_REP=C.END_DATE_REPAND A.PUBLISH_DATE=C.PUBLISH_DATELEFT 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    ) FON A.XXX = F.XXXAND 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) RAKFROM A WITH(NOLOCK)) CLEFT JOIN(SELECT  XXX,END_DATE_REP,RANK() OVER (PARTITION BY XXX,END_DATE_REP ORDER BY PUBLISH_DATE DESC) RAKFROM  B WITH(NOLOCK)) FON C.XXX=F.XXXAND C.END_DATE_REP = F.END_DATE_REPAND F.RAK = 1 WHERE C.RAK = 1