postgresql - dvdrental 실습(5)

반응형

참고: 패스트 캠퍼스~~~~ 패스트 캠퍼스~~~~

 

 

 

--dvd렌탈 시스템의 관리자는 매달 마다 매출 순위 1위를 한 고객에게 특별한 선물을 주고자 한다. 이러한 업무를 달성 하기 위해서
--CUSTOMER_RANK_YYYYMM이라는 테이블을 CTAS 기법으로 생성하는 SQL 문을 작성하라. 🙌🙌🙌🙌

(단, 선물 제공 기준을 정하기 위해 SUM_AMOUNT도 저장하라)

 

 

문제 푸는 순서.

 

1️⃣ 우선 payment 테이블은 customer_id, yyyymm 기준으로 group by gksgn amount 의 합계를 구한다.

2️⃣ 1번에 추출한 집합을 인라인뷰로 감싸서 yyyymm을 기준으로 amount 합계 별 순위를 구한다.

 

-> 인라인 뷰 사용할 때는 안에 넣은 집합을 먼저 돌려보면 쉽게 작업 할 수 있음!!

 

3️⃣ CTAS 문을 이용하여 최종적으로 테이블 생성 및 데이터를 입력한다.

 

 

 

---

1️⃣

SELECT

          c.customer_id

          ,sum(p.amount) AS sum_amount
  FROM customer c JOIN payment p
    ON c.customer_id=p.customer_id 
    GROUP BY c.customer_id;

 

 

 SELECT 

          a.customer_id

          ,sum(a.amount) AS sum_amount
 FROM 

           payment a
 GROUP BY 

           a.customer_id 

 

 

 

 

 

 SELECT 
              a.customer_id
              ,to_char(payment_date,'yyyymm') AS yyyymm , sum(a.amount)
              ,SUM(a.amount) AS sum_amount
 FROM 
              payment a
 GROUP BY
              a.customer_id
              ,to_char(payment_date,'yyyymm')

 

 

 

 

2️⃣

SELECT a.customer_id , a.yyyymm, a.sum_amount
           ,ROW_NUMBER() over(PARTITION BY a.yyyymm ORDER BY a.sum_amount desc) AS rank_yyyymm
   FROM 
           (
             select
                     a.customer_id
                     ,to_char(payment_date,'yyyymm') AS yyyymm , sum(a.amount)
                     ,SUM(a.amount) AS sum_amount
             FROM 
                      payment a
             GROUP BY
                       a.customer_id
                       ,to_char(payment_date,'yyyymm')
              ) a
ORDER BY yyyymm, rank_yyyymm;

 

 

 

3️⃣

테이블 생성해주기.

CREATE TABLE customer_rank_yyyymm AS
SELECT a.customer_id, a.yyyymm, a.sum_amount,
row_number() OVER(PARTITION BY a.yyyymm ORDER BY a.sum_amount desc) AS rank_yyyymm
 FROM 
 (
 select
  a.customer_id
  ,to_char(payment_date,'yyyymm') AS yyyymm , sum(a.amount)
  ,SUM(a.amount) AS sum_amount
 FROM 
         payment a
 GROUP BY
  a.customer_id
  ,to_char(payment_date,'yyyymm')
) a
ORDER BY yyyymm, rank_yyyymm;

 

 

 

 

SELECT * FROM customer_rank_yyyymm
WHERE rank_yyyymm IN (1,2,3);

 

돌리면, 

 

굉장히 중요한 부분이므로 다시 복습할 필요 ✔✔

 

 

저작자표시비영리변경금지

'DB > postgres' 카테고리의 다른 글

postgresql - dvdrental 실습(7) : Cartesian Product  (0)	2021.03.24
postgresql - dvdrental 실습(6)  (0)	2021.03.11
postgresql - dvdrental 실습(5)  (0)	2021.03.10
postgresql - dvdrental 실습(4)  (0)	2021.03.09
postgresql - dvdrental 실습(3)  (0)	2021.02.25
postgresql - dvdrental 실습(2)  (0)	2021.02.24