{section}{column:width=50%}
SELECT RPAD(mon,2)||'mon' sec,
SUM(now_mm) now_mm,
SUM(now_mm - bef_mm) rate_m,
TO_CHAR(SUM(DECODE(now_mm,0,NULL,
ROUND((now_mm - bef_mm)*100/now_mm,2))), '990.90' ) rate_p
FROM (SELECT DECODE(NO, 2, MM+1, MM+0) mon,
NVL(SUM(DECODE(NO,1,AMT)), 0) now_mm,
NVL(SUM(DECODE(NO,2,AMT)), 0) bef_mm
FROM(SELECT DECODE(YYMM, '199612', '00',SUBSTR(YYMM,5,2)) MM,
SUM(AMT) AMT
FROM SALE_SUMM
WHERE SAUP= '1공장'
AND YYMM BETWEEN '199612' AND '199712'
GROUP BY DECODE(YYMM, '199612', '00', SUBSTR(YYMM,5,2))) X, COPY_T Y
WHERE NO between decode(MM,'00',2,1) and decode(MM,'12',1,2)
group by DECODE(NO,2,MM+1,MM+0)
UNION ALL
SELECT NO mon, 0 now_mm, 0 bef_mm
FROM COPY_T
WHERE NO <= 12)
GROUP BY mon ;
{column} {column:width=50%}
SEC NOW_MM RATE_M RATE_P
-------- ---------- ---------- -------
1 mon 624091340 -21497840 -3.44
2 mon 762040000 137948660 18.10
3 mon 824160000 62120000 7.54
4 mon 784020000 -40140000 -5.12
5 mon 856000040 71980040 8.41
6 mon 830040000 -25960040 -3.13
7 mon 788184000 -41856000 -5.31
8 mon 840020120 51836120 6.17
9 mon 862000000 21979880 2.55
10 mon 924000000 62000000 6.71
11 mon 856020000 -67980000 -7.94
12 mon 945720000 89700000 9.48
12 rows selected.
Elapsed: 00:00:00.17
{column}{section}{column:width=50%}
Execution Plan
----------------------------------------------------------
Plan hash value: 2933700629
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 27 | 1053 | 14 (36)| 00:00:01 |
| 1 | SORT GROUP BY | | 27 | 1053 | 14 (36)| 00:00:01 |
| 2 | VIEW | | 27 | 1053 | 13 (31)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
| 4 | HASH GROUP BY | | 2 | 40 | 10 (40)| 00:00:01 |
| 5 | VIEW | | 2 | 40 | 9 (34)| 00:00:01 |
| 6 | HASH GROUP BY | | 2 | 52 | 9 (34)| 00:00:01 |
| 7 | MERGE JOIN | | 2 | 52 | 8 (25)| 00:00:01 |
| 8 | SORT JOIN | | 13 | 260 | 4 (25)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | SALE_SUMM | 13 | 260 | 3 (0)| 00:00:01 |
|* 10 | FILTER | | | | | |
|* 11 | SORT JOIN | | 66 | 396 | 4 (25)| 00:00:01 |
| 12 | TABLE ACCESS FULL| COPY_T | 66 | 396 | 3 (0)| 00:00:01 |
|* 13 | TABLE ACCESS FULL | COPY_T | 25 | 75 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - filter("SAUP"='1공장' AND "YYMM">='199612' AND "YYMM"<='199712')
10 - filter("NO"<=DECODE(DECODE("YYMM",'199612','00',SUBSTR("YYMM",5,2)),'12', 1,2))
11 - access("NO">=DECODE(DECODE("YYMM",'199612','00',SUBSTR("YYMM",5,2)),'00', 2,1))
filter("NO">=DECODE(DECODE("YYMM",'199612','00',SUBSTR("YYMM",5,2)),'00', 2,1))
13 - filter("NO"<=12)
{column}