[빅쿼리(BigQuery) - SQL] 2개의 값 비율 계산하기(NULLIF 함수)

2개의 값 비율 계산하기(NULLIF 함수)

 

 

여기서는 하나의 레코드에 포함된 값을 조합해서 비율을 계산하는 방법을 소개한다. 다음은 광고 통계 정보를 나타나는 테이블이다. 메일의 광고 노출 수와 클릭 수를 집계한다. 2017-04-02의 ad_id:001 데이터는 광고를 하지 않은 날이라 노출 수와 클릭 수가 모두 0으로 되어 있다고 생각한다.

 

 

 

 

 

정수 자료형의 데이터 나누기

다음 코드 예에서는 하루 (2017-04-01) 데이터에서 각 광고의 CTR(Click Through Rate)을 계산한다. CTR이란 '클릭 / 노출 수'이다.

 

하나의 레코드에 포함된 값을 나눌 때는 SELECT 구문 내부에서 '/'를 사용한다. 

 

결과를 퍼센트로 나타낼 때는 ctr 컬럼의 결과에 100을 곱한다. 참고로 하나하나 구할 때 ctr_as_perent 컬럼처럼 click에 100.0을 곱해 계산하면, 자료형 변환이 자동으로 이루어지므로 쿼리가 간단해진다.

 

 

 

 

 

0으로 나누는 것 피하기

2017-04-02 데이터는 impression이 0이다. 따라서 앞의 코드 예를 적용하면 0으로 나누게 되어 오류가 발생한다.

 

0으로 나누는 것을 피하는 첫 번째 방법은 CASE 식을 사용해 impression가 0인지 확인하는 것이다. 다음의 ctr_as_percent_by_case 컬럼은 impression가 0보다 큰 경우에는 CTR을 계산하고, 이외의 경우에는 NULL을 출력한다.

 

추가로 NULL 전파를 사용하면 0으로 나누는 것을 피할 수 있다. NULL 전파란 NULL을 포함한 데이터의 연산 결과가 모두 NULL이 되는 SQL의 성질이다. 다음의 ctr_as_percent_by_null처럼 NULLIF(impressions, 0) 부분은 impressions 값이 0이라면 NULL이 된다. 그 결과 impressions의 값이 0이라면 NULL 전반으로 CTR 값도 NULL이 되어, CASE 식을 사용한 방법과 같은 결과를 얻을 수 있다.

 

 

 

 

 

# NULLIF 함수의 자세한 설명은 https://cloud.google.com/bigquery/docs/reference/standard-sql/conditional_expressions?hl=ko#nullif 에서 찾아볼 수 있다.