Appendix. BigQuery 유용한 함수

1. Column이 중요한 BigQuery

데이터를 저장할 때 행 또는 열 기반으로 저장한다. 그 중에서 빅쿼리는 열 기반으로 저장하는 방법을 사용한다. 열 기반으로 데이터를 저장하게 되면 특정 열에 접근해서 열에 대한 집계를 수행하는 것은 효율적으로 할 수 있다. 대신에 쿼리를 작성할 때에도 사용할 컬럼만 가져와서 쿼리를 쓰는게 효율적인 쿼리가 될 수 있다.

2. BigQuery에 유용한 함수

2.1 EXCEPT()

필요 없는 컬럼 빼고 테이블 데이터 불러오기

SELECT * EXCEPT ('제외할 컬럼')
FROM table;

EXCEPT() 함수 쓰지 않는 경우:

select *
from `olist.olist_order_items`
limit 100
;

EXCEPT() 함수 쓰는 경우:

select * except (shipping_limit_date, freight_value)
from `olist.olist_order_items`
;

2.2 REPLACE()

불러올 때 간단하게 바꾸기

SELECT * REPLACE ('대체할 값' AS '컬럼명')
FROM table;

REPLACE() 함수 쓰지 않는 경우:

select *, price * 10000 as price
from `olist.olist_order_items`
limit 100
;

REPLACE() 함수 쓰는 경우:

select * replace (price * 10000 as price)
from `olist.olist_order_items`
;

2.3 CAST() & SAFE_CAST()

데이터 타입 바꾸기

CAST('값' AS '데이터타입')
SAFE_CAST('값' AS '데이터타입')

예시:

select
  cast('12345' as int64) as cast_result,			-- int64
  safe_cast('12345' as int64) as safe_case_result	-- int64
;

select
  cast('abcd' as int64) as cast_result		-- 에러!
;

select
  safe_cast('abcd' as int64) as cast_result 	-- NULL
;

2.4 SAFE 연산

• 에러가 있을 경우 에러를 발생시키지 않고 null로 반환한다.

SAFE_ADD(x, y) = x + y
SAFE_SUBTRACT(x, y) = x - y
SAFE_MULTIPLY(x, y) = x * y
SAFE_DIVIDE(x, y) = x / y

★ SAFE_DIVIDE()의 예시:

x	y	SAFE_DIVIDE(x, y)
20	4	5
0	20	0
20	0	NULL

select
  5 / 0
;

select
  safe_divide(5, 0)
;

Division by 0 을 하면 NULL로 반환이 되기 때문에 SAFE_DIVIDE()와 IFNULL과 조합하면 0으로도 반환이 가능하다.

select
  safe_divide(5, 0),
  ifnull(safe_divide(5, 0), 0)
;

2.5 IN & NOT IN

컬럼에 a, b, c, d, e 중 하나의 값이 있다고 가정

WHERE '컬럼' IN (a, b, c)		-- a, b, c에 해당
WHERE '컬럼' NOT IN (a, b, c)	-- d, e에 해당

예시:

• 특정 카테고리 매출만 뽑아주세요 > WHERE 카테고리 IN (a, b, c, ...)
• 특정 지역에 사는 고객만 뽑아주세요 > WHERE 거주지 IN (a, b, c, ...)
• 특정 판매자의 제품만 뽑아주세요 > WHERE 판매자 IN (a, b, c, ...)