Step 1. 6개의 예측 결과값을 병합한다.
QTY : 2019년 18주차의 실제 판매량
FCST : 6주 전 ~ 1주 전 예측 판매량
Step 2. 예측한 값과 실제 값을 뺀 절대값을 구한다.
ABS8W : ABS(FCST - QTY)로 예측과 실제의 편차의 절댓값
주의 1 : FCST null값 처리에 신경 쓴다.
Step 3. FCST의 정확도를 ABS를 이용해 구한다. (값이 0인 경우 0처리, 이상치 정제 처리)
ACC8W : 1 ) QTY(실제) 또는 FCST(예측)이 존재하지 않을 경우 0으로(이 경우 계산이 의미 없으니까),
2 ) 그렇지 않은 경우 QTY/FCST가 2보다 크다면(실제 판매량이 예측치의 2배를 초과한다면) 이 역시 예측의 범위를 크게 벗어나는 특정한 값으로 버린다. 0으로
3 ) QTY/FCST가 2보다 작거나 같다면 의미 있는 값으로, 1 - (ABS8W / FCST), 오차값(ABS8W)를 기준으로 한 FCST의 정확도다.
주의 1 : 보통 정확도를 계산할 때 '예측/실제' 이렇게 'FCST/QTY'로 실제 대비 얼마나 맞췄냐를 계산하는데 ACC8W는 그렇지 않다. 예측치(FCST)의 오차값(ABS8W)를 기준으로 정확도를 계산한 것으로, 1 - (ABS8W / FCST)가 되는 것이다. 정확히 맞추었을 경우 ABS8W가 0이 되니까 1이 되고, 오차가 커 ABS8W가 커질수록 1에서 작아지게된다. 이 값은 QTY/FCST를 한 값과는 다른 값이 도출된다.
주의 2 : 검증시 엑셀과 오라클 둘의 계산 정확도 범위가 달라 미세한 산술 오차가 발생한다. 해결 방법은 엑셀과 오라클 모두 같은 수준으로 반올림 해서 자릿수를 제한하거나, 오라클에서 특정 값 이하의 오차는 무시하도록 해야한다. 엑셀을 건드리지 않기 위해 후자의 방법을 사용했다. 검증 2단계에서는 오차가 10^(-8)보다 작을 경우 무시하고, 검증 3단계에서는 개별 값에 대한 편차로 더욱 엄격한 기준을 적용해 10^(-10)보다 작을 경우 무시했다. (단, FCST_AVG에서는 개별 값에서 오차를 10^(-10)으로 잡으면 산술 오차가 검출이 되어 동일하게 10^(-8)로 잡았다.)
Step 4. 예측값(FCST)와 위 Step 3에서 구한 예측값 정확도를 곱한다.
ACC8W_QTY : FCST x ACC8W, 예측값과, 예측값의 정확도를 곱한다.
주의 1 : FCST null값 처리만 신경쓰면 된다.
주의 2 : Step 3의 결과를 이용하기 때문에 마찬가지로 산술 오차에 대해 무시할 범위를 지정해줘야한다.
Step 5. 각 row의 FCST 6 ~ 1 주차 평균을 구한다.
FCST_AVG : null값인 경우 나누는 분모의 개수에 더하지 않는다. 0인 경우는 더한다.
즉, 컬럼 6개가 모두 값이 있다면 모두 FCST의 합을 6으로 나누고,
3개가 값이 있고 3개가 null이라면 FCST의 합을 3으로 나누고,
3개가 유효한 값이 있고 1개가 0이라면 FCST의 합을 4로 나눈다. (어쨌든 0이라는 값이 있고 4개의 값이 있는거니까)
그래서... 6개의 컬럼의 값이 각각 null이 있는지 판단하고, null이 아닌 경우만 더하고, 그 개수로 나눠야하는건데... 이렇게 하려면 수식이 복잡하고 어려워진다.
A + B + C 같은 단순 수식에서 조차 하나라도 null이면 결과가 null이 되기 때문에, 차라리 덧셈에서 에러 나지 않도록 null인 경우 모두 0으로 DECODE를 해서 더해주고, null이 아닌 경우의 개수를 구해서 나눠주는 방법으로 접근했다. (DECODE 함수 대신 null값을 치환해주는 NVL 함수를 써도 된다.)
주의 : 일괄적으로 6으로 나누지 않도록 한다.
Step 6. 각 row의 ACC8W_QTY 6 ~ 1 주차 평균을 구한다.
여기는 딱히 주의할 게 없다.
주의(거의 모든 단계) : FCST가 0으로 처리된 경우도 있지만 처리되지 못한 채 빈 값(null)로 들어온 경우도 있다. 계산에서 FCST가 사용되는 모든 과정에서 null처리를 해줘야한다.(ABS8W, ACC8W, ACC8W_QTY, FCST_AVG)
주의 : 눈으로 볼 때도 엑셀과 동일한 차트를 보기 위해 모든 단계에서 SEG1, SEG2, SEG3 순으로 오름차순 정렬했다. 단, 데이터베이스는 순서가 없기 때문에 이 정렬 과정은 사실 없어도 된다. 그리고... 어차피 엑셀이 완전히 오름차순 정렬이 다 되어있는게 아니라 중간중간 어긋나는 순서가 있다 ㅜㅜ
----------------------------------------------------------------------------------------------------------------------------
Step 7. Step 6에서 완성된 테이블을 이용해 SEG1 그룹별 정확도를 계산한다.
UNION을 사용하기 전에는 ORDER BY를 이용한 정렬과, AS '한글' 수식이 모두 먹혔지만 UNION을 한 이후에는 정렬도, 한글 수식도 먹히지 않는 것에 유의한다.
만약 정렬과 한글 수식을 하고 싶다면 서브쿼리만으로는 불가능하고, 중간에 테이블을 저장한 다음 그 테이블을 불러와 작업하는 방법으로 해야한다.
검증단계에 대한 설명
엑셀의 답지와, 내가 풀어서 도출한 결과를 비교한다.
검증 1 : 컬럼수가 일치하는지 확인
검증 2 : (답지의 특정 컬럼의 합) - (내가 풀어서 도출한 결과의 특정 컬럼의 합)을 비교한다. 0이 나와야 정상.
검증 3 : 전체 합의 경우 놓치는 경우가 있을 수 있다. 각 행렬을 모두 비교해 차이가 0인지 확인한다. 0이 나와야 정상.
검증 3.1 : 위 검증 3번을 일일히 눈으로 확인하기 어렵다. 따라서 검증 3의 경과가 0이 아닌 행렬이 있는지 COUNT를 해서 확인한다.
0이 나와야 정상이고, 검증 2를 통과했으나, 검증 3에서 통과 못 한 경우 검증 3.1이 최소 2개 이상을 발견할 것이다.
(오차가 +1, -1 이면 합은 0이니까 검증 2를 통과했다면 COUNT는 최소 2가 된다.)
검증 3.2 : 위 검증 3.1에서 통과하지 못 한 경우, 해당 값이 있는 row가 어디인지를 알려준다. 문제의 값을 포함하는 row를 알려준다.
정답지 엑셀 파일 가져오기...
테이블 생성 쿼리
CREATE TABLE PRO_FCST_EXCEL_KEY
(
SEG1 VARCHAR2(100),
SEG2 VARCHAR2(100),
SEG3 VARCHAR2(100),
TARGETWEEK VARCHAR2(100),
"YEAR" VARCHAR2(100),
"WEEK" VARCHAR2(100),
QTY NUMBER,
FCST_W6 NUMBER,
FCST_W5 NUMBER,
FCST_W4 NUMBER,
FCST_W3 NUMBER,
FCST_W2 NUMBER,
FCST_W1 NUMBER,
ABS8W_W6 NUMBER,
ABS8W_W5 NUMBER,
ABS8W_W4 NUMBER,
ABS8W_W3 NUMBER,
ABS8W_W2 NUMBER,
ABS8W_W1 NUMBER,
ACC8W_W6 NUMBER,
ACC8W_W5 NUMBER,
ACC8W_W4 NUMBER,
ACC8W_W3 NUMBER,
ACC8W_W2 NUMBER,
ACC8W_W1 NUMBER,
ACC8W_QTY_W6 NUMBER,
ACC8W_QTY_W5 NUMBER,
ACC8W_QTY_W4 NUMBER,
ACC8W_QTY_W3 NUMBER,
ACC8W_QTY_W2 NUMBER,
ACC8W_QTY_W1 NUMBER,
FCST_AVG NUMBER,
ACC_AVG NUMBER
);
컬럼헤더가 2줄 이상이라 테이블을 제대로 못 가져오는 문제가 있었다. -> 쓸모 없는 줄 삭제로 해결.
엑셀에서 특정 테이블을 선택해도 1번 테이블에 있는 컬럼수를 자꾸 인식해서 제대로 못 가져오는 문제가 있었다. -> 해당 테이블 하나만 존재하는 새 엑셀파일을 만들어 해결.
★★★★★★★★★★★★★★★★★ 쿼리 ★★★★★★★★★★★★★★★★★
Step 1.
더보기
-- Step 1. 6개의 예측 결과값을 병합한다.
-- 주의 : 최근 주차를 기준으로 과거 데이터를 붙인다.
--CREATE TABLE MAPE_STEP1 AS --CREATE 한 후 주석처리
SELECT A.PRD_SEG1 AS SEG1,
A.PRD_SEG2 AS SEG2,
11||A.PRD_SEG3 AS SEG3,
A.YEAR||A.WEEK AS TARGETWEEK,
A.YEAR,
A.WEEK,
A.QTY,
F.OUTFCST AS FCST_W6,
E.OUTFCST AS FCST_W5,
D.OUTFCST AS FCST_W4,
C.OUTFCST AS FCST_W3,
B.OUTFCST AS FCST_W2,
A.OUTFCST AS FCST_W1
FROM PRO_FCST_RESULT_1WEEK A
LEFT JOIN PRO_FCST_RESULT_2WEEK B
ON 1=1
AND (A.PRD_SEG1 = B.PRD_SEG1)
AND (A.PRD_SEG2 = B.PRD_SEG2)
AND (A.PRD_SEG3 = B.PRD_SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
LEFT JOIN PRO_FCST_RESULT_3WEEK C
ON 1=1
AND (A.PRD_SEG1 = C.PRD_SEG1)
AND (A.PRD_SEG2 = C.PRD_SEG2)
AND (A.PRD_SEG3 = C.PRD_SEG3)
AND (A.YEAR = C.YEAR)
AND (A.WEEK = C.WEEK AND A.WEEK = 18)
LEFT JOIN PRO_FCST_RESULT_4WEEK D
ON 1=1
AND (A.PRD_SEG1 = D.PRD_SEG1)
AND (A.PRD_SEG2 = D.PRD_SEG2)
AND (A.PRD_SEG3 = D.PRD_SEG3)
AND (A.YEAR = D.YEAR)
AND (A.WEEK = D.WEEK AND A.WEEK = 18)
LEFT JOIN PRO_FCST_RESULT_5WEEK E
ON 1=1
AND (A.PRD_SEG1 = E.PRD_SEG1)
AND (A.PRD_SEG2 = E.PRD_SEG2)
AND (A.PRD_SEG3 = E.PRD_SEG3)
AND (A.YEAR = E.YEAR)
AND (A.WEEK = E.WEEK AND A.WEEK = 18)
LEFT JOIN PRO_FCST_RESULT_6WEEK F
ON 1=1
AND (A.PRD_SEG1 = F.PRD_SEG1)
AND (A.PRD_SEG2 = F.PRD_SEG2)
AND (A.PRD_SEG3 = F.PRD_SEG3)
AND (A.YEAR = F.YEAR)
AND (A.WEEK = F.WEEK AND A.WEEK = 18)
ORDER BY SEG1 ASC, SEG2 ASC, SEG3 ASC;
-- STEP 1. 검증
SELECT * FROM MAPE_STEP1; -- 스텝 1단계 저장본.. 문제푼거
SELECT * FROM PRO_FCST_EXCEL_KEY -- 엑셀파일.. 답지
ORDER BY SEG1 ASC, SEG2 ASC, SEG3 ASC;
-- 검증 1. 컬럼수 비교
SELECT COUNT(*)
FROM MAPE_STEP1;
SELECT COUNT(*)
FROM PRO_FCST_EXCEL_KEY;
-- 검증 2. 합의 차 비교
SELECT SUM(A.FCST_W6 - B.FCST_W6) AS DIFF_FCST_W6,
SUM(A.FCST_W5 - B.FCST_W5) AS DIFF_FCST_W5,
SUM(A.FCST_W4 - B.FCST_W4) AS DIFF_FCST_W4,
SUM(A.FCST_W3 - B.FCST_W3) AS DIFF_FCST_W3,
SUM(A.FCST_W2 - B.FCST_W2) AS DIFF_FCST_W2,
SUM(A.FCST_W1 - B.FCST_W1) AS DIFF_FCST_W1
FROM MAPE_STEP1 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
-- 검증 3. 각 컬럼 차 비교
SELECT (A.FCST_W6 - B.FCST_W6) AS DIFF_FCST_W6,
(A.FCST_W5 - B.FCST_W5) AS DIFF_FCST_W5,
(A.FCST_W4 - B.FCST_W4) AS DIFF_FCST_W4,
(A.FCST_W3 - B.FCST_W3) AS DIFF_FCST_W3,
(A.FCST_W2 - B.FCST_W2) AS DIFF_FCST_W2,
(A.FCST_W1 - B.FCST_W1) AS DIFF_FCST_W1
FROM MAPE_STEP1 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
-- 검증 3.1 (Advanced)각 컬럼 차 비교 중 0이 아닌 것의 수를 조회
-- 주의 : 각 컬럼별로 조회되어야 하는데 AND나 OR 조건을 여러 컬럼에 동시에 넣을 수 없으니 하나씩 주석 해제 해가며 조회한다.
SELECT COUNT(DIFF_FCST_W6),
COUNT(DIFF_FCST_W5),
COUNT(DIFF_FCST_W4),
COUNT(DIFF_FCST_W3),
COUNT(DIFF_FCST_W2),
COUNT(DIFF_FCST_W1)
FROM(
SELECT (A.FCST_W6 - B.FCST_W6) AS DIFF_FCST_W6,
(A.FCST_W5 - B.FCST_W5) AS DIFF_FCST_W5,
(A.FCST_W4 - B.FCST_W4) AS DIFF_FCST_W4,
(A.FCST_W3 - B.FCST_W3) AS DIFF_FCST_W3,
(A.FCST_W2 - B.FCST_W2) AS DIFF_FCST_W2,
(A.FCST_W1 - B.FCST_W1) AS DIFF_FCST_W1
FROM MAPE_STEP1 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
)
WHERE 1=1
--AND DIFF_FCST_W6 != 0;
AND DIFF_FCST_W5 != 0;
--AND DIFF_FCST_W4 != 0;
--AND DIFF_FCST_W3 != 0;
--AND DIFF_FCST_W2 != 0;
--AND DIFF_FCST_W1 != 0;
-- 검증 3.2 (Advanced)각 컬럼 차 비교 중 0이 아닌 것의 수가 조회될 경우 어떤 row인지 찾는다.
-- 주의 : 각 컬럼별로 조회되어야 하는데 AND나 OR 조건을 여러 컬럼에 동시에 넣을 수 없으니 하나씩 주석 해제 해가며 조회한다.
SELECT B.*
FROM(
SELECT A.*,
(A.FCST_W6 - B.FCST_W6) AS DIFF_FCST_W6,
(A.FCST_W5 - B.FCST_W5) AS DIFF_FCST_W5,
(A.FCST_W4 - B.FCST_W4) AS DIFF_FCST_W4,
(A.FCST_W3 - B.FCST_W3) AS DIFF_FCST_W3,
(A.FCST_W2 - B.FCST_W2) AS DIFF_FCST_W2,
(A.FCST_W1 - B.FCST_W1) AS DIFF_FCST_W1
FROM MAPE_STEP1 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
) B
WHERE 1=1
--AND DIFF_FCST_W6 != 0;
--AND DIFF_FCST_W5 != 0;
AND DIFF_FCST_W4 != 0;
--AND DIFF_FCST_W3 != 0;
--AND DIFF_FCST_W2 != 0;
--AND DIFF_FCST_W1 != 0;Step 2.
더보기
-- Step 2. 예측한 값과 실제 값을 뺀 절대값을 구한다.
-- 주의 : FROM 절에 MAPE_STEP1를 지우고 (괄호)를 만들고, 그 괄호 안에 STEP1의 쿼리를 전부 서브쿼리로 복붙한다.
--CREATE TABLE MAPE_STEP2 AS -- CREATE 한 후 주석처리
SELECT A.*,
CASE WHEN A.FCST_W6 IS NOT NULL THEN ABS(A.QTY - A.FCST_W6)
ELSE A.QTY END
AS ABS8W_W6,
CASE WHEN A.FCST_W5 IS NOT NULL THEN ABS(A.QTY - A.FCST_W5)
ELSE A.QTY END
AS ABS8W_W5,
CASE WHEN A.FCST_W4 IS NOT NULL THEN ABS(A.QTY - A.FCST_W4)
ELSE A.QTY END
AS ABS8W_W4,
CASE WHEN A.FCST_W3 IS NOT NULL THEN ABS(A.QTY - A.FCST_W3)
ELSE A.QTY END
AS ABS8W_W3,
CASE WHEN A.FCST_W2 IS NOT NULL THEN ABS(A.QTY - A.FCST_W2)
ELSE A.QTY END
AS ABS8W_W2,
CASE WHEN A.FCST_W1 IS NOT NULL THEN ABS(A.QTY - A.FCST_W1)
ELSE A.QTY END
AS ABS8W_W1
FROM MAPE_STEP1 A
ORDER BY SEG1 ASC, SEG2 ASC, SEG3 ASC;
-- STEP 2. 검증
SELECT * FROM MAPE_STEP2; -- 스텝 2단계 저장본.. 문제푼거
SELECT * FROM PRO_FCST_EXCEL_KEY -- 엑셀파일.. 답지
ORDER BY SEG1 ASC, SEG2 ASC, SEG3 ASC;
-- 검증 1. 컬럼수 비교
SELECT COUNT(*)
FROM MAPE_STEP2;
SELECT COUNT(*)
FROM PRO_FCST_EXCEL_KEY;
-- 검증 2. 합의 차 비교
SELECT SUM(A.ABS8W_W6 - B.ABS8W_W6) AS DIFF_ABS8W_W6,
SUM(A.ABS8W_W5 - B.ABS8W_W5) AS DIFF_ABS8W_W5,
SUM(A.ABS8W_W4 - B.ABS8W_W4) AS DIFF_ABS8W_W4,
SUM(A.ABS8W_W3 - B.ABS8W_W3) AS DIFF_ABS8W_W3,
SUM(A.ABS8W_W2 - B.ABS8W_W2) AS DIFF_ABS8W_W2,
SUM(A.ABS8W_W1 - B.ABS8W_W1) AS DIFF_ABS8W_W1
FROM MAPE_STEP2 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
-- 검증 3. 각 컬럼 차 비교
SELECT (A.ABS8W_W6 - B.ABS8W_W6) AS DIFF_ABS8W_W6,
(A.ABS8W_W5 - B.ABS8W_W5) AS DIFF_ABS8W_W5,
(A.ABS8W_W4 - B.ABS8W_W4) AS DIFF_ABS8W_W4,
(A.ABS8W_W3 - B.ABS8W_W3) AS DIFF_ABS8W_W3,
(A.ABS8W_W2 - B.ABS8W_W2) AS DIFF_ABS8W_W2,
(A.ABS8W_W1 - B.ABS8W_W1) AS DIFF_ABS8W_W1
FROM MAPE_STEP2 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
-- 검증 3.1 (Advanced)각 컬럼 차 비교 중 0이 아닌 것의 수를 조회
-- 주의 : 각 컬럼별로 조회되어야 하는데 AND나 OR 조건을 여러 컬럼에 동시에 넣을 수 없으니 하나씩 주석 해제 해가며 조회한다.
SELECT COUNT(DIFF_ABS8W_W6),
COUNT(DIFF_ABS8W_W5),
COUNT(DIFF_ABS8W_W4),
COUNT(DIFF_ABS8W_W3),
COUNT(DIFF_ABS8W_W2),
COUNT(DIFF_ABS8W_W1)
FROM(
SELECT (A.ABS8W_W6 - B.ABS8W_W6) AS DIFF_ABS8W_W6,
(A.ABS8W_W5 - B.ABS8W_W5) AS DIFF_ABS8W_W5,
(A.ABS8W_W4 - B.ABS8W_W4) AS DIFF_ABS8W_W4,
(A.ABS8W_W3 - B.ABS8W_W3) AS DIFF_ABS8W_W3,
(A.ABS8W_W2 - B.ABS8W_W2) AS DIFF_ABS8W_W2,
(A.ABS8W_W1 - B.ABS8W_W1) AS DIFF_ABS8W_W1
FROM MAPE_STEP2 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
)
WHERE 1=1
--AND DIFF_ABS8W_W6 != 0;
AND DIFF_ABS8W_W5 != 0;
--AND DIFF_ABS8W_W4 != 0;
--AND DIFF_ABS8W_W3 != 0;
--AND DIFF_ABS8W_W2 != 0;
--AND DIFF_ABS8W_W1 != 0;
-- 검증 3.2 (Advanced)각 컬럼 차 비교 중 0이 아닌 것의 수가 조회될 경우 어떤 row인지 찾는다.
-- 주의 : 각 컬럼별로 조회되어야 하는데 AND나 OR 조건을 여러 컬럼에 동시에 넣을 수 없으니 하나씩 주석 해제 해가며 조회한다.
SELECT B.*
FROM(
SELECT A.*,
(A.ABS8W_W6 - B.ABS8W_W6) AS DIFF_ABS8W_W6,
(A.ABS8W_W5 - B.ABS8W_W5) AS DIFF_ABS8W_W5,
(A.ABS8W_W4 - B.ABS8W_W4) AS DIFF_ABS8W_W4,
(A.ABS8W_W3 - B.ABS8W_W3) AS DIFF_ABS8W_W3,
(A.ABS8W_W2 - B.ABS8W_W2) AS DIFF_ABS8W_W2,
(A.ABS8W_W1 - B.ABS8W_W1) AS DIFF_ABS8W_W1
FROM MAPE_STEP2 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
) B
WHERE 1=1
--AND DIFF_ABS8W_W6 != 0;
--AND DIFF_ABS8W_W5 != 0;
AND DIFF_ABS8W_W4 != 0;
--AND DIFF_ABS8W_W3 != 0;
--AND DIFF_ABS8W_W2 != 0;
--AND DIFF_ABS8W_W1 != 0;Step 3.
더보기
-- Step 3. FCST의 정확도를 ABS를 이용해 구한다. (값이 0인 경우 0처리, 이상치 정제 처리)
-- 주의 : CASE WHEN, WHEN... 으로 다중 IF ESLEIF ESLEIF... 로직 처리 후 마지막 ELSE가 끝나면 반드시 END로 전체 CASE WHEN 조건문을 닫아준다.
-- DECODE는 특정 컬럼에 대해 값을 동등한지 비교해 새로운 값으로 처리해주는 것으로 조건을 비교할 컬럼이 바뀌는 경우 혹은 '='이 아닌 부등호 '>', '<'는 처리할 수 없다.
--CREATE TABLE MAPE_STEP3 AS -- CREATE 한 후 주석처리
SELECT A.*,
CASE WHEN ((A.FCST_W6 = 0 OR A.FCST_W6 IS NULL) OR (A.QTY = 0)) THEN 0
WHEN (A.QTY / A.FCST_W6) > 2 THEN 0
ELSE (1 - (A.ABS8W_W6 / A.FCST_W6)) END
AS ACC8W_W6,
CASE WHEN ((A.FCST_W5 = 0 OR A.FCST_W5 IS NULL) OR (A.QTY = 0)) THEN 0
WHEN (A.QTY / A.FCST_W5) > 2 THEN 0
ELSE (1 - (A.ABS8W_W5 / A.FCST_W5)) END
AS ACC8W_W5,
CASE WHEN ((A.FCST_W4 = 0 OR A.FCST_W4 IS NULL) OR (A.QTY = 0)) THEN 0
WHEN (A.QTY / A.FCST_W4) > 2 THEN 0
ELSE (1 - (A.ABS8W_W4 / A.FCST_W4)) END
AS ACC8W_W4,
CASE WHEN ((A.FCST_W3 = 0 OR A.FCST_W3 IS NULL) OR (A.QTY = 0)) THEN 0
WHEN (A.QTY / A.FCST_W3) > 2 THEN 0
ELSE (1 - (A.ABS8W_W3 / A.FCST_W3)) END
AS ACC8W_W3,
CASE WHEN ((A.FCST_W2 = 0 OR A.FCST_W2 IS NULL) OR (A.QTY = 0)) THEN 0
WHEN (A.QTY / A.FCST_W2) > 2 THEN 0
ELSE (1 - (A.ABS8W_W2 / A.FCST_W2)) END
AS ACC8W_W2,
CASE WHEN ((A.FCST_W1 = 0 OR A.FCST_W1 IS NULL) OR (A.QTY = 0)) THEN 0
WHEN (A.QTY / A.FCST_W1) > 2 THEN 0
ELSE (1 - (A.ABS8W_W1 / A.FCST_W1)) END
AS ACC8W_W1
FROM MAPE_STEP2 A
ORDER BY SEG1 ASC, SEG2 ASC, SEG3 ASC;
-- STEP 3. 검증
SELECT * FROM MAPE_STEP3; -- 스텝 3단계 저장본.. 문제푼거
SELECT * FROM PRO_FCST_EXCEL_KEY -- 엑셀파일.. 답지
ORDER BY SEG1 ASC, SEG2 ASC, SEG3 ASC;
-- 검증 1. 컬럼수 비교
SELECT COUNT(*)
FROM MAPE_STEP3;
SELECT COUNT(*)
FROM PRO_FCST_EXCEL_KEY;
-- 검증 2. 합의 차 비교
-- 주의 : 그냥 'SUM(A.ACC8W_W6 - B.ACC8W_W6) AS DIFF_ACC8W_W6'을 할 경우 엑셀 답지와 오라클이 직접 계산한 결과가 약간씩 오차가 발생한다.
-- 따라서 오차가 일정 크기보다 작으면 0으로 처리한다. 여기서는 10^(-8)보다 작을 경우로 했다.
SELECT CASE WHEN(ABS(SUM(A.ACC8W_W6 - B.ACC8W_W6)) < POWER(10,-8)) THEN 0
ELSE SUM(A.ACC8W_W6 - B.ACC8W_W6) END
AS DIFF_ACC8W_W6,
CASE WHEN(ABS(SUM(A.ACC8W_W5 - B.ACC8W_W5)) < POWER(10,-8)) THEN 0
ELSE SUM(A.ACC8W_W5 - B.ACC8W_W5) END
AS DIFF_ACC8W_W5,
CASE WHEN(ABS(SUM(A.ACC8W_W4 - B.ACC8W_W4)) < POWER(10,-8)) THEN 0
ELSE SUM(A.ACC8W_W4 - B.ACC8W_W4) END
AS DIFF_ACC8W_W4,
CASE WHEN(ABS(SUM(A.ACC8W_W3 - B.ACC8W_W3)) < POWER(10,-8)) THEN 0
ELSE SUM(A.ACC8W_W3 - B.ACC8W_W3) END
AS DIFF_ACC8W_W3,
CASE WHEN(ABS(SUM(A.ACC8W_W2 - B.ACC8W_W2)) < POWER(10,-8)) THEN 0
ELSE SUM(A.ACC8W_W2 - B.ACC8W_W2) END
AS DIFF_ACC8W_W2,
CASE WHEN(ABS(SUM(A.ACC8W_W1 - B.ACC8W_W1)) < POWER(10,-8)) THEN 0
ELSE SUM(A.ACC8W_W1 - B.ACC8W_W1) END
AS DIFF_ACC8W_W1
FROM MAPE_STEP3 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
-- 검증 3. 각 컬럼 차 비교
-- 주의 : 마찬가지로 오차가 일정 크기보다 작으면 0으로 처리한다.
-- 여기서는 각 행렬 단일 비교이므로 조금 더 엄격한 기준으로10^(-10)보다 작을 경우로 했다.
SELECT CASE WHEN(ABS(A.ACC8W_W6 - B.ACC8W_W6) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_W6 - B.ACC8W_W6 END
AS DIFF_ACC8W_W6,
CASE WHEN(ABS(A.ACC8W_W5 - B.ACC8W_W5) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_W5 - B.ACC8W_W5 END
AS DIFF_ACC8W_W5,
CASE WHEN(ABS(A.ACC8W_W4 - B.ACC8W_W4) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_W4 - B.ACC8W_W4 END
AS DIFF_ACC8W_W4,
CASE WHEN(ABS(A.ACC8W_W3 - B.ACC8W_W3) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_W3 - B.ACC8W_W3 END
AS DIFF_ACC8W_W3,
CASE WHEN(ABS(A.ACC8W_W2 - B.ACC8W_W2) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_W2 - B.ACC8W_W2 END
AS DIFF_ACC8W_W2,
CASE WHEN(ABS(A.ACC8W_W1 - B.ACC8W_W1) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_W1 - B.ACC8W_W1 END
AS DIFF_ACC8W_W1
FROM MAPE_STEP3 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
-- 검증 3.1 (Advanced)각 컬럼 차 비교 중 0이 아닌 것의 수를 조회
-- 주의 : 각 컬럼별로 조회되어야 하는데 AND나 OR 조건을 여러 컬럼에 동시에 넣을 수 없으니 하나씩 주석 해제 해가며 조회한다.
SELECT COUNT(DIFF_ACC8W_W6),
COUNT(DIFF_ACC8W_W5),
COUNT(DIFF_ACC8W_W4),
COUNT(DIFF_ACC8W_W3),
COUNT(DIFF_ACC8W_W2),
COUNT(DIFF_ACC8W_W1)
FROM(
SELECT CASE WHEN(ABS(A.ACC8W_W6 - B.ACC8W_W6) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_W6 - B.ACC8W_W6 END
AS DIFF_ACC8W_W6,
CASE WHEN(ABS(A.ACC8W_W5 - B.ACC8W_W5) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_W5 - B.ACC8W_W5 END
AS DIFF_ACC8W_W5,
CASE WHEN(ABS(A.ACC8W_W4 - B.ACC8W_W4) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_W4 - B.ACC8W_W4 END
AS DIFF_ACC8W_W4,
CASE WHEN(ABS(A.ACC8W_W3 - B.ACC8W_W3) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_W3 - B.ACC8W_W3 END
AS DIFF_ACC8W_W3,
CASE WHEN(ABS(A.ACC8W_W2 - B.ACC8W_W2) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_W2 - B.ACC8W_W2 END
AS DIFF_ACC8W_W2,
CASE WHEN(ABS(A.ACC8W_W1 - B.ACC8W_W1) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_W1 - B.ACC8W_W1 END
AS DIFF_ACC8W_W1
FROM MAPE_STEP3 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
)
WHERE 1=1
--AND DIFF_ACC8W_W6 != 0;
AND DIFF_ACC8W_W5 != 0;
--AND DIFF_ACC8W_W4 != 0;
--AND DIFF_ACC8W_W3 != 0;
--AND DIFF_ACC8W_W2 != 0;
--AND DIFF_ACC8W_W1 != 0;
-- 검증 3.2 (Advanced)각 컬럼 차 비교 중 0이 아닌 것의 수가 조회될 경우 어떤 row인지 찾는다.
-- 주의 : 각 컬럼별로 조회되어야 하는데 AND나 OR 조건을 여러 컬럼에 동시에 넣을 수 없으니 하나씩 주석 해제 해가며 조회한다.
SELECT B.*
FROM(
SELECT A.*,
CASE WHEN(ABS(A.ACC8W_W6 - B.ACC8W_W6) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_W6 - B.ACC8W_W6 END
AS DIFF_ACC8W_W6,
CASE WHEN(ABS(A.ACC8W_W5 - B.ACC8W_W5) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_W5 - B.ACC8W_W5 END
AS DIFF_ACC8W_W5,
CASE WHEN(ABS(A.ACC8W_W4 - B.ACC8W_W4) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_W4 - B.ACC8W_W4 END
AS DIFF_ACC8W_W4,
CASE WHEN(ABS(A.ACC8W_W3 - B.ACC8W_W3) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_W3 - B.ACC8W_W3 END
AS DIFF_ACC8W_W3,
CASE WHEN(ABS(A.ACC8W_W2 - B.ACC8W_W2) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_W2 - B.ACC8W_W2 END
AS DIFF_ACC8W_W2,
CASE WHEN(ABS(A.ACC8W_W1 - B.ACC8W_W1) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_W1 - B.ACC8W_W1 END
AS DIFF_ACC8W_W1
FROM MAPE_STEP3 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
) B
WHERE 1=1
--AND DIFF_ACC8W_W6 != 0;
--AND DIFF_ACC8W_W5 != 0;
AND DIFF_ACC8W_W4 != 0;
--AND DIFF_ACC8W_W3 != 0;
--AND DIFF_ACC8W_W2 != 0;
--AND DIFF_ACC8W_W1 != 0;Step 4.
더보기
-- Step 4. 예측값(FCST)와 위 Step 3에서 구한 예측값 정확도를 곱한다.
-- 주의 : 위 Step 2, Step 3과 마찬가지로 FCST의 null값 처리만 신경쓰면 된다.
--CREATE TABLE MAPE_STEP4 AS -- CREATE 한 후 주석처리
SELECT A.*,
CASE WHEN (A.FCST_W6 IS NOT NULL) THEN A.FCST_W6 * A.ACC8W_W6
ELSE 0 END
AS ACC8W_QTY_W6,
CASE WHEN (A.FCST_W5 IS NOT NULL) THEN A.FCST_W5 * A.ACC8W_W5
ELSE 0 END
AS ACC8W_QTY_W5,
CASE WHEN (A.FCST_W4 IS NOT NULL) THEN A.FCST_W4 * A.ACC8W_W4
ELSE 0 END
AS ACC8W_QTY_W4,
CASE WHEN (A.FCST_W3 IS NOT NULL) THEN A.FCST_W3 * A.ACC8W_W3
ELSE 0 END
AS ACC8W_QTY_W3,
CASE WHEN (A.FCST_W2 IS NOT NULL) THEN A.FCST_W2 * A.ACC8W_W2
ELSE 0 END
AS ACC8W_QTY_W2,
CASE WHEN (A.FCST_W1 IS NOT NULL) THEN A.FCST_W1 * A.ACC8W_W1
ELSE 0 END
AS ACC8W_QTY_W1
FROM MAPE_STEP3 A
ORDER BY SEG1 ASC, SEG2 ASC, SEG3 ASC;
-- STEP 4. 검증
SELECT * FROM MAPE_STEP4; -- 스텝 4단계 저장본.. 문제푼거
SELECT * FROM PRO_FCST_EXCEL_KEY -- 엑셀파일.. 답지
ORDER BY SEG1 ASC, SEG2 ASC, SEG3 ASC;
-- 검증 1. 컬럼수 비교
SELECT COUNT(*)
FROM MAPE_STEP4;
SELECT COUNT(*)
FROM PRO_FCST_EXCEL_KEY;
-- 검증 2. 합의 차 비교
-- 주의 : 그냥 'SUM(A.ACC8W_QTY_W6 - B.ACC8W_QTY_W6) AS DIFF_ACC8W_QTY_W6'을 할 경우 엑셀 답지와 오라클이 직접 계산한 결과가 약간씩 오차가 발생한다.
-- 따라서 오차가 일정 크기보다 작으면 0으로 처리한다. 여기서는 10^(-8)보다 작을 경우로 했다.
SELECT CASE WHEN(ABS(SUM(A.ACC8W_QTY_W6 - B.ACC8W_QTY_W6)) < POWER(10,-8)) THEN 0
ELSE SUM(A.ACC8W_QTY_W6 - B.ACC8W_QTY_W6) END
AS DIFF_ACC8W_QTY_W6,
CASE WHEN(ABS(SUM(A.ACC8W_QTY_W5 - B.ACC8W_QTY_W5)) < POWER(10,-8)) THEN 0
ELSE SUM(A.ACC8W_QTY_W5 - B.ACC8W_QTY_W5) END
AS DIFF_ACC8W_QTY_W5,
CASE WHEN(ABS(SUM(A.ACC8W_QTY_W4 - B.ACC8W_QTY_W4)) < POWER(10,-8)) THEN 0
ELSE SUM(A.ACC8W_QTY_W4 - B.ACC8W_QTY_W4) END
AS DIFF_ACC8W_QTY_W4,
CASE WHEN(ABS(SUM(A.ACC8W_QTY_W3 - B.ACC8W_QTY_W3)) < POWER(10,-8)) THEN 0
ELSE SUM(A.ACC8W_QTY_W3 - B.ACC8W_QTY_W3) END
AS DIFF_ACC8W_QTY_W3,
CASE WHEN(ABS(SUM(A.ACC8W_QTY_W2 - B.ACC8W_QTY_W2)) < POWER(10,-8)) THEN 0
ELSE SUM(A.ACC8W_QTY_W2 - B.ACC8W_QTY_W2) END
AS DIFF_ACC8W_QTY_W2,
CASE WHEN(ABS(SUM(A.ACC8W_QTY_W1 - B.ACC8W_QTY_W1)) < POWER(10,-8)) THEN 0
ELSE SUM(A.ACC8W_QTY_W1 - B.ACC8W_QTY_W1) END
AS DIFF_ACC8W_QTY_W1
FROM MAPE_STEP4 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
-- 검증 3. 각 컬럼 차 비교
-- 주의 : 마찬가지로 오차가 일정 크기보다 작으면 0으로 처리한다.
-- 여기서는 각 행렬 단일 비교이므로 조금 더 엄격한 기준으로10^(-10)보다 작을 경우로 했다.
SELECT CASE WHEN(ABS(A.ACC8W_QTY_W6 - B.ACC8W_QTY_W6) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_QTY_W6 - B.ACC8W_QTY_W6 END
AS DIFF_ACC8W_QTY_W6,
CASE WHEN(ABS(A.ACC8W_QTY_W5 - B.ACC8W_QTY_W5) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_QTY_W5 - B.ACC8W_QTY_W5 END
AS DIFF_ACC8W_QTY_W5,
CASE WHEN(ABS(A.ACC8W_QTY_W4 - B.ACC8W_QTY_W4) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_QTY_W4 - B.ACC8W_QTY_W4 END
AS DIFF_ACC8W_QTY_W4,
CASE WHEN(ABS(A.ACC8W_QTY_W3 - B.ACC8W_QTY_W3) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_QTY_W3 - B.ACC8W_QTY_W3 END
AS DIFF_ACC8W_QTY_W3,
CASE WHEN(ABS(A.ACC8W_QTY_W2 - B.ACC8W_QTY_W2) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_QTY_W2 - B.ACC8W_QTY_W2 END
AS DIFF_ACC8W_QTY_W2,
CASE WHEN(ABS(A.ACC8W_QTY_W1 - B.ACC8W_QTY_W1) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_QTY_W1 - B.ACC8W_QTY_W1 END
AS DIFF_ACC8W_QTY_W1
FROM MAPE_STEP4 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
-- 검증 3.1 (Advanced)각 컬럼 차 비교 중 0이 아닌 것의 수를 조회
-- 주의 : 각 컬럼별로 조회되어야 하는데 AND나 OR 조건을 여러 컬럼에 동시에 넣을 수 없으니 하나씩 주석 해제 해가며 조회한다.
SELECT COUNT(DIFF_ACC8W_QTY_W6),
COUNT(DIFF_ACC8W_QTY_W5),
COUNT(DIFF_ACC8W_QTY_W4),
COUNT(DIFF_ACC8W_QTY_W3),
COUNT(DIFF_ACC8W_QTY_W2),
COUNT(DIFF_ACC8W_QTY_W1)
FROM(
SELECT CASE WHEN(ABS(A.ACC8W_QTY_W6 - B.ACC8W_QTY_W6) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_QTY_W6 - B.ACC8W_QTY_W6 END
AS DIFF_ACC8W_QTY_W6,
CASE WHEN(ABS(A.ACC8W_QTY_W5 - B.ACC8W_QTY_W5) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_QTY_W5 - B.ACC8W_QTY_W5 END
AS DIFF_ACC8W_QTY_W5,
CASE WHEN(ABS(A.ACC8W_QTY_W4 - B.ACC8W_QTY_W4) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_QTY_W4 - B.ACC8W_QTY_W4 END
AS DIFF_ACC8W_QTY_W4,
CASE WHEN(ABS(A.ACC8W_QTY_W3 - B.ACC8W_QTY_W3) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_QTY_W3 - B.ACC8W_QTY_W3 END
AS DIFF_ACC8W_QTY_W3,
CASE WHEN(ABS(A.ACC8W_QTY_W2 - B.ACC8W_QTY_W2) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_QTY_W2 - B.ACC8W_QTY_W2 END
AS DIFF_ACC8W_QTY_W2,
CASE WHEN(ABS(A.ACC8W_QTY_W1 - B.ACC8W_QTY_W1) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_QTY_W1 - B.ACC8W_QTY_W1 END
AS DIFF_ACC8W_QTY_W1
FROM MAPE_STEP4 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
)
WHERE 1=1
--AND DIFF_ACC8W_QTY_W6 != 0;
AND DIFF_ACC8W_QTY_W5 != 0;
--AND DIFF_ACC8W_QTY_W4 != 0;
--AND DIFF_ACC8W_QTY_W3 != 0;
--AND DIFF_ACC8W_QTY_W2 != 0;
--AND DIFF_ACC8W_QTY_W1 != 0;
-- 검증 3.2 (Advanced)각 컬럼 차 비교 중 0이 아닌 것의 수가 조회될 경우 어떤 row인지 찾는다.
-- 주의 : 각 컬럼별로 조회되어야 하는데 AND나 OR 조건을 여러 컬럼에 동시에 넣을 수 없으니 하나씩 주석 해제 해가며 조회한다.
SELECT B.*
FROM(
SELECT A.*,
CASE WHEN(ABS(A.ACC8W_QTY_W6 - B.ACC8W_QTY_W6) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_QTY_W6 - B.ACC8W_QTY_W6 END
AS DIFF_ACC8W_QTY_W6,
CASE WHEN(ABS(A.ACC8W_QTY_W5 - B.ACC8W_QTY_W5) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_QTY_W5 - B.ACC8W_QTY_W5 END
AS DIFF_ACC8W_QTY_W5,
CASE WHEN(ABS(A.ACC8W_QTY_W4 - B.ACC8W_QTY_W4) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_QTY_W4 - B.ACC8W_QTY_W4 END
AS DIFF_ACC8W_QTY_W4,
CASE WHEN(ABS(A.ACC8W_QTY_W3 - B.ACC8W_QTY_W3) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_QTY_W3 - B.ACC8W_QTY_W3 END
AS DIFF_ACC8W_QTY_W3,
CASE WHEN(ABS(A.ACC8W_QTY_W2 - B.ACC8W_QTY_W2) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_QTY_W2 - B.ACC8W_QTY_W2 END
AS DIFF_ACC8W_QTY_W2,
CASE WHEN(ABS(A.ACC8W_QTY_W1 - B.ACC8W_QTY_W1) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_QTY_W1 - B.ACC8W_QTY_W1 END
AS DIFF_ACC8W_QTY_W1
FROM MAPE_STEP4 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
) B
WHERE 1=1
--AND DIFF_ACC8W_QTY_W6 != 0;
--AND DIFF_ACC8W_QTY_W5 != 0;
AND DIFF_ACC8W_QTY_W4 != 0;
--AND DIFF_ACC8W_QTY_W3 != 0;
--AND DIFF_ACC8W_QTY_W2 != 0;
--AND DIFF_ACC8W_QTY_W1 != 0;Step 5.
더보기
-- Step 5. 각 row의 FCST 6 ~ 1 주차 평균을 구한다.
-- FCST에 대한 null값 처리(NULL인 경우 나눗셈에 넣지 않는다.)
--CREATE TABLE MAPE_STEP5 AS -- CREATE 한 후 주석처리
SELECT A.*,
((DECODE(A.FCST_W6, NULL, 0, A.FCST_W6)
+ DECODE(A.FCST_W5, NULL, 0, A.FCST_W5)
+ DECODE(A.FCST_W4, NULL, 0, A.FCST_W4)
+ DECODE(A.FCST_W3, NULL, 0, A.FCST_W3)
+ DECODE(A.FCST_W2, NULL, 0, A.FCST_W2)
+ DECODE(A.FCST_W1, NULL, 0, A.FCST_W1))
/ (DECODE(A.FCST_W6, NULL, 0, 1)
+ DECODE(A.FCST_W5, NULL, 0, 1)
+ DECODE(A.FCST_W4, NULL, 0, 1)
+ DECODE(A.FCST_W3, NULL, 0, 1)
+ DECODE(A.FCST_W2, NULL, 0, 1)
+ DECODE(A.FCST_W1, NULL, 0, 1)))
AS FCST_AVG
FROM MAPE_STEP4 A
ORDER BY SEG1 ASC, SEG2 ASC, SEG3 ASC;
-- 위와 동일한 수식이다. 단지, 덧셈할 때 DECODE 함수 대신 NVL 함수를 썼다.
SELECT A.*,
((NVL(A.FCST_W6, 0)
+ NVL(A.FCST_W5, 0)
+ NVL(A.FCST_W4, 0)
+ NVL(A.FCST_W3, 0)
+ NVL(A.FCST_W2, 0)
+ NVL(A.FCST_W1, 0))
/ (DECODE(A.FCST_W6, NULL, 0, 1)
+ DECODE(A.FCST_W5, NULL, 0, 1)
+ DECODE(A.FCST_W4, NULL, 0, 1)
+ DECODE(A.FCST_W3, NULL, 0, 1)
+ DECODE(A.FCST_W2, NULL, 0, 1)
+ DECODE(A.FCST_W1, NULL, 0, 1)))
AS FCST_AVG
FROM MAPE_STEP4 A
ORDER BY SEG1 ASC, SEG2 ASC, SEG3 ASC;
-- STEP 5. 검증
SELECT * FROM MAPE_STEP5; -- 스텝 5단계 저장본.. 문제푼거
SELECT * FROM PRO_FCST_EXCEL_KEY -- 엑셀파일.. 답지
ORDER BY SEG1 ASC, SEG2 ASC, SEG3 ASC;
-- 검증 1. 컬럼수 비교
SELECT COUNT(*)
FROM MAPE_STEP5;
SELECT COUNT(*)
FROM PRO_FCST_EXCEL_KEY;
-- 검증 2. 합의 차 비교
-- 주의 : 그냥 'SUM(A.FCST_AVG - B.FCST_AVG) AS DIFF_FCST_AVG'을 할 경우 엑셀 답지와 오라클이 직접 계산한 결과가 약간씩 오차가 발생한다.
-- 따라서 오차가 일정 크기보다 작으면 0으로 처리한다. 여기서는 10^(-8)보다 작을 경우로 했다.
SELECT CASE WHEN(ABS(SUM(A.FCST_AVG - B.FCST_AVG)) < POWER(10,-8)) THEN 0
ELSE SUM(A.FCST_AVG - B.FCST_AVG) END
AS DIFF_FCST_AVG
FROM MAPE_STEP5 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
-- 검증 3. 각 컬럼 차 비교
-- 주의 : 마찬가지로 오차가 일정 크기보다 작으면 0으로 처리한다.
-- 여기서는 각 행렬 단일 비교이므로 조금 더 엄격한 기준으로10^(-10)보다 작을 경우로 했다.
SELECT CASE WHEN(ABS(A.FCST_AVG - B.FCST_AVG) < POWER(10,-10)) THEN 0
ELSE A.FCST_AVG - B.FCST_AVG END
AS DIFF_FCST_AVG
FROM MAPE_STEP5 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
-- 검증 3.1 (Advanced)각 컬럼 차 비교 중 0이 아닌 것의 수를 조회
-- 주의 : Step 4 까지는 오차를 10^(-10)으로 했는데 여기서는 걸러내지 못 한다. 산술 오차 무시를 10^(-8)로 변경했다.
SELECT COUNT(DIFF_FCST_AVG)
FROM(
SELECT CASE WHEN(ABS(A.FCST_AVG - B.FCST_AVG) < POWER(10,-8)) THEN 0
ELSE A.FCST_AVG - B.FCST_AVG END
AS DIFF_FCST_AVG
FROM MAPE_STEP5 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
)
WHERE 1=1
AND DIFF_FCST_AVG != 0;
-- 검증 3.2 (Advanced)각 컬럼 차 비교 중 0이 아닌 것의 수가 조회될 경우 어떤 row인지 찾는다.
-- 주의 : 마찬가지로 오차 범위를 10^(-8)로 조정했다.
SELECT B.*
FROM(
SELECT A.*,
CASE WHEN(ABS(A.FCST_AVG - B.FCST_AVG) < POWER(10,-8)) THEN 0
ELSE A.FCST_AVG - B.FCST_AVG END
AS DIFF_FCST_AVG
FROM MAPE_STEP5 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
) B
WHERE 1=1
AND DIFF_FCST_AVG != 0;Step 6.
더보기
-- Step 6. 각 row의 ACC8W_QTY 6 ~ 1 주차 평균을 구한다.
-- ACC8W_QTY는 이미 null값 처리가 되어 있기 때문에 그냥 6으로 나누면 된다.
--CREATE TABLE MAPE_STEP6 AS -- CREATE 한 후 주석처리
SELECT A.*,
((A.ACC8W_QTY_W6
+ A.ACC8W_QTY_W5
+ A.ACC8W_QTY_W4
+ A.ACC8W_QTY_W3
+ A.ACC8W_QTY_W2
+ A.ACC8W_QTY_W1)
/ 6)
AS ACC_AVG
FROM MAPE_STEP5 A
ORDER BY SEG1 ASC, SEG2 ASC, SEG3 ASC;
-- STEP 6. 검증
SELECT * FROM MAPE_STEP6; -- 스텝 6단계 저장본.. 문제푼거
SELECT * FROM PRO_FCST_EXCEL_KEY -- 엑셀파일.. 답지
ORDER BY SEG1 ASC, SEG2 ASC, SEG3 ASC;
-- 검증 1. 컬럼수 비교
SELECT COUNT(*)
FROM MAPE_STEP6;
SELECT COUNT(*)
FROM PRO_FCST_EXCEL_KEY;
-- 검증 2. 합의 차 비교
-- 주의 : 그냥 'SUM(A.ACC_AVG - B.ACC_AVG) AS DIFF_ACC_AVG'을 할 경우 엑셀 답지와 오라클이 직접 계산한 결과가 약간씩 오차가 발생한다.
-- 따라서 오차가 일정 크기보다 작으면 0으로 처리한다. 여기서는 10^(-8)보다 작을 경우로 했다.
SELECT CASE WHEN(ABS(SUM(A.ACC_AVG - B.ACC_AVG)) < POWER(10,-8)) THEN 0
ELSE SUM(A.ACC_AVG - B.ACC_AVG) END
AS DIFF_ACC_AVG
FROM MAPE_STEP6 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
-- 검증 3. 각 컬럼 차 비교
-- 주의 : 마찬가지로 오차가 일정 크기보다 작으면 0으로 처리한다.
-- 여기서는 각 행렬 단일 비교이므로 조금 더 엄격한 기준으로10^(-10)보다 작을 경우로 했다.
SELECT CASE WHEN(ABS(A.ACC_AVG - B.ACC_AVG) < POWER(10,-10)) THEN 0
ELSE A.ACC_AVG - B.ACC_AVG END
AS DIFF_ACC_AVG
FROM MAPE_STEP6 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
-- 검증 3.1 (Advanced)각 컬럼 차 비교 중 0이 아닌 것의 수를 조회
-- 주의 : Step 4 까지는 오차를 10^(-10)으로 했는데 여기서는 걸러내지 못 한다. 산술 오차 무시를 10^(-8)로 변경했다.
SELECT COUNT(DIFF_ACC_AVG)
FROM(
SELECT CASE WHEN(ABS(A.ACC_AVG - B.ACC_AVG) < POWER(10,-8)) THEN 0
ELSE A.ACC_AVG - B.ACC_AVG END
AS DIFF_ACC_AVG
FROM MAPE_STEP6 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
)
WHERE 1=1
AND DIFF_ACC_AVG != 0;
-- 검증 3.2 (Advanced)각 컬럼 차 비교 중 0이 아닌 것의 수가 조회될 경우 어떤 row인지 찾는다.
-- 주의 : 마찬가지로 오차 범위를 10^(-8)로 조정했다.
SELECT B.*
FROM(
SELECT A.*,
CASE WHEN(ABS(A.ACC_AVG - B.ACC_AVG) < POWER(10,-8)) THEN 0
ELSE A.ACC_AVG - B.ACC_AVG END
AS DIFF_ACC_AVG
FROM MAPE_STEP6 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
) B
WHERE 1=1
AND DIFF_ACC_AVG != 0;
합본 (step by step)
-- Step 1. 6개의 예측 결과값을 병합한다.
-- 주의 : 최근 주차를 기준으로 과거 데이터를 붙인다.
--CREATE TABLE MAPE_STEP1 AS --CREATE 한 후 주석처리
SELECT A.PRD_SEG1 AS SEG1,
A.PRD_SEG2 AS SEG2,
11||A.PRD_SEG3 AS SEG3,
A.YEAR||A.WEEK AS TARGETWEEK,
A.YEAR,
A.WEEK,
A.QTY,
F.OUTFCST AS FCST_W6,
E.OUTFCST AS FCST_W5,
D.OUTFCST AS FCST_W4,
C.OUTFCST AS FCST_W3,
B.OUTFCST AS FCST_W2,
A.OUTFCST AS FCST_W1
FROM PRO_FCST_RESULT_1WEEK A
LEFT JOIN PRO_FCST_RESULT_2WEEK B
ON 1=1
AND (A.PRD_SEG1 = B.PRD_SEG1)
AND (A.PRD_SEG2 = B.PRD_SEG2)
AND (A.PRD_SEG3 = B.PRD_SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
LEFT JOIN PRO_FCST_RESULT_3WEEK C
ON 1=1
AND (A.PRD_SEG1 = C.PRD_SEG1)
AND (A.PRD_SEG2 = C.PRD_SEG2)
AND (A.PRD_SEG3 = C.PRD_SEG3)
AND (A.YEAR = C.YEAR)
AND (A.WEEK = C.WEEK AND A.WEEK = 18)
LEFT JOIN PRO_FCST_RESULT_4WEEK D
ON 1=1
AND (A.PRD_SEG1 = D.PRD_SEG1)
AND (A.PRD_SEG2 = D.PRD_SEG2)
AND (A.PRD_SEG3 = D.PRD_SEG3)
AND (A.YEAR = D.YEAR)
AND (A.WEEK = D.WEEK AND A.WEEK = 18)
LEFT JOIN PRO_FCST_RESULT_5WEEK E
ON 1=1
AND (A.PRD_SEG1 = E.PRD_SEG1)
AND (A.PRD_SEG2 = E.PRD_SEG2)
AND (A.PRD_SEG3 = E.PRD_SEG3)
AND (A.YEAR = E.YEAR)
AND (A.WEEK = E.WEEK AND A.WEEK = 18)
LEFT JOIN PRO_FCST_RESULT_6WEEK F
ON 1=1
AND (A.PRD_SEG1 = F.PRD_SEG1)
AND (A.PRD_SEG2 = F.PRD_SEG2)
AND (A.PRD_SEG3 = F.PRD_SEG3)
AND (A.YEAR = F.YEAR)
AND (A.WEEK = F.WEEK AND A.WEEK = 18)
ORDER BY SEG1 ASC, SEG2 ASC, SEG3 ASC;
-- STEP 1. 검증
SELECT * FROM MAPE_STEP1; -- 스텝 1단계 저장본.. 문제푼거
SELECT * FROM PRO_FCST_EXCEL_KEY -- 엑셀파일.. 답지
ORDER BY SEG1 ASC, SEG2 ASC, SEG3 ASC;
-- 검증 1. 컬럼수 비교
SELECT COUNT(*)
FROM MAPE_STEP1;
SELECT COUNT(*)
FROM PRO_FCST_EXCEL_KEY;
-- 검증 2. 합의 차 비교
SELECT SUM(A.FCST_W6 - B.FCST_W6) AS DIFF_FCST_W6,
SUM(A.FCST_W5 - B.FCST_W5) AS DIFF_FCST_W5,
SUM(A.FCST_W4 - B.FCST_W4) AS DIFF_FCST_W4,
SUM(A.FCST_W3 - B.FCST_W3) AS DIFF_FCST_W3,
SUM(A.FCST_W2 - B.FCST_W2) AS DIFF_FCST_W2,
SUM(A.FCST_W1 - B.FCST_W1) AS DIFF_FCST_W1
FROM MAPE_STEP1 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
-- 검증 3. 각 컬럼 차 비교
SELECT (A.FCST_W6 - B.FCST_W6) AS DIFF_FCST_W6,
(A.FCST_W5 - B.FCST_W5) AS DIFF_FCST_W5,
(A.FCST_W4 - B.FCST_W4) AS DIFF_FCST_W4,
(A.FCST_W3 - B.FCST_W3) AS DIFF_FCST_W3,
(A.FCST_W2 - B.FCST_W2) AS DIFF_FCST_W2,
(A.FCST_W1 - B.FCST_W1) AS DIFF_FCST_W1
FROM MAPE_STEP1 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
-- 검증 3.1 (Advanced)각 컬럼 차 비교 중 0이 아닌 것의 수를 조회
-- 주의 : 각 컬럼별로 조회되어야 하는데 AND나 OR 조건을 여러 컬럼에 동시에 넣을 수 없으니 하나씩 주석 해제 해가며 조회한다.
SELECT COUNT(DIFF_FCST_W6),
COUNT(DIFF_FCST_W5),
COUNT(DIFF_FCST_W4),
COUNT(DIFF_FCST_W3),
COUNT(DIFF_FCST_W2),
COUNT(DIFF_FCST_W1)
FROM(
SELECT (A.FCST_W6 - B.FCST_W6) AS DIFF_FCST_W6,
(A.FCST_W5 - B.FCST_W5) AS DIFF_FCST_W5,
(A.FCST_W4 - B.FCST_W4) AS DIFF_FCST_W4,
(A.FCST_W3 - B.FCST_W3) AS DIFF_FCST_W3,
(A.FCST_W2 - B.FCST_W2) AS DIFF_FCST_W2,
(A.FCST_W1 - B.FCST_W1) AS DIFF_FCST_W1
FROM MAPE_STEP1 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
)
WHERE 1=1
--AND DIFF_FCST_W6 != 0;
AND DIFF_FCST_W5 != 0;
--AND DIFF_FCST_W4 != 0;
--AND DIFF_FCST_W3 != 0;
--AND DIFF_FCST_W2 != 0;
--AND DIFF_FCST_W1 != 0;
-- 검증 3.2 (Advanced)각 컬럼 차 비교 중 0이 아닌 것의 수가 조회될 경우 어떤 row인지 찾는다.
-- 주의 : 각 컬럼별로 조회되어야 하는데 AND나 OR 조건을 여러 컬럼에 동시에 넣을 수 없으니 하나씩 주석 해제 해가며 조회한다.
SELECT B.*
FROM(
SELECT A.*,
(A.FCST_W6 - B.FCST_W6) AS DIFF_FCST_W6,
(A.FCST_W5 - B.FCST_W5) AS DIFF_FCST_W5,
(A.FCST_W4 - B.FCST_W4) AS DIFF_FCST_W4,
(A.FCST_W3 - B.FCST_W3) AS DIFF_FCST_W3,
(A.FCST_W2 - B.FCST_W2) AS DIFF_FCST_W2,
(A.FCST_W1 - B.FCST_W1) AS DIFF_FCST_W1
FROM MAPE_STEP1 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
) B
WHERE 1=1
--AND DIFF_FCST_W6 != 0;
--AND DIFF_FCST_W5 != 0;
AND DIFF_FCST_W4 != 0;
--AND DIFF_FCST_W3 != 0;
--AND DIFF_FCST_W2 != 0;
--AND DIFF_FCST_W1 != 0;
--------------------------------------------------------------------------------
-- Step 2. 예측한 값과 실제 값을 뺀 절대값을 구한다.
-- 주의 : FROM 절에 MAPE_STEP1를 지우고 (괄호)를 만들고, 그 괄호 안에 STEP1의 쿼리를 전부 서브쿼리로 복붙한다.
--CREATE TABLE MAPE_STEP2 AS -- CREATE 한 후 주석처리
SELECT A.*,
CASE WHEN A.FCST_W6 IS NOT NULL THEN ABS(A.QTY - A.FCST_W6)
ELSE A.QTY END
AS ABS8W_W6,
CASE WHEN A.FCST_W5 IS NOT NULL THEN ABS(A.QTY - A.FCST_W5)
ELSE A.QTY END
AS ABS8W_W5,
CASE WHEN A.FCST_W4 IS NOT NULL THEN ABS(A.QTY - A.FCST_W4)
ELSE A.QTY END
AS ABS8W_W4,
CASE WHEN A.FCST_W3 IS NOT NULL THEN ABS(A.QTY - A.FCST_W3)
ELSE A.QTY END
AS ABS8W_W3,
CASE WHEN A.FCST_W2 IS NOT NULL THEN ABS(A.QTY - A.FCST_W2)
ELSE A.QTY END
AS ABS8W_W2,
CASE WHEN A.FCST_W1 IS NOT NULL THEN ABS(A.QTY - A.FCST_W1)
ELSE A.QTY END
AS ABS8W_W1
FROM MAPE_STEP1 A
ORDER BY SEG1 ASC, SEG2 ASC, SEG3 ASC;
-- STEP 2. 검증
SELECT * FROM MAPE_STEP2; -- 스텝 2단계 저장본.. 문제푼거
SELECT * FROM PRO_FCST_EXCEL_KEY -- 엑셀파일.. 답지
ORDER BY SEG1 ASC, SEG2 ASC, SEG3 ASC;
-- 검증 1. 컬럼수 비교
SELECT COUNT(*)
FROM MAPE_STEP2;
SELECT COUNT(*)
FROM PRO_FCST_EXCEL_KEY;
-- 검증 2. 합의 차 비교
SELECT SUM(A.ABS8W_W6 - B.ABS8W_W6) AS DIFF_ABS8W_W6,
SUM(A.ABS8W_W5 - B.ABS8W_W5) AS DIFF_ABS8W_W5,
SUM(A.ABS8W_W4 - B.ABS8W_W4) AS DIFF_ABS8W_W4,
SUM(A.ABS8W_W3 - B.ABS8W_W3) AS DIFF_ABS8W_W3,
SUM(A.ABS8W_W2 - B.ABS8W_W2) AS DIFF_ABS8W_W2,
SUM(A.ABS8W_W1 - B.ABS8W_W1) AS DIFF_ABS8W_W1
FROM MAPE_STEP2 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
-- 검증 3. 각 컬럼 차 비교
SELECT (A.ABS8W_W6 - B.ABS8W_W6) AS DIFF_ABS8W_W6,
(A.ABS8W_W5 - B.ABS8W_W5) AS DIFF_ABS8W_W5,
(A.ABS8W_W4 - B.ABS8W_W4) AS DIFF_ABS8W_W4,
(A.ABS8W_W3 - B.ABS8W_W3) AS DIFF_ABS8W_W3,
(A.ABS8W_W2 - B.ABS8W_W2) AS DIFF_ABS8W_W2,
(A.ABS8W_W1 - B.ABS8W_W1) AS DIFF_ABS8W_W1
FROM MAPE_STEP2 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
-- 검증 3.1 (Advanced)각 컬럼 차 비교 중 0이 아닌 것의 수를 조회
-- 주의 : 각 컬럼별로 조회되어야 하는데 AND나 OR 조건을 여러 컬럼에 동시에 넣을 수 없으니 하나씩 주석 해제 해가며 조회한다.
SELECT COUNT(DIFF_ABS8W_W6),
COUNT(DIFF_ABS8W_W5),
COUNT(DIFF_ABS8W_W4),
COUNT(DIFF_ABS8W_W3),
COUNT(DIFF_ABS8W_W2),
COUNT(DIFF_ABS8W_W1)
FROM(
SELECT (A.ABS8W_W6 - B.ABS8W_W6) AS DIFF_ABS8W_W6,
(A.ABS8W_W5 - B.ABS8W_W5) AS DIFF_ABS8W_W5,
(A.ABS8W_W4 - B.ABS8W_W4) AS DIFF_ABS8W_W4,
(A.ABS8W_W3 - B.ABS8W_W3) AS DIFF_ABS8W_W3,
(A.ABS8W_W2 - B.ABS8W_W2) AS DIFF_ABS8W_W2,
(A.ABS8W_W1 - B.ABS8W_W1) AS DIFF_ABS8W_W1
FROM MAPE_STEP2 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
)
WHERE 1=1
--AND DIFF_ABS8W_W6 != 0;
AND DIFF_ABS8W_W5 != 0;
--AND DIFF_ABS8W_W4 != 0;
--AND DIFF_ABS8W_W3 != 0;
--AND DIFF_ABS8W_W2 != 0;
--AND DIFF_ABS8W_W1 != 0;
-- 검증 3.2 (Advanced)각 컬럼 차 비교 중 0이 아닌 것의 수가 조회될 경우 어떤 row인지 찾는다.
-- 주의 : 각 컬럼별로 조회되어야 하는데 AND나 OR 조건을 여러 컬럼에 동시에 넣을 수 없으니 하나씩 주석 해제 해가며 조회한다.
SELECT B.*
FROM(
SELECT A.*,
(A.ABS8W_W6 - B.ABS8W_W6) AS DIFF_ABS8W_W6,
(A.ABS8W_W5 - B.ABS8W_W5) AS DIFF_ABS8W_W5,
(A.ABS8W_W4 - B.ABS8W_W4) AS DIFF_ABS8W_W4,
(A.ABS8W_W3 - B.ABS8W_W3) AS DIFF_ABS8W_W3,
(A.ABS8W_W2 - B.ABS8W_W2) AS DIFF_ABS8W_W2,
(A.ABS8W_W1 - B.ABS8W_W1) AS DIFF_ABS8W_W1
FROM MAPE_STEP2 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
) B
WHERE 1=1
--AND DIFF_ABS8W_W6 != 0;
--AND DIFF_ABS8W_W5 != 0;
AND DIFF_ABS8W_W4 != 0;
--AND DIFF_ABS8W_W3 != 0;
--AND DIFF_ABS8W_W2 != 0;
--AND DIFF_ABS8W_W1 != 0;
--------------------------------------------------------------------------------
-- Step 3. FCST의 정확도를 ABS를 이용해 구한다. (값이 0인 경우 0처리, 이상치 정제 처리)
-- 주의 : CASE WHEN, WHEN... 으로 다중 IF ESLEIF ESLEIF... 로직 처리 후 마지막 ELSE가 끝나면 반드시 END로 전체 CASE WHEN 조건문을 닫아준다.
-- DECODE는 특정 컬럼에 대해 값을 동등한지 비교해 새로운 값으로 처리해주는 것으로 조건을 비교할 컬럼이 바뀌는 경우 혹은 '='이 아닌 부등호 '>', '<'는 처리할 수 없다.
--CREATE TABLE MAPE_STEP3 AS -- CREATE 한 후 주석처리
SELECT A.*,
CASE WHEN ((A.FCST_W6 = 0 OR A.FCST_W6 IS NULL) OR (A.QTY = 0)) THEN 0
WHEN (A.QTY / A.FCST_W6) > 2 THEN 0
ELSE (1 - (A.ABS8W_W6 / A.FCST_W6)) END
AS ACC8W_W6,
CASE WHEN ((A.FCST_W5 = 0 OR A.FCST_W5 IS NULL) OR (A.QTY = 0)) THEN 0
WHEN (A.QTY / A.FCST_W5) > 2 THEN 0
ELSE (1 - (A.ABS8W_W5 / A.FCST_W5)) END
AS ACC8W_W5,
CASE WHEN ((A.FCST_W4 = 0 OR A.FCST_W4 IS NULL) OR (A.QTY = 0)) THEN 0
WHEN (A.QTY / A.FCST_W4) > 2 THEN 0
ELSE (1 - (A.ABS8W_W4 / A.FCST_W4)) END
AS ACC8W_W4,
CASE WHEN ((A.FCST_W3 = 0 OR A.FCST_W3 IS NULL) OR (A.QTY = 0)) THEN 0
WHEN (A.QTY / A.FCST_W3) > 2 THEN 0
ELSE (1 - (A.ABS8W_W3 / A.FCST_W3)) END
AS ACC8W_W3,
CASE WHEN ((A.FCST_W2 = 0 OR A.FCST_W2 IS NULL) OR (A.QTY = 0)) THEN 0
WHEN (A.QTY / A.FCST_W2) > 2 THEN 0
ELSE (1 - (A.ABS8W_W2 / A.FCST_W2)) END
AS ACC8W_W2,
CASE WHEN ((A.FCST_W1 = 0 OR A.FCST_W1 IS NULL) OR (A.QTY = 0)) THEN 0
WHEN (A.QTY / A.FCST_W1) > 2 THEN 0
ELSE (1 - (A.ABS8W_W1 / A.FCST_W1)) END
AS ACC8W_W1
FROM MAPE_STEP2 A
ORDER BY SEG1 ASC, SEG2 ASC, SEG3 ASC;
-- STEP 3. 검증
SELECT * FROM MAPE_STEP3; -- 스텝 3단계 저장본.. 문제푼거
SELECT * FROM PRO_FCST_EXCEL_KEY -- 엑셀파일.. 답지
ORDER BY SEG1 ASC, SEG2 ASC, SEG3 ASC;
-- 검증 1. 컬럼수 비교
SELECT COUNT(*)
FROM MAPE_STEP3;
SELECT COUNT(*)
FROM PRO_FCST_EXCEL_KEY;
-- 검증 2. 합의 차 비교
-- 주의 : 그냥 'SUM(A.ACC8W_W6 - B.ACC8W_W6) AS DIFF_ACC8W_W6'을 할 경우 엑셀 답지와 오라클이 직접 계산한 결과가 약간씩 오차가 발생한다.
-- 따라서 오차가 일정 크기보다 작으면 0으로 처리한다. 여기서는 10^(-8)보다 작을 경우로 했다.
SELECT CASE WHEN(ABS(SUM(A.ACC8W_W6 - B.ACC8W_W6)) < POWER(10,-8)) THEN 0
ELSE SUM(A.ACC8W_W6 - B.ACC8W_W6) END
AS DIFF_ACC8W_W6,
CASE WHEN(ABS(SUM(A.ACC8W_W5 - B.ACC8W_W5)) < POWER(10,-8)) THEN 0
ELSE SUM(A.ACC8W_W5 - B.ACC8W_W5) END
AS DIFF_ACC8W_W5,
CASE WHEN(ABS(SUM(A.ACC8W_W4 - B.ACC8W_W4)) < POWER(10,-8)) THEN 0
ELSE SUM(A.ACC8W_W4 - B.ACC8W_W4) END
AS DIFF_ACC8W_W4,
CASE WHEN(ABS(SUM(A.ACC8W_W3 - B.ACC8W_W3)) < POWER(10,-8)) THEN 0
ELSE SUM(A.ACC8W_W3 - B.ACC8W_W3) END
AS DIFF_ACC8W_W3,
CASE WHEN(ABS(SUM(A.ACC8W_W2 - B.ACC8W_W2)) < POWER(10,-8)) THEN 0
ELSE SUM(A.ACC8W_W2 - B.ACC8W_W2) END
AS DIFF_ACC8W_W2,
CASE WHEN(ABS(SUM(A.ACC8W_W1 - B.ACC8W_W1)) < POWER(10,-8)) THEN 0
ELSE SUM(A.ACC8W_W1 - B.ACC8W_W1) END
AS DIFF_ACC8W_W1
FROM MAPE_STEP3 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
-- 검증 3. 각 컬럼 차 비교
-- 주의 : 마찬가지로 오차가 일정 크기보다 작으면 0으로 처리한다.
-- 여기서는 각 행렬 단일 비교이므로 조금 더 엄격한 기준으로10^(-10)보다 작을 경우로 했다.
SELECT CASE WHEN(ABS(A.ACC8W_W6 - B.ACC8W_W6) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_W6 - B.ACC8W_W6 END
AS DIFF_ACC8W_W6,
CASE WHEN(ABS(A.ACC8W_W5 - B.ACC8W_W5) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_W5 - B.ACC8W_W5 END
AS DIFF_ACC8W_W5,
CASE WHEN(ABS(A.ACC8W_W4 - B.ACC8W_W4) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_W4 - B.ACC8W_W4 END
AS DIFF_ACC8W_W4,
CASE WHEN(ABS(A.ACC8W_W3 - B.ACC8W_W3) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_W3 - B.ACC8W_W3 END
AS DIFF_ACC8W_W3,
CASE WHEN(ABS(A.ACC8W_W2 - B.ACC8W_W2) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_W2 - B.ACC8W_W2 END
AS DIFF_ACC8W_W2,
CASE WHEN(ABS(A.ACC8W_W1 - B.ACC8W_W1) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_W1 - B.ACC8W_W1 END
AS DIFF_ACC8W_W1
FROM MAPE_STEP3 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
-- 검증 3.1 (Advanced)각 컬럼 차 비교 중 0이 아닌 것의 수를 조회
-- 주의 : 각 컬럼별로 조회되어야 하는데 AND나 OR 조건을 여러 컬럼에 동시에 넣을 수 없으니 하나씩 주석 해제 해가며 조회한다.
SELECT COUNT(DIFF_ACC8W_W6),
COUNT(DIFF_ACC8W_W5),
COUNT(DIFF_ACC8W_W4),
COUNT(DIFF_ACC8W_W3),
COUNT(DIFF_ACC8W_W2),
COUNT(DIFF_ACC8W_W1)
FROM(
SELECT CASE WHEN(ABS(A.ACC8W_W6 - B.ACC8W_W6) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_W6 - B.ACC8W_W6 END
AS DIFF_ACC8W_W6,
CASE WHEN(ABS(A.ACC8W_W5 - B.ACC8W_W5) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_W5 - B.ACC8W_W5 END
AS DIFF_ACC8W_W5,
CASE WHEN(ABS(A.ACC8W_W4 - B.ACC8W_W4) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_W4 - B.ACC8W_W4 END
AS DIFF_ACC8W_W4,
CASE WHEN(ABS(A.ACC8W_W3 - B.ACC8W_W3) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_W3 - B.ACC8W_W3 END
AS DIFF_ACC8W_W3,
CASE WHEN(ABS(A.ACC8W_W2 - B.ACC8W_W2) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_W2 - B.ACC8W_W2 END
AS DIFF_ACC8W_W2,
CASE WHEN(ABS(A.ACC8W_W1 - B.ACC8W_W1) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_W1 - B.ACC8W_W1 END
AS DIFF_ACC8W_W1
FROM MAPE_STEP3 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
)
WHERE 1=1
--AND DIFF_ACC8W_W6 != 0;
AND DIFF_ACC8W_W5 != 0;
--AND DIFF_ACC8W_W4 != 0;
--AND DIFF_ACC8W_W3 != 0;
--AND DIFF_ACC8W_W2 != 0;
--AND DIFF_ACC8W_W1 != 0;
-- 검증 3.2 (Advanced)각 컬럼 차 비교 중 0이 아닌 것의 수가 조회될 경우 어떤 row인지 찾는다.
-- 주의 : 각 컬럼별로 조회되어야 하는데 AND나 OR 조건을 여러 컬럼에 동시에 넣을 수 없으니 하나씩 주석 해제 해가며 조회한다.
SELECT B.*
FROM(
SELECT A.*,
CASE WHEN(ABS(A.ACC8W_W6 - B.ACC8W_W6) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_W6 - B.ACC8W_W6 END
AS DIFF_ACC8W_W6,
CASE WHEN(ABS(A.ACC8W_W5 - B.ACC8W_W5) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_W5 - B.ACC8W_W5 END
AS DIFF_ACC8W_W5,
CASE WHEN(ABS(A.ACC8W_W4 - B.ACC8W_W4) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_W4 - B.ACC8W_W4 END
AS DIFF_ACC8W_W4,
CASE WHEN(ABS(A.ACC8W_W3 - B.ACC8W_W3) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_W3 - B.ACC8W_W3 END
AS DIFF_ACC8W_W3,
CASE WHEN(ABS(A.ACC8W_W2 - B.ACC8W_W2) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_W2 - B.ACC8W_W2 END
AS DIFF_ACC8W_W2,
CASE WHEN(ABS(A.ACC8W_W1 - B.ACC8W_W1) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_W1 - B.ACC8W_W1 END
AS DIFF_ACC8W_W1
FROM MAPE_STEP3 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
) B
WHERE 1=1
--AND DIFF_ACC8W_W6 != 0;
--AND DIFF_ACC8W_W5 != 0;
AND DIFF_ACC8W_W4 != 0;
--AND DIFF_ACC8W_W3 != 0;
--AND DIFF_ACC8W_W2 != 0;
--AND DIFF_ACC8W_W1 != 0;
--------------------------------------------------------------------------------
-- Step 4. 예측값(FCST)와 위 Step 3에서 구한 예측값 정확도를 곱한다.
-- 주의 : 위 Step 2, Step 3과 마찬가지로 FCST의 null값 처리만 신경쓰면 된다.
--CREATE TABLE MAPE_STEP4 AS -- CREATE 한 후 주석처리
SELECT A.*,
CASE WHEN (A.FCST_W6 IS NOT NULL) THEN A.FCST_W6 * A.ACC8W_W6
ELSE 0 END
AS ACC8W_QTY_W6,
CASE WHEN (A.FCST_W5 IS NOT NULL) THEN A.FCST_W5 * A.ACC8W_W5
ELSE 0 END
AS ACC8W_QTY_W5,
CASE WHEN (A.FCST_W4 IS NOT NULL) THEN A.FCST_W4 * A.ACC8W_W4
ELSE 0 END
AS ACC8W_QTY_W4,
CASE WHEN (A.FCST_W3 IS NOT NULL) THEN A.FCST_W3 * A.ACC8W_W3
ELSE 0 END
AS ACC8W_QTY_W3,
CASE WHEN (A.FCST_W2 IS NOT NULL) THEN A.FCST_W2 * A.ACC8W_W2
ELSE 0 END
AS ACC8W_QTY_W2,
CASE WHEN (A.FCST_W1 IS NOT NULL) THEN A.FCST_W1 * A.ACC8W_W1
ELSE 0 END
AS ACC8W_QTY_W1
FROM MAPE_STEP3 A
ORDER BY SEG1 ASC, SEG2 ASC, SEG3 ASC;
-- STEP 4. 검증
SELECT * FROM MAPE_STEP4; -- 스텝 4단계 저장본.. 문제푼거
SELECT * FROM PRO_FCST_EXCEL_KEY -- 엑셀파일.. 답지
ORDER BY SEG1 ASC, SEG2 ASC, SEG3 ASC;
-- 검증 1. 컬럼수 비교
SELECT COUNT(*)
FROM MAPE_STEP4;
SELECT COUNT(*)
FROM PRO_FCST_EXCEL_KEY;
-- 검증 2. 합의 차 비교
-- 주의 : 그냥 'SUM(A.ACC8W_QTY_W6 - B.ACC8W_QTY_W6) AS DIFF_ACC8W_QTY_W6'을 할 경우 엑셀 답지와 오라클이 직접 계산한 결과가 약간씩 오차가 발생한다.
-- 따라서 오차가 일정 크기보다 작으면 0으로 처리한다. 여기서는 10^(-8)보다 작을 경우로 했다.
SELECT CASE WHEN(ABS(SUM(A.ACC8W_QTY_W6 - B.ACC8W_QTY_W6)) < POWER(10,-8)) THEN 0
ELSE SUM(A.ACC8W_QTY_W6 - B.ACC8W_QTY_W6) END
AS DIFF_ACC8W_QTY_W6,
CASE WHEN(ABS(SUM(A.ACC8W_QTY_W5 - B.ACC8W_QTY_W5)) < POWER(10,-8)) THEN 0
ELSE SUM(A.ACC8W_QTY_W5 - B.ACC8W_QTY_W5) END
AS DIFF_ACC8W_QTY_W5,
CASE WHEN(ABS(SUM(A.ACC8W_QTY_W4 - B.ACC8W_QTY_W4)) < POWER(10,-8)) THEN 0
ELSE SUM(A.ACC8W_QTY_W4 - B.ACC8W_QTY_W4) END
AS DIFF_ACC8W_QTY_W4,
CASE WHEN(ABS(SUM(A.ACC8W_QTY_W3 - B.ACC8W_QTY_W3)) < POWER(10,-8)) THEN 0
ELSE SUM(A.ACC8W_QTY_W3 - B.ACC8W_QTY_W3) END
AS DIFF_ACC8W_QTY_W3,
CASE WHEN(ABS(SUM(A.ACC8W_QTY_W2 - B.ACC8W_QTY_W2)) < POWER(10,-8)) THEN 0
ELSE SUM(A.ACC8W_QTY_W2 - B.ACC8W_QTY_W2) END
AS DIFF_ACC8W_QTY_W2,
CASE WHEN(ABS(SUM(A.ACC8W_QTY_W1 - B.ACC8W_QTY_W1)) < POWER(10,-8)) THEN 0
ELSE SUM(A.ACC8W_QTY_W1 - B.ACC8W_QTY_W1) END
AS DIFF_ACC8W_QTY_W1
FROM MAPE_STEP4 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
-- 검증 3. 각 컬럼 차 비교
-- 주의 : 마찬가지로 오차가 일정 크기보다 작으면 0으로 처리한다.
-- 여기서는 각 행렬 단일 비교이므로 조금 더 엄격한 기준으로10^(-10)보다 작을 경우로 했다.
SELECT CASE WHEN(ABS(A.ACC8W_QTY_W6 - B.ACC8W_QTY_W6) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_QTY_W6 - B.ACC8W_QTY_W6 END
AS DIFF_ACC8W_QTY_W6,
CASE WHEN(ABS(A.ACC8W_QTY_W5 - B.ACC8W_QTY_W5) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_QTY_W5 - B.ACC8W_QTY_W5 END
AS DIFF_ACC8W_QTY_W5,
CASE WHEN(ABS(A.ACC8W_QTY_W4 - B.ACC8W_QTY_W4) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_QTY_W4 - B.ACC8W_QTY_W4 END
AS DIFF_ACC8W_QTY_W4,
CASE WHEN(ABS(A.ACC8W_QTY_W3 - B.ACC8W_QTY_W3) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_QTY_W3 - B.ACC8W_QTY_W3 END
AS DIFF_ACC8W_QTY_W3,
CASE WHEN(ABS(A.ACC8W_QTY_W2 - B.ACC8W_QTY_W2) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_QTY_W2 - B.ACC8W_QTY_W2 END
AS DIFF_ACC8W_QTY_W2,
CASE WHEN(ABS(A.ACC8W_QTY_W1 - B.ACC8W_QTY_W1) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_QTY_W1 - B.ACC8W_QTY_W1 END
AS DIFF_ACC8W_QTY_W1
FROM MAPE_STEP4 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
-- 검증 3.1 (Advanced)각 컬럼 차 비교 중 0이 아닌 것의 수를 조회
-- 주의 : 각 컬럼별로 조회되어야 하는데 AND나 OR 조건을 여러 컬럼에 동시에 넣을 수 없으니 하나씩 주석 해제 해가며 조회한다.
SELECT COUNT(DIFF_ACC8W_QTY_W6),
COUNT(DIFF_ACC8W_QTY_W5),
COUNT(DIFF_ACC8W_QTY_W4),
COUNT(DIFF_ACC8W_QTY_W3),
COUNT(DIFF_ACC8W_QTY_W2),
COUNT(DIFF_ACC8W_QTY_W1)
FROM(
SELECT CASE WHEN(ABS(A.ACC8W_QTY_W6 - B.ACC8W_QTY_W6) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_QTY_W6 - B.ACC8W_QTY_W6 END
AS DIFF_ACC8W_QTY_W6,
CASE WHEN(ABS(A.ACC8W_QTY_W5 - B.ACC8W_QTY_W5) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_QTY_W5 - B.ACC8W_QTY_W5 END
AS DIFF_ACC8W_QTY_W5,
CASE WHEN(ABS(A.ACC8W_QTY_W4 - B.ACC8W_QTY_W4) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_QTY_W4 - B.ACC8W_QTY_W4 END
AS DIFF_ACC8W_QTY_W4,
CASE WHEN(ABS(A.ACC8W_QTY_W3 - B.ACC8W_QTY_W3) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_QTY_W3 - B.ACC8W_QTY_W3 END
AS DIFF_ACC8W_QTY_W3,
CASE WHEN(ABS(A.ACC8W_QTY_W2 - B.ACC8W_QTY_W2) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_QTY_W2 - B.ACC8W_QTY_W2 END
AS DIFF_ACC8W_QTY_W2,
CASE WHEN(ABS(A.ACC8W_QTY_W1 - B.ACC8W_QTY_W1) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_QTY_W1 - B.ACC8W_QTY_W1 END
AS DIFF_ACC8W_QTY_W1
FROM MAPE_STEP4 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
)
WHERE 1=1
--AND DIFF_ACC8W_QTY_W6 != 0;
AND DIFF_ACC8W_QTY_W5 != 0;
--AND DIFF_ACC8W_QTY_W4 != 0;
--AND DIFF_ACC8W_QTY_W3 != 0;
--AND DIFF_ACC8W_QTY_W2 != 0;
--AND DIFF_ACC8W_QTY_W1 != 0;
-- 검증 3.2 (Advanced)각 컬럼 차 비교 중 0이 아닌 것의 수가 조회될 경우 어떤 row인지 찾는다.
-- 주의 : 각 컬럼별로 조회되어야 하는데 AND나 OR 조건을 여러 컬럼에 동시에 넣을 수 없으니 하나씩 주석 해제 해가며 조회한다.
SELECT B.*
FROM(
SELECT A.*,
CASE WHEN(ABS(A.ACC8W_QTY_W6 - B.ACC8W_QTY_W6) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_QTY_W6 - B.ACC8W_QTY_W6 END
AS DIFF_ACC8W_QTY_W6,
CASE WHEN(ABS(A.ACC8W_QTY_W5 - B.ACC8W_QTY_W5) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_QTY_W5 - B.ACC8W_QTY_W5 END
AS DIFF_ACC8W_QTY_W5,
CASE WHEN(ABS(A.ACC8W_QTY_W4 - B.ACC8W_QTY_W4) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_QTY_W4 - B.ACC8W_QTY_W4 END
AS DIFF_ACC8W_QTY_W4,
CASE WHEN(ABS(A.ACC8W_QTY_W3 - B.ACC8W_QTY_W3) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_QTY_W3 - B.ACC8W_QTY_W3 END
AS DIFF_ACC8W_QTY_W3,
CASE WHEN(ABS(A.ACC8W_QTY_W2 - B.ACC8W_QTY_W2) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_QTY_W2 - B.ACC8W_QTY_W2 END
AS DIFF_ACC8W_QTY_W2,
CASE WHEN(ABS(A.ACC8W_QTY_W1 - B.ACC8W_QTY_W1) < POWER(10,-10)) THEN 0
ELSE A.ACC8W_QTY_W1 - B.ACC8W_QTY_W1 END
AS DIFF_ACC8W_QTY_W1
FROM MAPE_STEP4 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
) B
WHERE 1=1
--AND DIFF_ACC8W_QTY_W6 != 0;
--AND DIFF_ACC8W_QTY_W5 != 0;
AND DIFF_ACC8W_QTY_W4 != 0;
--AND DIFF_ACC8W_QTY_W3 != 0;
--AND DIFF_ACC8W_QTY_W2 != 0;
--AND DIFF_ACC8W_QTY_W1 != 0;
--------------------------------------------------------------------------------
-- Step 5. 각 row의 FCST 6 ~ 1 주차 평균을 구한다.
-- FCST에 대한 null값 처리(NULL인 경우 나눗셈에 넣지 않는다.)
--CREATE TABLE MAPE_STEP5 AS -- CREATE 한 후 주석처리
SELECT A.*,
((DECODE(A.FCST_W6, NULL, 0, A.FCST_W6)
+ DECODE(A.FCST_W5, NULL, 0, A.FCST_W5)
+ DECODE(A.FCST_W4, NULL, 0, A.FCST_W4)
+ DECODE(A.FCST_W3, NULL, 0, A.FCST_W3)
+ DECODE(A.FCST_W2, NULL, 0, A.FCST_W2)
+ DECODE(A.FCST_W1, NULL, 0, A.FCST_W1))
/ (DECODE(A.FCST_W6, NULL, 0, 1)
+ DECODE(A.FCST_W5, NULL, 0, 1)
+ DECODE(A.FCST_W4, NULL, 0, 1)
+ DECODE(A.FCST_W3, NULL, 0, 1)
+ DECODE(A.FCST_W2, NULL, 0, 1)
+ DECODE(A.FCST_W1, NULL, 0, 1)))
AS FCST_AVG
FROM MAPE_STEP4 A
ORDER BY SEG1 ASC, SEG2 ASC, SEG3 ASC;
-- 위와 동일한 수식이다. 단지, 덧셈할 때 DECODE 함수 대신 NVL 함수를 썼다.
SELECT A.*,
((NVL(A.FCST_W6, 0)
+ NVL(A.FCST_W5, 0)
+ NVL(A.FCST_W4, 0)
+ NVL(A.FCST_W3, 0)
+ NVL(A.FCST_W2, 0)
+ NVL(A.FCST_W1, 0))
/ (DECODE(A.FCST_W6, NULL, 0, 1)
+ DECODE(A.FCST_W5, NULL, 0, 1)
+ DECODE(A.FCST_W4, NULL, 0, 1)
+ DECODE(A.FCST_W3, NULL, 0, 1)
+ DECODE(A.FCST_W2, NULL, 0, 1)
+ DECODE(A.FCST_W1, NULL, 0, 1)))
AS FCST_AVG
FROM MAPE_STEP4 A
ORDER BY SEG1 ASC, SEG2 ASC, SEG3 ASC;
-- STEP 5. 검증
SELECT * FROM MAPE_STEP5; -- 스텝 5단계 저장본.. 문제푼거
SELECT * FROM PRO_FCST_EXCEL_KEY -- 엑셀파일.. 답지
ORDER BY SEG1 ASC, SEG2 ASC, SEG3 ASC;
-- 검증 1. 컬럼수 비교
SELECT COUNT(*)
FROM MAPE_STEP5;
SELECT COUNT(*)
FROM PRO_FCST_EXCEL_KEY;
-- 검증 2. 합의 차 비교
-- 주의 : 그냥 'SUM(A.FCST_AVG - B.FCST_AVG) AS DIFF_FCST_AVG'을 할 경우 엑셀 답지와 오라클이 직접 계산한 결과가 약간씩 오차가 발생한다.
-- 따라서 오차가 일정 크기보다 작으면 0으로 처리한다. 여기서는 10^(-8)보다 작을 경우로 했다.
SELECT CASE WHEN(ABS(SUM(A.FCST_AVG - B.FCST_AVG)) < POWER(10,-8)) THEN 0
ELSE SUM(A.FCST_AVG - B.FCST_AVG) END
AS DIFF_FCST_AVG
FROM MAPE_STEP5 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
-- 검증 3. 각 컬럼 차 비교
-- 주의 : 마찬가지로 오차가 일정 크기보다 작으면 0으로 처리한다.
-- 여기서는 각 행렬 단일 비교이므로 조금 더 엄격한 기준으로10^(-10)보다 작을 경우로 했다.
SELECT CASE WHEN(ABS(A.FCST_AVG - B.FCST_AVG) < POWER(10,-10)) THEN 0
ELSE A.FCST_AVG - B.FCST_AVG END
AS DIFF_FCST_AVG
FROM MAPE_STEP5 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
-- 검증 3.1 (Advanced)각 컬럼 차 비교 중 0이 아닌 것의 수를 조회
-- 주의 : Step 4 까지는 오차를 10^(-10)으로 했는데 여기서는 걸러내지 못 한다. 산술 오차 무시를 10^(-8)로 변경했다.
SELECT COUNT(DIFF_FCST_AVG)
FROM(
SELECT CASE WHEN(ABS(A.FCST_AVG - B.FCST_AVG) < POWER(10,-8)) THEN 0
ELSE A.FCST_AVG - B.FCST_AVG END
AS DIFF_FCST_AVG
FROM MAPE_STEP5 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
)
WHERE 1=1
AND DIFF_FCST_AVG != 0;
-- 검증 3.2 (Advanced)각 컬럼 차 비교 중 0이 아닌 것의 수가 조회될 경우 어떤 row인지 찾는다.
-- 주의 : 마찬가지로 오차 범위를 10^(-8)로 조정했다.
SELECT B.*
FROM(
SELECT A.*,
CASE WHEN(ABS(A.FCST_AVG - B.FCST_AVG) < POWER(10,-8)) THEN 0
ELSE A.FCST_AVG - B.FCST_AVG END
AS DIFF_FCST_AVG
FROM MAPE_STEP5 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
) B
WHERE 1=1
AND DIFF_FCST_AVG != 0;
--------------------------------------------------------------------------------
-- Step 6. 각 row의 ACC8W_QTY 6 ~ 1 주차 평균을 구한다.
-- ACC8W_QTY는 이미 null값 처리가 되어 있기 때문에 그냥 6으로 나누면 된다.
--CREATE TABLE MAPE_STEP6 AS -- CREATE 한 후 주석처리
SELECT A.*,
((A.ACC8W_QTY_W6
+ A.ACC8W_QTY_W5
+ A.ACC8W_QTY_W4
+ A.ACC8W_QTY_W3
+ A.ACC8W_QTY_W2
+ A.ACC8W_QTY_W1)
/ 6)
AS ACC_AVG
FROM MAPE_STEP5 A
ORDER BY SEG1 ASC, SEG2 ASC, SEG3 ASC;
-- STEP 6. 검증
SELECT * FROM MAPE_STEP6; -- 스텝 6단계 저장본.. 문제푼거
SELECT * FROM PRO_FCST_EXCEL_KEY -- 엑셀파일.. 답지
ORDER BY SEG1 ASC, SEG2 ASC, SEG3 ASC;
-- 검증 1. 컬럼수 비교
SELECT COUNT(*)
FROM MAPE_STEP6;
SELECT COUNT(*)
FROM PRO_FCST_EXCEL_KEY;
-- 검증 2. 합의 차 비교
-- 주의 : 그냥 'SUM(A.ACC_AVG - B.ACC_AVG) AS DIFF_ACC_AVG'을 할 경우 엑셀 답지와 오라클이 직접 계산한 결과가 약간씩 오차가 발생한다.
-- 따라서 오차가 일정 크기보다 작으면 0으로 처리한다. 여기서는 10^(-8)보다 작을 경우로 했다.
SELECT CASE WHEN(ABS(SUM(A.ACC_AVG - B.ACC_AVG)) < POWER(10,-8)) THEN 0
ELSE SUM(A.ACC_AVG - B.ACC_AVG) END
AS DIFF_ACC_AVG
FROM MAPE_STEP6 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
-- 검증 3. 각 컬럼 차 비교
-- 주의 : 마찬가지로 오차가 일정 크기보다 작으면 0으로 처리한다.
-- 여기서는 각 행렬 단일 비교이므로 조금 더 엄격한 기준으로10^(-10)보다 작을 경우로 했다.
SELECT CASE WHEN(ABS(A.ACC_AVG - B.ACC_AVG) < POWER(10,-10)) THEN 0
ELSE A.ACC_AVG - B.ACC_AVG END
AS DIFF_ACC_AVG
FROM MAPE_STEP6 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
-- 검증 3.1 (Advanced)각 컬럼 차 비교 중 0이 아닌 것의 수를 조회
-- 주의 : Step 4 까지는 오차를 10^(-10)으로 했는데 여기서는 걸러내지 못 한다. 산술 오차 무시를 10^(-8)로 변경했다.
SELECT COUNT(DIFF_ACC_AVG)
FROM(
SELECT CASE WHEN(ABS(A.ACC_AVG - B.ACC_AVG) < POWER(10,-8)) THEN 0
ELSE A.ACC_AVG - B.ACC_AVG END
AS DIFF_ACC_AVG
FROM MAPE_STEP6 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
)
WHERE 1=1
AND DIFF_ACC_AVG != 0;
-- 검증 3.2 (Advanced)각 컬럼 차 비교 중 0이 아닌 것의 수가 조회될 경우 어떤 row인지 찾는다.
-- 주의 : 마찬가지로 오차 범위를 10^(-8)로 조정했다.
SELECT B.*
FROM(
SELECT A.*,
CASE WHEN(ABS(A.ACC_AVG - B.ACC_AVG) < POWER(10,-8)) THEN 0
ELSE A.ACC_AVG - B.ACC_AVG END
AS DIFF_ACC_AVG
FROM MAPE_STEP6 A
FULL OUTER JOIN PRO_FCST_EXCEL_KEY B
ON 1=1
AND (A.SEG1 = B.SEG1)
AND (A.SEG2 = B.SEG2)
AND (A.SEG3 = B.SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
) B
WHERE 1=1
AND DIFF_ACC_AVG != 0;
FinalTest_step by step.sql
0.03MB
합본 (서브 쿼리로 하나로 말아 넣기)
SELECT A6.*,
((A6.ACC8W_QTY_W6
+ A6.ACC8W_QTY_W5
+ A6.ACC8W_QTY_W4
+ A6.ACC8W_QTY_W3
+ A6.ACC8W_QTY_W2
+ A6.ACC8W_QTY_W1)
/ 6)
AS ACC_AVG
FROM (SELECT A5.*,
((DECODE(A5.FCST_W6, NULL, 0, A5.FCST_W6)
+ DECODE(A5.FCST_W5, NULL, 0, A5.FCST_W5)
+ DECODE(A5.FCST_W4, NULL, 0, A5.FCST_W4)
+ DECODE(A5.FCST_W3, NULL, 0, A5.FCST_W3)
+ DECODE(A5.FCST_W2, NULL, 0, A5.FCST_W2)
+ DECODE(A5.FCST_W1, NULL, 0, A5.FCST_W1))
/ (DECODE(A5.FCST_W6, NULL, 0, 1)
+ DECODE(A5.FCST_W5, NULL, 0, 1)
+ DECODE(A5.FCST_W4, NULL, 0, 1)
+ DECODE(A5.FCST_W3, NULL, 0, 1)
+ DECODE(A5.FCST_W2, NULL, 0, 1)
+ DECODE(A5.FCST_W1, NULL, 0, 1)))
AS FCST_AVG
FROM (SELECT A4.*,
CASE WHEN (A4.FCST_W6 IS NOT NULL) THEN A4.FCST_W6 * A4.ACC8W_W6
ELSE 0 END
AS ACC8W_QTY_W6,
CASE WHEN (A4.FCST_W5 IS NOT NULL) THEN A4.FCST_W5 * A4.ACC8W_W5
ELSE 0 END
AS ACC8W_QTY_W5,
CASE WHEN (A4.FCST_W4 IS NOT NULL) THEN A4.FCST_W4 * A4.ACC8W_W4
ELSE 0 END
AS ACC8W_QTY_W4,
CASE WHEN (A4.FCST_W3 IS NOT NULL) THEN A4.FCST_W3 * A4.ACC8W_W3
ELSE 0 END
AS ACC8W_QTY_W3,
CASE WHEN (A4.FCST_W2 IS NOT NULL) THEN A4.FCST_W2 * A4.ACC8W_W2
ELSE 0 END
AS ACC8W_QTY_W2,
CASE WHEN (A4.FCST_W1 IS NOT NULL) THEN A4.FCST_W1 * A4.ACC8W_W1
ELSE 0 END
AS ACC8W_QTY_W1
FROM (SELECT A3.*,
CASE WHEN ((A3.FCST_W6 = 0 OR A3.FCST_W6 IS NULL) OR (A3.QTY = 0)) THEN 0
WHEN (A3.QTY / A3.FCST_W6) > 2 THEN 0
ELSE (1 - (A3.ABS8W_W6 / A3.FCST_W6)) END
AS ACC8W_W6,
CASE WHEN ((A3.FCST_W5 = 0 OR A3.FCST_W5 IS NULL) OR (A3.QTY = 0)) THEN 0
WHEN (A3.QTY / A3.FCST_W5) > 2 THEN 0
ELSE (1 - (A3.ABS8W_W5 / A3.FCST_W5)) END
AS ACC8W_W5,
CASE WHEN ((A3.FCST_W4 = 0 OR A3.FCST_W4 IS NULL) OR (A3.QTY = 0)) THEN 0
WHEN (A3.QTY / A3.FCST_W4) > 2 THEN 0
ELSE (1 - (A3.ABS8W_W4 / A3.FCST_W4)) END
AS ACC8W_W4,
CASE WHEN ((A3.FCST_W3 = 0 OR A3.FCST_W3 IS NULL) OR (A3.QTY = 0)) THEN 0
WHEN (A3.QTY / A3.FCST_W3) > 2 THEN 0
ELSE (1 - (A3.ABS8W_W3 / A3.FCST_W3)) END
AS ACC8W_W3,
CASE WHEN ((A3.FCST_W2 = 0 OR A3.FCST_W2 IS NULL) OR (A3.QTY = 0)) THEN 0
WHEN (A3.QTY / A3.FCST_W2) > 2 THEN 0
ELSE (1 - (A3.ABS8W_W2 / A3.FCST_W2)) END
AS ACC8W_W2,
CASE WHEN ((A3.FCST_W1 = 0 OR A3.FCST_W1 IS NULL) OR (A3.QTY = 0)) THEN 0
WHEN (A3.QTY / A3.FCST_W1) > 2 THEN 0
ELSE (1 - (A3.ABS8W_W1 / A3.FCST_W1)) END
AS ACC8W_W1
FROM (SELECT A2.*,
CASE WHEN A2.FCST_W6 IS NOT NULL THEN ABS(A2.QTY - A2.FCST_W6)
ELSE A2.QTY END
AS ABS8W_W6,
CASE WHEN A2.FCST_W5 IS NOT NULL THEN ABS(A2.QTY - A2.FCST_W5)
ELSE A2.QTY END
AS ABS8W_W5,
CASE WHEN A2.FCST_W4 IS NOT NULL THEN ABS(A2.QTY - A2.FCST_W4)
ELSE A2.QTY END
AS ABS8W_W4,
CASE WHEN A2.FCST_W3 IS NOT NULL THEN ABS(A2.QTY - A2.FCST_W3)
ELSE A2.QTY END
AS ABS8W_W3,
CASE WHEN A2.FCST_W2 IS NOT NULL THEN ABS(A2.QTY - A2.FCST_W2)
ELSE A2.QTY END
AS ABS8W_W2,
CASE WHEN A2.FCST_W1 IS NOT NULL THEN ABS(A2.QTY - A2.FCST_W1)
ELSE A2.QTY END
AS ABS8W_W1
FROM (SELECT A1.PRD_SEG1 AS SEG1,
A1.PRD_SEG2 AS SEG2,
11||A1.PRD_SEG3 AS SEG3,
A1.YEAR||A1.WEEK AS TARGETWEEK,
A1.YEAR,
A1.WEEK,
A1.QTY,
F1.OUTFCST AS FCST_W6,
E1.OUTFCST AS FCST_W5,
D1.OUTFCST AS FCST_W4,
C1.OUTFCST AS FCST_W3,
B1.OUTFCST AS FCST_W2,
A1.OUTFCST AS FCST_W1
FROM PRO_FCST_RESULT_1WEEK A1
LEFT JOIN PRO_FCST_RESULT_2WEEK B1
ON 1=1
AND (A1.PRD_SEG1 = B1.PRD_SEG1)
AND (A1.PRD_SEG2 = B1.PRD_SEG2)
AND (A1.PRD_SEG3 = B1.PRD_SEG3)
AND (A1.YEAR = B1.YEAR)
AND (A1.WEEK = B1.WEEK AND A1.WEEK = 18)
LEFT JOIN PRO_FCST_RESULT_3WEEK C1
ON 1=1
AND (A1.PRD_SEG1 = C1.PRD_SEG1)
AND (A1.PRD_SEG2 = C1.PRD_SEG2)
AND (A1.PRD_SEG3 = C1.PRD_SEG3)
AND (A1.YEAR = C1.YEAR)
AND (A1.WEEK = C1.WEEK AND A1.WEEK = 18)
LEFT JOIN PRO_FCST_RESULT_4WEEK D1
ON 1=1
AND (A1.PRD_SEG1 = D1.PRD_SEG1)
AND (A1.PRD_SEG2 = D1.PRD_SEG2)
AND (A1.PRD_SEG3 = D1.PRD_SEG3)
AND (A1.YEAR = D1.YEAR)
AND (A1.WEEK = D1.WEEK AND A1.WEEK = 18)
LEFT JOIN PRO_FCST_RESULT_5WEEK E1
ON 1=1
AND (A1.PRD_SEG1 = E1.PRD_SEG1)
AND (A1.PRD_SEG2 = E1.PRD_SEG2)
AND (A1.PRD_SEG3 = E1.PRD_SEG3)
AND (A1.YEAR = E1.YEAR)
AND (A1.WEEK = E1.WEEK AND A1.WEEK = 18)
LEFT JOIN PRO_FCST_RESULT_6WEEK F1
ON 1=1
AND (A1.PRD_SEG1 = F1.PRD_SEG1)
AND (A1.PRD_SEG2 = F1.PRD_SEG2)
AND (A1.PRD_SEG3 = F1.PRD_SEG3)
AND (A1.YEAR = F1.YEAR)
AND (A1.WEEK = F1.WEEK AND A1.WEEK = 18)) A2) A3) A4) A5) A6
ORDER BY SEG1 ASC, SEG2 ASC, SEG3 ASC;
FinalTest_sub query.sql
0.01MB
여기서 각 서브 쿼리의 expression은 그냥 위에 'step by step'에서 했던대로 전부 A를 써도 되지만 가독성 때문에 A1, A2, A3... 등으로 변경했다. 굳이 바꾸지 않아도 쿼리는 정상적으로 실행된다.
합본 (WITH AS 를 활용한 서브쿼리)
WITH STEP1 AS
(
SELECT A.PRD_SEG1 AS SEG1,
A.PRD_SEG2 AS SEG2,
11||A.PRD_SEG3 AS SEG3,
A.YEAR||A.WEEK AS TARGETWEEK,
A.YEAR,
A.WEEK,
A.QTY,
F.OUTFCST AS FCST_W6,
E.OUTFCST AS FCST_W5,
D.OUTFCST AS FCST_W4,
C.OUTFCST AS FCST_W3,
B.OUTFCST AS FCST_W2,
A.OUTFCST AS FCST_W1
FROM PRO_FCST_RESULT_1WEEK A
LEFT JOIN PRO_FCST_RESULT_2WEEK B
ON 1=1
AND (A.PRD_SEG1 = B.PRD_SEG1)
AND (A.PRD_SEG2 = B.PRD_SEG2)
AND (A.PRD_SEG3 = B.PRD_SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
LEFT JOIN PRO_FCST_RESULT_3WEEK C
ON 1=1
AND (A.PRD_SEG1 = C.PRD_SEG1)
AND (A.PRD_SEG2 = C.PRD_SEG2)
AND (A.PRD_SEG3 = C.PRD_SEG3)
AND (A.YEAR = C.YEAR)
AND (A.WEEK = C.WEEK AND A.WEEK = 18)
LEFT JOIN PRO_FCST_RESULT_4WEEK D
ON 1=1
AND (A.PRD_SEG1 = D.PRD_SEG1)
AND (A.PRD_SEG2 = D.PRD_SEG2)
AND (A.PRD_SEG3 = D.PRD_SEG3)
AND (A.YEAR = D.YEAR)
AND (A.WEEK = D.WEEK AND A.WEEK = 18)
LEFT JOIN PRO_FCST_RESULT_5WEEK E
ON 1=1
AND (A.PRD_SEG1 = E.PRD_SEG1)
AND (A.PRD_SEG2 = E.PRD_SEG2)
AND (A.PRD_SEG3 = E.PRD_SEG3)
AND (A.YEAR = E.YEAR)
AND (A.WEEK = E.WEEK AND A.WEEK = 18)
LEFT JOIN PRO_FCST_RESULT_6WEEK F
ON 1=1
AND (A.PRD_SEG1 = F.PRD_SEG1)
AND (A.PRD_SEG2 = F.PRD_SEG2)
AND (A.PRD_SEG3 = F.PRD_SEG3)
AND (A.YEAR = F.YEAR)
AND (A.WEEK = F.WEEK AND A.WEEK = 18)
),
STEP2 AS
(
SELECT A.*,
CASE WHEN A.FCST_W6 IS NOT NULL THEN ABS(A.QTY - A.FCST_W6)
ELSE A.QTY END
AS ABS8W_W6,
CASE WHEN A.FCST_W5 IS NOT NULL THEN ABS(A.QTY - A.FCST_W5)
ELSE A.QTY END
AS ABS8W_W5,
CASE WHEN A.FCST_W4 IS NOT NULL THEN ABS(A.QTY - A.FCST_W4)
ELSE A.QTY END
AS ABS8W_W4,
CASE WHEN A.FCST_W3 IS NOT NULL THEN ABS(A.QTY - A.FCST_W3)
ELSE A.QTY END
AS ABS8W_W3,
CASE WHEN A.FCST_W2 IS NOT NULL THEN ABS(A.QTY - A.FCST_W2)
ELSE A.QTY END
AS ABS8W_W2,
CASE WHEN A.FCST_W1 IS NOT NULL THEN ABS(A.QTY - A.FCST_W1)
ELSE A.QTY END
AS ABS8W_W1
FROM STEP1 A
),
STEP3 AS
(
SELECT A.*,
CASE WHEN ((A.FCST_W6 = 0 OR A.FCST_W6 IS NULL) OR (A.QTY = 0)) THEN 0
WHEN (A.QTY / A.FCST_W6) > 2 THEN 0
ELSE (1 - (A.ABS8W_W6 / A.FCST_W6)) END
AS ACC8W_W6,
CASE WHEN ((A.FCST_W5 = 0 OR A.FCST_W5 IS NULL) OR (A.QTY = 0)) THEN 0
WHEN (A.QTY / A.FCST_W5) > 2 THEN 0
ELSE (1 - (A.ABS8W_W5 / A.FCST_W5)) END
AS ACC8W_W5,
CASE WHEN ((A.FCST_W4 = 0 OR A.FCST_W4 IS NULL) OR (A.QTY = 0)) THEN 0
WHEN (A.QTY / A.FCST_W4) > 2 THEN 0
ELSE (1 - (A.ABS8W_W4 / A.FCST_W4)) END
AS ACC8W_W4,
CASE WHEN ((A.FCST_W3 = 0 OR A.FCST_W3 IS NULL) OR (A.QTY = 0)) THEN 0
WHEN (A.QTY / A.FCST_W3) > 2 THEN 0
ELSE (1 - (A.ABS8W_W3 / A.FCST_W3)) END
AS ACC8W_W3,
CASE WHEN ((A.FCST_W2 = 0 OR A.FCST_W2 IS NULL) OR (A.QTY = 0)) THEN 0
WHEN (A.QTY / A.FCST_W2) > 2 THEN 0
ELSE (1 - (A.ABS8W_W2 / A.FCST_W2)) END
AS ACC8W_W2,
CASE WHEN ((A.FCST_W1 = 0 OR A.FCST_W1 IS NULL) OR (A.QTY = 0)) THEN 0
WHEN (A.QTY / A.FCST_W1) > 2 THEN 0
ELSE (1 - (A.ABS8W_W1 / A.FCST_W1)) END
AS ACC8W_W1
FROM STEP2 A
),
STEP4 AS
(
SELECT A.*,
CASE WHEN (A.FCST_W6 IS NOT NULL) THEN A.FCST_W6 * A.ACC8W_W6
ELSE 0 END
AS ACC8W_QTY_W6,
CASE WHEN (A.FCST_W5 IS NOT NULL) THEN A.FCST_W5 * A.ACC8W_W5
ELSE 0 END
AS ACC8W_QTY_W5,
CASE WHEN (A.FCST_W4 IS NOT NULL) THEN A.FCST_W4 * A.ACC8W_W4
ELSE 0 END
AS ACC8W_QTY_W4,
CASE WHEN (A.FCST_W3 IS NOT NULL) THEN A.FCST_W3 * A.ACC8W_W3
ELSE 0 END
AS ACC8W_QTY_W3,
CASE WHEN (A.FCST_W2 IS NOT NULL) THEN A.FCST_W2 * A.ACC8W_W2
ELSE 0 END
AS ACC8W_QTY_W2,
CASE WHEN (A.FCST_W1 IS NOT NULL) THEN A.FCST_W1 * A.ACC8W_W1
ELSE 0 END
AS ACC8W_QTY_W1
FROM STEP3 A
),
STEP5 AS
(
SELECT A.*,
((NVL(A.FCST_W6, 0)
+ NVL(A.FCST_W5, 0)
+ NVL(A.FCST_W4, 0)
+ NVL(A.FCST_W3, 0)
+ NVL(A.FCST_W2, 0)
+ NVL(A.FCST_W1, 0))
/ (DECODE(A.FCST_W6, NULL, 0, 1)
+ DECODE(A.FCST_W5, NULL, 0, 1)
+ DECODE(A.FCST_W4, NULL, 0, 1)
+ DECODE(A.FCST_W3, NULL, 0, 1)
+ DECODE(A.FCST_W2, NULL, 0, 1)
+ DECODE(A.FCST_W1, NULL, 0, 1)))
AS FCST_AVG
FROM STEP4 A
),
STEP6 AS
(
SELECT A.*,
((A.ACC8W_QTY_W6
+ A.ACC8W_QTY_W5
+ A.ACC8W_QTY_W4
+ A.ACC8W_QTY_W3
+ A.ACC8W_QTY_W2
+ A.ACC8W_QTY_W1)
/ 6)
AS ACC_AVG
FROM STEP5 A
)
SELECT *
FROM STEP6
ORDER BY SEG1 ASC, SEG2 ASC, SEG3 ASC;
테이블은 다 만들었고... 마지막 이 표 만들기...
Step 7. SEG1 그룹별 최종 정확도 산출
WITH STEP1 AS
(
SELECT A.PRD_SEG1 AS SEG1,
A.PRD_SEG2 AS SEG2,
11||A.PRD_SEG3 AS SEG3,
A.YEAR||A.WEEK AS TARGETWEEK,
A.YEAR,
A.WEEK,
A.QTY,
F.OUTFCST AS FCST_W6,
E.OUTFCST AS FCST_W5,
D.OUTFCST AS FCST_W4,
C.OUTFCST AS FCST_W3,
B.OUTFCST AS FCST_W2,
A.OUTFCST AS FCST_W1
FROM PRO_FCST_RESULT_1WEEK A
LEFT JOIN PRO_FCST_RESULT_2WEEK B
ON 1=1
AND (A.PRD_SEG1 = B.PRD_SEG1)
AND (A.PRD_SEG2 = B.PRD_SEG2)
AND (A.PRD_SEG3 = B.PRD_SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
LEFT JOIN PRO_FCST_RESULT_3WEEK C
ON 1=1
AND (A.PRD_SEG1 = C.PRD_SEG1)
AND (A.PRD_SEG2 = C.PRD_SEG2)
AND (A.PRD_SEG3 = C.PRD_SEG3)
AND (A.YEAR = C.YEAR)
AND (A.WEEK = C.WEEK AND A.WEEK = 18)
LEFT JOIN PRO_FCST_RESULT_4WEEK D
ON 1=1
AND (A.PRD_SEG1 = D.PRD_SEG1)
AND (A.PRD_SEG2 = D.PRD_SEG2)
AND (A.PRD_SEG3 = D.PRD_SEG3)
AND (A.YEAR = D.YEAR)
AND (A.WEEK = D.WEEK AND A.WEEK = 18)
LEFT JOIN PRO_FCST_RESULT_5WEEK E
ON 1=1
AND (A.PRD_SEG1 = E.PRD_SEG1)
AND (A.PRD_SEG2 = E.PRD_SEG2)
AND (A.PRD_SEG3 = E.PRD_SEG3)
AND (A.YEAR = E.YEAR)
AND (A.WEEK = E.WEEK AND A.WEEK = 18)
LEFT JOIN PRO_FCST_RESULT_6WEEK F
ON 1=1
AND (A.PRD_SEG1 = F.PRD_SEG1)
AND (A.PRD_SEG2 = F.PRD_SEG2)
AND (A.PRD_SEG3 = F.PRD_SEG3)
AND (A.YEAR = F.YEAR)
AND (A.WEEK = F.WEEK AND A.WEEK = 18)
),
STEP2 AS
(
SELECT A.*,
CASE WHEN A.FCST_W6 IS NOT NULL THEN ABS(A.QTY - A.FCST_W6)
ELSE A.QTY END
AS ABS8W_W6,
CASE WHEN A.FCST_W5 IS NOT NULL THEN ABS(A.QTY - A.FCST_W5)
ELSE A.QTY END
AS ABS8W_W5,
CASE WHEN A.FCST_W4 IS NOT NULL THEN ABS(A.QTY - A.FCST_W4)
ELSE A.QTY END
AS ABS8W_W4,
CASE WHEN A.FCST_W3 IS NOT NULL THEN ABS(A.QTY - A.FCST_W3)
ELSE A.QTY END
AS ABS8W_W3,
CASE WHEN A.FCST_W2 IS NOT NULL THEN ABS(A.QTY - A.FCST_W2)
ELSE A.QTY END
AS ABS8W_W2,
CASE WHEN A.FCST_W1 IS NOT NULL THEN ABS(A.QTY - A.FCST_W1)
ELSE A.QTY END
AS ABS8W_W1
FROM STEP1 A
),
STEP3 AS
(
SELECT A.*,
CASE WHEN ((A.FCST_W6 = 0 OR A.FCST_W6 IS NULL) OR (A.QTY = 0)) THEN 0
WHEN (A.QTY / A.FCST_W6) > 2 THEN 0
ELSE (1 - (A.ABS8W_W6 / A.FCST_W6)) END
AS ACC8W_W6,
CASE WHEN ((A.FCST_W5 = 0 OR A.FCST_W5 IS NULL) OR (A.QTY = 0)) THEN 0
WHEN (A.QTY / A.FCST_W5) > 2 THEN 0
ELSE (1 - (A.ABS8W_W5 / A.FCST_W5)) END
AS ACC8W_W5,
CASE WHEN ((A.FCST_W4 = 0 OR A.FCST_W4 IS NULL) OR (A.QTY = 0)) THEN 0
WHEN (A.QTY / A.FCST_W4) > 2 THEN 0
ELSE (1 - (A.ABS8W_W4 / A.FCST_W4)) END
AS ACC8W_W4,
CASE WHEN ((A.FCST_W3 = 0 OR A.FCST_W3 IS NULL) OR (A.QTY = 0)) THEN 0
WHEN (A.QTY / A.FCST_W3) > 2 THEN 0
ELSE (1 - (A.ABS8W_W3 / A.FCST_W3)) END
AS ACC8W_W3,
CASE WHEN ((A.FCST_W2 = 0 OR A.FCST_W2 IS NULL) OR (A.QTY = 0)) THEN 0
WHEN (A.QTY / A.FCST_W2) > 2 THEN 0
ELSE (1 - (A.ABS8W_W2 / A.FCST_W2)) END
AS ACC8W_W2,
CASE WHEN ((A.FCST_W1 = 0 OR A.FCST_W1 IS NULL) OR (A.QTY = 0)) THEN 0
WHEN (A.QTY / A.FCST_W1) > 2 THEN 0
ELSE (1 - (A.ABS8W_W1 / A.FCST_W1)) END
AS ACC8W_W1
FROM STEP2 A
),
STEP4 AS
(
SELECT A.*,
CASE WHEN (A.FCST_W6 IS NOT NULL) THEN A.FCST_W6 * A.ACC8W_W6
ELSE 0 END
AS ACC8W_QTY_W6,
CASE WHEN (A.FCST_W5 IS NOT NULL) THEN A.FCST_W5 * A.ACC8W_W5
ELSE 0 END
AS ACC8W_QTY_W5,
CASE WHEN (A.FCST_W4 IS NOT NULL) THEN A.FCST_W4 * A.ACC8W_W4
ELSE 0 END
AS ACC8W_QTY_W4,
CASE WHEN (A.FCST_W3 IS NOT NULL) THEN A.FCST_W3 * A.ACC8W_W3
ELSE 0 END
AS ACC8W_QTY_W3,
CASE WHEN (A.FCST_W2 IS NOT NULL) THEN A.FCST_W2 * A.ACC8W_W2
ELSE 0 END
AS ACC8W_QTY_W2,
CASE WHEN (A.FCST_W1 IS NOT NULL) THEN A.FCST_W1 * A.ACC8W_W1
ELSE 0 END
AS ACC8W_QTY_W1
FROM STEP3 A
),
STEP5 AS
(
SELECT A.*,
((NVL(A.FCST_W6, 0)
+ NVL(A.FCST_W5, 0)
+ NVL(A.FCST_W4, 0)
+ NVL(A.FCST_W3, 0)
+ NVL(A.FCST_W2, 0)
+ NVL(A.FCST_W1, 0))
/ (DECODE(A.FCST_W6, NULL, 0, 1)
+ DECODE(A.FCST_W5, NULL, 0, 1)
+ DECODE(A.FCST_W4, NULL, 0, 1)
+ DECODE(A.FCST_W3, NULL, 0, 1)
+ DECODE(A.FCST_W2, NULL, 0, 1)
+ DECODE(A.FCST_W1, NULL, 0, 1)))
AS FCST_AVG
FROM STEP4 A
),
STEP6 AS
(
SELECT A.*,
((A.ACC8W_QTY_W6
+ A.ACC8W_QTY_W5
+ A.ACC8W_QTY_W4
+ A.ACC8W_QTY_W3
+ A.ACC8W_QTY_W2
+ A.ACC8W_QTY_W1)
/ 6)
AS ACC_AVG
FROM STEP5 A
)
SELECT SEG1 AS "행 레이블",
SUM(FCST_AVG) AS "합계 : FCST_AVG",
SUM(ACC_AVG) AS "합계 : ACC_AVG",
(SUM(ACC_AVG) / SUM(FCST_AVG)) AS "SEG1 그룹별 정확도"
FROM STEP6
GROUP BY SEG1
ORDER BY DECODE(SEG1, 'PRDA', 1, 'PRDC', 2, 'PRDB', 3, 'PRDD', 4);그냥 ORDER BY SEG1 ASC; 하는게 더 난 것 같은데... 왠지 모르겠지만 A > C > B > D 순으로 나와 있어서 그냥 맞춰줬다.
주의 : 여기까지는 UNION을 사용해 두 테이블을 합치지 않기 때문에 정렬도 되고, expression 수식을 이용한 한글 Alias(별칭 주기)도 된다.(AS '한글')
하지만 아래 총합계를 구하기 위해서는 위 결과를 이용해 하나의 새로운 SUM 쿼리를 만들어 합쳐야 한다. 이 때 UNION을 사용하게 되면 ORDER BY를 통한 정렬은 물론, AS '한글' 같은 별칭 주기 모두 에러가 발생한다. 따라서, 특정 순서로 정렬을 하고 싶거나 한글 별칭을 꼭 사용해야한다면 이 과정에서 결과를 별도의 테이블로 저장을 한 다음 진행해야한다. 여기서는 그냥 서브쿼리를 이용하기 위해 정렬을 포기하고, 컬럼명은 영문으로 대체한다.
WITH STEP1 AS
(
SELECT A.PRD_SEG1 AS SEG1,
A.PRD_SEG2 AS SEG2,
11||A.PRD_SEG3 AS SEG3,
A.YEAR||A.WEEK AS TARGETWEEK,
A.YEAR,
A.WEEK,
A.QTY,
F.OUTFCST AS FCST_W6,
E.OUTFCST AS FCST_W5,
D.OUTFCST AS FCST_W4,
C.OUTFCST AS FCST_W3,
B.OUTFCST AS FCST_W2,
A.OUTFCST AS FCST_W1
FROM PRO_FCST_RESULT_1WEEK A
LEFT JOIN PRO_FCST_RESULT_2WEEK B
ON 1=1
AND (A.PRD_SEG1 = B.PRD_SEG1)
AND (A.PRD_SEG2 = B.PRD_SEG2)
AND (A.PRD_SEG3 = B.PRD_SEG3)
AND (A.YEAR = B.YEAR)
AND (A.WEEK = B.WEEK AND A.WEEK = 18)
LEFT JOIN PRO_FCST_RESULT_3WEEK C
ON 1=1
AND (A.PRD_SEG1 = C.PRD_SEG1)
AND (A.PRD_SEG2 = C.PRD_SEG2)
AND (A.PRD_SEG3 = C.PRD_SEG3)
AND (A.YEAR = C.YEAR)
AND (A.WEEK = C.WEEK AND A.WEEK = 18)
LEFT JOIN PRO_FCST_RESULT_4WEEK D
ON 1=1
AND (A.PRD_SEG1 = D.PRD_SEG1)
AND (A.PRD_SEG2 = D.PRD_SEG2)
AND (A.PRD_SEG3 = D.PRD_SEG3)
AND (A.YEAR = D.YEAR)
AND (A.WEEK = D.WEEK AND A.WEEK = 18)
LEFT JOIN PRO_FCST_RESULT_5WEEK E
ON 1=1
AND (A.PRD_SEG1 = E.PRD_SEG1)
AND (A.PRD_SEG2 = E.PRD_SEG2)
AND (A.PRD_SEG3 = E.PRD_SEG3)
AND (A.YEAR = E.YEAR)
AND (A.WEEK = E.WEEK AND A.WEEK = 18)
LEFT JOIN PRO_FCST_RESULT_6WEEK F
ON 1=1
AND (A.PRD_SEG1 = F.PRD_SEG1)
AND (A.PRD_SEG2 = F.PRD_SEG2)
AND (A.PRD_SEG3 = F.PRD_SEG3)
AND (A.YEAR = F.YEAR)
AND (A.WEEK = F.WEEK AND A.WEEK = 18)
),
STEP2 AS
(
SELECT A.*,
CASE WHEN A.FCST_W6 IS NOT NULL THEN ABS(A.QTY - A.FCST_W6)
ELSE A.QTY END
AS ABS8W_W6,
CASE WHEN A.FCST_W5 IS NOT NULL THEN ABS(A.QTY - A.FCST_W5)
ELSE A.QTY END
AS ABS8W_W5,
CASE WHEN A.FCST_W4 IS NOT NULL THEN ABS(A.QTY - A.FCST_W4)
ELSE A.QTY END
AS ABS8W_W4,
CASE WHEN A.FCST_W3 IS NOT NULL THEN ABS(A.QTY - A.FCST_W3)
ELSE A.QTY END
AS ABS8W_W3,
CASE WHEN A.FCST_W2 IS NOT NULL THEN ABS(A.QTY - A.FCST_W2)
ELSE A.QTY END
AS ABS8W_W2,
CASE WHEN A.FCST_W1 IS NOT NULL THEN ABS(A.QTY - A.FCST_W1)
ELSE A.QTY END
AS ABS8W_W1
FROM STEP1 A
),
STEP3 AS
(
SELECT A.*,
CASE WHEN ((A.FCST_W6 = 0 OR A.FCST_W6 IS NULL) OR (A.QTY = 0)) THEN 0
WHEN (A.QTY / A.FCST_W6) > 2 THEN 0
ELSE (1 - (A.ABS8W_W6 / A.FCST_W6)) END
AS ACC8W_W6,
CASE WHEN ((A.FCST_W5 = 0 OR A.FCST_W5 IS NULL) OR (A.QTY = 0)) THEN 0
WHEN (A.QTY / A.FCST_W5) > 2 THEN 0
ELSE (1 - (A.ABS8W_W5 / A.FCST_W5)) END
AS ACC8W_W5,
CASE WHEN ((A.FCST_W4 = 0 OR A.FCST_W4 IS NULL) OR (A.QTY = 0)) THEN 0
WHEN (A.QTY / A.FCST_W4) > 2 THEN 0
ELSE (1 - (A.ABS8W_W4 / A.FCST_W4)) END
AS ACC8W_W4,
CASE WHEN ((A.FCST_W3 = 0 OR A.FCST_W3 IS NULL) OR (A.QTY = 0)) THEN 0
WHEN (A.QTY / A.FCST_W3) > 2 THEN 0
ELSE (1 - (A.ABS8W_W3 / A.FCST_W3)) END
AS ACC8W_W3,
CASE WHEN ((A.FCST_W2 = 0 OR A.FCST_W2 IS NULL) OR (A.QTY = 0)) THEN 0
WHEN (A.QTY / A.FCST_W2) > 2 THEN 0
ELSE (1 - (A.ABS8W_W2 / A.FCST_W2)) END
AS ACC8W_W2,
CASE WHEN ((A.FCST_W1 = 0 OR A.FCST_W1 IS NULL) OR (A.QTY = 0)) THEN 0
WHEN (A.QTY / A.FCST_W1) > 2 THEN 0
ELSE (1 - (A.ABS8W_W1 / A.FCST_W1)) END
AS ACC8W_W1
FROM STEP2 A
),
STEP4 AS
(
SELECT A.*,
CASE WHEN (A.FCST_W6 IS NOT NULL) THEN A.FCST_W6 * A.ACC8W_W6
ELSE 0 END
AS ACC8W_QTY_W6,
CASE WHEN (A.FCST_W5 IS NOT NULL) THEN A.FCST_W5 * A.ACC8W_W5
ELSE 0 END
AS ACC8W_QTY_W5,
CASE WHEN (A.FCST_W4 IS NOT NULL) THEN A.FCST_W4 * A.ACC8W_W4
ELSE 0 END
AS ACC8W_QTY_W4,
CASE WHEN (A.FCST_W3 IS NOT NULL) THEN A.FCST_W3 * A.ACC8W_W3
ELSE 0 END
AS ACC8W_QTY_W3,
CASE WHEN (A.FCST_W2 IS NOT NULL) THEN A.FCST_W2 * A.ACC8W_W2
ELSE 0 END
AS ACC8W_QTY_W2,
CASE WHEN (A.FCST_W1 IS NOT NULL) THEN A.FCST_W1 * A.ACC8W_W1
ELSE 0 END
AS ACC8W_QTY_W1
FROM STEP3 A
),
STEP5 AS
(
SELECT A.*,
((NVL(A.FCST_W6, 0)
+ NVL(A.FCST_W5, 0)
+ NVL(A.FCST_W4, 0)
+ NVL(A.FCST_W3, 0)
+ NVL(A.FCST_W2, 0)
+ NVL(A.FCST_W1, 0))
/ (DECODE(A.FCST_W6, NULL, 0, 1)
+ DECODE(A.FCST_W5, NULL, 0, 1)
+ DECODE(A.FCST_W4, NULL, 0, 1)
+ DECODE(A.FCST_W3, NULL, 0, 1)
+ DECODE(A.FCST_W2, NULL, 0, 1)
+ DECODE(A.FCST_W1, NULL, 0, 1)))
AS FCST_AVG
FROM STEP4 A
),
STEP6 AS
(
SELECT A.*,
((A.ACC8W_QTY_W6
+ A.ACC8W_QTY_W5
+ A.ACC8W_QTY_W4
+ A.ACC8W_QTY_W3
+ A.ACC8W_QTY_W2
+ A.ACC8W_QTY_W1)
/ 6)
AS ACC_AVG
FROM STEP5 A
),
STEP7 AS
(
SELECT SEG1 AS SEG1_Label,
SUM(FCST_AVG) AS SUM_FCST_AVG,
SUM(ACC_AVG) AS SUM_ACC_AVG_,
(SUM(ACC_AVG) / SUM(FCST_AVG)) AS GROUPED_SEG1_ACC
FROM STEP6
GROUP BY SEG1
ORDER BY DECODE(SEG1, 'PRDA', 1, 'PRDC', 2, 'PRDB', 3, 'PRDD', 4)
),
STEP8 AS
(
SELECT * FROM STEP7
UNION
SELECT REPLACE(COUNT(SEG1_Label), COUNT(SEG1_Label), '총합계'),
SUM(SUM_FCST_AVG),
SUM(SUM_ACC_AVG_),
(SUM(SUM_ACC_AVG_) / SUM(SUM_FCST_AVG))
FROM STEP7
)
SELECT *
FROM STEP8
최종 결과
설명 :
Step 7. 은
이거를 구하는 과정이고,
Step 8. 은
이거를 구해 Step 7과 UNION으로 결합하는 과정이다.
주의 1 : 'ORDER BY DECODE(SEG1, 'PRDA', 1, 'PRDC', 2, 'PRDB', 3, 'PRDD', 4)'를 했음에도 정렬이 되지 않음을 알 수 있다.
STEP8 혹은 마지막 조회 SELECT문에 해당 정렬을 넣을 경우 에러가 발생한다.
주의 2 : UNION 때문에 컬럼명에 한글 별칭을 넣을 수 없어 영문으로 변경했다.
FinalTest_with as sub query_and_seg1 group by acc.sql
0.01MB
'개발자 > Database & SQL' 카테고리의 다른 글
| Oracle (오라클) 백업/복구 (0) | 2020.11.10 |
|---|---|
| 맥 MariaDB 설치하기 (포트 변경) (0) | 2020.10.03 |
| MariaDB/MySQL Query (0) | 2020.06.30 |
| Oracle (오라클) 함수 - 정렬, UNION, CONCAT, 포맷팅, GROUP BY, 형변환 (0) | 2020.06.29 |
| Oracle (오라클) 산술연산자, 결측값, 변수 입력 받기 (0) | 2020.06.29 |