分析函数优化自关联2

数据库环境：SQL SERVER 2005

　　今天抓了一条返回数据比较少，但是执行时间相对较长的SQL。返回421行，执行了50S，

说明SQL的执行效率有提升的空间。

　　我们先来看一下SQL文本

SELECT  a.dept_id ,
        c.dept_name ,
        a.clerk_id ,
        d.clerk_name ,
        a.item_id ,
        RTRIM(e.item_name) item_name ,
        a.dec_sqty ,
        b.dec_sum ,
        a.fiscal_year ,
        a.fiscal_period
FROM    v_ctlm8666_sum a ,
        ctlm1003 c ,
        ctlm1006 d ,
        ctlm1012 e ,
        ctlm8201 f ,
        ( SELECT    dept_id ,
                    clerk_id ,
                    SUM(dec_sqty) AS dec_sum
          FROM      v_ctlm8666_sum
          WHERE     fiscal_year = 2015
                    AND fiscal_period <= 7
                    AND dept_id LIKE '%'
                    AND clerk_id LIKE '%'
                    AND dept_id IN ( SELECT dept_id
                                     FROM   v_ctlm1010
                                     WHERE  clerk_id = '020123' )
          GROUP BY  dept_id ,
                    clerk_id
        ) b
WHERE   a.item_id = f.item_id
        AND a.dept_id = f.dept_id
        AND a.fiscal_year = f.fiscal_year
        AND a.dept_id = b.dept_id
        AND a.clerk_id = b.clerk_id
        AND a.dept_id = c.dept_id
        AND a.clerk_id = d.clerk_id
        AND a.item_id = e.item_id
        AND a.dept_id LIKE '%'
        AND a.clerk_id LIKE '%'
        AND a.fiscal_year = 2015
        AND a.fiscal_period = 7
        AND a.dept_id IN ( SELECT   dept_id
                           FROM     v_ctlm1010
                           WHERE    clerk_id = '020123' ) 

　　我们注意到，内联视图b访问的表及过滤条件和外部很相似，且内联视图要实现的功能是查询

本年度1到7月份的销量合计。因此，我们通过分析函数来实现同样的功能，这样，对视图v_ctlm8666_sum

和v_ctlm1010就只访问一次。

　　按照上面的思路，我们将SQL改写如下：

/*将内联视图和外部表相同过滤条件的放到with中*/
WITH    x0
          AS ( SELECT   a.dept_id ,
                        a.clerk_id ,
                        a.item_id ,
                        a.dec_sqty ,
                        SUM(dec_sqty) OVER ( PARTITION BY a.dept_id,
                                             a.clerk_id ) AS dec_sum ,
                        a.fiscal_year ,
                        a.fiscal_period
               FROM     v_ctlm8666_sum a
               WHERE    fiscal_year = 2015
                        AND fiscal_period <= 7
                        AND dept_id LIKE '%'
                        AND clerk_id LIKE '%'
                        AND dept_id IN ( SELECT dept_id
                                         FROM   v_ctlm1010
                                         WHERE  clerk_id = '020123' )
             )
/*缩小x0的范围，只取本年度7月份的数据*/
    SELECT  a.dept_id ,
            c.dept_name ,
            a.clerk_id ,
            d.clerk_name ,
            a.item_id ,
            RTRIM(e.item_name) item_name ,
            a.dec_sqty ,
            a.dec_sum ,
            a.fiscal_year ,
            a.fiscal_period
    FROM    x0 a ,
            ctlm1003 c ,
            ctlm1006 d ,
            ctlm1012 e ,
            ctlm8201 f
    WHERE   a.item_id = f.item_id
            AND a.dept_id = f.dept_id
            AND a.fiscal_year = f.fiscal_year
            AND a.dept_id = c.dept_id
            AND a.clerk_id = d.clerk_id
            AND a.item_id = e.item_id
            AND a.fiscal_year = 2015
            AND a.fiscal_period = 7

　　我们将改写后SQL的执行下，只执行4S钟就全部出结果了。再核对一下数据，和原先的SQL执行出来的结果一致，

说明通过分析函数改写确实提高了SQL的执行效率。

　　由于在v_ctlm8666_sum的字段fiscal_year、fiscal_period上没有索引，通过在这两个字段上建组合索引，将会对

提升SQL的执行效率有一定帮助。

（本文完）