작성일자 : 2024-09-18
Ver 0.1.1
0. 배경
TQRDC는 Technology, Quality, Responsiveness, Delivery, Cost의 약자로 고객관리 5대 요소이다.
월, 분기, 반기 등과 같이 일정한 주기에 따라 고객이 위 항목에 대해서 평가를 실시한다.
피평가대상인 공급처는 위 결과에 따라 어떤 부분에 더 신경을 쓰고 관리를 해야하는지 전략을 세워 효과적인 CRM을 할 수 있게 된다.
Salesforce에 적재되는 데이터 형태대로 Excel로 가데이터를 생성하여 TQRDC 관련 요건을 적용한 쿼리와 이를 시각화 한 대시보드를 만들어 보고자 한다.
개인 Oracle DB 구동 -> Table 생성 및 가데이터 Insert -> VIEW 개발-> Tableau 용 사용자 지정 쿼리 개발 -> 대시보드 개발 -> 인사이트 도출
[TQRDC] 2. 요건 및 VIEW 생성
작성일자 : 2024-09-04Ver 0.1.10. 배경TQRDC는 Technology, Quality, Responsiveness, Delivery, Cost의 약자로 고객관리 5대 요소이다.월, 분기, 반기 등과 같이 일정한 주기에 따라 고객이 위 항목에 대해서 평가를 실
junius96.tistory.com
1. TQRDC 용 사용자 지정 쿼리
-- Final SQL
-- TQRDC 구하기(사사오입)
SELECT 'ACTUAL' AS UNIT1
,ACCOUNT
,PRODUCT
,APPLICATION
,'TQRDC' AS DIVISION
,'TQRDC_RANK' AS UNIT2
,EVAL_YEAR
,EVAL_QUARTER
,START_YYYYQQ
,END_YYYYQQ
,FINAL_VALUE
,LAG(FINAL_VALUE) OVER (PARTITION BY ACCOUNT, PRODUCT, APPLICATION ORDER BY ACCOUNT, PRODUCT, APPLICATION, EVAL_YEAR || EVAL_QUARTER) AS BEFORE_VALUE
,FINAL_VALUE - LAG(FINAL_VALUE) OVER (PARTITION BY ACCOUNT, PRODUCT, APPLICATION
ORDER BY ACCOUNT, PRODUCT, APPLICATION, EVAL_YEAR || EVAL_QUARTER) AS VARIATION
FROM (
SELECT ACCOUNT
,PRODUCT
,APPLICATION
,EVAL_YEAR
,EVAL_QUARTER
,START_YYYYQQ
,END_YYYYQQ
,CASE
WHEN AVG(FINAL_VALUE) <= 1.4 THEN 1
WHEN AVG(FINAL_VALUE) > 1.4 AND AVG(FINAL_VALUE) <= 2.4 THEN 2
WHEN AVG(FINAL_VALUE) > 2.4 AND AVG(FINAL_VALUE) <= 3.4 THEN 3
WHEN AVG(FINAL_VALUE) > 3.4 AND AVG(FINAL_VALUE) <= 4.4 THEN 4
ELSE 5
END AS FINAL_VALUE
FROM JAYJAY.V_MTQRDC_RESULT
WHERE EVAL_YEAR || EVAL_QUARTER || ACCOUNT || PRODUCT || APPLICATION
IN (
SELECT EVAL_YEAR || EVAL_QUARTER || ACCOUNT || PRODUCT || APPLICATION
FROM JAYJAY.V_MTQRDC_RESULT
WHERE UNIT2 IN ('RDC_RANK', 'T_RANK', 'Q_RANK')
GROUP BY EVAL_YEAR || EVAL_QUARTER || ACCOUNT || PRODUCT || APPLICATION
HAVING COUNT(EVAL_YEAR || EVAL_QUARTER || ACCOUNT || PRODUCT || APPLICATION) >= 3
)
GROUP BY ACCOUNT
,PRODUCT
,APPLICATION
,EVAL_YEAR
,EVAL_QUARTER
,START_YYYYQQ
,END_YYYYQQ
)
UNION
-- ACTUAL
SELECT 'ACTUAL' AS UNIT1
,ACCOUNT
,PRODUCT
,APPLICATION
,DIVISION
,UNIT2
,EVAL_YEAR
,EVAL_QUARTER
,START_YYYYQQ
,END_YYYYQQ
,FINAL_VALUE
,LAG(FINAL_VALUE) OVER (PARTITION BY ACCOUNT, PRODUCT, APPLICATION ORDER BY ACCOUNT, PRODUCT, APPLICATION, EVAL_YEAR || EVAL_QUARTER) AS BEFORE_VALUE
,FINAL_VALUE - LAG(FINAL_VALUE) OVER (PARTITION BY ACCOUNT, PRODUCT, APPLICATION
ORDER BY ACCOUNT, PRODUCT, APPLICATION, EVAL_YEAR || EVAL_QUARTER) AS VARIATION
FROM JAYJAY.V_MTQRDC_RESULT
UNION
-- 표 레이아웃 유지용 TEMP Table
SELECT 'ACTUAL'
,NULL AS ACCOUNT
,A.PRODUCT
,NULL AS APPLICATION
,C.DIVISION
,C.UNIT2
,B.EVAL_YEAR
,B.EVAL_QUARTER
,NULL AS START_YYYYQQ
,NULL AS END_YYYYQQ
,NULL AS FINAL_VALUE
,NULL AS BEFORE_VALUE
,NULL AS VARIATION
FROM (
SELECT DISTINCT PRODUCT
FROM JAYJAY.V_MTQRDC_RESULT
) A
JOIN (
SELECT '2021' AS EVAL_YEAR
,LEVEL || 'Q' AS EVAL_QUARTER
FROM DUAL
CONNECT BY LEVEL <= 4
) B
ON 1=1
JOIN (
SELECT 'RDC' AS DIVISION
,'RDC_RANK' AS UNIT2
FROM DUAL
UNION
SELECT 'Technology' AS DIVISION
,'T_RANK' AS UNIT2
FROM DUAL
UNION
SELECT 'TQRDC' AS DIVISION
,'TQRDC' AS UNIT2
FROM DUAL
) C
ON 1=1
UNION
-- TARGET
SELECT 'TARGET' AS UNIT1
,NULL AS ACCOUNT
,NULL AS PRODUCT
,NULL AS APPLICATION
,DIVISION
,NULL AS UNIT2
,EVAL_YEAR
,EVAL_QUARTER
,NULL AS START_YYYYQQ
,NULL AS END_YYYYQQ
,FINAL_VALUE
,NULL AS BEFORE_VALUE
,NULL AS VARIATION
FROM TQRDC_TARGET
UNPIVOT (FINAL_VALUE FOR QUARTER IN (FIRST_QUARTER AS 'Q1'
,SECOND_QUARTER AS 'Q2'
,THIRD_QUARTER. AS 'Q3'
,FOURTH_QUARTER AS 'Q4'))2. Radar Chart 용 사용자 지정 쿼리
-- T Radar
WITH T_RESULT AS
(
SELECT PRODUCT
,EVAL_YEAR
,EVAL_QUARTER
,T_DIMENSION
,RANKING
FROM JAYJAY.TQRDC_RESULT
UNPIVOT (RANKING FOR T_DIMENSION IN (T_DIMENSION1 AS '1_DIMENSION1'
,T_DIMENSION2 AS '2_DIMENSION2'
,T_DIMENSION3 AS '3_DIMENSION3'
,T_DIMENSION4 AS '4_DIMENSION4'
,T_DIMENSION5 AS '5_DIMENSION5'))
)
SELECT PRODUCT
,EVAL_YEAR
,EVAL_QUARTER
,T_DIMENSION
,AVG(RANKING) AS RAKING
FROM T_RESULT
GROUP BY PRODUCT
,EVAL_YEAR
,EVAL_QUARTER
,T_DIMENSION
UNION
-- Radar Chart Path용 1번과 동일한 값 만들기
SELECT PRODUCT
,EVAL_YEAR
,EVAL_QUARTER
,'6_DIEMSION6' AS T_DIMENSION
,AVG(RANKING) AS RANKING
FROM T_RESULT
WHERE T_DIMENSION = '1_DIMENSION1'
GROUP BY PRODUCT
,EVAL_YEAR
,EVAL_QUARTER
,'6_DIEMSION6'
UNION
-- Total 집계
SELECT 'TOTAL' AS PRODUCT
,EVAL_YEAR
,EVAL_QUARTER
,T_DIMENSION
,AVG(RANKING) AS RAKING
FROM T_RESULT
GROUP BY 'TOTAL'
,EVAL_YEAR
,EVAL_QUARTER
,T_DIMENSION
UNION
-- Radar Chart Path용 1번과 동일한 값 만들기
SELECT 'TOTAL' AS PRODUCT
,EVAL_YEAR
,EVAL_QUARTER
,'6_DIEMSION6' AS T_DIMENSION
,AVG(RANKING) AS RANKING
FROM T_RESULT
WHERE T_DIMENSION = '1_DIMENSION1'
GROUP BY 'TOTAL'
,EVAL_YEAR
,EVAL_QUARTER
,'6_DIEMSION6'
UNION
-- Radar Chart Background을 위한 TEMP Data
SELECT A.PRODUCT
,B.YEAR AS EVAL_YEAR
,B.QUARTER AS EVAL_QUARTER
,C.T_DIMENSION
,C.RANKING
FROM (SELECT DISTINCT PRODUCT
FROM T_RESULT
UNION
SELECT 'TOTAL' AS PRODUCT
FROM DUAL) A
JOIN (SELECT '1998' AS YEAR
,LEVEL || 'Q' AS QUARTER
,LEVEL AS "LEVEL"
FROM DUAL
CONNECT BY LEVEL <= 4
UNION
SELECT '1999' AS YEAR
,LEVEL || 'Q' AS QUARTER
,LEVEL + 4 AS "LEVEL"
FROM DUAL
CONNECT BY LEVEL <= 3) B -- 배경 7줄 만들 에정
ON 1=1
JOIN (SELECT '1_DIEMSION1' AS T_DIMENSION
,LEVEL AS RANKING
FROM DUAL
CONNECT BY LEVEL <= 7
UNION
SELECT '2_DIEMSION2' AS T_DIMENSION
,LEVEL AS RANKING
FROM DUAL
CONNECT BY LEVEL <= 7
UNION
SELECT '3_DIEMSION3' AS T_DIMENSION
,LEVEL AS RANKING
FROM DUAL
CONNECT BY LEVEL <= 7
UNION
SELECT '4_DIEMSION4' AS T_DIMENSION
,LEVEL AS RANKING
FROM DUAL
CONNECT BY LEVEL <= 7
UNION
SELECT '5_DIEMSION5' AS T_DIMENSION
,LEVEL AS RANKING
FROM DUAL
CONNECT BY LEVEL <= 7
UNION
SELECT '6_DIEMSION6' AS T_DIMENSION
,LEVEL AS RANKING
FROM DUAL
CONNECT BY LEVEL <= 7) C
ON 1=1
WHERE B."LEVEL" = C.RANKING
ORDER BY PRODUCT
,EVAL_YEAR DESC
,EVAL_QUARTER
,T_DIMENSION