Day 3 복습
- 중복 제거할 때는 어떻게 할까?
- 데이터 그룹화는 언제 쓸까?
- 그룹화한 뒤 총계출산을 하고 싶다면?
- 총계출산한 자료명을 지정하려면 ?
- 그룹화한 뒤 조건에 맞게 조회하고 싶다면 ?
- 집계함수 쓸 때 주의할 점은 무엇일까?
- 집계함수와 다른 컬럼을 연결하여 나타내고 싶을 떈 어찌할까?
1. 오늘 배운 내용
- union, 스칼라 서브쿼리,파생테이블,where절에서 서브쿼리활용, 더불어 Any, All, in, exists까지 배웠다. 파생테이블을 깔끔히 정리할 수 있는 CTE를 배웠다. 그리고 부가적으로 csv를 MySQL로 가져와 활용할 수 있는 법을 배웠다.
2. 이해하기 어려웠던 부분
1)UNION 절
- 데이터를 합하여 조회하고 싶을 때 쓴다
- 단 조회할 컬럼명의 개수는 맞춰야한다.
- UNION 뒤에 DISTINCT나 ALL 써 중복 제거처리를 할 수 있다.
- 아무 것도 쓰지 않으면 DISTINCT이다.(기본)
select 컬럼1, 컬럼2
from 테이블1
union -- DISTINCT(default) or ALL
select 컬럼A, 컬럼B
from 테이블2
(order by 1
limit 10); #order by, limit를 함께 쓰려면 괄호를 써야한다.3. 기억해야할 부분
1) 구별하여,
A) 종류별 서브쿼리
a) 스칼라 서브쿼리
- SELECT 절에 사용
b) 파생 테이블
- FROM 뒤 테이블을 임시로 생성
c) where절
- WHERE 절 뒤 조건으로 사용
-- a) 스칼라 서브쿼리
-- 연도별 1위 관객수 평균보다 관객수가 높았던 연도의 영화와 매출액
select year(release_date),movie_name,audience_num,
(select round(avg(audience_num)) as avg_audi_num from box_office where ranks =1) '연도별 1위 관객수 평균'
from box_office where ranks =1 order by 1;
-- case문 활용하여 관객수 평균보다 낮은지 높은지 판단
select year(release_date),movie_name,sale_amt,
case when audience_num > (select round(avg(audience_num)) as avg_audi_num from box_office where ranks =1) then audience_num else '평균 관객수보다 적음' end '평균 관객수 비교'
from box_office where ranks =1 order by 1;
-- b) 파생테이블
-- 연도별 1위 관객수 평균보다 관객수가 높았던 연도의 영화와 관객수만 나타내기
select year(release_date),movie_name,audience_num
from box_office a,
(select round(avg(audience_num)) as avg_audi_num from box_office where ranks =1) b
where ranks =1 and a.audience_num >= b.avg_audi_num order by 1;
-- c) where 절
select year(release_date),movie_name,audience_num
from box_office
where ranks =1 and
audience_num >= (select round(avg(audience_num)) as avg_audi_num from box_office where ranks =1)
order by 1;
c_2) 파생 테이블과 CTE
- CTE를 쓰면 좋은 점
- 파생테이블보다 코드가 깔끔해진다.
- CTE 안에 파생테이블을 하나 더 만들 수 있다.
#15년 동안 랭킹 1위 영화의 관객수 평균과 매출액 평균보다 많은 영화(17년~19년 개봉 작품 중)
with total as (
select round(avg(audience_num)) as avg_audi_num,
round(avg(sale_amt)) as avg_amt
from box_office where ranks =1)
select year(a.release_date), a.movie_name, a.audience_num, a.sale_amt
from box_office a,
total b
where year(a.release_date) between 2017 and 2019
and a.audience_num >= b.avg_audi_num
and a.sale_amt > b.avg_amt
order by 1;c_2) ANY(SOME), ALL, IN, EXISTS 활용
ANY: 뒤에 위치한 서브쿼리가 반환하는 값 중 어느 한 조건을 만족해도 인정
ALL: 뒤에 위치한 서브쿼리가 반환하는 값 모든 조건을 만족해야 인정
-- Any
#2019년 영화 중 2018년 3위 영화 관객수보다 높은 영화 목록
select movie_name, audience_num
from box_office
where year(release_date) = 2019
and audience_num >= any(select audience_num
from box_office
where year(release_date) = 2018
and ranks between 1 and 3);
#2018 TOP3 관객수 922만명 ~ 1227만명(서브쿼리 반환값)
#극한직업, 어벤져스: 엔드게임, 겨울왕국 2 ,알라딘, 기생충,엑시트
#2019년 영화 중 2018년 1위 영화 관객수보다 높은 영화 목록
select movie_name, audience_num
from box_office
where year(release_date) = 2019
and audience_num >= all(select audience_num
from box_office
where year(release_date) = 2018
and ranks between 1 and 3);
#극한직업, 어벤져스: 엔드게임,겨울왕국 2, 알라딘
-- in
# 2015~2018년 사이 top5 했던 적이 있는 감독 중 2019년에도 top5인 영화감독
select director,movie_name, audience_num
from box_office
where year(release_date) = 2019 and ranks between 1 and 5
and director in (select director
from box_office
where year(release_date) between 2015 and 2018
and ranks between 1 and 5);
# 안소니 루소,조 루소_앤드게임 감독(시빌워와 인피니티워로 top5에 들었던 적이 있다)
-- EXISTS
#2018년 2019년 탑10 중에 2018년과 2019년 개봉요일이 같은 영화와 랭킹, 개봉요일,관객수
select a.ranks, a.movie_name, DAYNAME(a.release_date) 개봉요일, round(a.audience_num / 10000) as '관객수(만명)' from box_office a where year(release_date) = 2019 and ranks between 1 and 10 and
exists (select 1 from box_office b where year(release_date) = 2018
and ranks between 1 and 10 and DAYNAME(a.release_date) = DAYNAME(b.release_date)) order by 1;2) 잘 쓸 거 같아,
A) csv를 MySQL로 가져와서 활용하기
기간별 박스오피스 조회하여 CSV 파일로
A_1) cp949 error
- MySQL은 오류라하면 90프로는 인코딩오류인 것 같다... 한글로 된 자료는 아얘 불러오질 못하거나 불러와도 깨지는 경우가 많다. 영화진흥위원회에서 엑셀로 받은 박스오피스를 csv파일로 저장할 때부터 한글이 깨졌다.
a) 네이버 오피스에서 csv로 저장
b) csv파일을 AcroEdit로 불러와 ANSI 문자셋으로 변환
c) 2021년 영화 업데이트 성공 :tada:
Learning&Running