구글 빅쿼리 완벽 가이드 - 3장(3) - Bool 다루기

▣ Bool 다루기

• 불리언 변수는 True 또는 False 중 하나의 값만 갖는 변수다. SQL은 대소문자를 구분하지 않으므로 TRUE나 true나 모두 같은 의미를 갖는다.

---

■ 논리 연산

• 필터링에 대해 설명할 때 WHERE 절에 AND나 OR 또는 NOT 등을 포함한 불리언 표현식은 물론 실행 순서를 제어하기 위해 괄호를 사용할 수 있다는 점을 설명했다. 그떄 예제로 사용했던 쿼리는 다음과 같다.

SELECT
  gender,
  tripduration
FROM
  `bigquery-public-data`.new_york_citibike.citibike_trips
WHERE
  (tripduration < 600
    AND gender = 'femaie')
  OR gender = 'male'

다음과 같이 불리언 변수와 함께 비교 연산자를 사용해도 된다.

WITH
  example AS (
  SELECT
    NULL AS is_vowel, NULL AS letter, -1 AS position
  UNION ALL SELECT TRUE,'a',1
  UNION ALL SELECT FALSE, 'b',2
  UNION ALL SELECT FALSE,'c',3 
)
SELECT * FROM example WHERE is_vowel != FALSE

그러나 다음 예제처럼 내장 상수와 비교할 때는 IS 연산자를 사용하는 것이 더 간단하다.

WITH
  example AS (
  SELECT
    NULL AS is_vowel, NULL AS letter, -1 AS position
  UNION ALL SELECT TRUE,'a',1
  UNION ALL SELECT FALSE, 'b',2
  UNION ALL SELECT FALSE,'c',3 
)
SELECT * FROM example WHERE is_vowel IS NOT false

이때 두 쿼리의 실행 결과가 다른 것을 알 수 있다. 비교 연산자(=, !=, < 등)는 NULL과 비교하면 NULL을 리턴하지만 IS 연산자는 그렇지 않기 때문이다.

---

■ 조건식

• 불리언이 WHERE 절에서만 유용한 것은 아니다. SELECT에서 조건식을 사용하면 쿼리를 단순화할 수 있다. 예를 들어 카탈로그의 품목별로 희망하는 가격 인상율 및 세율을 적용해 최종 판매 가격을 계산해야 한다고 가정해 보자. 이때 카탈로그에서 인상율이나 세율이 누락되어 있으면 기본 인상율이나 세율을 적용하고자 한다. 이런 경우 IF 함수를 사용하면 된다.

WITH catalog AS(
    SELECT 30.0 AS costPrice, 0.15 AS markup, 0.1 AS taxRate
    UNION ALL SELECT NULL, 0.21, 0.15
    UNION ALL SELECT 30.0, NULL, 0.09
    UNION ALL SELECT NULL, NULL, 0.09
    UNION ALL SELECT 30.0, NULL, NULL 
)
SELECT *, ROUND(
    costPrice *
    IF(markup is NULL, 1.05, 1+markup) *
    IF(taxRate IS NULL, 1.10, 1+taxRate), 2) AS salesPrice
FROM catalog

•  IF 함수의 첫 번째 파라미터는 평가할 조건이다. 조건이 참이면 두 번째 파라미터를 반환하고, 그렇지 않으면 세 번째 파라미터를 반환한다. 이 함수를 SELECT에서 호출했으므로 해당 작업은 각 행마다 실행된다.

---

■ Coalesce로 Null 값을 깨끗하게 처리하기

• 단일 값이 누락된 경우에는 보완이 가능하지만, 둘 이상의 값이 누락되어 값을 보완할 수 없다면 어떻게 할까? 다시 말해, 세율만 누락된 경우에는 10%의 세율을 적용하고, 품목의 가격 인상률까지 누락된 경우에는 그렇게 하지 않으려고 한다.
• 이런 경우 Coalesce 함수를 사용하면 Null이 아닌 값을 얻을 때까지 표현식을 계속 평가할 수 있어 편리하다.

WITH catalog AS(
    SELECT 30.0 AS costPrice, 0.15 AS markup, 0.1 AS taxRate
    UNION ALL SELECT NULL, 0.21, 0.15
    UNION ALL SELECT 30.0, NULL, 0.09
    UNION ALL SELECT NULL, NULL, 0.09
    UNION ALL SELECT 30.0, NULL, NULL 
)
SELECT *, ROUND(COALESCE(
    costPrice * (1+markup) * (1+taxRate),
    costPrice * 1.05 * (1+taxRate),
    costPrice * (1+markup) * 1.10,
    NULL
    ), 2) AS salesPrice
FROM catalog

• Coalesce는 가능할 때마다 계산을 단락short-circuit 평가한다. 즉, Null이 아닌 결과를 얻은 후에는 식을 평가하지 않는다. 따라서 Coalesce 함수의 마지막 파라미터인 NULL은 필요하지 않지만 그 의도를 더 명확히 표현하기 위한 것이다.
• 빅쿼리는 입력값이 2개이면 Coalesce보다 간단하게 사용할 수 있는 IFNULL 함수도 지원한다. IFNULL(a,b)는 Coalesce(a,b)와 동일하며 a가 NULL이면 b를 반환한다. 즉 IFNULL(a,b)는 IF(a IS NULL, b, a)와 동일하다.
• 조건부 표현식에 대한 이 절의 첫 번째 쿼리는 다음처럼 단순하게 표현할 수 있다.

