WITH alldata AS(
SELECT JOB_NAME ,JOB_ID ,FAB_ID ,AREA_ID ,REAL_POINT_COUNT ,ppk_value ,report_date
,CASE WHEN area_id='C-测试' THEN '测试厂'
WHEN job_name LIKE '%3.1%' OR job_name LIKE '%3.2%' THEN '制造三厂'
WHEN job_name LIKE '%2.1%' OR job_name LIKE '%2.2%' THEN '制造二厂'
ELSE '制造一厂' END AS factory
,CASE WHEN JOB_NAME LIKE '%AE%' THEN '车规' ELSE '非车规' END AS CAR_SPEC
--,TO_CHAR(report_date , 'IW') AS iso_week_number
,TO_CHAR(report_date , 'YYYYMM') AS iso_month_number
FROM REP_CPK_DETAIL_NONRT
where
REPORT_NAME IN ('成都封测质量关注周报-测试-周二','成都封测质量关注周报-测试-周六','成都封测质量关注周报-测试-周日','成都封测质量关注周报-测试-周三'
,'成都封测质量关注周报-测试-周四','成都封测质量关注周报-测试-周五','成都封测质量关注周报-测试-周一','成都封测质量关注周报-成型-周二'
,'成都封测质量关注周报-成型-周六','成都封测质量关注周报-成型-周日','成都封测质量关注周报-成型-周三','成都封测质量关注周报-成型-周四'
,'成都封测质量关注周报-成型-周五','成都封测质量关注周报-成型-周一','成都封测质量关注周报-电镀-周二','成都封测质量关注周报-电镀-周六'
,'成都封测质量关注周报-电镀-周日','成都封测质量关注周报-电镀-周三','成都封测质量关注周报-电镀-周四','成都封测质量关注周报-电镀-周五'
,'成都封测质量关注周报-电镀-周一','成都封测质量关注周报-划片-周二','成都封测质量关注周报-划片-周六','成都封测质量关注周报-划片-周日'
,'成都封测质量关注周报-划片-周三','成都封测质量关注周报-划片-周四','成都封测质量关注周报-划片-周五','成都封测质量关注周报-划片-周一'
,'成都封测质量关注周报-键合1-周二','成都封测质量关注周报-键合1-周六','成都封测质量关注周报-键合1-周日','成都封测质量关注周报-键合1-周三'
,'成都封测质量关注周报-键合1-周四','成都封测质量关注周报-键合1-周五','成都封测质量关注周报-键合1-周一','成都封测质量关注周报-键合2-周二'
,'成都封测质量关注周报-键合2-周六','成都封测质量关注周报-键合2-周日','成都封测质量关注周报-键合2-周三','成都封测质量关注周报-键合2-周四'
,'成都封测质量关注周报-键合2-周五','成都封测质量关注周报-键合2-周一','成都封测质量关注周报-键合3-周二','成都封测质量关注周报-键合3-周六'
,'成都封测质量关注周报-键合3-周日','成都封测质量关注周报-键合3-周三','成都封测质量关注周报-键合3-周四','成都封测质量关注周报-键合3-周五'
,'成都封测质量关注周报-键合3-周一','成都封测质量关注周报-键合4-周二','成都封测质量关注周报-键合4-周六','成都封测质量关注周报-键合4-周日'
,'成都封测质量关注周报-键合4-周三','成都封测质量关注周报-键合4-周四','成都封测质量关注周报-键合4-周五','成都封测质量关注周报-键合4-周一'
,'成都封测质量关注周报-键合5-周二','成都封测质量关注周报-键合5-周六','成都封测质量关注周报-键合5-周日','成都封测质量关注周报-键合5-周三'
,'成都封测质量关注周报-键合5-周四','成都封测质量关注周报-键合5-周五','成都封测质量关注周报-键合5-周一','成都封测质量关注周报-键合6-周二'
,'成都封测质量关注周报-键合6-周六','成都封测质量关注周报-键合6-周日','成都封测质量关注周报-键合6-周三','成都封测质量关注周报-键合6-周四'
,'成都封测质量关注周报-键合6-周五','成都封测质量关注周报-键合6-周一','成都封测质量关注周报-键合7-周二','成都封测质量关注周报-键合7-周六'
,'成都封测质量关注周报-键合7-周日','成都封测质量关注周报-键合7-周三','成都封测质量关注周报-键合7-周四','成都封测质量关注周报-键合7-周五'
,'成都封测质量关注周报-键合7-周一','成都封测质量关注周报-键合8-周二','成都封测质量关注周报-键合8-周六','成都封测质量关注周报-键合8-周日'
,'成都封测质量关注周报-键合8-周三','成都封测质量关注周报-键合8-周四','成都封测质量关注周报-键合8-周五','成都封测质量关注周报-键合8-周一'
,'成都封测质量关注周报-塑封-周二','成都封测质量关注周报-塑封-周六','成都封测质量关注周报-塑封-周日','成都封测质量关注周报-塑封-周三'
,'成都封测质量关注周报-塑封-周四','成都封测质量关注周报-塑封-周五','成都封测质量关注周报-塑封-周一','成都封测质量关注周报-装片1-周二'
,'成都封测质量关注周报-装片1-周六','成都封测质量关注周报-装片1-周日','成都封测质量关注周报-装片1-周三','成都封测质量关注周报-装片1-周四'
,'成都封测质量关注周报-装片1-周五','成都封测质量关注周报-装片1-周一','成都封测质量关注周报-装片2-周二','成都封测质量关注周报-装片2-周六'
,'成都封测质量关注周报-装片2-周日','成都封测质量关注周报-装片2-周三','成都封测质量关注周报-装片2-周四','成都封测质量关注周报-装片2-周五'
,'成都封测质量关注周报-装片2-周一','成都封测质量关注周报-装片3-周二','成都封测质量关注周报-装片3-周六','成都封测质量关注周报-装片3-周日'
,'成都封测质量关注周报-装片3-周三','成都封测质量关注周报-装片3-周四','成都封测质量关注周报-装片3-周五','成都封测质量关注周报-装片3-周一'
,'成都封测质量关注周报-装片4-周二','成都封测质量关注周报-装片4-周六','成都封测质量关注周报-装片4-周日','成都封测质量关注周报-装片4-周三'
,'成都封测质量关注周报-装片4-周四','成都封测质量关注周报-装片4-周五','成都封测质量关注周报-装片4-周一')
AND JOB_ID NOT IN ('72054'
,'72055'
,'72065'
,'73425'
,'73426'
,'73427'
,'73428'
,'73522'
,'73953'
,'73954'
,'74134'
,'74135'
,'74538'
,'74539'
,'74544'
,'74545'
,'74546'
,'74547'
,'74576'
,'74577'
,'74578'
,'74579'
,'74653'
,'74654'
,'74788'
,'74789'
,'74818'
,'74929'
,'74940'
,'40057'
,'40061'
,'44993'
,'51741'
,'64872'
,'69099'
,'69102'
,'69105'
,'70360'
,'70364'
,'70368'
,'71543'
,'71814'
,'71819'
,'72010'
,'72656'
,'72657'
,'72658'
,'72662'
,'72663'
,'72668'
,'72672'
,'72677'
,'73237'
,'73272'
,'73277'
,'74099'
,'74104'
,'74107'
,'74108'
,'74112'
,'74508'
,'74588'
,'74684'
,'74734'
,'74752'
,'74756'
,'74917'
,'75185'
,'75204'
,'75206'
,'75596'
,'75772'
,'75773'
,'75774'
,'40054'
,'40058'
,'40059'
,'44990'
,'44991'
,'51739'
,'57129'
,'64869'
,'64870'
,'69096'
,'69097'
,'69100'
,'69103'
,'70358'
,'70361'
,'70362'
,'70365'
,'71541'
,'71811'
,'71812'
,'71816'
,'71817'
,'72654'
,'72655'
,'72660'
,'72661'
,'72665'
,'72666'
,'72669'
,'72670'
,'72673'
,'72674'
,'73234'
,'73270'
,'73271'
,'73274'
,'73275'
,'74101'
,'74102'
,'74105'
,'74109'
,'74110'
,'74518'
,'74519'
,'74585'
,'74681'
,'74682'
,'74731'
,'74732'
,'74749'
,'74750'
,'74753'
,'74754'
,'75182'
,'75183'
,'75202'
,'75203'
,'75593'
,'75770'
,'75771'
,'40056'
,'40060'
,'44992'
,'45806'
,'51740'
,'57130'
,'64871'
,'69098'
,'69101'
,'69104'
,'70359'
,'70363'
,'70366'
,'71542'
,'71815'
,'71818'
,'71848'
,'71872'
,'71892'
,'72305'
,'72308'
,'72659'
,'72664'
,'72667'
,'72671'
,'72676'
,'73235'
,'73273'
,'73276'
,'74103'
,'74106'
,'74111'
,'74124'
,'74507'
,'74586'
,'74683'
,'74733'
,'74751'
,'74755'
,'75184'
,'75207'
,'75594'
,'75686'
,'75793'
,'49336'
,'49337'
,'49338'
,'70369'
,'71556'
,'71849'
,'71870'
,'71871'
,'71893'
,'71899'
,'72304'
,'72307'
,'72314'
,'72315'
,'73238'
,'74100'
,'74589'
,'75597'
,'75684'
,'75685'
,'26121'
,'26122'
,'26123'
,'26131'
,'26134'
,'38885'
,'38891'
,'43117'
,'45979'
,'48317'
,'49736'
,'49740'
,'56456'
,'56457'
,'58088'
,'58224'
,'58231'
,'58234'
,'62430'
,'62433'
,'62436'
,'62443'
,'63944'
,'63951'
,'63955'
,'63957'
,'64664'
,'65653'
,'65801'
,'65804'
,'65807'
,'65816'
,'65819'
,'67182'
,'67185'
,'67354'
,'67361'
,'67707'
,'68703'
,'69054'
,'69057'
,'69060'
,'69392'
,'71809'
,'71827'
,'71830'
,'71833'
,'71836'
,'71839'
,'71865'
,'72271'
,'72279'
,'72287'
,'72296'
,'72299'
,'72302'
,'72313'
,'72316'
,'72317'
,'72362'
,'72384'
,'72855'
,'72924'
,'74449'
,'74452'
,'74455'
,'74861'
,'74970'
,'74973'
,'74976'
,'75431'
,'75432'
,'75879'
,'75888'
,'75895'
,'75932'
,'75935'
,'75938'
,'61122'
,'61123'
,'61124'
,'61125'
,'61195'
,'61196'
,'61197'
,'61198'
,'61199'
,'61200'
,'61201'
,'61202'
,'61203'
,'61204'
,'61205'
,'61206'
,'61207'
,'61208'
,'61209'
,'61210'
,'61211'
,'61212'
,'61213'
,'61214'
,'61215'
,'61216'
,'61217'
,'61218'
,'61219'
,'61249'
,'61250'
,'61251'
,'61252'
,'72971'
,'72972'
,'72973'
,'73612'
,'73630'
,'73631'
,'73691'
,'73692'
,'73693'
,'73694'
,'73695'
,'73696'
,'73904'
,'73905'
,'73986'
,'73987'
,'74015'
,'74016'
,'74017'
,'74018'
,'74019'
,'74775'
,'75008'
,'75009'
,'75645'
,'75357'
,'75358'
,'75602'
,'75603'
,'75604'
,'75605'
,'75650'
,'75651'
,'75652'
,'75653'
,'75654'
,'76205'
,'76206'
,'76207'
,'76208'
,'76209'
,'73298'
,'73299'
,'73300'
,'74712'
,'73357'
,'73358'
,'73359'
,'73360'
,'73361'
,'73362'
,'73699'
,'73700'
,'73701'
,'73702'
,'73703'
,'73704'
,'73717'
,'73718'
,'73719'
,'73720'
,'73721'
,'73722'
,'73960'
,'73961'
,'73962'
,'73963'
,'73964'
,'73965'
,'73988'
,'73989'
,'73990'
,'73991'
,'73992'
,'73993'
,'74232'
,'74233'
,'74234'
,'74231'
,'74235'
,'74236'
,'74237'
,'74238'
,'74239'
,'74240'
,'74241'
,'74242'
,'74243'
,'74244'
,'74245'
,'74246'
,'74249'
,'74248'
,'74250'
,'74251'
,'74252'
,'74253'
,'74254'
,'74255'
,'74256'
,'74257'
,'74258'
,'74259'
,'74260'
,'74261'
,'75266'
,'75267'
,'75268'
,'75269'
,'75270'
,'75271'
,'75272'
,'75273'
,'75339'
,'75340'
,'75341'
,'75342'
,'75343'
,'75344'
,'75345'
,'75346'
,'75347'
,'75348'
,'75349'
,'75350'
,'75351'
,'75352'
,'75353'
,'75354'
,'75355'
,'75356'
,'72645'
,'72646'
,'72647'
,'72648'
,'73239'
,'73249'
,'73403'
,'73409'
,'73408'
,'73414'
,'73307'
,'73308'
,'72649'
,'72650'
,'72651'
,'72652'
,'73243'
,'73244'
,'73404'
,'73410'
,'73305'
,'73306'
,'73407'
,'73413'
,'72684'
,'72685'
,'72984'
,'72985'
,'72986'
,'72987'
,'72988'
,'72989'
,'72990'
,'72991'
,'73245'
,'73246'
,'73405'
,'73411'
,'73406'
,'73412'
,'73301'
,'73302'
,'72992'
,'72993'
,'72994'
,'72995'
,'72996'
,'72997'
,'72998'
,'72999'
,'73000'
,'73001'
,'73002'
,'73003'
,'73247'
,'73248'
,'73296'
,'73297'
,'75447'
,'75448'
,'75449'
,'75450'
,'73367'
,'73368'
,'73369'
,'73697'
,'73698'
,'73705'
,'73706'
,'73373'
,'73370'
,'73372'
,'73375'
,'61121'
,'61193'
,'61194'
,'72140'
,'72183'
,'72430'
,'73754'
,'76731'
,'72021'
,'72039'
,'72041'
,'72043'
,'72045'
,'72056'
,'72058'
,'72062'
,'72064'
,'72206'
,'73521'
,'73523'
,'73948'
,'74321'
,'74322'
,'74323'
,'74324'
,'74446'
,'74492'
,'74536'
,'74541'
,'74616'
,'74646'
,'74648'
,'74650'
,'74652'
,'74656'
,'74658'
,'74660'
,'74708'
,'74710'
,'74735'
,'74744'
,'74746'
,'74748'
,'74817'
,'74819'
,'74839'
,'74886'
,'74903'
,'74927'
,'74942'
,'74951'
,'75161'
,'75166'
,'75628'
,'75674'
,'75678'
,'75929'
,'75958'
,'75982'
,'76226'
,'76228'
,'76230'
,'76235'
,'76237'
,'76705'
,'76724'
,'76737'
,'76820'
,'76846'
,'76860'
,'77062'
,'72020'
,'72026'
,'72040'
,'72042'
,'72044'
,'72057'
,'72061'
,'72063'
,'72067'
,'72205'
,'73520'
,'73946'
,'74317'
,'74318'
,'74319'
,'74320'
,'74445'
,'74491'
,'74535'
,'74540'
,'74615'
,'74645'
,'74647'
,'74649'
,'74651'
,'74655'
,'74657'
,'74659'
,'74709'
,'74711'
,'74736'
,'74743'
,'74745'
,'74747'
,'74816'
,'74838'
,'74885'
,'74904'
,'74926'
,'74928'
,'74939'
,'74941'
,'74952'
,'75165'
,'75629'
,'75673'
,'75677'
,'75928'
,'75957'
,'75981'
,'76225'
,'76227'
,'76229'
,'76236'
,'76238'
,'76704'
,'76723'
,'76736'
,'76821'
,'76845'
,'76859'
,'77061'
)
AND REPORT_DATE <TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-DD') , 'YYYY-MM-DD')
AND REPORT_DATE >= TO_DATE(TO_CHAR(SYSDATE, 'YYYY') || '-01-01', 'YYYY-MM-DD')
)
,enddata AS (
SELECT a.* ,CASE WHEN CAR_SPEC = '车规' THEN CASE WHEN ppk_value > 1.67 THEN 'CPK>1.67' ELSE 'CPK<1.67' END
ELSE CASE WHEN ppk_value > 1.33 THEN 'CPK>1.33' ELSE 'CPK<1.33' END END AS is_ok
FROM alldata a
-- ${if(len(factory) > 0,"WHERE factory ='"+factory+"'","")}
)
--月常规
,month_data as(
SELECT factory,area_id,car_spec ,is_ok,iso_month_number, count(*) AS countd
FROM enddata
GROUP BY factory,area_id,car_spec,is_ok ,iso_month_number
)
--月汇总all
,month_data_count as(
SELECT factory,area_id,'汇总' AS car_spec ,'CPK统计总数' AS is_ok
,'月' AS flagcol, sum(countd) AS countd,iso_month_number
FROM month_data
GROUP BY factory,area_id,iso_month_number
UNION
SELECT factory,area_id,'汇总' AS car_spec ,'CPK不达标个数' AS is_ok
,'月' AS flagcol, sum(countd) AS countd,iso_month_number
FROM month_data
WHERE is_ok = 'CPK<1.67' OR is_ok='CPK<1.33'
GROUP BY factory,area_id,iso_month_number
)
SELECT factory,area_id ,car_spec,is_ok,countd ,iso_month_number
FROM month_data_count
UNION
SELECT factory,area_id ,car_spec,is_ok,countd ,iso_month_number
FROM month_data 优化SQL