최종 예시

SQL로 하는 데이터 기반 의사결정

Tim Verdonck

Professor Statistics and Data Science

비즈니스 과제

MovieNow는 신작 투자 여부를 검토합니다.
최근 제작 영화는 구작보다 제공 비용이 더 듭니다.
데이터 분석 1단계:
- 최근 제작 영화가 구작보다 평점이 높은가?
- 국가별 차이가 있는가?

1. 데이터 조인

필요한 정보:

평점이 포함된 영화 대여 renting 기록
고객의 국가 정보 customers

영화의 개봉 연도 movies

SELECT *
FROM renting AS r
LEFT JOIN customers AS c
ON c.customer_id = r.customer_id
LEFT JOIN movies AS m
ON m.movie_id = r.movie_id;

2. 관련 기록 선택

평점이 최소 4개인 영화만 사용
2018-04-01 이후 대여 기록만 사용

SELECT *
FROM renting AS r
LEFT JOIN customers AS c
ON c.customer_id = r.customer_id
LEFT JOIN movies AS m
ON m.movie_id = r.movie_id
WHERE r.movie_id IN (
    SELECT movie_id
    FROM renting
    GROUP BY movie_id
    HAVING COUNT(rating) >= 4)
AND r.date_renting >= '2018-04-01';

3. 집계

집계 유형:

대여 횟수 집계
서로 다른 영화 수 집계
평균 평점 계산

집계 수준:

전체 집계
개봉 연도별 영화
개봉 연도 × 고객 국가별 영화

3. 집계

SELECT c.country,
       m.year_of_release,
       COUNT(*) AS n_rentals,
       COUNT(DISTINCT r.movie_id) AS n_movies,
       AVG(rating) AS avg_rating
FROM renting AS r
LEFT JOIN customers AS c
ON c.customer_id = r.customer_id
LEFT JOIN movies AS m
ON m.movie_id = r.movie_id
WHERE r.movie_id IN (
    SELECT movie_id
    FROM renting
    GROUP BY movie_id
    HAVING COUNT(rating) >= 4)
AND r.date_renting >= '2018-04-01'
GROUP BY ROLLUP (m.year_of_release, c.country)
ORDER BY c.country, m.year_of_release;

결과 테이블

| year_of_release | country | n_rentals | n_movies | avg_rating         |
|-----------------|---------|-----------|----------|--------------------|
| 2009            | null    | 10        | 1        | 8.7500000000000000 | 
| 2010            | null    | 41        | 5        | 7.9629629629629630 | 
| 2011            | null    | 14        | 2        | 8.2222222222222222 | 
| 2012            | null    | 28        | 5        | 8.1111111111111111 | 
| 2013            | null    | 10        | 2        | 7.6000000000000000 | 
| 2014            | null    | 5         | 1        | 8.0000000000000000 | 
| null            | null    | 333       | 50       | 7.9024390243902439 |

연습해 봅시다!

SQL로 하는 데이터 기반 의사결정