REGION |
COUNTRY |
PRODUCT |
BRAND1 |
GUBUN |
PER1 |
PER2 |
PER3 |
PER4 |
BRAND2 |
PER11 |
PER22 |
PER33 |
PER44 |
cnt |
CHINA |
CHINA |
REFRIGERATOR |
SS |
API |
144 |
141 |
141 |
144 |
|
|
|
|
|
4 |
CHINA |
CHINA |
REFRIGERATOR |
|
TPPI |
|
|
|
|
SS |
73 |
75 |
75 |
86 |
4 |
CHINA |
CHINA |
WASHING MACHINE |
LG |
API |
104 |
102 |
104 |
101 |
|
|
|
|
|
4 |
CHINA |
CHINA |
WASHING MACHINE |
SIEMENS |
API |
116 |
116 |
113 |
113 |
|
|
|
|
|
4 |
CHINA |
CHINA |
WASHING MACHINE |
|
TPPI |
|
|
|
|
SIEMENS |
81 |
96 |
91 |
95 |
4 |
CHINA |
CHINA |
WASHING MACHINE |
|
TPPI |
|
|
|
|
SS |
84 |
105 |
102 |
106 |
4 |
CIS |
KAZAKHSTAN |
CLEANING |
LG |
API |
95 |
90 |
95 |
93 |
|
|
|
|
|
4 |
CIS |
KAZAKHSTAN |
CLEANING |
SS |
API |
83 |
85 |
87 |
85 |
|
|
|
|
|
4 |
CIS |
KAZAKHSTAN |
CLEANING |
|
TPPI |
|
|
|
|
SS |
131 |
106 |
110 |
118 |
4 |
CIS |
KAZAKHSTAN |
REFRIGERATOR |
LG |
API |
113 |
114 |
115 |
114 |
|
|
|
|
|
4 |
CIS |
KAZAKHSTAN |
REFRIGERATOR |
SS |
API |
125 |
129 |
134 |
123 |
|
|
|
|
|
4 |
CIS |
KAZAKHSTAN |
REFRIGERATOR |
|
TPPI |
|
|
|
|
SS |
93 |
84 |
86 |
85 |
4 |
CIS |
RUSSIA |
CLEANING |
LG |
API |
90 |
81 |
81 |
74 |
|
|
|
|
|
6 |
CIS |
RUSSIA |
CLEANING |
SS |
API |
76 |
75 |
74 |
76 |
|
|
|
|
|
6 |
CIS |
RUSSIA |
CLEANING |
|
TPPI |
|
|
|
|
SS |
128 |
113 |
120 |
105 |
6 |
CIS |
RUSSIA |
MICROWAVE |
LG |
API |
106 |
107 |
106 |
107 |
|
|
|
|
|
6 |
CIS |
RUSSIA |
MICROWAVE |
SS |
API |
109 |
111 |
114 |
118 |
|
|
|
|
|
6 |
CIS |
RUSSIA |
MICROWAVE |
|
TPPI |
|
|
|
|
SS |
110 |
100 |
101 |
98 |
6 |
CIS |
RUSSIA |
REFRIGERATOR |
LG |
API |
107 |
113 |
107 |
108 |
|
|
|
|
|
6 |
CIS |
RUSSIA |
REFRIGERATOR |
SS |
API |
129 |
122 |
124 |
123 |
|
|
안녕하세요.
고수님들께 조언 좀 구하고자 글을 올리게 되었습니다.
쿼리는 아래와 같습니다.
SELECT
A.REGION,A.COUNTRY,A.PRODUCT,
A.BRAND1, A.GUBUN,
MAX(A.PER1) AS PER1,MAX(A.PER2) AS PER2,MAX(A.PER3) AS PER3,MAX(A.PER4) AS PER4,
A.BRAND2,
MAX(A.PER11) AS PER11,MAX(A.PER22) AS PER22,MAX(A.PER33) AS PER33,MAX(A.PER44) AS PER44, COUNT(*) CNT
FROM
(
SELECT
A.REGION,A.COUNTRY,A.PRODUCT,A.GUBUN,
CASE WHEN A.GUBUN = 'API' THEN A.BRAND END AS BRAND1,
CASE WHEN A.GUBUN = 'API' THEN A.PER1 END AS PER1,
CASE WHEN A.GUBUN = 'API' THEN A.PER2 END AS PER2,
CASE WHEN A.GUBUN = 'API' THEN A.PER3 END AS PER3,
CASE WHEN A.GUBUN = 'API' THEN A.PER4 END AS PER4,
CASE WHEN A.GUBUN = 'TPPI' THEN A.BRAND END AS BRAND2,
CASE WHEN A.GUBUN = 'TPPI' THEN A.PER1 END AS PER11,
CASE WHEN A.GUBUN = 'TPPI' THEN A.PER2 END AS PER22,
CASE WHEN A.GUBUN = 'TPPI' THEN A.PER3 END AS PER33,
CASE WHEN A.GUBUN = 'TPPI' THEN A.PER4 END AS PER44
FROM
(
SELECT REGION
,COUNTRY
,PRODUCT
,BRAND
,MAX(DECODE(YEAR_MON, '201110' ,PER)) AS per1
,MAX(DECODE(YEAR_MON, '201109' ,PER)) AS per2
,MAX(DECODE(YEAR_MON, '201108' ,PER)) AS per3
,MAX(DECODE(YEAR_MON, '201107' ,PER)) AS per4
,MAX(AT_DESC)
,GUBUN
FROM TB_IN_API_TPPI_M
WHERE 1=1
AND YEAR_MON BETWEEN '201107' AND '201110'
GROUP BY REGION, COUNTRY, PRODUCT, BRAND, GUBUN
ORDER BY REGION,COUNTRY,PRODUCT ASC
) A
) A GROUP BY A.REGION,A.COUNTRY,A.PRODUCT,A.BRAND1,A.BRAND2,GUBUN
ORDER BY A.REGION,A.COUNTRY,A.PRODUCT
현재 저는 빨강색 표시부분에서 brand1, brand2 명이 같은 행은 하나로 합치고 싶습니다.
또한, brand1,brand2 가 같다는 가정하에 country별 cnt는 테이블표 같이 나왔으면 하는데요...
아직 집합적 사고 방식이 부족하여 더이상 진행하기가 버거운 상황입니다.
고수님들의 조언 간절히 부탁드리겠습니다.
감사합니다.!!! |