用分析函数去掉标量子查询

原语句如下（里面字段及表名称替换过的）

SELECT A.CODE AS CODE,
       A.M_CODE AS M_CODE,
       A.STKTYPE AS F_STYPE,
       A.E_YEAR AS E_YEAR,
       B.SNAME AS SNAME,
       A.C_DATE AS C_DATE,
       TO_CHAR(SYSDATE, 'YYYYMMDD') AS CREATETIME,
       TO_CHAR(SYSDATE, 'YYYYMMDD') AS UPDATETIME,
       (SELECT STDDEV(VALUEF2)
          FROM A T
         WHERE T.CODE = A.CODE
           AND T.C_DATE BETWEEN
               TO_CHAR(TO_DATE(A.C_DATE, 'YYYYMMDD') - 180, 'YYYYMMDD') AND
               A.C_DATE
           AND T.E_YEAR = A.E_YEAR) F70115_70011,
       (SELECT STDDEV(VALUEF1)
          FROM A T
         WHERE T.CODE = A.CODE
           AND T.C_DATE BETWEEN
               TO_CHAR(TO_DATE(A.C_DATE, 'YYYYMMDD') - 180, 'YYYYMMDD') AND
               A.C_DATE
           AND T.E_YEAR = A.E_YEAR) F70104_70011,
       (SELECT STDDEV(VALUEF6)
          FROM A T
         WHERE T.CODE = A.CODE
           AND T.C_DATE BETWEEN
               TO_CHAR(TO_DATE(A.C_DATE, 'YYYYMMDD') - 180, 'YYYYMMDD') AND
               A.C_DATE
           AND T.E_YEAR = A.E_YEAR) F70126_70011,
       (SELECT STDDEV(VALUEF5)
          FROM A T
         WHERE T.CODE = A.CODE
           AND T.C_DATE BETWEEN
               TO_CHAR(TO_DATE(A.C_DATE, 'YYYYMMDD') - 180, 'YYYYMMDD') AND
               A.C_DATE
           AND T.E_YEAR = A.E_YEAR) F70131_70011,
       '-' AS F_UNIT
  FROM A, B@LINK B
 WHERE A.CODE = B.SCODE
   AND B.STYPE = 2
   AND B.STATUS = 1
   AND C_DATE >= TO_CHAR(SYSDATE - 3, 'YYYYMMDD');

这里面对a表的访问次数太多了。如果可以最好改写一下，经分析这个可以用分析函数改写，改写后语句如下：

SELECT A.*,
       B.SNAME AS SNAME,
       TO_CHAR(SYSDATE, 'YYYYMMDD') AS CREATETIME,
       TO_CHAR(SYSDATE, 'YYYYMMDD') AS UPDATETIME
  FROM (SELECT A.CODE AS CODE,
               A.M_CODE AS M_CODE,
               A.STKTYPE AS F_STYPE,
               A.E_YEAR AS E_YEAR,
               A.C_DATE AS C_DATE,
               CASE
                 WHEN A.C_DATE >= TO_CHAR(SYSDATE - 3, 'YYYYMMDD') THEN
                  STDDEV(VALUEF2)
                  OVER(PARTITION BY A.CODE,
                       A.E_YEAR ORDER BY TO_DATE(C_DATE, 'YYYYMMDD')
                       RANGE RANGE BETWEEN 180 PRECEDING AND CURRENT ROW)
               END AS F70115_70011,
               CASE
                 WHEN A.C_DATE >= TO_CHAR(SYSDATE - 3, 'YYYYMMDD') THEN
                  STDDEV(VALUEF1)
                  OVER(PARTITION BY A.CODE,
                       A.E_YEAR ORDER BY TO_DATE(C_DATE, 'YYYYMMDD')
                       RANGE RANGE BETWEEN 180 PRECEDING AND CURRENT ROW)
               END AS F70104_70011,
               CASE
                 WHEN A.C_DATE >= TO_CHAR(SYSDATE - 3, 'YYYYMMDD') THEN
                  STDDEV(VALUEF6)
                  OVER(PARTITION BY A.CODE,
                       A.E_YEAR ORDER BY TO_DATE(C_DATE, 'YYYYMMDD')
                       RANGE RANGE BETWEEN 180 PRECEDING AND CURRENT ROW)
               END AS F70126_70011,
               CASE
                 WHEN A.C_DATE >= TO_CHAR(SYSDATE - 3, 'YYYYMMDD') THEN
                  STDDEV(VALUEF5)
                  OVER(PARTITION BY A.CODE,
                       A.E_YEAR ORDER BY TO_DATE(C_DATE, 'YYYYMMDD')
                       RANGE RANGE BETWEEN 180 PRECEDING AND CURRENT ROW)
               END AS F70131_70011,
               '-' AS F_UNIT
          FROM A
         WHERE A.C_DATE >= TO_CHAR(SYSDATE - 3 - 180, 'YYYYMMDD')
           AND A.CODE = '000001') A
 INNER JOIN B@LINK B ON (A.CODE = B.SCODE)
 WHERE B.STYPE = 2
   AND B.STATUS = 1
   AND A.C_DATE >= TO_CHAR(SYSDATE - 3, 'YYYYMMDD');

结果如何呢？看反馈，哈哈

11:50:14 

这个没问题      数据没问题

11:51:34 

速度有点吓到我了呀  跑了22308条记录采用了28S

我的那个每天都是半个小时左右