Oracle (오라클) 6.17 JOIN(UNION), DECODE

-- 1. 다음 중 사용자의 입력값을 받기 위해 사용하는 특수문자는?
-- *, $, &, @     답 : &

-- 2. 다음 중 YEARWEEK 오름차순, PRODUCT 내림차순으로 정렬하는 방법 중 옳은 것은?
-- ORDER BY YEARWEEK ASC, PRODUCT DESC

-- 3. 112 서버 내 "SORT_EXAMPLE"테이블을 WEEK 별로 내림차순정렬하는 쿼리문을 작성하세요.
SELECT * FROM SORT_EXAMPLE
WHERE 1=1
ORDER BY TO_NUMBER(WEEK) DESC;

-- 4. 번문제 (kopo_channel_result)
SELECT PRODUCTGROUP, SUM(QTY) AS SUM_QTY
FROM KOPO_CHANNEL_RESULT
WHERE 1=1
GROUP BY PRODUCTGROUP
ORDER BY DECODE (PRODUCTGROUP,'TV',1,'APS',2,'MOBILE',3,'REF',4,'WM',5)

-- CASE WHEN 사용
SELECT PRODUCTGROUP, SUM(QTY) AS SUM_QTY
FROM KOPO_CHANNEL_RESULT
WHERE 1=1
GROUP BY PRODUCTGROUP
ORDER BY CASE WHEN PRODUCTGROUP = 'TV' THEN 1
              WHEN PRODUCTGROUP = 'APS' THEN 2
              WHEN PRODUCTGROUP = 'MOBILE' THEN 3
              WHEN PRODUCTGROUP = 'TV' THEN 4
              WHEN PRODUCTGROUP = 'WM' THEN 5 END

SELECT PRODUCTGROUP, SUM(QTY) AS SUM_QTY
FROM KOPO_CHANNEL_RESULT
WHERE 1=1
GROUP BY PRODUCTGROUP
ORDER BY CASE WHEN PRODUCTGROUP = 'TV' THEN 1
              WHEN     PRODUCTGROUP = 'APS' THEN 2
              WHEN PRODUCTGROUP = 'MOBILE' THEN 3
              WHEN PRODUCTGROUP = 'TV' THEN 4
              ELSE 5 END

-- 5. [SQL함수] - 다음 중 대문자로 변경하기 위한 함수는?
-- 답 : UPPER

-- 6. [SQL함수] - 실제로 보이기엔 STR인데 길이함수를 활용하여 LENGTH('컬럼명') 10 이라는 숫자가 나왔다. 이때 공백을 제거하는 함수는?
-- LPAD, TRIM, SUBSTR      답 : TRIM




-- SUB Query (1. FROM, 2. WHERE, 3. SELECT, 4. WITH AS)
-- 1. FROM SUB Query
SELECT A.*
FROM (
    SELECT *
    FROM KOPO_CHANNEL_SEASONALITY_NEW
    WHERE 1=1
    AND REGIONID = 'A01'
    ) A  -- () 안의 조회한 내용을 'A'라는 별칭으로 두겠다.

-- 검증하기
SELECT DISTINCT REGIONID FROM KOPO_CHANNEL_SEASONALITY_NEW  -- 원본에는 REGIONID가 여러개 존재.

SELECT DISTINCT REGIONID    -- FROM SUB Query에 의해 A01만 조회된다.
FROM (
    SELECT *
    FROM KOPO_CHANNEL_SEASONALITY_NEW
    WHERE 1=1
    AND REGIONID = 'A01'
    ) A  -- () 안의 조회한 내용을 'A'라는 별칭으로 두겠다.

   
SELECT *
FROM KOPO_CHANNEL_SEASONALITY_NEW
WHERE 1=1
AND PRODUCT IN (SELECT DISTINCT PRODUCT FROM KOPO_CHANNEL_RESULT_NEW)

SELECT DISTINCT PRODUCT
FROM KOPO_CHANNEL_SEASONALITY_NEW
WHERE 1=1

SELECT DISTINCT PRODUCT
FROM KOPO_CHANNEL_RESULT_NEW
WHERE 1=1

SELECT A.*,
        (SELECT AVG(QTY)
        FROM KOPO_CHANNEL_SEASONALITY_NEW
        WHERE REGIONID = A.REGIONID
        AND PRODUCT = A.PRODUCT
        GROUP BY A.REGIONID, A.PRODUCT) AS SUM_QTY
FROM KOPO_CHANNEL_SEASONALITY_NEW A

SELECT *
FROM KOPO_CHANNEL_SEASONALITY_NEW
WHERE 1=1
AND PRODUCT = 'PRODUCT47'
AND YEARWEEK = 201402;

-- 여기에다가~~~ 할인가격 -> 제수가 0인 경우를 평균으로?
SELECT A.*, A.IR/A.MAP_PRICE
FROM KOPO_PROMOTION_HK A

