고객 그룹의 선호 배우 파악

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

Irene Ortner

Data Scientist at Applied Statistics

하나의 쿼리로 SQL 구문 결합

LEFT JOIN
WHERE
GROUP BY
HAVING
ORDER BY

대여 기록에서 고객·배우 정보로

질문: 특정 고객 그룹의 선호 배우는 누구인가요?

테이블 renting을 다음과 조인:

customers
actsin
actors

SELECT *
FROM renting as r
LEFT JOIN customers AS c
ON r.customer_id = c.customer_id
LEFT JOIN actsin as ai
ON r.movie_id = ai.movie_id
LEFT JOIN actors as a
ON ai.actor_id = a.actor_id;

남성 고객

남성 고객이 본 영화에 가장 자주 출연한 배우.

SELECT a.name, 
       COUNT(*)
FROM renting as r
LEFT JOIN customers AS c
ON r.customer_id = c.customer_id
LEFT JOIN actsin as ai
ON r.movie_id = ai.movie_id
LEFT JOIN actors as a
ON ai.actor_id = a.actor_id

WHERE c.gender = 'male'
GROUP BY a.name;

선호 배우는 누구인가요?

가장 많이 시청된 배우.
시청 시 평균 평점이 가장 높은 배우.

SELECT a.name, 
       COUNT(*) AS number_views, 
       AVG(r.rating) AS avg_rating
FROM renting as r
LEFT JOIN customers AS c
ON r.customer_id = c.customer_id
LEFT JOIN actsin as ai
ON r.movie_id = ai.movie_id
LEFT JOIN actors as a
ON ai.actor_id = a.actor_id

WHERE c.gender = 'male'
GROUP BY a.name;

HAVING과 ORDER BY 추가

SELECT a.name, 
       COUNT(*) AS number_views, 
       AVG(r.rating) AS avg_rating
FROM renting as r
LEFT JOIN customers AS c
ON r.customer_id = c.customer_id
LEFT JOIN actsin as ai
ON r.movie_id = ai.movie_id
LEFT JOIN actors as a
ON ai.actor_id = a.actor_id

WHERE c.gender = 'male'
GROUP BY a.name
HAVING AVG(r.rating) IS NOT NULL
ORDER BY avg_rating DESC, number_views DESC;

HAVING과 ORDER BY 추가

| name               | number_views | avg_rating |
|--------------------|--------------|------------|
| Ray Romano         | 3            | 10.00      |
| Sean Bean          | 2            | 10.00      |
| Leonardo DiCaprio  | 3            | 9.33       |
| Christoph Waltz    | 3            | 9.33       |

Ayo berlatih!

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