SQL(big query) 연도별, 분기별, 월별 ,날짜별 집계 하는법

엑셀로는 용량이 커서 다루기 힘든 데이터는 주로 sql문을 통해서 데이터 분석이 이루어지는데요.

하지만 이 sql 문이 실행되기 위해 필요한 DB(데이터 베이스)는 관리한다는게 쉬운 일이 아니더라구요...

컴퓨터에 설치도 오래걸리고, csv 파일을 db로 옮길때 컴퓨터 시스템 설정이 다르면 에러도 많이 났었어요..

 

 

구글의 데이터 웨어하우스 서비스인 빅쿼리

그래서 최근에 설치없이 DB를 사용할 수 있는 구글의 big query를 사용하기로 결정하였습니다.

오늘은 bigquery를 이용한 날짜에 따른 매출 집계를 sql문으로 해보려고 합니다.

 

사용할 데이터는 데이터 예측모델 및 분석 대회 플랫폼인 Kaggle에서 얻은 데이터 입니다. 

https://www.kaggle.com/competitions/store-sales-time-series-forecasting/data

Store Sales - Time Series Forecasting | Kaggle

현재 데이터 세트와 스키마 구조는 이렇습니다.(잘 모르시겠으면 넘어가셔도 되요!!)

• 연도별, 월별, 날짜별로 매출 합계 구하기!(sql 함수 extract)

사용할 sql의 함수는 extract인데요. sql의 날짜열(칼럼)에서 년도, 월, 일, 요일을 분리해서 새로운 칼럼을 만들어주는 함수입니다. 빅쿼리 문서에 extract함수 관련 내용이 있어 링크 남겨요!

https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions?hl=ko#extract

날짜 함수  |  BigQuery  |  Google Cloud

출처: google big query 문서

설명

지정된 날짜 부분에 해당하는 값을 반환합니다. part는 다음 중 하나여야 합니다.

• DAYOFWEEK: 한 주의 첫날이 일요일인 [1,7] 범위의 값을 반환합니다.
• DAY
• DAYOFYEAR
• WEEK: [0, 53] 범위에서 날짜의 주 번호를 반환합니다. 주는 일요일부터 시작되며 그 해의 첫 번째 일요일 이전 날짜는 0번째 주에 속합니다.
• WEEK(<WEEKDAY>): [0, 53] 범위에서 해당 날짜의 주 번호를 반환합니다. 주는 WEEKDAY에 시작됩니다. 그 해의 첫 번째 WEEKDAY 이전의 날짜는 0번째 주에 속합니다. 유효한 WEEKDAY 값은 SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY입니다.
• ISOWEEK: date_expression의 ISO 8601 주 번호를 반환합니다. ISOWEEK는 월요일에 시작됩니다. 반환 값은 [1, 53] 범위에 속합니다. 각 ISO 연도의 첫 번째 ISOWEEK는 태양력 연도의 첫 번째 목요일 이전의 월요일에 시작됩니다.
• MONTH
• QUARTER: [1,4] 범위의 값을 반환합니다.
• YEAR
• ISOYEAR: date_expression이 속한 주의 목요일을 포함하는 태양력 연도인 ISO 8601 주 번호 지정 연도를 반환합니다.

반환 데이터 유형

INT64

 

select
    T.date AS date,
    extract(year FROM T.date) AS year,
    extract(month FROM T.date) AS month,
    extract(day FROM T.date) AS day,
    extract(dayofweek FROM T.date) As day_of_week,
    CAST(SUM(T.sales) AS int) AS total_sales

from market_product_sales.train AS T

GROUP BY
        T.date,
        extract(year FROM T.date),
        extract(month FROM T.date),
        extract(month FROM T.date),
        extract(day FROM T.date),
        extract(dayofweek FROM T.date);

쿼리 실행결과  원래는 yyyy-mm-dd형식만 있었지만 year, month, day, day_of_week가 추가 되었습니다.

day_of_week의 숫자의 의미는 1:일요일 , 2:월요일, 3:화요일,... 7:토요일으로 구성되어 있습니다.

빅쿼리는 BI 프로그램인 tableau와도 연동이 됩니다. 빅쿼리에서 나온 뷰를 테이블로 저장해주고 테이블을 태블로오 연동을 해주었어요. 요일별 매출의 숫자를 요일이름으로 바꾸어 주었으면 좋았을텐데 아쉽네요..

다음에 도전해볼려구요!