6. 조건문

6.1. CASE문 6.1.1. CASE문 6.1.2. PIVOT 6.2. IF문 6.2.1. IF문 6.2.2. IFNULL 6.2.3. NULLIF

조건문은 데이터베이스 쿼리와 프로그래밍에서 데이터를 필터링하고 제어하는 데 사용되는 중요한 도구입니다. Google BigQuery에서는 조건문을 사용하여 데이터를 선택하고 조작하는 다양한 방법을 제공합니다. 이 챕터에서는 BigQuery에서 조건문을 활용하는 방법과 주요 조건문 함수에 대해 살펴보겠습니다.

6.1. CASE문

6.1.1. CASE문

CASE문은 여러 조건을 비교하고 조건에 따라 다른 결과를 반환합니다. 데이터 변환이나 분류 시 유용하게 사용됩니다. 여러 조건이 설정된 경우, 먼저 작성된 조건이 우선 적용되고 이에 해당하지 않으면 다음 조건이 적용되는 순으로 동작하기 때문에 먼저 걸러지는 조건을 앞 순서로 배치하는 것이 좋습니다. ELSE는 생략할 수 있고, CASE를 WHERE 절에서 사용할 때에는 END 뒤에 ALIAS를 사용할 수 없습니다.


CASE
		WHEN condition THEN true_value1
	  WHEN condition THEN true_value2
ELSE else_value
END

예시 1

“employees” 테이블에서 임금이 5천만 원 미만일 경우에는 “5천 미만”, 5천만 원 이상이고 1억 원 미만일 경우에는 “5천 이상 1억 미만”, 1억 원 이상일 경우에는 “1억 이상”으로 분류하여 “job_id”와 임금 분포를 상위 10개로 제한하여 조회합니다.


SELECT
      job_id
    , salary
		,	CASE 
			    WHEN salary < 5000 THEN "5천 미만"
			    WHEN salary < 10000 THEN "5천이상 1억 미만"
		      ELSE "1억 이상"
			END AS sal_dist
FROM `HR.employees` 
LIMIT 10;

예시 2

“employees” 테이블에서 “hire_date”가 2005년부터 2008년인 데이터에서 “job_id”가 SA로 시작하는 직무를 “SA” 그룹, ST로 시작하는 직무를 “ST” 그룹, AD로 시작하는 직무를 “AD” 그룹, 그리고 나머지 직무들은 “ETC” 그룹으로 분류하여 그룹별 임금 합계를 조회합니다.


SELECT
      EXTRACT(YEAR FROM hire_date) AS year
		, CASE 
					WHEN job_id LIKE 'SA%' THEN "SA"
					WHEN job_id LIKE 'ST%' THEN "ST"
          WHEN job_id LIKE 'AD%' THEN "AD"
      ELSE "ETC"
			END AS job_group
    , SUM(salary) AS sal_sum
FROM `HR.employees` 
WHERE EXTRACT(YEAR FROM hire_date) BETWEEN 2005 AND 2008
GROUP BY year, job_group
ORDER BY sal_sum DESC;

예시 3

다음은 중첩 CASE문을 쓴 예시입니다. 로직이 복잡해질 수 있으므로 유의하여 사용해야 합니다. 예시 1에서 임금이 5천 미만, 5천 이상 1억 그리고 1억 이상일 경우로 분류했었는데, 아래에서는 임금이 5천 이상 1억 미만인 경우를 CASE문을 사용하여 더 세부적으로 나눠보겠습니다. 임금이 5천 이상 1억 미만인 경우에서 7천5백 미만일 때는 “7천5백 미만”, 7천5백 이상 1억 미만일 경우에는 “7천5백 이상 1억 미만”으로 분류하여 “job_id”와 임금 분포를 살펴보면 다음과 같습니다.


SELECT 
      job_id
    , salary
    , CASE 
          WHEN salary < 5000 THEN "5천 미만"
          WHEN salary < 10000 THEN
              CASE 
                  WHEN salary < 7500 THEN "7천5백 미만"
              ELSE "7천5백 이상 1억 미만"
              END
      ELSE "1억 이상"
      END AS sal_dist
FROM `HR.employees`
LIMIT 10;

예시 4

다음은 CASE문을 WHERE절에 쓴 예시입니다. 이 경우에는 END 뒤에 ALIAS를 적을 수 없습니다.

“employees” 테이블에서 먼저 “hire_date”가 2005년부터 2008년이며, “job_id”가 SA로 시작하는 직무를 “SA” 그룹, ST로 시작하는 직무를 “ST” 그룹으로 분류하여 “SA”와 “ST” 그룹을 조건으로 설정하였습니다. 여기서 연도별, “job_id”별 임금 합계를 내림차순으로 조회해 보면 다음과 같습니다.


SELECT
      EXTRACT(YEAR FROM hire_date) AS year
    , job_id
    , SUM(salary) AS sal_sum
FROM `HR.employees` 
WHERE EXTRACT(YEAR FROM hire_date) BETWEEN 2005 AND 2008 
AND CASE 
  	    WHEN job_id LIKE 'SA%' THEN "SA"
  	    WHEN job_id LIKE 'ST%' THEN "ST"
    END IN ("SA", "ST")
GROUP BY year,job_id
ORDER BY sal_sum DESC;

6.1.2. PIVOT

