[초보자를 위한 BigQuery 입문] SQL 쿼리 잘 작성하기

[지금 무료]초보자를 위한 BigQuery(SQL) 입문 강의 | 카일스쿨 - 인프런

 

도입

몇 달간 SQL 문제 풀이 스터디를 진행하다 보니 리트코드, 해커랭크 등 여러 플랫폼을 경험하게 되었습니다.

두 번 정도 SQL 코테를 풀다보니 해당 사이트들의 문제와 실제 코딩테스트, 현업의 쿼리 간에 간극이 존재한다는 것을 알게 되었어요. 실제 현업에 도움이 되는 SQL 작성법이나 노하우를 배울 수 없을까?라는 생각에 강의를 찾아보게 되었고 유튜브로 자주 보던 카일스쿨님의 '초보자를 위한 BigQuery 입문' 강의를 듣게 되었습니다!

50% 이상의 진도를 완료해 보니

한 줄로 정리해 보자면 이어지는 활용편도 결제해서 보고 싶다!라는 생각이 드는 좋은 강의인 것 같습니다.

특히 현업에서 어떤 실수를 자주 하게 되는지, 쿼리를 작성하는 흐름부터 오류를 처리하는 방법까지 강의를 수강하고 난 이후에 수강자가 스스로 공식문서를 찾아보고 오류 디버깅까지 할 수 있도록 차근차근 알려주시는 점이 좋았어요.

예를 들자면, TIMESTAMP와 DATETIME의 차이를 알려주는 것뿐만 아니라 그 차이로 인해 발생할 수 있는 문제점들까지 설명해 주셔서 더 쉽게 이해할 수 있었어요.

 

아래는 강의를 들으며 새롭게 알게 되었거나, 다시 한번 보면 좋을 내용들을 공유드리고자 합니다!

 

BigQuery의 장점

빅쿼리란?

• 구글 클라우드의 OLAP + 데이터 웨어하우스

장점

1. SQL을 사용해 쉽게 데이터 추출 가능해 파이썬, 자바 등에 비해 쉽다
2. OLAP 도구이므로 속도가 빠름 ( 단, 그만큼 돈을 지불 )
3. Firebase(앱), GA4 데이터를 쉽게 추출할 수 있다.
   • 사용 기기, 위치(시 단위까지 표현), OS 버전, 이벤트 행동 획득 가능 ( 별도의 로깅 필요 없이)
4. 데이터 웨어하우스를 사용하기 위해 서버(컴퓨터)를 띄울 필요 없이 구글에서 인프라를 관리함
   • 보통 서비스 할 때는 AWS EC2에서 서버를 띄움. 앱과 고객이 왔다 갔다 하면서 통신 → 서버 관리가 필요하다. → 구글이 대신해 줌!

사용하는 이유

• 회사에서 앱, 웹에서 Firebase, GA 4 활용할 경우
• 운영을 적은 비용(인력)으로 진행하기 위해

쿼리를 작성하는 흐름

1. 지표 고민
   • 어떤 문제를 해결하기 위해 데이터가 필요한가?
2. 지표 구체화
   • 추상적이지 않고 구체적인(이름 구체적으로) 지표 명시 (분자, 분모 표시)
3. 지표 탐색
   • 유사한 문제를 해결한 케이스가 있나 확인
   • 있다면 해당 쿼리 리뷰, 없다면 구글에 검색해 보기
4. 지표가 없다면 쿼리 작성 (있다면 있는 거 활용)
   • 데이터가 있는 테이블 찾기
     • 2개 이상이라면 연결 방법 고민(JOIN)
5. 데이터 정합성 확인
   • 예상 결과와 동일한지 확인
6. 쿼리 가독성
   • 나중을 위해 깔끔하게 쿼리 작성
7. 쿼리 저장
   • 쿼리는 재사용되므로 문서로 저장

시간 데이터 다루기

• GMT (한국 시간 : GMT + 9)
• UTC (한국 시간 : UTC + 9)
  • 국제적인 표준 시간
  • 타임존이 존재한다 = 특정 지역의 표준 시간대
• TIME STAMP
  • UTC로부터 경과한 시간을 나타내는 값
  • Time Zone 정보 있음

TIMESTAMP와 DATETIME 구분하는 법

SELECT
	CURRENT_TIMESTAMP(), -- UTC라고 타임존이 나옴, 한국 시간 -9시간
	DATETIME(CURRENT_TIMESTAMP(), 'Asia/Seoul') -- T가 나옴, 한국 Zone 사용 시 한국 시간과 동일

EXTRACT - DATETIME에서 특정 부분만 추출하고 싶은 경우

SELECT
	EXTRACT(DATE FROM DATETIME "2024-01-02 14:00:00") date,
	EXTRACT(YEAR FROM DATETIME "2024-01-02 14:00:00") year,
	EXTRACT(MONTH FROM DATETIME "2024-01-02 14:00:00") month,
	EXTRACT(DAY FROM DATETIME "2024-01-02 14:00:00") day,
	EXTRACT(HOUR FROM DATETIME "2024-01-02 14:00:00") hour,
	EXTRACT(MINUTE FROM DATETIME "2024-01-02 14:00:00") minute,
	EXTRACT(DAYOFWEEK FROM DATETIME "2024-01-02 14:00:00") week;
	-- 한 주의 첫날이 일요일인 [1,7] 범위의 값을 반환

DATETIME_TRUNC - DATE와 HOUR만 남기고 싶은 경우 → 시간 자르기

SELECT
	DATETIME_TRUNC(datetime_col, HOUR)
	-- HOUR로 자르면 그 이후가 날아감(분,초)

PARSE_DATETIME - 문자열로 저장된 DATETIME을 DATETIME 타입으로 바꾸고 싶은 경우

SELECT
	PARSE_DATETIME('%Y-%m-%d %H:%M:%S", '2024-01-11 12:35:35')
	-- 문자열 파싱

FORMAT_DATETIME -  DATETIME → 문자열로 변환

SELECT
	FORMAT_DATETIME("%c", DATETIME"2024-01-11 12:35:35")

LAST_DAY - 마지막 날을 알고 싶은 경우 : 자동으로 월의 마지막 값을 계산해서 특정 연산을 할 경우

SELECT 
	LAST_DAY(DATETIME '2024-01-03 15:30:00', WEEK(SUNDAY))

DATETIME_DIFF - 두 DATETIME의 차이를 알고 싶은 경우

SELECT
	DATETIME_DIFF(first_datetime,second_datetime,DAY)
	-- (first - second)로 계산한다.