1213 [Sakila] 통계 쿼리 (나라 별 최다 대여 카테고리)

나라별 가장 많이 대여된 카테고리

1) 나라 이름

     rental -> customer -> address -> city -> country

2) 카테고리 이름

     rental -> inventory -> film_category -> category

3) 나라, 카테고리로 group by -> count

 

4) max

  -> 카테고리 이름을 출력할 수 없음

SELECT country, MAX(cnt)
FROM 
(SELECT co.country, ca.name category, COUNT(*) cnt
FROM rental r INNER JOIN customer cu
ON r.customer_id = cu.customer_id
	INNER JOIN address ad
	ON cu.address_id = ad.address_id
		INNER JOIN city ct
		ON ad.city_id = ct.city_id
			INNER JOIN country co
			ON ct.country_id = co.country_id  -- 1) 나라 이름
				INNER JOIN inventory inv
				ON r.inventory_id = inv.inventory_id
					INNER JOIN film_category fc
					ON inv.film_id = fc.film_id
						INNER JOIN category ca
						ON fc.category_id = ca.category_id -- 2) 카테고리 이름
GROUP BY co.country, ca.name) t -- 3)
GROUP BY country;

count 구한 쿼리 + max 구한 쿼리 join -> (국가, max)값이 같은 행의 category 값 가져오기

 

 

 

*** WITH 절

WITH temp AS (SELECT co.country, ca.name category, COUNT(*) cnt
					FROM rental r INNER JOIN customer cu
					ON r.customer_id = cu.customer_id
						INNER JOIN address ad
						ON cu.address_id = ad.address_id
							INNER JOIN city ct
							ON ad.city_id = ct.city_id
								INNER JOIN country co
								ON ct.country_id = co.country_id  -- 1) 나라 이름
									INNER JOIN inventory inv
									ON r.inventory_id = inv.inventory_id
										INNER JOIN film_category fc
										ON inv.film_id = fc.film_id
											INNER JOIN category ca
											ON fc.category_id = ca.category_id -- 2) 카테고리 이름
					GROUP BY co.country, ca.name)
					
SELECT country, category, cnt
FROM temp
WHERE (country,cnt) in (SELECT country, MAX(cnt)
								FROM temp
								GROUP BY country);

아까의 결과물을 새로운 테이블로 만들기 - 가독성 높이기 위해

 

--> WITH 문을 안 쓴다면

SELECT t1.country, t1.category, t1.cnt
FROM (SELECT co.country, ca.name category, COUNT(*) cnt
		FROM rental r INNER JOIN customer cu
		ON r.customer_id = cu.customer_id
			INNER JOIN address ad
			ON cu.address_id = ad.address_id
				INNER JOIN city ct
				ON ad.city_id = ct.city_id
					INNER JOIN country co
					ON ct.country_id = co.country_id
						INNER JOIN inventory inv
						ON r.inventory_id = inv.inventory_id
							INNER JOIN film_category fc
							ON inv.film_id = fc.film_id
								INNER JOIN category ca
								ON fc.category_id = ca.category_id
		GROUP BY co.country, ca.name)t1
WHERE (t1.country,t1.cnt) in (SELECT t2.country, MAX(t2.cnt)
								FROM (SELECT co.country, ca.name category, COUNT(*) cnt
										FROM rental r INNER JOIN customer cu
										ON r.customer_id = cu.customer_id
											INNER JOIN address ad
											ON cu.address_id = ad.address_id
												INNER JOIN city ct
												ON ad.city_id = ct.city_id
													INNER JOIN country co
													ON ct.country_id = co.country_id
														INNER JOIN inventory inv
														ON r.inventory_id = inv.inventory_id
															INNER JOIN film_category fc
															ON inv.film_id = fc.film_id
																INNER JOIN category ca
																ON fc.category_id = ca.category_id
										GROUP BY co.country, ca.name)t2
													GROUP BY t2.country);

 

*** 집계함수 사용

SELECT  
   country, name categoryName, cnt
FROM 
	(SELECT 
	   co.country,
	   ca.name, 
	   COUNT(ca.name) cnt,
	    RANK() OVER(PARTITION BY co.country ORDER BY COUNT(ca.name) DESC) rn
	FROM country co INNER JOIN city ci
			ON co.country_id = ci.country_id 
		INNER JOIN address a 
			ON ci.city_id = a.city_id 
		INNER JOIN customer cu
			ON a.address_id = cu.address_id 
		INNER JOIN rental r
			ON cu.customer_id = r.customer_id 
		INNER JOIN inventory i
			ON r.inventory_id = i.inventory_id 
		INNER JOIN film f
			ON i.film_id = f.film_id 
		INNER JOIN film_category fc
			ON f.film_id = fc.film_id 
		INNER JOIN category ca
			ON fc.category_id = ca.category_id
	GROUP BY co.country, ca.name) category
WHERE rn = 1
ORDER BY country;