PostgreSQL 실습문제2

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

 

SELECT * FROM RENTAL;

실습1

SELECT * FROM CUSTOMER;

실습2

 

 

- MAX , WHERE 

SELECT
	ROW_NUMBER() OVER (
	ORDER BY COUNT(A.RENTAL_ID) DESC) AS RENTAL_RANK ,
	MAX(B.FIRST_NAME) AS FIRST_NAME,
	MAX(B.LAST_NAME) AS LAST_NAME,
	A.CUSTOMER_ID,
	COUNT(*) RENTAL_COUNT
FROM
	RENTAL A,
	CUSTOMER B
WHERE
	A.CUSTOMER_ID = B.CUSTOMER_ID
GROUP BY
	A.CUSTOMER_ID
ORDER BY
	RENTAL_RANK
LIMIT 1;

- MAX,INNER JOIN

SELECT
	ROW_NUMBER() OVER (
ORDER BY
	COUNT(A.RENTAL_ID) DESC) AS RENTAL_RANK ,
	MAX(B.FIRST_NAME) AS FIRST_NAME,
	MAX(B.LAST_NAME) AS LAST_NAME,
	A.CUSTOMER_ID,
	COUNT(*) RENTAL_COUNT
FROM
	RENTAL A
INNER JOIN CUSTOMER B ON
	(A.CUSTOMER_ID = B.CUSTOMER_ID)
GROUP BY
	A.CUSTOMER_ID
ORDER BY
	RENTAL_RANK
LIMIT 1;

- INNER JOIN, 여러개의 GROUP BY

SELECT
	ROW_NUMBER() OVER (
ORDER BY
	COUNT(A.RENTAL_ID) DESC) AS RENTAL_RANK ,
	B.FIRST_NAME,
	B.LAST_NAME,
	A.CUSTOMER_ID,
	COUNT(*) RENTAL_COUNT
FROM
	RENTAL A
INNER JOIN CUSTOMER B ON
	(A.CUSTOMER_ID = B.CUSTOMER_ID)
GROUP BY
	A.CUSTOMER_ID , B.FIRST_NAME , B.LAST_NAME
ORDER BY
	RENTAL_RANK
LIMIT 1;

- 서브쿼리

SELECT
	A.RENTAL_RANK,
	B.FIRST_NAME,
	B.LAST_NAME,
	B.CUSTOMER_ID,
	A.RENTAL_COUNT
FROM
	(
	SELECT
		ROW_NUMBER() OVER (
	ORDER BY
		COUNT(A.RENTAL_ID) DESC) AS RENTAL_RANK ,
		A.CUSTOMER_ID,
		COUNT(*) RENTAL_COUNT
	FROM
		RENTAL A
	GROUP BY
		A.CUSTOMER_ID
	ORDER BY
		RENTAL_RANK
	LIMIT 1 ) A,
	CUSTOMER B
WHERE
	A.CUSTOMER_ID = B.CUSTOMER_ID;

 

4가지 방법 모두 같은 결과의 값을 조회합니다.

 

결과