[Bigquery, 고객센터 예제] _TABLE_SUFFIX 날짜 단위로 나뉜 테이블을 모두 불러오기, DATE_SUB으로 지난 날짜 구하기

*위 글은 구글 빅쿼리 고객센터의 예제를 일부 변형하여 해석한 글입니다.

저작권 문제 시 댓글을 남겨주시면 바로 조치하겠습니다.

 

 

 

 

'2017년 9월 1일로부터 지난 90일 간 방문자수, 페이지뷰수, 거래수, 매출액'을 구하기

예제 원문 링크: <지난 1,095일> support.google.com/analytics/answer/4419694?hl=ko#1095days 

#이 쿼리는 Bigquery에서 실행가능합니다.
#다만 FROM의 ''따옴표는 예제 원문링크에서 복사하시길 바랍니다.

SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`

WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB('2017-09-01', INTERVAL 90 DAY))
AND FORMAT_DATE('%Y%m%d',DATE_SUB('2017-09-01', INTERVAL 1 DAY))

GROUP BY date
ORDER BY date ASC

 

1. FROM의 '*' 과 TABLE SUFFIX에 대해 알아보자

FROM의 *는 와일드 카드로, 아래 WHERE의 _TABLE_SUFFIX의 값이 들어가는 자리이다.

 

예를 들면, TABLE SUFFIX BETWEEN 20170701 AND 20170731로 인해 ga_sessions_*는

ga_session_20170701부터 ga_sessions_20170731테이블 까지 불러올 수 있게된다.

 

이런 건 테이블이 날짜 순, 번호 순으로 많이 존재할 때 사용하면 좋다고 생각한다.

 

 

 

 

 

2. 이제 WHERE의 구조를 먼저 파악해보자.

FROM `~ . ga_sessions_*`

WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE( ~ ,DATE_SUB( ~ , INTERVAL 90 DAY))
		        AND FORMAT_DATE( ~ ,DATE_SUB( ~ , INTERVAL 1 DAY))

_TABLE_SUFFIX로 * 를 채울건데 20170706의 형태로 채워야 한다. (테이블 명이 그렇게 되어있다.)

 

이 때 FORMAT_DATE는 날짜의 형태를 만들기 위해 사용됐고,

DATE_SUB는 특정 날짜에서 며칠/몇 달/ 몇 년을 뺀 날짜 값을 구하기 위해 사용됐다.

(이렇게 하면 90일 전 날짜를 따로 계산하지 않아도 된다.)

 

 

 

 

따라서 아래 코드는 다음과 같이 해석한다.

WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB('2017-09-01', INTERVAL 90 DAY))
	                AND FORMAT_DATE('%Y%m%d',DATE_SUB('2017-09-01', INTERVAL 1 DAY))

20170706같은 형태로 만들기 위해 FORMAT_DATE(어떤 형태, 어떤 날짜)를 사용한다.

• 어떤 형태: %Y%m%d
• 어떤 날짜: DATE_SUB(특정날짜, INTERVAL ~ DAY)

DATE_SUB는 특정날짜에서 ~DAY를 뺀 날짜를 반환한다.

여기선 2017년 9월 1일에서 90일 이전의 날짜를 반환한다.

이때 INTERVAL ~DAY는 INTERVAL ~MONTH등 다른 옵션도 있다.

결과적으로 2017년 9월 1일에서 90일 이전의 날짜와 1일 이전의 날짜를 %Y%m%d 형태로 반환해서

*BETWEEN ~ AND ~를 완성한 후 이 내용이 에 들어가는 것이다.

참고링크: FORMAT_DATE, DATE_SUB

 

 

cf) 이렇게 날짜 범위를 정해 테이블을 FROM, WHERE로 불러와서

SELECT에 있는 것들을 다시 골라온다.