[SQL] Netflix 데이터 분석하기

# 데이터 dump--> 복원을 하려고 함!!!
# ==> GUI 환경 상에서 진행을 하려고 함!!!!

use netflix;
show tables;
select * from credits;
select * from titles;

# 참고) PK 명시적으로 하지 않고, 그냥 데이터만 밀어 넣은 상태!!!!
# 사전 체크
select count(1) from credits; -- 77801 데이터!!!
select count(1) from titles; -- 5806 데이터!!!

# 1) IMDB 평점 기준 상위 10개 "영화"는 무엇
select * from titles;
# --> 기준 : imdb평점 기준,,,상위--> 정렬--> 10개만 limit
select * from titles
order by imdb_score desc;
#
select * from titles
order by imdb_score desc
limit 10;
# ++ 대상을 "영화"로 한정 : filtering --> where 정리!!!!
select distinct(type) from titles;
# ==> 필터링의 조건 : MOVIE
select * from titles
where type="MOVIE"
order by imdb_score desc
limit 10;

#2) IMDb 평점 기준 하위 10개 "TV쇼"는 무엇
select * from titles;
#
select * from titles where type="SHOW"
order by imdb_score asc
limit 10;

# 3-1) TV 쇼와 영화의 평균 IMDb 평점과 
#    TMDB 평점은 얼마였나요?(단, 평균 소수점 2자리까지만)
select * from titles;
#
select type, imdb_score, tmdb_score from titles;
#
select type, avg(imdb_score), avg(tmdb_score) 
from titles;
# --> 보려는 3개의 항목이 다 따로  따로~~~~
# --> 질문의 의도 : TYPE별로 각기 평점들이 어찌되냐~~~~==> group by
select type, avg(imdb_score), avg(tmdb_score) 
from titles
group by type;
#
select type, avg(imdb_score) `avg_IMDB`, avg(tmdb_score) `avg_TMDB`
from titles
group by type;
# --> 너무 계산 결과가 길어요;;;; ==> 유효숫자 처리..: round( 뭐를, 몇 자리) 
#     구글링 + gpt etc 
select type, round(avg(imdb_score),2 )`avg_IMDB`, round(avg(tmdb_score),2) `avg_TMDB`
from titles
group by type;

# 3-2) 위에와 유사한 스타일의 문제 
# 영화와 TV 쇼의 평균 상영 시간은 각각 얼마였나요?
select * from titles;
#
select type, runtime from titles;
#
select type, avg(runtime) from titles
group by type;
#
select type, round(avg(runtime),3 ) from titles
group by type;
#
select type, round(avg(runtime),3 ) `avg_rt`
from titles
group by type;

# 4) 각 decade(10년대)별 영화와 TV 쇼의 수는 얼마인가요?
# ==> case by case --> case when!!!!! 
#     + 자주 사용되는 방식 중 하나 : 나누기 이용!!
select 11/10;
select floor(11/10)*10;
select floor(12/10)*10;
select floor(10/10)*10;
#
select * from titles;
#
select release_year from titles;
#
select release_year, floor(release_year/10)*10 from titles;
#
select release_year, floor(release_year/10)*10 `decade`
from titles;
#
select  floor(release_year/10)*10 `decade`,count(1)
from titles
group by `decade`;
# ==> 장르별로 쪼개서 보면 됨!!!!
select type, floor(release_year/10)*10 `decade`, count(1)
from titles
group by type, `decade`;
#
select type, floor(release_year/10)*10 `decade`, count(1) `Counting`
from titles
group by type, `decade`
order by `decade` desc;

# ---> 이거 case when하려면,,1940,50,60,70,80,90,00,10,20 
#     9가지 경우 써야 해서;;;;;하셔도 되기는 합니다!!!!
select min(release_year), max(release_year) from titles;

# 5) 각 제작 국가별(production_countries)평균 IMDb 평점과 
#    TMDB 평점은 얼마였나요?"
# --> 1개 국가로 하지 마시고,,,위에 지정된 컬럼의 유니크한 값 기준.....
select * from titles;
#
select production_countries, imdb_score, tmdb_score 
from titles;
#
select production_countries, 
       round(avg(imdb_score),2) `imdb_avg`, 
       round(avg(tmdb_score),2 ) `tmdb_avg`
from titles
group by production_countries;
#
select production_countries, 
		   round(avg(imdb_score),2) `imdb_avg`, 
       round(avg(tmdb_score),2 ) `tmdb_avg`
from titles
group by production_countries
order by 2 desc;
#
select production_countries, 
		   round(avg(imdb_score),2) `imdb_avg`, 
       round(avg(tmdb_score),2 ) `tmdb_avg`
