{column:width=50%}
품명 항목 전월 합계 1주 2주 3주 4주 5주 6주
---------------------------------------------------------------------------
A 합격 4 2 2
A 검사 14 14 4 10
A 율(%) 28.571 14.286 50 0
3 rows selected.
Elapsed: 00:00:00.18
{column}{column:width=50%}
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;
{column} {column:width=50%}
Execution Plan
----------------------------------------------------------
Plan hash value: 1059543376
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 71 | 30 (17)| 00:00:01 |
| 1 | HASH GROUP BY | | 1 | 71 | 30 (17)| 00:00:01 |
| 2 | VIEW | | 1 | 71 | 29 (14)| 00:00:01 |
| 3 | HASH GROUP BY | | 1 | 64 | 29 (14)| 00:00:01 |
| 4 | NESTED LOOPS | | 6 | 384 | 28 (11)| 00:00:01 |
| 5 | VIEW | | 16 | 928 | 9 (34)| 00:00:01 |
| 6 | UNION-ALL | | | | | |
| 7 | HASH GROUP BY | | 14 | 280 | 4 (25)| 00:00:01 |
|* 8 | TABLE ACCESS FULL | INVEST | 14 | 280 | 3 (0)| 00:00:01 |
| 9 | HASH GROUP BY | | 2 | 74 | 5 (40)| 00:00:01 |
| 10 | COUNT | | | | | |
|* 11 | FILTER | | | | | |
| 12 | VIEW | | 2 | 74 | 4 (25)| 00:00:01 |
| 13 | HASH GROUP BY | | 2 | 38 | 4 (25)| 00:00:01 |
|* 14 | TABLE ACCESS FULL| RESULT | 8 | 152 | 3 (0)| 00:00:01 |
|* 15 | TABLE ACCESS FULL | COPY_T | 1 | 6 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - filter("CHK_ITEM"='A3' AND "IDATE">='20060101' AND "IDATE"<='20061231')
11 - filter("CH_IT"="PS_IT" OR "PS_IT" IS NOT NULL AND ROWNUM=1)
14 - filter("CHK_ITEM"='A3' AND "IDATE">='20060101' AND "IDATE"<='20061231')
{column}