작성일자 : 2024-09-04
Ver 0.1.1
0. 배경
TQRDC는 Technology, Quality, Responsiveness, Delivery, Cost의 약자로 고객관리 5대 요소이다.
월, 분기, 반기 등과 같이 일정한 주기에 따라 고객이 위 항목에 대해서 평가를 실시한다.
피평가대상인 공급처는 위 결과에 따라 어떤 부분에 더 신경을 쓰고 관리를 해야하는지 전략을 세워 효과적인 CRM을 할 수 있게 된다.
Salesforce에 적재되는 데이터 형태대로 Excel로 가데이터를 생성하여 TQRDC 관련 요건을 적용한 쿼리와 이를 시각화 한 대시보드를 만들어 보고자 한다.
개인 Oracle DB 구동 -> Table 생성 및 가데이터 Insert -> VIEW 개발 -> Tableau 용 사용자 지정 쿼리 개발 -> 대시보드 개발 -> 인사이드 도출
1. 데이터 확인
1-1. Meta Data
(실적)
1-2. Data
(실적)
SELECT *
FROM JAYJAY.TQRDC_RESULT
ORDER BY ACCOUNT, PRODUCT, APPLICATION, EVAL_YEAR || EVAL_QUARTER;
[OUTPUT1]
[OUTPUT2]
2. 배경 및 요건
- ACCOUNT + PRODUCT + APPLICATION을 평가 대상 1개라고 인지하고 분기마다 각 분과에 대해서 평가한다.
- 평가 대상 별로 모든 분과에 대해서 평가를 하지는 않는다.
- 평가 대상인데 평가를 안했을 경우 가장 최근에 펴악한 값을 가져와 사용하고, 평가를 하지 않았음을 대시보드에 표시해 준다.
- RDC 분과는 R, D, C 분과의 평균으로 산정하고, 이를 반올림하여 정수로 표현한다.
- 1 ~ 1.4 등 -> 1등 / x.5 ~ x+1.4등 -> x + 1 등
- 평가 대상별로 T, Q, RDC 분과에 대해서 모두 평가를 했을 때 종합 순위를 산정하고, 평균을 산정한뒤 반올림을 적용한다.
- 1 ~ 1.4 등 -> 1등 / x.5 ~ x+1.4등 -> x + 1 등
- T 분과는 5가지 항목에 대해 점수를 평가하며, 이는 Radar Chart로 표현한다. (조회 분기 vs 조회 분기 -1 분기 비교)
3. VIEW
-- VIEW
CREATE OR REPLACE FORCE VIEW "JAYJAY"."V_MTQRDC_RESULT"
("ACCOUNT","PRODUCT","APPLICATION", "DIVISION", "UNIT2", "EVAL_YEAR", "EVAL_QUARTER", "START_YYYYQQ", "END_YYYYQQ", "VALUE", "FINAL_VALUE", "VARIATION", "IS_NULL")
AS
(
SELECT ACCOUNT
,PRODUCT
,APPLICATION
,DIVISION
,UNIT2
,EVAL_YEAR
,EVAL_QUARTER
,START_YYYYQQ
,END_YYYYQQ
,VALUE
,FINAL_VALUE
,FINAL_VALUE - LAG(FINAL_VALUE) OVER (PARTITION BY ACCOUNT, PRODUCT, APPLICATION, UNIT2
ORDER BY ACCOUNT, PRODUCT, APPLICATION, UNIT2, EVAL_YEAR || EVAL_QUARTER ASC) AS VARIATION
,IS_NULL
FROM (
-- RDC_RESULT
SELECT P.ACCOUNT
,P.PRODUCT
,P.APPLICATION
,P.DIVISION
,P.UNIT2
,P.YEAR AS EVAL_YEAR
,P.QUARTER AS EVAL_QUARTER
,P.START_YYYYQQ
,P.END_YYYYQQ
,R.VALUE
,LAST_VALUE(R.VALUE IGNORE NULLS) OVER (PARTITION BY P.ACCOUNT, P.PRODUCT, P.APPLICATION, P.UNIT2
ORDER BY P.ACCOUNT, P.PRODUCT, P.APPLICATION, P.UNIT2, P.YEAR || P.QUARTER) AS FINAL_VALUE -- 최신값 불러오기 (Null값은 Partition 기준 최신 입력값으로 대체
,NVL2(R.VALUE, 'N', 'Y') AS IS_NULL -- 입력 유무
FROM ( -- SUB QUERY 4 : RDC 모수
SELECT M.ACCOUNT
,M.PRODUCT
,M.APPLICATION
,M.DIVISION
,M.UNIT2
,D.YEAR
,D.QUARTER
,M.START_YYYYQQ
,M.END_YYYYQQ
FROM (
SELECT Y.YEAR
,Q.QUARTER
FROM (
SELECT TO_CHAR(LEVEL) AS YEAR FROM DUAL
WHERE TO_CHAR(LEVEL) > 2021
CONNECT BY LEVEL <= 2024
) Y
JOIN (
SELECT 'Q' || LEVEL AS QUARTER FROM DUAL
CONNECT BY LEVEL <= 4
) Q
ON 1=1
) D
JOIN
(-- SUB QUERY 3 : RDC_MASTER
SELECT DISTINCT ACCOUNT
,PRODUCT
,APPLICATION
,START_YYYYQQ
,END_YYYYQQ
,DIVISION
,UNIT2
FROM ( -- SUB QUERY 2 : 결과 기준 START_YYYYQQ & END_YYYYQQ 정보 산
SELECT ACCOUNT
,PRODUCT
,APPLICATION
,EVAL_YEAR
,EVAL_QUARTER
,MIN(EVAL_YEAR || EVAL_QUARTER) OVER (PARTITION BY ACCOUNT, PRODUCT, APPLICATION) AS START_YYYYQQ
--,MAX(EVAL_YEAR || EVAL_QUARTER) OVER (PARTITION BY ACCOUNT, PRODUCT, APPLICATION) AS MAX_YYYYQQ
-- END YYYYQQ 로직 적용 (전제 : MAX YYYYQQ 값이 1년 전보다 더 오래되었으면, MAX가 END YYYYQQ, 아닌 경우 당분기에도 평가 대상
,CASE WHEN MAX(EVAL_YEAR || EVAL_QUARTER) OVER (PARTITION BY ACCOUNT, PRODUCT, APPLICATION) < '2023Q4' -- SYSTEM 적용 : TO_CHAR(ADD_MONTHS(SYSDATE, -12), 'YYYY"Q"Q')
THEN TO_CHAR(MAX(EVAL_YEAR || EVAL_QUARTER) OVER (PARTITION BY ACCOUNT, PRODUCT, APPLICATION))
ELSE '2024Q4'
END AS END_YYYYQQ
,DIVISION
,UNIT2
,VALUE
FROM (-- SUB QUERY 1 : RDC_RESULT
SELECT ACCOUNT
,PRODUCT
,APPLICATION
,EVAL_YEAR
,EVAL_QUARTER
,'RDC' AS DIVISION
,UNIT2
,VALUE
FROM JAYJAY.TQRDC_RESULT
UNPIVOT (VALUE FOR UNIT2 IN (RDC_RANK, R_RANK, D_RANK, C_RANK))
ORDER BY ACCOUNT
,PRODUCT
,APPLICATION
,EVAL_YEAR || EVAL_QUARTER
)-- SUB QUERY 1
) -- SUB QUERY 2
) M -- SUB QUERY 3
ON 1=1
WHERE D.YEAR || D.QUARTER BETWEEN M.START_YYYYQQ AND M.END_YYYYQQ -- 평가 항목 START YYYYQQ & END YYYYQQ 기간 한정
ORDER BY M.ACCOUNT
,M.PRODUCT
,M.APPLICATION
,D.YEAR || D.QUARTER
) P -- SUB QUERY 4
LEFT
JOIN (-- SUB QUERY 1 : RDC_RESULT
SELECT ACCOUNT
,PRODUCT
,APPLICATION
,EVAL_YEAR
,EVAL_QUARTER
,'RDC' AS DIVISION
,UNIT2
,VALUE
FROM JAYJAY.TQRDC_RESULT
UNPIVOT (VALUE FOR UNIT2 IN (RDC_RANK, R_RANK, D_RANK, C_RANK))
ORDER BY ACCOUNT
,PRODUCT
,APPLICATION
,EVAL_YEAR || EVAL_QUARTER
) R -- SUB QUERY 1
ON P.ACCOUNT = R.ACCOUNT
AND P.PRODUCT = R.PRODUCT
AND P.APPLICATION = R.APPLICATION
AND P.YEAR = R.EVAL_YEAR
AND P.QUARTER = R.EVAL_QUARTER
AND P.UNIT2 = R.UNIT2
UNION
-- Q_RESULT
SELECT P.ACCOUNT
,P.PRODUCT
,P.APPLICATION
,P.DIVISION
,P.UNIT2
,P.YEAR AS EVAL_YEAR
,P.QUARTER AS EVAL_QUARTER
,P.START_YYYYQQ
,P.END_YYYYQQ
,R.VALUE
,LAST_VALUE(R.VALUE IGNORE NULLS) OVER (PARTITION BY P.ACCOUNT, P.PRODUCT, P.APPLICATION
ORDER BY P.ACCOUNT, P.PRODUCT, P.APPLICATION, P.YEAR || P.QUARTER) AS FINAL_VALUE -- 최신값 불러오기 (Null값은 Partition 기준 최신 입력값으로 대체
,NVL2(R.VALUE, 'N', 'Y') AS IS_NULL -- 입력 유무
FROM ( -- SUB QUERY 4 : Q 모수
SELECT M.ACCOUNT
,M.PRODUCT
,M.APPLICATION
,M.DIVISION
,M.UNIT2
,D.YEAR
,D.QUARTER
,M.START_YYYYQQ
,M.END_YYYYQQ
FROM (
SELECT Y.YEAR
,Q.QUARTER
FROM (
SELECT TO_CHAR(LEVEL) AS YEAR FROM DUAL
WHERE TO_CHAR(LEVEL) > 2021
CONNECT BY LEVEL <= 2024
) Y
JOIN (
SELECT 'Q' || LEVEL AS QUARTER FROM DUAL
CONNECT BY LEVEL <= 4
) Q
ON 1=1
) D
JOIN
(-- SUB QUERY 3 : Q_MASTER
SELECT DISTINCT ACCOUNT
,PRODUCT
,APPLICATION
,START_YYYYQQ
,END_YYYYQQ
,DIVISION
,UNIT2
FROM ( -- SUB QUERY 2 : 결과 기준 START_YYYYQQ & END_YYYYQQ 정보 산
SELECT ACCOUNT
,PRODUCT
,APPLICATION
,EVAL_YEAR
,EVAL_QUARTER
,MIN(EVAL_YEAR || EVAL_QUARTER) OVER (PARTITION BY ACCOUNT, PRODUCT, APPLICATION) AS START_YYYYQQ
--,MAX(EVAL_YEAR || EVAL_QUARTER) OVER (PARTITION BY ACCOUNT, PRODUCT, APPLICATION) AS MAX_YYYYQQ
-- END YYYYQQ 로직 적용 (전제 : MAX YYYYQQ 값이 1년 전보다 더 오래되었으면, MAX가 END YYYYQQ, 아닌 경우 당분기에도 평가 대상
,CASE WHEN MAX(EVAL_YEAR || EVAL_QUARTER) OVER (PARTITION BY ACCOUNT, PRODUCT, APPLICATION) < '2023Q4' -- SYSTEM 적용 : TO_CHAR(ADD_MONTHS(SYSDATE, -12), 'YYYY"Q"Q')
THEN TO_CHAR(MAX(EVAL_YEAR || EVAL_QUARTER) OVER (PARTITION BY ACCOUNT, PRODUCT, APPLICATION))
ELSE '2024Q4'
END AS END_YYYYQQ
,DIVISION
,UNIT2
,VALUE
FROM (-- SUB QUERY 1 : Q_RESULT
SELECT ACCOUNT
,PRODUCT
,APPLICATION
,EVAL_YEAR
,EVAL_QUARTER
,'Q' AS DIVISION
,'Q_RANK' AS UNIT2
,Q_RANK AS VALUE
FROM JAYJAY.TQRDC_RESULT
WHERE Q_RANK IS NOT NULL
)-- SUB QUERY 1
) -- SUB QUERY 2
) M -- SUB QUERY 3
ON 1=1
WHERE D.YEAR || D.QUARTER BETWEEN M.START_YYYYQQ AND M.END_YYYYQQ -- 평가 항목 START YYYYQQ & END YYYYQQ 기간 한정
ORDER BY M.ACCOUNT
,M.PRODUCT
,M.APPLICATION
,D.YEAR || D.QUARTER
) P -- SUB QUERY 4
LEFT
JOIN (-- SUB QUERY 1 : Q_RESULT
SELECT ACCOUNT
,PRODUCT
,APPLICATION
,EVAL_YEAR
,EVAL_QUARTER
,'Q' AS DIVISION
,'Q_RANK' AS UNIT2
,Q_RANK AS VALUE
FROM JAYJAY.TQRDC_RESULT
WHERE Q_RANK IS NOT NULL
) R -- SUB QUERY 1
ON P.ACCOUNT = R.ACCOUNT
AND P.PRODUCT = R.PRODUCT
AND P.APPLICATION = R.APPLICATION
AND P.YEAR = R.EVAL_YEAR
AND P.QUARTER = R.EVAL_QUARTER
UNION
-- T_RESULT
SELECT P.ACCOUNT
,P.PRODUCT
,P.APPLICATION
,P.DIVISION
,P.UNIT2
,P.YEAR AS EVAL_YEAR
,P.QUARTER AS EVAL_QUARTER
,P.START_YYYYQQ
,P.END_YYYYQQ
,R.VALUE
,LAST_VALUE(R.VALUE IGNORE NULLS) OVER (PARTITION BY P.ACCOUNT, P.PRODUCT, P.APPLICATION
ORDER BY P.ACCOUNT, P.PRODUCT, P.APPLICATION, P.YEAR || P.QUARTER) AS FINAL_VALUE -- 최신값 불러오기 (Null값은 Partition 기준 최신 입력값으로 대체
,NVL2(R.VALUE, 'N', 'Y') AS IS_NULL -- 입력 유무
FROM ( -- SUB QUERY 4 : T 모수
SELECT M.ACCOUNT
,M.PRODUCT
,M.APPLICATION
,M.DIVISION
,M.UNIT2
,D.YEAR
,D.QUARTER
,M.START_YYYYQQ
,M.END_YYYYQQ
FROM (
SELECT Y.YEAR
,Q.QUARTER
FROM (
SELECT TO_CHAR(LEVEL) AS YEAR FROM DUAL
WHERE TO_CHAR(LEVEL) > 2021
CONNECT BY LEVEL <= 2024
) Y
JOIN (
SELECT 'Q' || LEVEL AS QUARTER FROM DUAL
CONNECT BY LEVEL <= 4
) Q
ON 1=1
) D
JOIN
(-- SUB QUERY 3 : T_MASTER
SELECT DISTINCT ACCOUNT
,PRODUCT
,APPLICATION
,START_YYYYQQ
,END_YYYYQQ
,DIVISION
,UNIT2
FROM ( -- SUB QUERY 2 : 결과 기준 START_YYYYQQ & END_YYYYQQ 정보 산
SELECT ACCOUNT
,PRODUCT
,APPLICATION
,EVAL_YEAR
,EVAL_QUARTER
,MIN(EVAL_YEAR || EVAL_QUARTER) OVER (PARTITION BY ACCOUNT, PRODUCT, APPLICATION) AS START_YYYYQQ
--,MAX(EVAL_YEAR || EVAL_QUARTER) OVER (PARTITION BY ACCOUNT, PRODUCT, APPLICATION) AS MAX_YYYYQQ
-- END YYYYQQ 로직 적용 (전제 : MAX YYYYQQ 값이 1년 전보다 더 오래되었으면, MAX가 END YYYYQQ, 아닌 경우 당분기에도 평가 대상
,CASE WHEN MAX(EVAL_YEAR || EVAL_QUARTER) OVER (PARTITION BY ACCOUNT, PRODUCT, APPLICATION) < '2023Q4' -- SYSTEM 적용 : TO_CHAR(ADD_MONTHS(SYSDATE, -12), 'YYYY"Q"Q')
THEN TO_CHAR(MAX(EVAL_YEAR || EVAL_QUARTER) OVER (PARTITION BY ACCOUNT, PRODUCT, APPLICATION))
ELSE '2024Q4'
END AS END_YYYYQQ
,DIVISION
,UNIT2
,VALUE
FROM (-- SUB QUERY 1 : T_RESULT
SELECT ACCOUNT
,PRODUCT
,APPLICATION
,EVAL_YEAR
,EVAL_QUARTER
,'T' AS DIVISION
,'T_RANK' AS UNIT2
,T_RANK AS VALUE
FROM JAYJAY.TQRDC_RESULT
WHERE T_RANK IS NOT NULL
)-- SUB QUERY 1
) -- SUB QUERY 2
) M -- SUB QUERY 3
ON 1=1
WHERE D.YEAR || D.QUARTER BETWEEN M.START_YYYYQQ AND M.END_YYYYQQ -- 평가 항목 START YYYYQQ & END YYYYQQ 기간 한정
ORDER BY M.ACCOUNT
,M.PRODUCT
,M.APPLICATION
,D.YEAR || D.QUARTER
) P -- SUB QUERY 4
LEFT
JOIN (-- SUB QUERY 1 : T_RESULT
SELECT ACCOUNT
,PRODUCT
,APPLICATION
,EVAL_YEAR
,EVAL_QUARTER
,'T' AS DIVISION
,'T_RANK' AS UNIT2
,T_RANK AS VALUE
FROM JAYJAY.TQRDC_RESULT
WHERE T_RANK IS NOT NULL
) R -- SUB QUERY 1
ON P.ACCOUNT = R.ACCOUNT
AND P.PRODUCT = R.PRODUCT
AND P.APPLICATION = R.APPLICATION
AND P.YEAR = R.EVAL_YEAR
AND P.QUARTER = R.EVAL_QUARTER
)
)