헷갈리는 BigQuery 함수 정리

프로젝트를 진행하면서 DBeaver로 MySQL 쿼리를 작성하여 데이터를 추출하다가 속도의 문제로 Google Cloud에 데이터를 업로드하여 BigQuery를 사용하여 데이터를 추출하고 있습니다! 동일하게 쿼리를 작성했는데 오류가 발생하는 경우들이 발생하더라구요!

 

BIgquery같은 경우는 Standard SQL을 제공한다고 하는데 제가 나중에 다시 보기위해서 정리를 해봅니다!

 

---

(1) EXTRCT(필요한값 FROM 시간컬럼명)

: DATETIME에서 필요한 값(일자, 요일, 주차, 월, 분기, 연도, 시간 등)을 추출

MySQL에서는 비교적 쉽게 추출이 가능합니다. YEAR(시간컬럼명) 이러한 방식으로 작성하면 원하는 값을 쉽게 추출 가능합니다!

• YEAR : 연도 추출
• MONTH : 월 추출
• DAY : 일 추출 
• WEEK : 주 추출
• HOUR : 시 추출
• MINUTE : 분 추출
• SECOND : 초 추출

 

 

(2) COUNT(DISTINCT CONCAT(컬럼1, '-', 컬럼2))

전환율을 계산하는 과정에서 user_id, user_session을 모두 고려하여 COUNT를 해주어야하는 상황이었으나 빅쿼리에서 "Number of arguments does not match for aggregate function COUNT. Supported signature: COUNT(ANY) ~" 이런식의 오류가 나타나더라구요!

 

처음에는 인수 수가 맞지 않다는 오류 때문에 제가 쿼리를 잘못짰나? 이렇게 생각을 했었는데 알고보니 BigQuery에서는 COUNT(DISTINCT 컬럼1, 컬럼2)가 맞는 문법이 아니더라구요.

 

COUNT(DISTINCT CONCAT(컬럼1, '-', 컬럼2)), 이렇게 "-"로 연결하여 쿼리를 작성하니 바로 오류가 해결되었습니다!

 

 

(3) DATE(FORMAT_DATETIME("%Y-%m-01", 시간컬럼))

각각의 코호트별로 시간의 흐름에 따라 고객들의 재구매를 추적해보는 클래식 리텐션 계산에서 DATE_FORMAT(시간컬럼명, "%Y-%m-01") 함수를 통해 "YYYY-MM-01" 형태로 가공하여 데이터를 확인해보곤 했는데요! 이 함수 역시 BigQuery에서는 오류가 발생하더라구요!

 

BigQuery에서는 DATE_FORMAT()가 아닌 FORMAT_DATE() 함수를 사용하며 들어가는 속성의 순서도 약간 다르다는 것을 유의해서 쿼리를 작성하시면 될 것 같습니다!

 

소개 | BigQuery Guide Book - 빅쿼리 가이드북

BigQuery 문법이 잘 정리되어있는 블로그도 소개드리면서 이번 글을 마치겠습니다!