【실습】 데이터 조작 및 테이블 관리


[1] DVD렌탈 시스템의 관리자가 고객별 매출 순위를 알고 싶다. 신규 테이블을 생성해서 고객의 매출 순위를 관리하고 싶으며 신규 테이블의 이름은 CUSTOMER_RANK이고 테이블 구성은 CUSTOMER_ID, CUSTOMER_RANK로 정했다. CTAS 기법을 이용하여 신규 테이블을 생성하면서 데이터를 입력해라.

image-20201222171831673

>> 문제 풀이

1
SELECT * FROM PAYMENT LIMIT 10;
image-20201222175413890

  1. 우선 고객별 총 매출액을 계산한다

    1
    2
    3
    4
    5
      SELECT CUSTOMER_ID,
             SUM(AMOUNT) AS AMOUNT_SUM
        FROM PAYMENT
    GROUP BY CUSTOMER_ID
    ORDER BY AMOUNT_SUM DESC;
    image-20201222172206108

  2. 그 다음 고객 총매출 순위를 매긴다 (내림차순)

    1
    2
    3
    4
    5
    6
    7
    8
    9
    SELECT A.CUSTOMER_ID,
           RANK() OVER(ORDER BY A.AMOUNT_SUM DESC) AS CUSTOMER_RANK
      FROM (
              SELECT CUSTOMER_ID,
              		 SUM(AMOUNT) AS AMOUNT_SUM
        	    FROM PAYMENT
            GROUP BY CUSTOMER_ID
            ORDER BY AMOUNT_SUM DESC
           ) A;
    image-20201222172543446

  1. 마지막으로 CTAS 문을 이용하여 CUSTOMER_RANK 테이블 생성하고 데이터를 입력한다.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    CREATE TABLE CUSTOMER_RANK (CUSTOMER_ID, CUSTOMER_RANK) AS
    SELECT A.CUSTOMER_ID,
           RANK() OVER(ORDER BY A.AMOUNT_SUM DESC) AS CUSTOMER_RANK
      FROM (
              SELECT CUSTOMER_ID,
              		 SUM(AMOUNT) AS AMOUNT_SUM
        	    FROM PAYMENT
            GROUP BY CUSTOMER_ID
            ORDER BY AMOUNT_SUM DESC
           ) A;
    1
    SELECT * FROM CUSTOMER_RANK;
    image-20201222172543446



[2] DVD렌탈 시스템의 관리자는 매달 마다 매출 순위 1위를 한 고객에게 특별한 선물을 주고자 한다. 이러한 업무를 달성하기 위해서 CUSTOMER_RANK_YYYYMM이라는 테이블을 CTAS 기법으로 생성하는 SQL문을 작성하라. (단 선물 제공 기준을 정하기 위해 고객별 총 매출액도 저장하라)

image-20201222185331599

>> 문제 풀이

1
SELECT * FROM PAYMENT LIMIT 10;
image-20201222175413890

  1. 먼저 년월별 고객별 총 매출액을 계산한다

    1
    2
    3
    4
    5
    6
    7
      SELECT CUSTOMER_ID,
             TO_CHAR(PAYMENT_DATE, 'YYYYMM') AS YYYYMM,
             SUM(AMOUNT) AS AMOUNT_SUM
        FROM PAYMENT
    GROUP BY TO_CHAR(PAYMENT_DATE, 'YYYYMM'),
             CUSTOMER_ID
    ORDER BY YYYYMM, AMOUNT_SUM DESC;
    image-20201222184237522

  1. 총 매출액 기준으로 년월별 고객 순위를 매긴다

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    SELECT A.CUSTOMER_ID,
           A.YYYYMM,
           A.AMOUNT_SUM,
           RANK() OVER(PARTITION BY A.YYYYMM ORDER BY AMOUNT_SUM DESC) AS RANK_YYYYMM
      FROM (
    		  SELECT CUSTOMER_ID,
    		         TO_CHAR(PAYMENT_DATE, 'YYYYMM') AS YYYYMM,
    		         SUM(AMOUNT) AS AMOUNT_SUM
    		    FROM PAYMENT
    		GROUP BY TO_CHAR(PAYMENT_DATE, 'YYYYMM'),
    		         CUSTOMER_ID
    		ORDER BY YYYYMM, AMOUNT_SUM DESC 
           ) A;
    image-20201222184359999

  1. 마지막으로 CTAS 문을 이용하여 CUSTOMER_RANK_YYYYMM테이블 생성하고 데이터를 입력한다.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    CREATE TABLE CUSTOMER_RANK_YYYYMM AS      
    SELECT A.CUSTOMER_ID,
           A.YYYYMM,
           A.AMOUNT_SUM,
           RANK() OVER(PARTITION BY A.YYYYMM ORDER BY AMOUNT_SUM DESC) AS RANK_YYYYMM
      FROM (
    		  SELECT CUSTOMER_ID,
    		         TO_CHAR(PAYMENT_DATE, 'YYYYMM') AS YYYYMM,
    		         SUM(AMOUNT) AS AMOUNT_SUM
    		    FROM PAYMENT
    		GROUP BY TO_CHAR(PAYMENT_DATE, 'YYYYMM'),
    		         CUSTOMER_ID
    		ORDER BY YYYYMM, AMOUNT_SUM DESC 
           ) A;
    1
    SELECT * FORM CUSTOMER_RANK_YYYYMM;
    image-20201222184557948