-- 여기에다가~~~ MAP_PRICE가 0인 경우, PRODUCT의 평균 가격으로 대체한다.
-- 내꺼
SELECT A.*,
    CASE WHEN A.MAP_PRICE = 0 OR A.MAP_PRICE IS NULL THEN
        (
        SELECT AVG(MAP_PRICE)
        FROM KOPO_PROMOTION_HK
        WHERE REGIONID = A.REGIONID
        AND PRODUCT = A.PRODUCT
        GROUP BY REGIONID, PRODUCT)
        ELSE A.MAP_PRICE END AS MAP_PRICE
FROM KOPO_PROMOTION_HK A

-- ITEM이 다른게 있네...
SELECT A.*,
    CASE WHEN A.MAP_PRICE = 0 OR A.MAP_PRICE IS NULL THEN
        (
        SELECT AVG(MAP_PRICE)
        FROM KOPO_PROMOTION_HK
        WHERE REGIONID = A.REGIONID
        AND PRODUCT = A.PRODUCT
        AND ITEM = A.ITEM
        GROUP BY REGIONID, PRODUCT, ITEM)
        ELSE A.MAP_PRICE END AS MAP_PRICE
FROM KOPO_PROMOTION_HK A

--민석
SELECT A.*,
    CASE WHEN A.MAP_PRICE=0 OR A.MAP_PRICE IS NULL THEN
    (
    SELECT AVG(MAP_PRICE)
    FROM KOPO_PROMOTION_HK B
    WHERE B.PRODUCT=A.PRODUCT GROUP BY B.PRODUCT)
    ELSE A.MAP_PRICE
    END AS MAP_PRICE_NEW
FROM KOPO_PROMOTION_HK A
WHERE 1=1

-- 상민


SELECT
    A.REGIONID,
    A.ITEM,
    A.TARGETWEEK,
    A.PLANWEEK,
    NVL (A.MAP_PRICE,0) AS MAP_PRICE,
    A.IR,
    A.PRODUCT
FROM KOPO_PROMOTION_HK A

SELECT
    A.REGIONID,
    A.ITEM,
    A.TARGETWEEK,
    A.PLANWEEK,
    NVL2 (A.MAP_PRICE,A.MAP_PRICE,0) AS MAP_PRICE,
    A.IR,
    A.PRODUCT
FROM KOPO_PROMOTION_HK A

-- 0을 빼고 평균을 구할지, 0을 포함하고 평균을 구할지도 고민해야한다.

SELECT B.*,
    CASE WHEN B.MAP_PRICE = 0 THEN ( SELECT AVG(MAP_PRICE)
                                       FROM KOPO_PROMOTION_HK C
                                       WHERE 1=1
                                       AND C.REGIONID = B.REGIONID
                                       AND C.ITEM = B.ITEM
                                    )
        ELSE B.MAP_PRICE END AS MAP_PRICE_NEW
FROM (
        SELECT
            A.REGIONID,
            A.ITEM,
            A.TARGETWEEK,
            A.PLANWEEK,
            NVL (A.MAP_PRICE,0) AS MAP_PRICE,
            A.IR,
            A.PRODUCT
        FROM KOPO_PROMOTION_HK A
    ) B
WHERE 1=1

AND REGIONID = 'A01'
AND ITEM = 'ITEM00533'
GROUP BY REGIONID, ITEM


SELECT *
FROM (
        SELECT * FROM KOPO_CHANNEL_RESULT
        WHERE 1=1
        AND PRODUCTGROUP = 'TV'
    ) A
    
WITH A AS
    (
        SELECT * FROM KOPO_CHANNEL_RESULT
        WHERE 1=1
        AND PRODUCTGROUP = 'TV'
    )
SELECT * FROM A;


-- 데이터 조인
SELECT *    -- 124,658건
FROM KOPO_CHANNEL_SEASONALITY_NEW

SELECT *    -- 73건
FROM KOPO_REGION_MST

SELECT *    -- 124,658 X 73 = 9,100,034건
FROM KOPO_CHANNEL_SEASONALITY_NEW,
KOPO_REGION_MST

-- ANSI METHOD

SELECT A.*, B.REGIONNAME
FROM KOPO_CHANNEL_SEASONALITY_NEW A
INNER JOIN KOPO_REGION_MST B
ON A.REGIONID = B.REGIONID

SELECT A.*, B.*   -- 124,344건 (REGIONID 컬럼을 기준으로, A와 B의 교집합.)
FROM KOPO_CHANNEL_SEASONALITY_NEW A
INNER JOIN KOPO_REGION_MST B
ON A.REGIONID = B.REGIONID

SELECT A.*, B.*    -- 124,658건 (REGIONID 컬럼을 기준으로, A를 기준으로 B를 조인. B는 없고 A에만 있는 데이터 314건이 추가로 포함됨.)
FROM KOPO_CHANNEL_SEASONALITY_NEW A
LEFT JOIN KOPO_REGION_MST B
ON A.REGIONID = B.REGIONID

SELECT A.*, B.*    -- 124,348건 (REGIONID 컬럼을 기준으로, B를 기준으로 A를 조인. A는 없고 B에만 있는 데이터 4건이 추가로 포함됨.)
FROM KOPO_CHANNEL_SEASONALITY_NEW A
RIGHT JOIN KOPO_REGION_MST B
ON A.REGIONID = B.REGIONID

