[SQL예제][PostgreSQL] 4. 조인과 집계데이터(INNER JOIN, ON, ROW_NUMBER(), OVER(), COUNT())

RENTAL과 CUSTOMER 테이블을 이용하여 현재까지 가장 많이 RENTAL을 한 고객의 고객ID, 렌탈순위, 누적렌탈횟수, 이름을 출력하라

SELECT * FROM CUSTOMER

 

SELECT * FROM RENTAL

---

# 풀이 순서

1. 테이블 CUSTOMER 와 RENTAL 을 INNER JOIN 하여 고객의 ID, 성, 이름 과 찍지어준다.
2. COUNT() 를 사용하여 고객의 렌탈 횟수를 조회한다.
3. ROW_NUMBER() 와 OVER() 를 사용하여 순위를 매겨준다.

---

# 문제 풀이

 

1. 두 테이블을 조인하여 교집합 부분만 조회한다.

  - 고객 아이디, 성, 이름, RENTAL_ID 만 조회

SELECT	
	 C.CUSTOMER_ID 
	, C.FIRST_NAME
	, C.LAST_NAME 
	, R.RENTAL_ID
FROM RENTAL R 
	INNER JOIN CUSTOMER C 
	ON R.CUSTOMER_ID = C.CUSTOMER_ID
ORDER BY C.CUSTOMER_ID

아래로 더 많다.

JOIN : 다수의 테이블을 합쳐(?)준다.

 

2. GROUP BY 를 사용하여 한 명의 고객을 하나의 그룹으로 묶어주면서 + RENTAL_ID 에 COUNT() 를 사용하여 총 렌탈 건수를 넣어준다.

  - ORDER BY 로 CUSTOMER_ID 를 기준으로 정렬해주면 당장은 예쁘겠지만,

  - ROW_NUMBER() 를 COUNT(RENTAL_ID) 기준으로 정렬할 거라 불필요함

SELECT	C.CUSTOMER_ID 
	, C.FIRST_NAME
	, C.LAST_NAME 
	, COUNT(R.RENTAL_ID) AS RENTAL_COUNT
FROM RENTAL R 
	INNER JOIN CUSTOMER C 
	ON R.CUSTOMER_ID = C.CUSTOMER_ID
GROUP BY C.CUSTOMER_ID , C.FIRST_NAME , C.LAST_NAME

COUNT() : 집계함수

 

3. ROW_NUMBER() 와 OVER() 를 사용하여 조건을 정해 순위 매기기

  - ROW_NUMBER() 는 적용된 각 ROW 에 순서대로 번호를 매기는 함수

  + ROW_NUMBER() 는 중복을 허용하지 않기 때문에 무조건 1,2,3, ...100 

  - OVER() 는 집계함수에 조건을 걸어줄 때? 사용한다 (불필요한 서브쿼리, GROUP BY 를 안 써도 된다.)

 

  + 2번에서 조회한 데이터를 COUNT(R.RENTAL_ID) 기준으로 정렬해준다.

     1) COUNT(R.RENTAL_ID) 를 계산하고

     2) ORDER BY  DESC 를 통해 정렬한다.

     3) 정렬한 데이터에 ROW_NUMBER() 가 번호를 부여한다.

ROW_NUMBER(), RANK, DENSE_RANK : 중복과 공동 등수를 어떻게 처리하는가에 따라 다르다.
OVER() : 집계함수, 분석함수에 조건 걸기 (PARTION BY 컬럼 / ORDER BY 컬럼 / 세부분할기준)

SELECT    ROW_NUMBER() OVER(ORDER BY COUNT(R.RENTAL_ID) DESC) AS RENTAL_RANK
	, R.CUSTOMER_ID
	, C.FIRST_NAME
	, C.LAST_NAME _NAME
	, COUNT(R.RENTAL_ID) AS RENTAL_COUNT
FROM RENTAL R 
	INNER JOIN CUSTOMER C
	ON R.CUSTOMER_ID = C.CUSTOMER_ID 
GROUP BY R.CUSTOMER_ID, C.FIRST_NAME , C.LAST_NAME

  -