from titles
group by production_countries
order by 3 desc;
        

# 6) TV 쇼 + 영화의 각 연령 등급별(age_certification) 
#    평균 IMDb 평점과 TMDB 평점은 얼마였나요?
#    평균 IMDb 평점을 기준 내림차순으로 정렬해서
select * from titles;
#
select age_certification, avg(imdb_score), avg(tmdb_score )
from titles
group by age_certification;
#
select age_certification, avg(imdb_score), avg(tmdb_score )
from titles
group by age_certification
order by 2 desc;
# ==> group by의 기준이 되는 필드가 꼭 Not Null아닐 수 있다!!!!
#     null도 들어가는 필드를 기준으로 group by를 하면,,,이 친구들도 1개의 종류!!
# ==> 빈문자열 //null 체크!!! 구별해서 모두 체크!!!!! ** python에서도 동일함!
#     why? 숫자가 달라지기 떄문에;;;;;;

# 7) 영화에서 가장 많은 연령 등급 5개 찾아보세요
#    ( 등급이 없는 데이터는 제외하고)
# --> 조건 : type =="movie", 등급은 많은거 3개,,, + 등급없거나 없는것은 제외
select * from titles;
#
desc titles; -- age 컬럼이 문자열 타입!!!
#
select * from titles where age_certification is null;
# ===> null은 아니고,,,""빈 문자열!!!!!!
#
select age_certification from titles;
#
select age_certification from titles
where type="MOVIE" and age_certification != "";
#
select age_certification, count(1) `CNT` 
from titles
where type="MOVIE" and age_certification != ""
group by age_certification;
#
select age_certification, count(1) `CNT` 
from titles
where type="MOVIE" and age_certification != ""
group by age_certification
order by 2 desc;
#
select age_certification, count(1) `CNT`
from titles
where type="MOVIE" and age_certification != ""
group by age_certification
order by 2 desc
limit 3;
# *** 중요 *** 빈 문자열 vs null 정확하게 체크해서 필터링!!!!

