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

来源：互联网发布：js怎么判断日期格式编辑：程序博客网时间：2024/05/22 00:37

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

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

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

0 0