报表刷取慢，如何优化sql

/*筛选设备*/

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, '[[:alpha:]]') 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, '[[:alpha:]]') 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, '[[:alpha:]]') 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, '[[:alpha:]]') 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 PRODUCTPROCESSEDMACHINENAME



SQL如上，执行时间接近2分钟，是否有好的优化方向减少查询时间，分区与索引都尽可能的用上了，求大神指点
FineReport 用户c2750520 发布于 2020-12-23 09:54
1min目标场景问卷 立即参与
回答问题
悬赏：3 F币 + 添加悬赏
举报
提示：增加悬赏、完善问题、追问等操作，可使您的问题被置顶，并向所有关注者发送通知
共4回答
最佳回答
0
luojian0323Lv7资深互助
发布于2020-12-23 09:57

写存储过程把数据存储到静态表中

没有其他办法，就你这sql

我想也没有多大优化空间。
举报 收起评论(7)
  • 用户c2750520 用户c2750520(提问者) 因为boss随时都有可能查询最新的数据，存储过程按分钟级别执行，内存占用方面也是问题
    2020-12-23 10:12 
  • luojian0323 luojian0323 回复 用户c2750520(提问者) 你不写存储过程，boss打开你一个报表要２分钟 你是不想干了吧。
    2020-12-23 10:12 
  • luojian0323 luojian0323 回复 用户c2750520(提问者) 需要实时的数据，往往不需要复杂计算　 需要复杂计算的往往不需要实时
    2020-12-23 10:13 
  • 用户c2750520 用户c2750520(提问者) 回复 luojian0323 这是生产类的数据，每小时会有数万条数据，原始数据不计算没法报表汇总
    2020-12-23 10:22 
  • luojian0323 luojian0323 回复 用户c2750520(提问者) 研究一下别的etl工具吧 　一般的sql查询解决不了你的需求。
    2020-12-23 10:24 
最佳回答
0
沉默的反补Lv6中级互助
发布于2020-12-23 09:57

三百多行一个字段的过滤写了七八遍。。。不慢天理难容~~
举报 收起评论(2)
  • 用户c2750520 用户c2750520(提问者) 有好的方式改善吗
    2020-12-23 10:09 
  • 沉默的反补 沉默的反补 回复 用户c2750520(提问者) １.数据量过大，避免超大量数据一次性查询．如果数据量很量，用分页查询，或者用条件属性设置分页． ２.空白单元格不合理父格导致的笛卡尔积，不要有空白单元格，报表中有不同数据集数据时，左父格，上父格，设置明确 ３.类Excel制作的模板，尽量避免，条件属性，单元格公式(特别是sql公式)，单元格数据过滤，尽量少设置． ４.插入的图表或者图片不宜过多． ５.填报控件较多时，避免控件直接显示在报表中，控件的数据字典要尽量优化，不宜查询过慢． ６.报表元素过多，扩展图表使用过于频繁，尽量避免． ７.过大过多的背景图片，尽量避免，不要觉得几百KB不大，告诉你，一样卡．
    2020-12-23 10:12 
最佳回答
0
小县城Lv4见习互助
发布于2020-12-23 10:16

单独写存储过程。弄个定时任务，定时把资料跑出来。把结果都插入到一个表里。每次查询就查询这个表呗。和领导说每半个小时或者一个小时更新一次资料呗。
举报 收起评论(1)
  • 用户c2750520 用户c2750520(提问者) 要从4个大表中捞出数据计算，情况是根据查询时间的不同，如果按小时提前计算出结果存储，查询多个小时甚至天的数据时，一次性计算出的结果，和每小时查询的结果汇总会有数据差距，因为涉及到数据抛出，一次抛出和多次抛出情况不相同
    2020-12-23 10:39 
最佳回答
0
DeepWhiteLv5见习互助
发布于2020-12-23 10:39

要不把with as 改成select into 试一下？
举报 收起评论(1)
  • 用户c2750520 用户c2750520(提问者) 这样写是为了提高一些可读性，方便其他人能看懂，select into 我尝试一下
    2020-12-23 10:41 
  • 4关注人数
  • 463浏览人数
    • 最后回答于：2020-12-23 10:39
    活动推荐 更多
    热门课程 更多
    请选择关闭问题的原因
    确定 取消
    隐私政策
    帆软用户服务协议
    联系管理员
    联系帆软
    Copyright© 帆软软件有限公司 版权所有
    苏ICP备18065767号
    返回顶部