[강의명]
패스트캠퍼스 SQL 100제 + 자격증 완성 온라인 완주반
[수강목록]
생각대로 SQL - 8
[강의내용]
생각대로 SQL - 8
문제2번) 매출을 가장 많이 올린 dvd 고객 이름은? (analystic funtion 활용)
payment 테이블에서 customer_id 기준으로 amount 합계를 계산하고, 그중 1위인 고객의 이름을 구하면 된다.
[내 풀이]
우선 customer별 amount 합계를 구한다.
SELECT customer_id, sum(amount)
FROM payment p
GROUP BY customer_id
고객의 이름을 구해야하므로 customer 테이블과 JOIN한다.
또한 순위를 구하기위해 RANK함수를 사용한다.
SELECT c.customer_id, c.first_name, c.last_name, rank() over(ORDER BY sum desc)
FROM customer c , (
SELECT customer_id, sum(amount)
FROM payment p
GROUP BY customer_id
) AS t1
WHERE c.customer_id = t1.customer_id
LIMIT 1;
[모범답안]
모범답안에서는 서브쿼리에서 매출합계와 순위를 구하고, 메인쿼리에서 customer테이블 JOIN 후 고객 이름을 구한다.
RANK 대신 ROW_NUMBER를 사용했고 1위 행을 구하기 위해 WHERE절을 사용했다 (나는 LIMIT 1 활용)
전체적인 결과는 동일하다.
SELECT first_name, last_name
FROM customer c
JOIN (SELECT customer_id, sum(amount) AS sum_amount,
ROW_NUMBER() OVER(ORDER BY sum(amount) DESC) AS rnum
FROM payment
GROUP BY customer_id) AS t1
ON c.customer_id = t1.customer_id
WHERE rnum = 1;
문제5번) 월별 매출액을 구하고 이전 월보다 매출액이 줄어든 월을 구하세요. (일자는 payment_date 기준)
월별 매출액을 구하기 위해 payment_date 컬럼에서 DATE 정보를 추출해야 한다. EXTRACT 함수를 사용해본다.
EXTRACT 함수를 사용하면 DATE 타입 컬럼에서 원하는 값을 추출할 수 있다.
EXTRACT(추출형식 FROM 날짜)추출형식 자리에는 YEAR , MONTH , DAY , HOUR , MINUTE , SECOND가 들어갈 수 있다.
날짜 자리에는 DATE형식의 컬럼 또는 현재시간(SYSDATE)도 들어갈 수 있다.
SELECT EXTRACT(YEAR FROM date(payment_date)) AS yr,
EXTRACT(MONTH FROM date(payment_date)) AS mon,
sum(amount),
COALESCE(lag(sum(amount), 1) over(ORDER BY EXTRACT(MONTH FROM date(payment_date))), 0) AS pre_mon_amount,
sum(amount) - COALESCE(lag(sum(amount), 1) over(ORDER BY EXTRACT(MONTH FROM date(payment_date))), 0) AS gap
FROM payment
GROUP BY EXTRACT(YEAR FROM date(payment_date)),
EXTRACT(MONTH FROM date(payment_date))
COALESCE 함수는 조건이 NULL일때 특정값을 반환하는 함수이다. 아래와 같은 형식으로 사용한다.
COALESCE(조건, 특정값)조건이 NULL이면 특정값을 반환한다.
이전월보다 매출액이 적은 월, 즉 현재월 - 이전월 < 0 인 값을 찾으면 된다.
WHERE절에서 필터링해주기 위해 서브쿼리에 넣어준다.
SELECT *
FROM (
SELECT EXTRACT(YEAR FROM date(payment_date)) AS yr,
EXTRACT(MONTH FROM date(payment_date)) AS mon,
sum(amount),
COALESCE(lag(sum(amount), 1) over(ORDER BY EXTRACT(MONTH FROM date(payment_date))), 0) AS pre_mon_amount,
sum(amount) - COALESCE(lag(sum(amount), 1) over(ORDER BY EXTRACT(MONTH FROM date(payment_date))), 0) AS gap
FROM payment
GROUP BY EXTRACT(YEAR FROM date(payment_date)),
EXTRACT(MONTH FROM date(payment_date))
) AS t1
WHERE t1.gap < 0;
문제7번) 대여점별 매출 순위를 구하세요.
store_id 별 amount의 합계를 구하고, 1위를 찾으면 된다.
3가지 순위함수를 다 사용해본다.
SELECT s.store_id, sum(p.amount) AS sum_amount,
ROW_NUMBER () OVER (ORDER BY sum(p.amount) DESC) AS rnum,
RANK () OVER (ORDER BY sum(p.amount) DESC) AS rnk,
DENSE_RANK () OVER (ORDER BY sum(p.amount) DESC) AS dense_rnk
FROM payment p
JOIN rental r ON p.rental_id = r.rental_id
JOIN inventory i ON r.inventory_id = i.inventory_id
JOIN store s ON i.store_id = s.store_id
GROUP BY s.store_id;
문제9번) 영화 카테고리 (Category) 별로 대여가 가장 많이 된 영화 TOP 5를 구하세요
SELECT t1.*
FROM (SELECT c.name, f.title, count(r.rental_id),
ROW_NUMBER () OVER (PARTITION BY c.name ORDER BY count(r.rental_id) DESC, title) AS rnum
FROM category c
JOIN film_category fc ON c.category_id = fc.category_id
JOIN inventory i ON fc.film_id = i.film_id
JOIN rental r ON i.inventory_id = r.inventory_id
JOIN film f ON fc.film_id = f.film_id
GROUP BY c.name, f.title) AS t1
WHERE t1.rnum <= 5;
TOP5를 구하기 위해서는 WHERE절이나 HAVING절을 사용하여 필터링을 걸어주면 된다.
하지만 WHERE절과 HAVING절은 SQL 문법상 SELECT절보다 먼저 사용되기 때문에
ROW_NUMBER로 구한 순위 컬럼에 영향을 미칠 수 없다.
그래서 서브쿼리를 사용해서 WHERE절을 사용해야 한다.
문제10번) 매출이 가장 많은 영화 카테고리와 매출이 가장 작은 영화 카테고리를 구하세요. (first_value, last_value)
SELECT DISTINCT FIRST_VALUE (t1.name) OVER (ORDER BY t1.amount) AS top_cat,
LAST_VALUE (t1.name)
OVER (ORDER BY t1.amount RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS bottom_cat
FROM (
SELECT c.name, sum(p.amount) AS amount
FROM category c
JOIN film_category fc ON c.category_id = fc.category_id
JOIN inventory i ON fc.film_id = i.film_id
JOIN rental r ON i.inventory_id = r.inventory_id
JOIN payment p ON r.rental_id = p.rental_id
GROUP BY c.name
) AS t1;
LAST_VALUE 함수를 사용할 때는
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 구문을 적어주어야
범위가 정상적으로 설정됨에 주의한다.
위의 구문을 명시해주지 않으면 각각의 행에서 현재행까지로 범위가 제한되게 된다.
