이 스크립트는 김강환님께서 공유해주셨습니다.
{column:width=50%}
conn scott/loveora77
spool 2-58.lst
set timing off
set autotrace off
set linesize 1000
set pagesize 1000
prompt 1.품질checks 테이블
drop table INVEST cascade constraints purge;
drop table RESULT cascade constraints purge;
CREATE TABLE INVEST(
ITEM VARCHAR2(10),
IDATE VARCHAR2(8),
SEQ NUMBER,
CHK_ITEM VARCHAR2(10));
INSERT INTO INVEST VALUES('A','20061001',1,'A1');
INSERT INTO INVEST VALUES('A','20061001',1,'A2');
INSERT INTO INVEST VALUES('A','20061001',1,'A3');
INSERT INTO INVEST VALUES('A','20061001',1,'A4');
INSERT INTO INVEST VALUES('A','20061001',1,'A5');
INSERT INTO INVEST VALUES('A','20061001',2,'A1');
INSERT INTO INVEST VALUES('A','20061001',3,'A2');
INSERT INTO INVEST VALUES('A','20061007',1,'A3');
INSERT INTO INVEST VALUES('A','20061011',1,'A4');
INSERT INTO INVEST VALUES('A','20061015',1,'A5');
INSERT INTO INVEST VALUES('A','20061021',1,'A6');
INSERT INTO INVEST VALUES('A','20061021',2,'A1');
INSERT INTO INVEST VALUES('A','20061021',3,'A2');
INSERT INTO INVEST VALUES('A','20061030',1,'A3');
INSERT INTO INVEST VALUES('A','20061030',1,'A3');
INSERT INTO INVEST VALUES('A','20061030',1,'A3');
INSERT INTO INVEST VALUES('A','20061030',1,'A3');
INSERT INTO INVEST VALUES('A','20061030',1,'A3');
INSERT INTO INVEST VALUES('B','20061001',1,'B1');
INSERT INTO INVEST VALUES('B','20061001',2,'B1');
INSERT INTO INVEST VALUES('B','20061001',3,'B1');
INSERT INTO INVEST VALUES('B','20061007',1,'B1');
INSERT INTO INVEST VALUES('B','20061011',1,'B1');
INSERT INTO INVEST VALUES('B','20061015',1,'B1');
INSERT INTO INVEST VALUES('B','20061021',1,'B1');
INSERT INTO INVEST VALUES('B','20061021',2,'B1');
INSERT INTO INVEST VALUES('B','20061021',3,'B1');
INSERT INTO INVEST VALUES('B','20061030',1,'B1');
INSERT INTO INVEST VALUES('C','20061001',1,'C1');
INSERT INTO INVEST VALUES('C','20061001',2,'C1');
INSERT INTO INVEST VALUES('C','20061001',3,'C1');
INSERT INTO INVEST VALUES('D','20061007',1,'C1');
INSERT INTO INVEST VALUES('E','20061011',1,'C1');
INSERT INTO INVEST VALUES('F','20061015',1,'C1');
INSERT INTO INVEST VALUES('F','20061021',1,'C1');
INSERT INTO INVEST VALUES('F','20061021',2,'C1');
INSERT INTO INVEST VALUES('F','20061021',3,'C1');
INSERT INTO INVEST VALUES('G','20061030',1,'C1');
INSERT INTO INVEST VALUES('G','20061030',1,'C2');
INSERT INTO INVEST VALUES('G','20061030',1,'C3');
INSERT INTO INVEST VALUES('A','20061101',1,'A1');
INSERT INTO INVEST VALUES('A','20061101',1,'A2');
INSERT INTO INVEST VALUES('A','20061101',1,'A3');
INSERT INTO INVEST VALUES('A','20061101',1,'A4');
INSERT INTO INVEST VALUES('A','20061101',1,'A5');
INSERT INTO INVEST VALUES('A','20061101',2,'A1');
INSERT INTO INVEST VALUES('A','20061101',3,'A2');
INSERT INTO INVEST VALUES('A','20061107',1,'A3');
INSERT INTO INVEST VALUES('A','20061111',1,'A4');
INSERT INTO INVEST VALUES('A','20061115',1,'A5');
INSERT INTO INVEST VALUES('A','20061121',1,'A6');
INSERT INTO INVEST VALUES('A','20061121',2,'A1');
INSERT INTO INVEST VALUES('A','20061121',3,'A2');
INSERT INTO INVEST VALUES('A','20061130',1,'A3');
INSERT INTO INVEST VALUES('A','20061130',1,'A3');
INSERT INTO INVEST VALUES('A','20061130',1,'A3');
INSERT INTO INVEST VALUES('A','20061130',1,'A3');
INSERT INTO INVEST VALUES('A','20061130',1,'A3');
INSERT INTO INVEST VALUES('B','20061101',1,'B1');
INSERT INTO INVEST VALUES('B','20061101',2,'B1');
INSERT INTO INVEST VALUES('B','20061101',3,'B1');
INSERT INTO INVEST VALUES('B','20061107',1,'B1');
INSERT INTO INVEST VALUES('B','20061111',1,'B1');
INSERT INTO INVEST VALUES('B','20061115',1,'B1');
INSERT INTO INVEST VALUES('B','20061121',1,'B1');
INSERT INTO INVEST VALUES('B','20061121',2,'B1');
INSERT INTO INVEST VALUES('B','20061121',3,'B1');
INSERT INTO INVEST VALUES('B','20061130',1,'B1');
INSERT INTO INVEST VALUES('C','20061101',1,'C1');
INSERT INTO INVEST VALUES('C','20061101',2,'C1');
INSERT INTO INVEST VALUES('C','20061101',3,'C1');
INSERT INTO INVEST VALUES('D','20061107',1,'C1');
INSERT INTO INVEST VALUES('E','20061111',1,'C1');
INSERT INTO INVEST VALUES('F','20061115',1,'C1');
INSERT INTO INVEST VALUES('F','20061121',1,'C1');
INSERT INTO INVEST VALUES('F','20061121',2,'C1');
INSERT INTO INVEST VALUES('F','20061121',3,'C1');
INSERT INTO INVEST VALUES('G','20061130',1,'C1');
INSERT INTO INVEST VALUES('G','20061130',1,'C2');
INSERT INTO INVEST VALUES('G','20061130',1,'C3');
prompt 2.checks결과 테이블
CREATE TABLE RESULT(
ITEM VARCHAR2(10),
IDATE VARCHAR2(8),
SEQ NUMBER,
CHK_ITEM VARCHAR2(10),
GRADE VARCHAR2(1));
INSERT INTO RESULT VALUES('A','20061001',1,'A1','1');
INSERT INTO RESULT VALUES('A','20061001',1,'A2','2');
INSERT INTO RESULT VALUES('A','20061001',1,'A3','4');
INSERT INTO RESULT VALUES('A','20061001',1,'A4','5');
INSERT INTO RESULT VALUES('A','20061001',1,'A5','3');
INSERT INTO RESULT VALUES('A','20061001',2,'A1','1');
INSERT INTO RESULT VALUES('A','20061001',3,'A2','1');
INSERT INTO RESULT VALUES('A','20061007',1,'A3','1');
INSERT INTO RESULT VALUES('A','20061011',1,'A4','4');
INSERT INTO RESULT VALUES('A','20061015',1,'A5','1');
INSERT INTO RESULT VALUES('A','20061021',1,'A6','1');
INSERT INTO RESULT VALUES('A','20061021',2,'A1','5');
INSERT INTO RESULT VALUES('A','20061021',3,'A2','1');
INSERT INTO RESULT VALUES('A','20061030',1,'A3','1');
INSERT INTO RESULT VALUES('A','20061030',1,'A3','2');
INSERT INTO RESULT VALUES('A','20061030',1,'A3','3');
INSERT INTO RESULT VALUES('A','20061030',1,'A3','4');
INSERT INTO RESULT VALUES('A','20061030',1,'A3','5');
INSERT INTO RESULT VALUES('B','20061001',1,'B1','1');
INSERT INTO RESULT VALUES('B','20061001',2,'B1','1');
INSERT INTO RESULT VALUES('B','20061001',3,'B1','3');
INSERT INTO RESULT VALUES('B','20061007',1,'B1','3');
INSERT INTO RESULT VALUES('B','20061011',1,'B1','4');
INSERT INTO RESULT VALUES('B','20061015',1,'B1','4');
INSERT INTO RESULT VALUES('B','20061021',1,'B1','4');
INSERT INTO RESULT VALUES('B','20061021',2,'B1','4');
INSERT INTO RESULT VALUES('B','20061021',3,'B1','5');
INSERT INTO RESULT VALUES('B','20061030',1,'B1','5');
INSERT INTO RESULT VALUES('C','20061001',1,'C1','1');
INSERT INTO RESULT VALUES('C','20061001',2,'C1','3');
INSERT INTO RESULT VALUES('C','20061001',3,'C1','3');
INSERT INTO RESULT VALUES('D','20061007',1,'C1','3');
INSERT INTO RESULT VALUES('E','20061011',1,'C1','4');
INSERT INTO RESULT VALUES('F','20061015',1,'C1','4');
INSERT INTO RESULT VALUES('F','20061021',1,'C1','4');
INSERT INTO RESULT VALUES('F','20061021',2,'C1','5');
INSERT INTO RESULT VALUES('F','20061021',3,'C1','5');
INSERT INTO RESULT VALUES('G','20061030',1,'C1','1');
INSERT INTO RESULT VALUES('G','20061030',1,'C2','1');
INSERT INTO RESULT VALUES('G','20061030',1,'C3','1');
INSERT INTO RESULT VALUES('A','20061101',1,'A1','1');
INSERT INTO RESULT VALUES('A','20061101',1,'A2','2');
INSERT INTO RESULT VALUES('A','20061101',1,'A3','4');
INSERT INTO RESULT VALUES('A','20061101',1,'A4','5');
INSERT INTO RESULT VALUES('A','20061101',1,'A5','3');
INSERT INTO RESULT VALUES('A','20061101',2,'A1','1');
INSERT INTO RESULT VALUES('A','20061101',3,'A2','1');
INSERT INTO RESULT VALUES('A','20061107',1,'A3','1');
INSERT INTO RESULT VALUES('A','20061111',1,'A4','4');
INSERT INTO RESULT VALUES('A','20061115',1,'A5','1');
INSERT INTO RESULT VALUES('A','20061121',1,'A6','1');
INSERT INTO RESULT VALUES('A','20061121',2,'A1','5');
INSERT INTO RESULT VALUES('A','20061121',3,'A2','1');
INSERT INTO RESULT VALUES('A','20061130',1,'A3','1');
INSERT INTO RESULT VALUES('A','20061130',1,'A3','2');
INSERT INTO RESULT VALUES('A','20061130',1,'A3','3');
INSERT INTO RESULT VALUES('A','20061130',1,'A3','4');
INSERT INTO RESULT VALUES('A','20061130',1,'A3','5');
INSERT INTO RESULT VALUES('B','20061101',1,'B1','1');
INSERT INTO RESULT VALUES('B','20061101',2,'B1','1');
INSERT INTO RESULT VALUES('B','20061101',3,'B1','3');
INSERT INTO RESULT VALUES('B','20061107',1,'B1','3');
INSERT INTO RESULT VALUES('B','20061111',1,'B1','4');
INSERT INTO RESULT VALUES('B','20061115',1,'B1','4');
INSERT INTO RESULT VALUES('B','20061121',1,'B1','4');
INSERT INTO RESULT VALUES('B','20061121',2,'B1','4');
INSERT INTO RESULT VALUES('B','20061121',3,'B1','5');
INSERT INTO RESULT VALUES('B','20061130',1,'B1','5');
INSERT INTO RESULT VALUES('C','20061101',1,'C1','1');
INSERT INTO RESULT VALUES('C','20061101',2,'C1','3');
INSERT INTO RESULT VALUES('C','20061101',3,'C1','3');
INSERT INTO RESULT VALUES('D','20061107',1,'C1','3');
INSERT INTO RESULT VALUES('E','20061111',1,'C1','4');
INSERT INTO RESULT VALUES('F','20061115',1,'C1','4');
INSERT INTO RESULT VALUES('F','20061121',1,'C1','4');
INSERT INTO RESULT VALUES('F','20061121',2,'C1','5');
INSERT INTO RESULT VALUES('F','20061121',3,'C1','5');
INSERT INTO RESULT VALUES('G','20061130',1,'C1','1');
INSERT INTO RESULT VALUES('G','20061130',1,'C2','1');
INSERT INTO RESULT VALUES('G','20061130',1,'C3','1');
conn sys/loveora77 as sysdba
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;
conn scott/loveora77
set timing on
set autotrace on
select item, decode(NO,1,'checks',2,'passed','perce(%)'), sum(decode(wks,0,coun)),
decode(NO,3,round(sum(childs)*100/sum(pats),3),sum(childs)+sum(pats)),
sum(decode(wks,1,coun)),sum(decode(wks,2,coun)),sum(decode(wks,3,coun)),
sum(decode(wks,4,coun)),sum(decode(wks,5,coun)),sum(decode(wks,6,coun))
from(select item, NO, wks,
decode(NO,3,round(sum(ps_coun)*100/sum(ch_coun),3),
sum(ps_coun)+sum(ch_coun)) coun,
sum(decode(wks,0,null,ch_coun)) pats,
sum(decode(wks,0,null,ps_coun)) childs
from(select item, decode(to_char(to_date(idate,'yyyymmddhh24mi'),'yyyymm'),'200611','0',
ceil((to_char(to_date(idate,'yyyymmddhh24mi'),'dd')+to_char(trunc(to_date(idate,'yyyymmddhh24mi'),'mm'),'d')-1)/7)) wks,
count(*) ch_coun, 0 ps_coun, 1 SW
from INVEST
where chk_item='A3' and idate between '20060101' and '20061231'
group by item, decode(to_char(to_date(idate,'yyyymmddhh24mi'),'yyyymm'),'200611','0',
ceil((to_char(to_date(idate,'yyyymmddhh24mi'),'dd')+to_char(trunc(to_date(idate,'yyyymmddhh24mi'),'mm'),'d')-1)/7))
union all
select item, decode(to_char(to_date(idate,'yyyymmddhh24mi'),'yyyymm'),'200611','0',
ceil((to_char(to_date(idate,'yyyymmddhh24mi'),'dd')+to_char(trunc(to_date(idate,'yyyymmddhh24mi'),'mm'),'d')-1)/7)) wks,
0 ch_coun, count(ps_it) ps_coun, 2 SW
from(select item, idate, count(*) ch_it, sum(decode(greatest(grade,'1'),'1',1)) ps_it
from RESULT
where CHK_ITEM='A3' and idate between '20060101' and '20061231'
group by item, CHK_ITEM, idate, seq)
where (ch_it=ps_it) or (ps_it is not null and rownum=1)
group by item, decode(to_char(to_date(idate,'yyyymmddhh24mi'),'yyyymm'),'200611','0',
ceil((to_char(to_date(idate,'yyyymmddhh24mi'),'dd')+to_char(trunc(to_date(idate,'yyyymmddhh24mi'),'mm'),'d')-1)/7))) x
, COPY_T y
where NO in (SW,3) and NO <= 3
group by item, NO, wks)
group by item, NO;
set timing off
set autotrace off
spool off
{column}