구글 스프레드시트 사용법 (7) Query로 데이터 분석하기

블로그 통계데이터를 보니 구글 스프레드시트 사용법 (3) QUERY 포스팅이 가장 인기가 좋아 QUERY 활용 예시를 추가해봅니다.

QUERY는 대량의 데이터를 분석할 때 정말 편합니다. 이번에는 영화진흥위원회(http://www.kofic.or.kr)에서 2018년도 상반기(1월~6월) 월별 박스오피스 데이터를 다운로드 하여  Query와 스프레드시트의 함수, 계산식 등으로 분석하는 예시를 보여드리겠습니다.

데이터 준비

우선은 월별로 다운로드 받은 엑셀 데이터를 구글 드라이브에 올린 후,
[Google 스프레드시트(으)로 열기 버튼]으로 스프레드시트로 변환합니다.

가장 왼쪽에 열을 추가하고 월 정보를 추가합니다.
각 월별로 1월, 2월..6월 이렇게 데이터를 추가했습니다.

이렇게 준비된 시트: 월별박스오피스_2018상반기

이제부터 Query를 이용하여 2018년 상반기 영화 데이터를 분석해보겠습니다.

관객수 Top 20 영화

관객수가 가장 많은 영화 20개를 분석합니다.
★ 샘플 스프레드시트: ⓠ 영화 관객수 Top 20

QUERY:  select C, sum(H) group by C order by sum(H) desc limit 20

• select 표시할 데이터
  • 영화명 : C
  • 관객수 합계 : sum(H)
  • 매출액 합계 : sum(E)
  • 스크린 수 합계 : sum(J)
  • 상영횟수 합계 : sum(K)
• 영화명으로 그룹을 묶음 : group by C
• 관객수 합계 많은 순으로 정렬 : order by sum(H) desc
• 20개만 표시:  limit 20

분석: 관객수와 매출액은 정비례합니다.
1위인 <어벤져스: 인피니티 워>는 2위와의 차이가 워낙에 커서 2018년도 최대 관객수를 기록한 영화가 될 것으로 보입니다.

월별 관객수, 매출액 분석

월별 관객수, 매출액 등을 분석합니다.
★ 샘플 스프레드시트: ⓠ 월별 분석

QUERY: select A, sum(H), sum(E), count(C), sum(J), sum(K) where E > 0 group by A

• select 표시할 데이터
  • 월 : A
  • 관객수 합계 : sum(H)
  • 매출액 합계 : sum(E)
  • 영화수 : count(C)
  • 스크린 수 합계 : sum(J)
  • 상영횟수 합계 : sum(K)
• 매출이 0 이상인 데이터만 : where E > 0
• ‘월’로 그룹을 묶음 : group by A

월별 1위 영화 관객수, 매출액 등

QUERY: select A, B, C, H, E where B = 1 order by A

• select 표시할 데이터
  • 월 : A
  • 순위 : B
  • 영화명 : C
  • 관객수 : H
  • 매출액 : E
• 순위가 1인 데이터만 : where B = 1
• ‘월’로 정렬 : order by A

분석: 왜인지는 모르겠지만 1월 관객수 합계가  상반기 중 가장 많았습니다. 월별 1위 영화의 관객 점유율 평균은 30%입니다.

월별 개봉작 수를 분석합니다.

QUERY: select (month(D) +1 ), count(C) where D >= date ‘2018-01-01’ group by month(D)

• select 표시할 데이터
  • 개봉월 : (month(D) +1 )
  • 영화수 : count(C)
• 개봉일이 올해인 데이터만 : where D >= date ‘2018-01-01’
• 개봉월로 묶음 : group by month(D)

분석: 1월 관객수 합계가  많은 이유 중 하나는 1월달에 개봉한 영화가 많기 때문인 것 같습니다. 7월~10월 데이터가 잡히는 걸로 봐서, 개봉날 전에 상영하는 영화들이 있는 것 같습니다. 시사회겠지요?

관객수 Top 20 한국 영화

관객수가 가장 많은 한국 영화 20개를 분석합니다.
★ 샘플 스프레드시트: ⓠ 한국영화 관객수 Top 20

QUERY: select C, sum(H), sum(E), sum(K) where M = “한국” group by C order by sum(H) desc limit 20

• select 표시할 데이터
  • 영화명 : C
  • 관객수 합계 : sum(H)
  • 매출액 합계 : sum(E)
  • 상영횟수 합계 : sum(K)
• 대표국적이 한국인 데이터만 : where M = “한국”
• 영화명으로 그룹을 묶음 : group by C
• 관객수 합계 많은 순으로 정렬 : order by sum(H)
• 20개만 표시:  limit 20

분석: 영화 <마녀>는 상영횟수가 적은데 비해 상영별 관객수가 굉장히 높습니다. 상영별 관객수로만 보면 3위입니다. 상영을 더 많이 했다면 더 좋은 성적이 나왔을 것 같습니다.

국적별 분석

국적별 매출액, 영화수를 분석합니다.
★ 샘플 스프레드시트: ⓠ 대표 국적별 매출액

QUERY: select L, sum(E), count(C) where D >= date ‘2018-01-01’ group by L order by sum(E) desc

• select 표시할 데이터
  • 대표국적 : L
  • 매출액 합계 : sum(E)
  • 영화수 : count(C)
• 개봉일이 올해인 데이터만 : where D >= date ‘2018-01-01’
• 대표국적으로 그룹을 묶음 : group by L
• 매출액 합계 많은 순으로 정렬 : order by sum(E) desc

데이터는 전부 표시했지만, Top 10만 분석했습니다.

분석: 미국 영화는 영화 1개당 매출액이 한국 영화의 약 2배에 달합니다. 개봉한 영화수는 전체의 22%이지만, 매출액은 절반을 조금 넘는 56%입니다. 미국 영화의 파워를 다시금 확인할 수 있는 데이터였습니다.

한국과 미국의 영화수를 합치면 전체의 약 58%
한국과 미국의 매출액을 합치면 전체의 약 95%
영화 매출의 대부분을 미국영화, 한국영화가 가져가고 있습니다.

 

 

구글 스프레드시트의 다른 활용법을 알고싶으시다면!
» G Suite 사용법 #4 스프레드시트 활용 예시 모음