[TIL] SQL - 조건문

Core Concepts

CASE WHEN

• 여러 조건이 있을 때 사용한다.
• Syntax

  CASE
      WHEN condition1 THEN result1
      WHEN condition2 THEN result2
      WHEN conditionN THEN resultN
      ELSE result
  END;

• 조건1, 조건2에 둘 다 해당하면 앞선 순서를 따른다. (뒤의 조건은 확인하지 않는다.)
• 예

  CASE
      WHEN score >= 90 THEN 'A'
      WHEN score >= 80 THEN 'B'
      WHEN score >= 70 THEN 'C'
      WHEN score >= 60 THEN 'D'
      ELSE 'E'
  END;

  score가 94점인 경우, A에 해당한다.

  CASE
      WHEN score >= 50 THEN 'E'
      WHEN score >= 60 THEN 'D'
      WHEN score >= 70 THEN 'C'
      WHEN score >= 80 THEN 'B'
      ELSE 'A'
  END;

  score가 94점인 경우 E에 해당한다.

IF

• 조건이 1개일 때 사용한다.
• Syntax

  IF(condition, value_if_true, value_if_false)

• 예

  IF(score >= 70, '통과', '과락')

What I Learned

MySQL에서 보지 못한 함수들이 많이 존재한다!

• BigQuery는 분석(OLAP) 을 목적으로 설계된 데이터 웨어하우스이다.
• 그래서 집계, 비율, 조건부 계산을 한 줄로 처리할 수 있는 함수가 풍부하다.
• MySQL은 기본적으로 OLTP (온라인 트랜잭션 처리) 용 DB이다.
  • CRUD 중심
  • 정합성, 동시성, 트랜잭션 안정성 중시
• 분석용으로 자주 쓰이는 조건부 집계 함수는 거의 없다.
• BigQuery에서의 코드 - COUNTIF

  # 트레이너 별로 풀어준 포켓몬의 비율이 20%가 넘는 포켓몬 트레이너는 누구일까요?
  SELECT
    trainer_id,
    SAFE_DIVIDE(
      COUNTIF(status = 'Released'),
      COUNT(*)
    ) AS released_ratio
  FROM 
    basic.trainer_pokemon
  GROUP BY 
    trainer_id
  HAVING 
    released_ratio > 0.2;

• 같은 연산을 MySQL에서 한다면

  SUM(
    IF(status = 'Released', 1, 0)
  ) AS released_cnt

Confusing Points

alias의 사용 가능 구절

그동안 MySQL 환경에서 코딩테스트 문제를 풀 때 SEELCT절에서 사용하는 alias를 GROUP BY와 HAVING 절에서 자연스럽게 사용하고 있었는데 생각해보니 SQL 문법의 논리적 순서는 다음과 같았다.

1. FROM
2. WHERE
3. HAVING
4. GROUP BY
5. SELECT

따라서 WHERE에서 alias를 사용하면 오류가 난다!
하지만 왜 GROUP BY와 HAVING 절에서는 alias를 사용했을 때 오류가 안나는가?라는 의문이 들었다. 그래서 공식 문서를 찾아보았다.

MySQL 공식 문서

The MySQL extension permits the use of an alias in the HAVING clause for the aggregated column:

SELECT name, COUNT(name) AS c FROM orders
  GROUP BY name
  HAVING c = 1;

Standard SQL permits only column expressions in GROUP BY clauses, so a statement such as this is invalid because FLOOR(value/100) is a noncolumn expression:

SELECT id, FLOOR(value/100)
  FROM tbl_name
  GROUP BY id, FLOOR(value/100);

표준 SQL이라면 허용하지 않는 기능이지만, MySQL 등 다양한 환경에서 확장된 기능을 제공하는 것이었다! (Big Query도 마찬가지)

COUNT(*) vs COUNT(id)

1. COUNT(*)

• 행의 개수를 센다
• 특정 컬럼의 NULL 여부와 무관하다.

2. COUNT(id)

• 해당 컬럼이 NULL이 아닌 행만 센다
• 컬럼의 존재 여부가 의미를 가질 때 사용한다.

Today’s Insight

같은 SQL이라도 “왜 이 환경에서는 되는가”를 설명할 수 있어야 쿼리를 제대로 이해했다고 말할 수 있다는 것을 느꼈다.

Next Step

• CASE WHEN, IF를 활용한 조건부 집계 SQL 문제를 추가로 풀어본다.