|
SELECT *
FROM (SELECT A.LAUNCH_DATE, A.REGION, A.COUNTRY, A.PRODUCT, B.PRODUCT_TYPE, B.BRAND, B.MODEL,
A.PRODUCT_CNT, B.MODEL_CNT,
ROUND((B.MODEL_CNT/A.PRODUCT_CNT)*100, 2) AS DIS_SHARE,
ROUND(B.USD_PRICE, 2) AS USD_PRICE,
ROUND(B.EU_PRICE, 2) AS EU_PRICE,
ROUND(B.LOCAL_PRICE, 2) AS LOCAL_PRICE
FROM (
SELECT LAUNCH_DATE, REGION, COUNTRY, PRODUCT, COUNT(*) AS PRODUCT_CNT
FROM TABLE_SAMPLE
WHERE LAUNCH_DATE = '201209'
AND REGION = 'EUROPE'
AND COUNTRY = 'GERMANY'
AND PRODUCT = 'REFRIGERATOR'
GROUP BY LAUNCH_DATE, REGION, COUNTRY, PRODUCT) A,
(SELECT LAUNCH_DATE, REGION, COUNTRY, PRODUCT, MAX(PRODUCT_TYPE) PRODUCT_TYPE, BRAND, MODEL, COUNT(*) AS MODEL_CNT,
AVG(USD_TAG_PRICE) AS USD_PRICE,
AVG(EU_TAG_PRICE) AS EU_PRICE,
AVG(LOCAL_TAG_PRICE) AS LOCAL_PRICE
FROM TABLE_SAMPLE
WHERE LAUNCH_DATE = '201209'
AND REGION = 'EUROPE'
AND COUNTRY = 'GERMANY'
AND PRODUCT = 'REFRIGERATOR'
GROUP BY LAUNCH_DATE, REGION, COUNTRY, PRODUCT, BRAND, MODEL) B
WHERE A.LAUNCH_DATE = B.LAUNCH_DATE
AND A.REGION = B.REGION
AND A.COUNTRY = B.COUNTRY
AND A.PRODUCT = B.PRODUCT) X,
(SELECT A.LAUNCH_DATE, A.REGION, A.COUNTRY, A.PRODUCT, B.BRAND, B.MODEL,
A.BRAND_STORE_CNT, B.MODEL_STORE_CNT,
ROUND((B.MODEL_STORE_CNT/A.BRAND_STORE_CNT)*100, 2) AS DIST_RATE
FROM (SELECT LAUNCH_DATE, REGION, COUNTRY, PRODUCT,BRAND,
COUNT(STORE_ADDR) AS BRAND_STORE_CNT
FROM TABLE_SAMPLE
WHERE LAUNCH_DATE = '201209'
AND REGION = 'EUROPE'
AND COUNTRY = 'GERMANY'
AND PRODUCT = 'REFRIGERATOR'
GROUP BY LAUNCH_DATE, REGION, COUNTRY, PRODUCT,BRAND) A,
(SELECT LAUNCH_DATE, REGION, COUNTRY, PRODUCT, BRAND, MODEL,
COUNT(STORE_ADDR) AS MODEL_STORE_CNT
FROM TABLE_SAMPLE
WHERE LAUNCH_DATE = '201209'
AND REGION = 'EUROPE'
AND COUNTRY = 'GERMANY'
AND PRODUCT = 'REFRIGERATOR'
GROUP BY LAUNCH_DATE, REGION, COUNTRY, PRODUCT, BRAND, MODEL) B
WHERE A.LAUNCH_DATE = B.LAUNCH_DATE
AND A.REGION = B.REGION
AND A.COUNTRY = B.COUNTRY
AND A.PRODUCT = B.PRODUCT
AND A.BRAND = B.BRAND) Y
WHERE X.LAUNCH_DATE = Y.LAUNCH_DATE
AND X.REGION = Y.REGION
AND X.COUNTRY = Y.COUNTRY
AND X.PRODUCT = Y.PRODUCT
AND X.BRAND = Y.BRAND
AND X.MODEL = Y.MODEL
ORDER BY DECODE(X.BRAND, 'LG', '1', 'SAMSUNG', '2', X.BRAND)
다음과 같은 쿼리를 사용하고 있는데요, 음.. 파란색 부분은 결론적으로 중복되는 쿼리입니다. 한번만 사용해서 처리할 수도 있을거 같은데요..
제가 아직 초보라 좀 더 성능이나, 효율적인 부분에 대해 신경써서 만들 수 있는 수준이 아니라서요..
고수님들의 조언 좀 구하고자 합니다.
부탁드리겠습니다.
감사합니다. (- -) (_ _) 꾸벅 |