SELECT A.*, B.*    -- 124,662건 (REGIONID 컬럼을 기준으로, A와 B의 합칩합. 124,344 + 314 + 4 = 124,662)
FROM KOPO_CHANNEL_SEASONALITY_NEW A
FULL OUTER JOIN KOPO_REGION_MST B
ON A.REGIONID = B.REGIONID


-- 실습
SELECT * FROM KOPO_JOIN_TEST

SELECT * FROM KOPO_JOIN_TEST2

SELECT A.*, B.*
FROM KOPO_JOIN_TEST A
INNER JOIN KOPO_JOIN_TEST2 B
ON (A.REGIONID = B.REGIONID) AND (A.SALESID = B.SALESID)

SELECT A.*, B.*
FROM KOPO_JOIN_TEST A
LEFT JOIN KOPO_JOIN_TEST2 B
ON (A.REGIONID = B.REGIONID) AND (A.SALESID = B.SALESID)

SELECT A.*, B.*
FROM KOPO_JOIN_TEST A
RIGHT JOIN KOPO_JOIN_TEST2 B
ON (A.REGIONID = B.REGIONID) AND (A.SALESID = B.SALESID)

SELECT A.*, B.*
FROM KOPO_JOIN_TEST A
FULL OUTER JOIN KOPO_JOIN_TEST2 B
ON (A.REGIONID = B.REGIONID) AND (A.SALESID = B.SALESID)


-- PIVOT
SELECT * FROM DT_RESULT_FINAL2  -- RECORD COUNT : 44
--WHERE SALES = 2100
ORDER BY PRODUCTGROUP, PRODUCT, ITEM, YEARWEEK, "MEASURE"     -- MEASURE 가 예약어인데 컬럼명으로 들어가있어서 쌍따옴표를 해준다.


--CREATE TABLE DT_RESULT_FINAL3 AS      -- 피벗된 테이블을 'DT_RESULT_FINAL3'라는 테이블로 생성한다.
SELECT -- RECORD COUNT : 22 (PIVOT에 의해 동일한 PRODUCTGROUP, PRODUCT, ITEM, YEARWEEK, MEASURE을 가지면서 서로 다른 2개의 MEASURE(REAL_QTY, PREDICTION_QTY)를 각각 분리해 컬럼으로 만들어주어 절반으로 줄었다.
    *
     FROM (
        SELECT  *
        FROM DT_RESULT_FINAL2
)
PIVOT( 
    SUM(SALES)
    FOR MEASURE IN 
   ('REAL-QTY' AS REAL_QTY,'PREDICTION-QTY' AS PREDICTION_QTY)
)
ORDER BY PRODUCTGROUP, PRODUCT, ITEM, YEARWEEK

SELECT
    *
     FROM (
        SELECT  *
        FROM DT_RESULT_FINAL2
)
PIVOT( 
    AVG(SALES)      -- 피벗 후 row에 들어갈 'REAR_QTY'나 'PREDICTION_QTY'가 2개 이상일 수 있기 때문에 반드시 SUM이나 AVG를 해서 넣어줘야한다.
    FOR MEASURE IN 
   ('REAL-QTY' AS REAL_QTY,'PREDICTION-QTY' AS PREDICTION_QTY)
)
ORDER BY PRODUCTGROUP, PRODUCT, ITEM, YEARWEEK

-- UNPIVOT
WITH T AS( 
    SELECT * FROM DT_RESULT_FINAL3
)
SELECT * FROM T
UNPIVOT(QTY FOR MEASURE IN(REAL_QTY, PREDICTION_QTY))

-- UNPIVOT 검증하기
SELECT
    "MEASURE",
    PRODUCTGROUP,
    PRODUCT,
    ITEM,
    YEARWEEK,
    SALES
FROM DT_RESULT_FINAL2
WHERE 1=1
AND PRODUCTGROUP = 'PG02'
AND ITEM = 'ITEM0115'
AND YEARWEEK = 201632
ORDER BY PRODUCTGROUP, PRODUCT, ITEM, YEARWEEK, "MEASURE"

WITH T AS( 
    SELECT * FROM DT_RESULT_FINAL3
)
SELECT
    "MEASURE",
    PRODUCTGROUP,
    PRODUCT,
    ITEM,
    YEARWEEK,
    QTY
FROM T
UNPIVOT(QTY FOR MEASURE IN(REAL_QTY, PREDICTION_QTY))
WHERE 1=1
AND PRODUCTGROUP = 'PG02'
AND ITEM = 'ITEM0115'
AND YEARWEEK = 201632
ORDER BY PRODUCTGROUP, PRODUCT, ITEM, YEARWEEK, "MEASURE"


SELECT * FROM PRO_FCST_RESULT_1WEEK
WHERE 1=1
AND PRD_SEG1 = 'PRDA'
AND PRD_SEG2 = 'PRDA1'
AND PRD_SEG3 = '313'