Pivot은 데이터의 속성값을 열로 변환하는 기능입니다. 사용자의 목적에 따라 데이터를 재구성하고 요약하는 데 유용하게 사용됩니다. 행으로 나열된 값을 열로 변환하기 때문에 Pivot을 사용하면 더 적은 행 수를 유지하며 목록을 단순한 형태로 요약할 수 있다는 장점이 있습니다.

CASE문을 이용한 Pivot

BigQuery에서는 Pivot 기능이 따로 없었기 때문에 대신 CASE문을 사용하여 조건부 집계를 수행하고 원하는 열을 만들어 구현했었습니다.

예시

위의 예시 2의 결과를 CASE문을 사용하여 “job_id”의 속성값을 열로 바꿔보면 다음과 같습니다.


SELECT
      EXTRACT(YEAR FROM hire_date) AS year
		, SUM(CASE 
					    WHEN job_id LIKE 'SA%' THEN salary
          END) AS SA
    , SUM(CASE 
					    WHEN job_id LIKE 'ST%' THEN salary
          END) AS ST
    , SUM(CASE 
					    WHEN job_id LIKE 'AD%' THEN salary
          END) AS AD
    , SUM(CASE 
              WHEN job_id NOT LIKE 'SA%' AND 
                   job_id NOT LIKE 'ST%' AND
                   job_id NOT LIKE 'AD%' THEN salary
          END) AS ETC
FROM `HR.employees` 
WHERE EXTRACT(YEAR FROM hire_date) BETWEEN 2005 AND 2008
GROUP BY year
ORDER BY year;

PIVOT operator

하지만 2021년 BigQuery에 PIVOT 연산자가 추가되어 이전보다 훨씬 간편하게 Pivot 기능을 구현하는 것이 가능해졌습니다.


SELECT *
FROM (SELECT 
						column1, column2, column3 .. 
			FROM `Table1`)
PIVOT(aggregate_function(aggregate_column) FOR pivoting_column IN ('value1', ..) AS

예시

위의 예시2 결과에서 PIVOT을 이용하여 “job_id” 속성값을 열로 바꿔보면 다음과 같습니다.


SELECT *
FROM (
      SELECT
            EXTRACT(YEAR FROM hire_date) AS year
          , CASE 
                WHEN job_id LIKE 'SA%' THEN "SA"
                WHEN job_id LIKE 'ST%' THEN "ST"
                WHEN job_id LIKE 'AD%' THEN "AD"
            ELSE "ETC"
            END AS job_group
          , SUM(salary) AS sal_sum
      FROM `HR.employees` 
      WHERE EXTRACT(YEAR FROM hire_date) BETWEEN 2005 AND 2008
      GROUP BY year, job_group
      ORDER BY sal_sum DESC
			)
PIVOT(SUM(sal_sum) FOR job_group IN ('SA', 'ST', 'AD', 'ETC'));

6.2. IF문

6.2.1. IF문

IF 문은 조건이 하나일 때 유용한 방식입니다.


SELECT 
			IF(condition, true_value, false_value)

CASE와 IF 차이

IF문은 TRUE/ FALSE로 표현되는 이중 분기에서 사용하는 반면, CASE문은 분기가 2개 이상인 다중 분기에서 사용됩니다. 다중분기 시, IF문을 중첩한 중첩IF문을 사용할 수는 있지만 가독성 및 유지보수 측면에서 CASE문이 권장됩니다.

또한 IF 구문에서는 세 번째 인자로 false_value를 무조건 지정해야 하지만, CASE문은 ELSE가 생략가능하기 때문에 else_value를 지정하지 않아도 실행이 가능합니다.

예시

“employees” 테이블에서 “job_id”와 그 “job_id”의 임금이 1억 원 미만이면 “LOW”, 1억 원 이상이면 “HIGH”로 상위 10개만 조회해 본 결과는 다음과 같습니다.


SELECT
      job_id
    , salary
    , IF(salary < 10000, "LOW", "HIGH") AS result
FROM `HR.employees`
LIMIT 10;

6.2.2. IFNULL

조건이 NULL일 경우, 정의한 null_value를 반환합니다.


SELECT
			IFNULL(condition, null_value)
FROM Table1

조건의 데이터 유형과 동일한 유형의 값으로 null_value를 정의해야 합니다. 유형이 다를 경우, 아래와 같은 에러가 발생합니다.

예시

“employees” 테이블에서 “manager_id”가 NULL이면 0, 그 외는 “manager_id”로 출력되도록 상위 5개로 제한하여 조회합니다.


SELECT 
      manager_id
    , IFNULL(manager_id, 0) AS result
FROM `HR.employees`
LIMIT 5;

💡

IFNULL과 COALESCE(condition, null_result)는 동일한 기능을 수행하는 함수입니다.

6.2.3. NULLIF

첫 번째 인자와 두 번째 인자가 일치할 경우 NULL 값을 반환하고, 그 외의 경우에는 첫 번째 인자를 반환합니다. IFNULL과 마찬가지로 첫 번째 인자와 두 번째 인자는 같은 유형이어야 합니다.


SELECT 
			NULLIF(conditon, match_condition)
FROM Table1

예시

“employees” 테이블에서 “job_id”가 “IT_PROG”이면 NULL 값을 반환하고 그 외는 “job_id”가 출력되도록 상위 10개만 조회합니다.


SELECT 
      job_id
    , NULLIF(job_id, "IT_PROG") AS result
FROM `HR.employees`
LIMIT 10;