SQL데이터 분석 KPI 지표 수립 실습 (날짜,시간 별 분석 문제 모음)

이번 실습은 각 기업에서 실제로 통계자료로써 각종 KPI 지표들을 mysql을 통해  추출할 것이다.

 

실습1

2020년 7월의 평균 DAU를 구해주세요

DAU란 하루 동안 해당 서비스를 이용한 순수한 이용자 수를 나타내는 지표 이다.

저번 회차 실습때와 마찬가지로 날짜별 분석을 통해 값을 추출한다.

날자계산을 위해 date_format 함수를 사용하여 날짜를 추출한다.

date_format 사용시 format의 유의하여 사용한다.

자료가 많으니 실무자들도 많이 까먹고 구글링하여 검색한다고 한다.

아래는  format 모음이다.

 

from 절 인라인뷰를 사용하여 day당 사용자 수를 파악하고 평균을 낸다다만 해당 자료의 문제로 시간이 9시간 씩 멀어져 있어 9시간을 뺀 후 시간 계산을 하였다강사님의 말로는 여러 기업에서 나타나는 문제로써 시간을 먼저 확인 후 쿼리작성하는게 좋다 하셨다.마찬가지로 인라인뷰 끝에는 아무문자를 넣어줘야 쿼리실행이 가능하다.

 

실습2

2020년 7월의 평균 WAU를 구해주세요

WAU란 일주일 동안 해당 서비스를 이용한 순수한 이용자 수를 나타내는 지표 이다.

실습1과 거의 동일하지만 day를 week로 변경했기 때문에

week를 나타내는 %u를 사용하여 추출한다.

다만 week는 어떤날은 첫주차가 3일이고 마지막주차가 5일 이듯 불분명하기 때문에

캘린더를 이용하여 정확한 날짜를 계산한다.

 

캘린더를 확인해보면 5일~25일이 정확한 값을 추출하기에 알맞아 보인다.(7일을 모두 포함하기 때문)

 

실습3

2020년 7월의 평균 Daily Revenue 를 구해주세요

Revenue 즉 수익이다 . 7월동안 매일의 수익을 나타내는 것이다.

위의 실습1과 거의 동일한 유형의 쿼리문이다.

 

 

 실습4

2020년 7월의 평균 Weekly Revenue를 구해주세요.

실습 3과 date format 만 변경하여 사용하는 쿼리문이다.

%d 가 아닌 일주일을 나타내는  %u를 사용한다

 

실습5

2020년 7월 요일별 Daily Revenue를 구해주세요.

요일별 daily 이기 때문에 가장 마지막 select 문에 필요한 컬럼들을 추가한다.

요일별은 %W 사용하기에 추가한다.

마지막에 추가한 컬럼으로 group by 를 하여 묶는다.

 

 

실습6

2020년 7월 시간대별 시간당 Revenue를 구해주세요

실습5와 같이 필요한 컬럼들을 추가하여 사용한다.

시간대별 이기때문에 format은 %h을 사용한다.

 

실습7

2020년 7월 요일 및 시간대별 Revenue를 구해주세요

위의 실습들과 동일하고 컬럼이 하나 더 추가되었다.

요일을 사용하기 위한 format %w, 시간대별 을 사용하기 위한 format %h 사용