데이터베이스 어려운 문제.. 집계 함수에 대해 이해하기.. - OT

1. 각 연도 별 월 별 최고 인기 판매 상품(판매량이 많다)을 구하시오.

-- 내가 푼 것
SELECT E.YM, P.PRODUCT_NAME, E.QUANTITY
FROM (SELECT TO_CHAR(O.ORDER_DATE, 'YYYY-MM') AS YM,
                OI.PRODUCT_ID, 
                SUM(OI.QUANTITY) AS QUANTITY,
                RANK() OVER(PARTITION BY TO_CHAR(O.ORDER_DATE, 'YYYY-MM')
                            ORDER BY SUM(OI.QUANTITY) DESC) AS RNK
        FROM ORDERS O INNER JOIN ORDER_ITEMS OI
                              ON O.ORDER_ID = OI.ORDER_ID
        WHERE O.STATUS != 'Canceled'
        GROUP BY TO_CHAR(O.ORDER_DATE, 'YYYY-MM'), OI.PRODUCT_ID) E
            INNER JOIN PRODUCTS P
                    ON E.PRODUCT_ID = P.PRODUCT_ID
WHERE E.RNK = 1
;

-- 강사님 코드
-- DESCRIPTION은 그냥 강사님이 같이 뽑아보고 싶어서 하신 거래
SELECT O.YM, O.PRODUCT_NAME, 
        SUBSTR(O.DESCRIPTION, 1, 10) || '...' AS DESCRIPT, 
        O.QUANTITY
FROM (SELECT TO_CHAR(O.ORDER_DATE, 'YYYYMM')AS YM, 
      P.PRODUCT_NAME, P.DESCRIPTION,
      SUM(OI.QUANTITY) AS QUANTITY,
      RANK() OVER(PARTITION BY TO_CHAR(O.ORDER_DATE, 'YYYYMM')
                  ORDER BY SUM(OI.QUANTITY) DESC) AS RNK
      FROM ORDERS O INNER JOIN ORDER_ITEMS OI
                            ON O.ORDER_ID = OI.ORDER_ID
                    INNER JOIN PRODUCTS P
                            ON OI.PRODUCT_ID = P.PRODUCT_ID
      WHERE O.STATUS != 'Canceled'
      GROUP BY TO_CHAR(O.ORDER_DATE, 'YYYYMM'), P.PRODUCT_NAME, P.DESCRIPTION)O
WHERE O.RNK = 1
ORDER BY O.YM ASC
;

 

 

 

2. 고객사 별 구매 선호 제품(주문량X 주문건수O) 카테고리 1, 2위를 구하시오.

중요한 건 주문건수!

ORDER 하나 당 하나씩 카운트 해야 하는 것.

-- 1차.. Aflac 회사로 제한해서 작게 해봄..
SELECT E.NAME, E.CATEGORY_NAME, E.CNT, E.RNK
FROM (SELECT E.NAME, E.CATEGORY_NAME,
                COUNT(*) AS CNT,
                RANK() OVER(ORDER BY COUNT(*) DESC) AS RNK
        FROM (SELECT DISTINCT C.NAME, O.ORDER_ID, P.CATEGORY_ID, PC.CATEGORY_NAME
                FROM ORDERS O INNER JOIN ORDER_ITEMS OI
                                      ON O.ORDER_ID = OI.ORDER_ID
                                      AND O.STATUS != 'Canceled'
                                      AND O.CUSTOMER_ID = '16'
                              INNER JOIN PRODUCTS P
                                      ON OI.PRODUCT_ID = P.PRODUCT_ID
                              INNER JOIN PRODUCT_CATEGORIES PC
                                      ON P.CATEGORY_ID = PC.CATEGORY_ID
                              INNER JOIN CUSTOMERS C
                                      ON O.CUSTOMER_ID = C.CUSTOMER_ID) E
        GROUP BY E.NAME, E.CATEGORY_NAME) E
WHERE E.RNK IN (1, 2)
;

처음에 강사님꺼 출력 결과 보고 회사 하나(CUSTOMER_ID = 16) 찍어서 차근차근 뽑아서 접근해봤는데

위와 같이 하니 잘 나왔음!

근데 여기서 진행을 못하는 거다....

INNER JOIN에서 저 고객사ID 16 조건 하나만 지우면 되는 줄 알았는데 지우면 데이터가 이상해짐 ㅠㅠ

그래서 엄청 많이 고치고 시도하고 계속 고민했는데 안 돼서 정말 힘들었는데..결국 풀어냄!!

-> 저 조건(고객사)을 없애야 하는데 지우니 이상해진다면 그 조건 기준(고객사)을 추가해주면 되는 거 아닐까 해서 랭크의 PARTITION BY 로 CUSTOMER 기준(CUSTOMER의 이름)을 넣었음.

그랬더니 성공~~!!!!

-- 내가 푼 것
SELECT E.NAME, E.CATEGORY_NAME, E.CNT, E.RNK
FROM (SELECT E.NAME, E.CATEGORY_NAME,
                COUNT(*) AS CNT,
                RANK() OVER(PARTITION BY E.NAME
                            ORDER BY COUNT(*) DESC) AS RNK
        FROM (SELECT DISTINCT C.NAME, O.ORDER_ID, P.CATEGORY_ID, PC.CATEGORY_NAME
                FROM ORDERS O INNER JOIN ORDER_ITEMS OI
                                      ON O.ORDER_ID = OI.ORDER_ID
                                      AND O.STATUS != 'Canceled'
                              INNER JOIN PRODUCTS P
                                      ON OI.PRODUCT_ID = P.PRODUCT_ID
                              INNER JOIN PRODUCT_CATEGORIES PC
                                      ON P.CATEGORY_ID = PC.CATEGORY_ID
                              INNER JOIN CUSTOMERS C
                                      ON O.CUSTOMER_ID = C.CUSTOMER_ID) E
        GROUP BY E.NAME, E.CATEGORY_NAME) E
WHERE E.RNK IN (1, 2)
;

-- 강사님 쿼리
SELECT C.NAME, PC.CATEGORY_NAME, O.CNT, O.RNK
FROM (SELECT O.CUSTOMER_ID, O.CATEGORY_ID, COUNT(*) AS CNT,
             RANK() OVER(PARTITION BY O.CUSTOMER_ID
                         ORDER BY COUNT(*) DESC) AS RNK
      FROM (SELECT DISTINCT O.ORDER_ID, O.CUSTOMER_ID, P.CATEGORY_ID
            FROM ORDERS O INNER JOIN ORDER_ITEMS OI
                                  ON O.ORDER_ID = OI.ORDER_ID
                          INNER JOIN PRODUCTS P
                                  ON OI.PRODUCT_ID = P.PRODUCT_ID
            WHERE O.STATUS != 'Canceled')O
      GROUP BY O.CUSTOMER_ID, O.CATEGORY_ID)O
INNER JOIN CUSTOMERS C
        ON O.CUSTOMER_ID = C.CUSTOMER_ID
INNER JOIN PRODUCT_CATEGORIES PC
        ON O.CATEGORY_ID = PC.CATEGORY_ID
WHERE O.RNK IN (1,2)
ORDER BY C.NAME ASC, O.RNK ASC
;