/*筛选设备*/WITH MN1 AS(SELECT MACHINENAME FROM (SELECT MACHINENAME FROM RPTMGR.MACHINE_SEQ WHERE MACHINENAME IN ('${ddcUnit}')) ${IF(ddrAlarmYN= "N","WHERE 1=1","WHERE 1=2")} AND SUBSTR(MACHINENAME,9,4) NOT IN('UD01','UNLD','OPK1')),MN2 AS(SELECT MACHINENAME FROM (SELECT MACHINENAME FROM RPTMGR.MACHINE_SEQ WHERE MACHINENAME IN ('${ddcUnit}')) ${IF(ddrAlarmYN= "N","WHERE 1=1","WHERE 1=2")} AND SUBSTR(MACHINENAME,9,4) IN('UD01','UNLD','OPK1') )/*计算基础时间差*/,PTH AS(SELECT RN,TIMEKEY,PRODUCTNAME,PRODUCTPROCESSEDMACHINENAME,(CASE WHEN SUBSTR(PRODUCTPROCESSEDMACHINENAME,9,4) IN('UNLD','UD01','OPK1') THEN PRODUCTINTIME ELSE PRODUCTOUTTIME END) OUTTIME FROM (SELECT ROW_NUMBER() OVER(PARTITION BY PRODUCTNAME,PRODUCTPROCESSEDMACHINENAME ORDER BY TIMEKEY) RN,TIMEKEY,PRODUCTNAME,PRODUCTPROCESSEDMACHINENAME,PRODUCTINTIME,PRODUCTOUTTIME FROM MES_CT_PRODUCTTRACEHISTORY ${if(len(ddbDataPart)==0,"","PARTITION ("+ddbDataPart+")")},MN1 ${IF(ddrAlarmYN= "N","WHERE 1=1","WHERE 1=2")} AND PRODUCTOUTTIME >= REPLACE('${dtdDateStart}','-','')||REPLACE('${ddbTimeStart}','-','') AND PRODUCTOUTTIME <= REPLACE('${dtdDateEnd}','-','')||REPLACE('${ddbTimeEnd}','-','') ${if(len(ddcMachineName)==0,"","AND MES_CT_PRODUCTTRACEHISTORY.MACHINENAME IN ('"+ddcMachineName+"')")} AND PRODUCTPROCESSEDMACHINENAME = MN1.MACHINENAME ${if(len(ddcProductspec)==0,"","AND PRODUCTSPECNAME IN ('"+ddcProductspec+"')")} ${if(len(ddcOperation)==0,"","AND PROCESSOPERATIONNAME IN ('"+ddcOperation+"')")} ${if(RIGHT(TRIM(ddcUnit),3)= 'COL',"","AND COMPONENTTYPE <>'ComponentGlassBuffer'")} ${if(RIGHT(TRIM(ddcUnit),3)= 'COL',"","AND COMPONENTTYPE <>'ComponentPanelByBuffer'")} UNION ALL SELECT ROW_NUMBER() OVER(PARTITION BY PRODUCTNAME,PRODUCTPROCESSEDMACHINENAME ORDER BY TIMEKEY) RN,TIMEKEY,PRODUCTNAME,PRODUCTPROCESSEDMACHINENAME,PRODUCTINTIME,PRODUCTOUTTIME FROM MES_CT_PRODUCTTRACEHISTORY ${if(len(ddbDataPart)==0,"","PARTITION ("+ddbDataPart+")")},MN2 ${IF(ddrAlarmYN= "N","WHERE 1=1","WHERE 1=2")} AND PRODUCTINTIME >= REPLACE('${dtdDateStart}','-','')||REPLACE('${ddbTimeStart}','-','') AND PRODUCTINTIME <= REPLACE('${dtdDateEnd}','-','')||REPLACE('${ddbTimeEnd}','-','') ${if(len(ddcMachineName)==0,"","AND MES_CT_PRODUCTTRACEHISTORY.MACHINENAME IN ('"+ddcMachineName+"')")} AND PRODUCTPROCESSEDMACHINENAME = MN2.MACHINENAME ${if(len(ddcProductspec)==0,"","AND PRODUCTSPECNAME IN ('"+ddcProductspec+"')")} ${if(len(ddcOperation)==0,"","AND PROCESSOPERATIONNAME IN ('"+ddcOperation+"')")} ${if(RIGHT(TRIM(ddcUnit),3)= 'COL',"","AND COMPONENTTYPE <>'ComponentGlassBuffer'")} ${if(RIGHT(TRIM(ddcUnit),3)= 'COL',"","AND COMPONENTTYPE <>'ComponentPanelByBuffer'")} ) ORDER BY TIMEKEY) /*计算优化时间差*/,KK AS (SELECT ROW_NUMBER() OVER(PARTITION BY PRODUCTPROCESSEDMACHINENAME,RN ORDER BY natureTT) RNB ,PRODUCTPROCESSEDMACHINENAME ,natureTT ,RN FROM (SELECT AA.RN,AA.TIMEKEY,AA.PRODUCTPROCESSEDMACHINENAME,ROUND(AA.SECON-(CASE WHEN CC.T4result>0 THEN CC.T4result WHEN CC.T4result IS NULL THEN 0 ELSE 0 END )-(CASE WHEN DD.T1result>0 THEN DD.T1result WHEN DD.T1result IS NULL THEN 0 ELSE 0 END ),2) AS natureTT FROM (SELECT RN,TO_DATE(SUBSTR(TIMEKEY,0,14),'yyyy-MM-dd HH24:mi:ss') TIMEKEY ,PRODUCTNAME ,PRODUCTPROCESSEDMACHINENAME ,ROUND((TO_DATE(SUBSTR(OUTTIME,0,14),'yyyy-MM-dd HH24:mi:ss')-LAG(TO_DATE(SUBSTR(OUTTIME,0,14),'yyyy-MM-dd HH24:mi:ss'),1,NULL) OVER(PARTITION BY PRODUCTPROCESSEDMACHINENAME,RN ORDER BY OUTTIME))* 24 * 60 * 60,2) AS SECON FROM PTH ) AA /*匹配T4时间*/ LEFT JOIN (SELECT ROW_NUMBER() OVER(PARTITION BY MATERIALNAME,UNITNAME ORDER BY TIMEKEY) RN,BB.TIMEKEY,BB.MATERIALNAME,BB.UNITNAME,TO_NUMBER(BB.result) AS T4result FROM( SELECT (CASE WHEN regexp_like(result, ']') THEN '0' ELSE RESULT END)RESULT,b.TIMEKEY,b.UNITNAME,b.MATERIALNAME FROM RPTMGR.SPC_DCPROCESSDATARESULT ${if(len(ddbDataPart)==0,"","PARTITION ("+ddbDataPart+")")} a LEFT JOIN RPTMGR.SPC_DCDATA ${if(len(ddbDataPart)==0,"","PARTITION ("+ddbDataPart+")")} b ON a.DCDATAID=b.DCDATAID ${IF(ddrAlarmYN= "N","WHERE 1=1","WHERE 1=2")} AND b.PARTITIONKEY >= REPLACE('${dtdDateStart}','-','') AND b.PARTITIONKEY <= REPLACE('${dtdDateEnd}','-','') AND b.TIMEKEY >= REPLACE('${dtdDateStart}','-','')||REPLACE('${ddbTimeStart}','-','') AND b.TIMEKEY <= REPLACE('${dtdDateEnd}','-','')||REPLACE('${ddbTimeEnd}','-','') AND b.UNITNAME IN ('${ddcUnit}') AND a.ITEMNAME IN ('T4','STTT4') AND a.PARTITIONKEY=b.PARTITIONKEY UNION ALL SELECT (CASE WHEN regexp_like(result, ']') THEN '0' ELSE RESULT END)RESULT,b.TIMEKEY,b.UNITNAME,b.MATERIALNAME FROM RPTMGR.SPC_DCMEASUREDATARESULT ${if(len(ddbDataPart)==0,"","PARTITION ("+ddbDataPart+")")} a LEFT JOIN RPTMGR.SPC_DCDATA ${if(len(ddbDataPart)==0,"","PARTITION ("+ddbDataPart+")")} b ON a.DCDATAID=b.DCDATAID ${IF(ddrAlarmYN= "N","WHERE 1=1","WHERE 1=2")} AND b.PARTITIONKEY >= REPLACE('${dtdDateStart}','-','') AND b.PARTITIONKEY <= REPLACE('${dtdDateEnd}','-','') AND b.TIMEKEY >= REPLACE('${dtdDateStart}','-','')||REPLACE('${ddbTimeStart}','-','') AND b.TIMEKEY <= REPLACE('${dtdDateEnd}','-','')||REPLACE('${ddbTimeEnd}','-','') AND b.UNITNAME IN ('${ddcUnit}') AND a.ITEMNAME = 'T4' AND a.PARTITIONKEY=b.PARTITIONKEY )BB )CC ON AA.PRODUCTNAME=CC.MATERIALNAME AND CC.UNITNAME=AA.PRODUCTPROCESSEDMACHINENAME AND AA.RN=CC.RN AND ROUND(AA.SECON-NVL(CC.T4result,0)) >0 /*匹配T1时间*/ LEFT JOIN (SELECT ROW_NUMBER() OVER(PARTITION BY MATERIALNAME,UNITNAME ORDER BY TIMEKEY) RN,BB.TIMEKEY,BB.MATERIALNAME,BB.UNITNAME,TO_NUMBER(BB.result) AS T1result FROM (SELECT (CASE WHEN regexp_like(result, ']') THEN '0' ELSE RESULT END)RESULT,b.TIMEKEY,b.UNITNAME,b.MATERIALNAME FROM RPTMGR.SPC_DCPROCESSDATARESULT ${if(len(ddbDataPart)==0,"","PARTITION ("+ddbDataPart+")")} a LEFT JOIN RPTMGR.SPC_DCDATA ${if(len(ddbDataPart)==0,"","PARTITION ("+ddbDataPart+")")} b ON a.DCDATAID=b.DCDATAID ${IF(ddrAlarmYN= "N","WHERE 1=1","WHERE 1=2")} AND b.PARTITIONKEY >= REPLACE('${dtdDateStart}','-','') AND b.PARTITIONKEY <= REPLACE('${dtdDateEnd}','-','') AND b.TIMEKEY >= REPLACE('${dtdDateStart}','-','')||REPLACE('${ddbTimeStart}','-','') AND b.TIMEKEY <= REPLACE('${dtdDateEnd}','-','')||REPLACE('${ddbTimeEnd}','-','') AND b.UNITNAME IN ('${ddcUnit}') AND a.ITEMNAME IN ('T1','STT1') AND a.PARTITIONKEY=b.PARTITIONKEY UNION ALL SELECT (CASE WHEN regexp_like(result, ']') THEN '0' ELSE RESULT END)RESULT,b.TIMEKEY,b.UNITNAME,b.MATERIALNAME FROM RPTMGR.SPC_DCMEASUREDATARESULT ${if(len(ddbDataPart)==0,"","PARTITION ("+ddbDataPart+")")} a LEFT JOIN RPTMGR.SPC_DCDATA ${if(len(ddbDataPart)==0,"","PARTITION ("+ddbDataPart+")")} b ON a.DCDATAID=b.DCDATAID ${IF(ddrAlarmYN= "N","WHERE 1=1","WHERE 1=2")} AND b.PARTITIONKEY >= REPLACE('${dtdDateStart}','-','') AND b.PARTITIONKEY <= REPLACE('${dtdDateEnd}','-','') AND b.TIMEKEY >= REPLACE('${dtdDateStart}','-','')||REPLACE('${ddbTimeStart}','-','') AND b.TIMEKEY <= REPLACE('${dtdDateEnd}','-','')||REPLACE('${ddbTimeEnd}','-','') AND b.UNITNAME IN ('${ddcUnit}') AND a.ITEMNAME = 'T1' AND a.PARTITIONKEY=b.PARTITIONKEY )BB )DD ON AA.PRODUCTNAME=DD.MATERIALNAME AND DD.UNITNAME=AA.PRODUCTPROCESSEDMACHINENAME AND AA.RN=DD.RN AND ROUND(AA.SECON-NVL(DD.T1result,0)) >0 ${IF(ddrAlarmYN= "N","WHERE 1=1","WHERE 1=2")} AND AA.SECON >0 AND ABS(AA.TIMEKEY -NVL(TO_DATE(SUBSTR(CC.TIMEKEY,0,14),'yyyy-mm-dd hh24:mi:ss'),AA.TIMEKEY)) <=0.1 AND ABS(AA.TIMEKEY -NVL(TO_DATE(SUBSTR(DD.TIMEKEY,0,14),'yyyy-mm-dd hh24:mi:ss'),AA.TIMEKEY)) <=0.1 ORDER BY TIMEKEY ) ${IF(ddrAlarmYN= "N","WHERE 1=1","WHERE 1=2")} AND natureTT > 0 ORDER BY PRODUCTPROCESSEDMACHINENAME,natureTT )/*前后排除5%数据，剩余求均值*/SELECT PRODUCTPROCESSEDMACHINENAME,AVG(natureTT) CapaTT FROM (SELECT CASE WHEN MOD(RN,2)=1 THEN (CASE WHEN KK.PRODUCTPROCESSEDMACHINENAME IN ('A4PIL01-TRF5','A4PIL04-TRF5','A4PIL05-TRF5','A4PIL06-TRF5','A4PIL07-TRF5','A4PIL09-TRF5','A4PIL12-TRF5','A4PIL13-TRF5','A4PIL14-TRF5','A4PIL15-TRF5') THEN KK.PRODUCTPROCESSEDMACHINENAME||'(EXPO)' WHEN KK.PRODUCTPROCESSEDMACHINENAME IN ('A4PIL02-TRF5','A4PIL03-TRF5','A4PIL08-TRF5','A4PIL10-TRF5','A4PIL11-TRF5','A4PIL16-TRF5') THEN KK.PRODUCTPROCESSEDMACHINENAME||'(EXPA)' WHEN KK.PRODUCTPROCESSEDMACHINENAME IN ('A4PIL02-TRF8','A4PIL03-TRF8','A4PIL08-TRF8','A4PIL10-TRF8','A4PIL11-TRF8','A4PIL16-TRF8') THEN KK.PRODUCTPROCESSEDMACHINENAME||'(EXPB)' WHEN KK.PRODUCTPROCESSEDMACHINENAME IN ('A4PIL09-TRF9','A4PIL10-TRF9','A4PIL11-TRF9','A4PIL12-TRF9','A4PIL13-TRF9','A4PIL14-TRF9') THEN KK.PRODUCTPROCESSEDMACHINENAME||'(OVEN)' WHEN KK.PRODUCTPROCESSEDMACHINENAME LIKE 'F4PHL%TR05' THEN KK.PRODUCTPROCESSEDMACHINENAME||'(OVEN)' ELSE KK.PRODUCTPROCESSEDMACHINENAME END) WHEN MOD(RN,2)=0 THEN (CASE WHEN KK.PRODUCTPROCESSEDMACHINENAME IN('A4PIL01-TRF5','A4PIL04-TRF5','A4PIL05-TRF5','A4PIL06-TRF5','A4PIL07-TRF5') THEN KK.PRODUCTPROCESSEDMACHINENAME||'(TITL)' WHEN KK.PRODUCTPROCESSEDMACHINENAME IN ('A4PIL02-TRF5','A4PIL03-TRF5','A4PIL08-TRF5','A4PIL10-TRF5','A4PIL11-TRF5','A4PIL16-TRF5') THEN KK.PRODUCTPROCESSEDMACHINENAME||'(TRF6)' WHEN KK.PRODUCTPROCESSEDMACHINENAME IN ('A4PIL02-TRF8','A4PIL03-TRF8','A4PIL08-TRF8','A4PIL10-TRF8','A4PIL11-TRF8','A4PIL16-TRF8') THEN KK.PRODUCTPROCESSEDMACHINENAME||'(TRF6)' WHEN KK.PRODUCTPROCESSEDMACHINENAME IN ('A4PIL09-TRF5','A4PIL12-TRF5','A4PIL13-TRF5','A4PIL14-TRF5','A4PIL15-TRF5') THEN KK.PRODUCTPROCESSEDMACHINENAME||'(EETT)' WHEN KK.PRODUCTPROCESSEDMACHINENAME IN ('A4PIL09-TRF9','A4PIL10-TRF9','A4PIL11-TRF9','A4PIL12-TRF9','A4PIL13-TRF9','A4PIL14-TRF9') THEN KK.PRODUCTPROCESSEDMACHINENAME||'(AOIL)' WHEN KK.PRODUCTPROCESSEDMACHINENAME LIKE 'F4PHL%TR05' THEN KK.PRODUCTPROCESSEDMACHINENAME||'(AOI1)' ELSE KK.PRODUCTPROCESSEDMACHINENAME END) ELSE KK.PRODUCTPROCESSEDMACHINENAME END PRODUCTPROCESSEDMACHINENAME ,natureTT,RNB,MAXTT FROM KK LEFT JOIN (SELECT PRODUCTPROCESSEDMACHINENAME,MAX(RNB) MAXTT FROM KK GROUP BY PRODUCTPROCESSEDMACHINENAME,RN) KK2 ON KK.PRODUCTPROCESSEDMACHINENAME=KK2.PRODUCTPROCESSEDMACHINENAME ) ${IF(ddrAlarmYN= "N","WHERE 1=1","WHERE 1=2")} AND RNB >= MAXTT*0.05 AND RNB <= MAXTT*0.95 GROUP BY PRODUCTPROCESSEDMACHINENAMESQL如上，执行时间接近2分钟，是否有好的优化方向减少查询时间，分区与索引都尽可能的用上了，求大神指点