WITH catalog AS(
    SELECT 30.0 AS costPrice, 0.15 AS markup, 0.1 AS taxRate
    UNION ALL SELECT NULL, 0.21, 0.15
    UNION ALL SELECT 30.0, NULL, 0.09
    UNION ALL SELECT NULL, NULL, 0.09
    UNION ALL SELECT 30.0, NULL, NULL 
)
SELECT *, ROUND(
    costPrice * 
    (1 + IFNULL(markup, 0.05)) *
    (1 + IFNULL(taxRate, 0.10))
    ,2) AS salesPrice
FROM catalog

---

■ 타입 변환과 타입 강제

• 직원의 휴가 사유를 근무 시간 대신 기록하기 위해 근무 시간을 문자열로 저장하는 예제 데이터셋을 살펴보자.

WITH example AS(
    SELECT 'John' AS employee, 'Paternity Leave' AS hours_worked
    UNION ALL SELECT 'Janaki', '35'
    UNION ALL SELECT 'Jian', 'Vacation'
    UNION ALL SELECT 'Jose', '40'
)
SELECT SUM(hours_worked) FROM example

•  hours_worked 컬럼이 숫자 타입이 아닌 문자열이므로 쿼리는 작동하지 않는다.
• 올바른 값을 얻으려면 집계를 수행하기 전에 hours_worked 컬럼을 INT64로 명시적으로 변환해야 한다. 명시적 타입 변환을 캐스팅casting이라고 하며 CAST( )함수를 명시적으로 사용해야 한다. 캐스팅이 실패하면 빅쿼리는 오류를 반환한다. 오류 대신 NULL을 반환하려면 SAFE_CAST를 사용한다. 예를 들어 다음 쿼리는 오류를 발생한다.

SELECT CAST("true" AS bool), CAST("invalid" AS bool)

• 이제 SAFE_CAST를 사용해 보자.

SELECT CAST("true" AS bool), SAFE_CAST("invalid" AS bool)

• 임시적 변환을 타입 강제coercion라고 하며, 사용하는 데이터 타입과 필요한 데이터 타입이 다르면 자동으로 타입 강제가 이뤄진다. 예를 들어 FLOAT64가 필요한데 INT64를 사용하면 정수는 부동소수점 숫자로 강제로 변환된다. 빅쿼리가 수행하는 유일한 타입 강제는 INT64를 FLOAT64 및 NUMERIC으로, NUMERIC을 FLOAT64로 변환하는 것이다. 다른 모든 변환은 명시적으로 CAST를 사용해야 한다.
• 총 근무 시간을 구하는 문제에서 hours_worked 문자열 컬럼의 값 중에는 'Vacation'처럼 정수로 변환할 수 없는 값도 있으므로 SAFE_CAST를 사용해야 한다.

WITH example AS(
    SELECT 'John' AS employee, 'Paternity Leave' AS hours_worked
    UNION ALL SELECT 'Janaki', '35'
    UNION ALL SELECT 'Jian', 'Vacation'
    UNION ALL SELECT 'Jose', '40'
)
SELECT SUM(SAFE_CAST(hours_worked AS INT64)) FROM example

• 만약 단순히 스키마 문제로 모든 행의 데이터가 숫자를 표현하는 문자열로 저장된 경우라면, 즉 숫자가 아닌 문자열이나 NULL 값 등이 없다면 간단하게 CAST를 사용할 수 있다.

WITH example AS(
    SELECT 'John' AS employee, '0' AS hours_worked
    UNION ALL SELECT 'Janaki', '35'
    UNION ALL SELECT 'Jian', '0'
    UNION ALL SELECT 'Jose', '40'
)
SELECT SUM(CAST(hours_worked AS INT64)) FROM example

---

■ 불리언 변환을 피하기 위해 COUNTIF 사용하기

WITH example AS(
    SELECT true AS is_vowel, 'a' AS letter, 1 AS position
    UNION ALL SELECT false, 'b',2
    UNION ALL SELECT false, 'c',3
)
SELECT * FROM example

• SUM, AVG 등은 불리언 값과는 작동하지 않는다. 따라서 다음과 같이 집계를 수행하기 전에 불리언 값을 INT64로 변환해야 한다.

WITH example AS(
    SELECT true AS is_vowel, 'a' AS letter, 1 AS position
    UNION ALL SELECT false, 'b',2
    UNION ALL SELECT false, 'c',3
)
SELECT SUM(CAST(is_vowel AS INT64)) AS num_vowels FROM example

• 하지만 가능하면 타입 변환은 피하는 것이 좋다. 이 예제에서는 불리언 값에 IF 문을 사용하는 것이 더 깔끔하다.

WITH example AS(
    SELECT true AS is_vowel, 'a' AS letter, 1 AS position
    UNION ALL SELECT false, 'b',2
    UNION ALL SELECT false, 'c',3
)
SELECT SUM(IF (is_vowel,1,0)) AS num_vowels FROM example

• 그리고 이보다 더 나은 방법은 COUNTIF를 사용하는 것이다.

WITH example AS(
    SELECT true AS is_vowel, 'a' AS letter, 1 AS position
    UNION ALL SELECT false, 'b',2
    UNION ALL SELECT false, 'c',3
)
SELECT COUNTIF(is_vowel) AS num_vowels FROM example