原语句如下(里面字段及表名称替换过的)
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');
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
我的那个每天都是半个小时左右