# 8) 영화/TV 쇼 통합해서 가장 많이 출연한 배우
#    Top10을 찾아보세요.
# ==> 다작인 "배우"를 찾아주세요!!!
select * from credits;
# --> role 에 어떤 값들이 있는지 직접 체크!!!
select distinct(role) from credits;
# --> 대상 범위 : ACTOR로 한정!!!
select name, role from credits;
#
select name, role from credits where role="ACTOR";
# --> 간단하게 동명이인 생략하고,,이름으로 하겠습니다.. FM ==> person_id
select name, count(1) 
from credits 
where role="ACTOR"
group by name;
#
select name, count(1) `CNT`
from credits 
where role="ACTOR"
group by name
order by 2 desc;
#
select name, count(1) `CNT`
from credits 
where role="ACTOR"
group by name
order by 2 desc
limit 10;
# --> PK : person_id
select person_id, name, count(1) `CNT
from credits 
where role="ACTOR"
group by person_id
order by 3 desc
limit 10;

    
# 9) 2010년 이후에 출시된 영화들의 제목과 감독은 누구였나요?
# ==> release_year 역순으로 정렬해서 보여주세요.
# 하다보면,,, 제목은 같은데, 감독이 여러명인 경우가 있을 수 있는데
# 제목별 모든 감독을 표시도 해보면 몇 건이 되고(3235)
# 제목별 1명의 감독만 표시하면 몇 건이 되는지(2907)
# 각기 비교해보세요!
select * from titles; -- 기본 영화 정보,,
#
select distinct(role) from credits; -- 감독..
#
select * from credits where role="DIRECTOR";
# --> 처리하고 보여줄 사항 : 양쪽에 다 존재 join
select T.title, C.role, T.release_year 
from titles T
inner join credits C on T.id = c.id;
#
select T.title, C.role, T.release_year 
from titles T
inner join credits C on T.id = c.id
where T.type="MOVIE" and T.release_year>=2010 and C.role="DIRECTOR";
#
select T.title, C.role, T.release_year 
from titles T 
inner join credits C on T.id = c.id
where T.type="MOVIE" and T.release_year>=2010 and C.role="DIRECTOR"
order by T.release_year desc; -- 3235
# ==> 1개 영화에 감독1명 ., 2명,,,3명,,,,,,,,,, 다 포함!!!

# ++ 본인의 의도가 : 1개 영화에 1명 감독만 보자!!!!!!
select T.title, C.role, T.release_year 
from titles T 
inner join credits C on T.id = c.id
where T.type="MOVIE" and T.release_year>=2010 and C.role="DIRECTOR"
group by T.title
order by T.release_year desc; -- 2907 숫자가 달라질 수 있다!!!
# **** join하는 과정에서 1:1, 1:n ==> 숫자를 정확하게 체크!!!!
#      inner join : + / - => 운이 좋아서 0 ==> 1:1??? 보장 못함!!
#      left/right join : 기준 수 이상~~~~~
# *** join에서 숫자로 볼 때 ....관계성 염두를 꼭 하세요!!!!

# 10) IMDb 평점이 7.5 이상이고 
#     TMDB 인기 점수가 80 이상인 
#     영화들의 
#     제목과 감독은 누구였나요?
# --> 조건을 여러개!!!평점 + 인기점수 + 영화+ 감독/제목
# ==> 필요한 정보들을 보면,,양쪽 테이블 정보가 모두 필요함!!
select * from titles T 
inner join credits C on T.id = C.id;
#
select T.title, C.name from titles T 
inner join credits C on T.id = C.id;
#
select T.title, C.name from titles T 
inner join credits C on T.id = C.id;
# --> 가로 필터링에 대한 조건이 레코드에 있는 값 그대로 사용!!! where
select T.title, C.name 
from titles T inner join credits C on T.id = C.id
where T.imdb_score>=7.5 and T.tmdb_popularity>=80
      and T.type="MOVIE" and C.role="DIRECTOR";
# --->  5개정도의 레코드만 나올 듯 합니다!!!
# ---> 필터링의 조건이 여러개면,,,각기 소속만 명확하게 하면 된다!!!

# 11) IMDB 평점이 가장 높은 영화의 제목과, 평점을 출력
#     단, 혹시 최고점이 여러개가 있다면 다 보여주세요.
# --> 유사한 쿼리 : 키가 제일 큰 사람의 정보 보여주세요~~~
select * from titles;
#
select max(imdb_score) from titles; -- 9.6
#
select max(imdb_score) from titles where type="MOVIE"; -- 9.0
#
select max(imdb_score) from titles where type="SHOW"; -- 9.6
# --> 이 조건을 필터링의 조건!!!!!!
#     필터링의 조건의 값이 여러개가 나올 떄 : any, all
#     + 필터링 조건의 서브쿼리에 있어서는 정확하게 해야 함!!
select title, imdb_score from titles
	where imdb_score = (
		select max(imdb_score) from titles where type="MOVIE"
    );

# 12) 2015년에 출시된 영화 중에서
#     IMDb 평점이 가장 높은 영화의 제목과
#     감독의 이름은 무엇
# --> 11번의 확장 : 11번은 1개 테이블에서 진행,,, 12번은 join을 하고 진행!!!
#     why: 영화 정보 + 인물 정보....
select * from titles;
#
select T.title, T.imdb_score, C.name 
from titles T inner join credits C on T.id=C.id;
#
select T.title, T.imdb_score, C.name 
from titles T inner join credits C on T.id=C.id
where T.imdb_score =(
			                select max(imdb_score) from titles
				              where type="MOVIE" and release_year=2015
			               ) and 
      C.role="DIRECTOR";

# 13) 각 영화별로 감독이 2명 이상인 영화의 제목과 감독의 이름을 한 줄에 출력
#     ==> 참고 : 한 줄 에 출력  group_concat(C.name)
#    : 감독들의 이름을 한 줄에 출력해주세요!!!! + 제목,감독의 수도 같이 보여주세요!!
# --> 참고) 제목이 같아도 별개id라면,,,다른 영화로 취급해서,,,id기준으로만 보자!!
select T.title, C.name 
from titles T inner join credits C on T.id=C.id;
# --> 뭐가 필터링을 해야하는데,,,필터링 기준에 대한 값이 직접적인 것이 아님!!
#     가공을 해서 만들엇,,이것으로 필터링 having!!!
select T.title, C.name 
from titles T inner join credits C on T.id=C.id
where C.role="DIRECTOR";
#
select T.title, count(C.name) 
from titles T inner join credits C on T.id=C.id
where C.role="DIRECTOR"
group by T.id;
#
select T.title, count(C.name) `num_dir`
from titles T inner join credits C on T.id=C.id
where C.role="DIRECTOR"
group by T.id
having `num_dir`>=2;
#
select T.title, count(C.name) `num_dir`, group_concat(C.name) `DirsName`
from titles T inner join credits C on T.id=C.id
where C.role="DIRECTOR"
group by T.id
having `num_dir`>=2;