[SQL] #2. CASE WHEN

안녕하세요! 오늘은 mySQL의 필수적인 함수 CASE WHEN에 대해 알아보고 관련 문제를 풀어보는 포스팅을 하고자 합니다. (집계, 정렬과 같은 mySQL의 매우 기초적인 내용은 다루지 않을 예정입니다.)

1️⃣ CASE WHEN

CASE WHEN은 mySQL에서 조건문 역할을 하는 함수입니다. SQL이 대개 SELECT - FROM - WHERE 이러한 구조로 이루어지는데, CASE WHEN은 SELECT문에서 쓰입니다.

CASE WHEN의 기본적인 구조는 다음과 같습니다.

• CASE - END 사이에 WHEN에 조건절을 넣어 해당 조건 1이 만족하면 결과 1을, 조건 1을 만족하지 않으면 결과 1* 를 출력합니다.
• ELSE 다음에 아무것도 지정을 하지 않으면 NULL로 자동 지정됩니다.

  SELECT *
      CASE 
          WHEN (조건 1) THEN (결과 1) ELSE (결과 1*) 
          .
          .
          .
          .
      END AS (ALIAS명)
  FROM table
  

2️⃣ HackerRank 예제

HackerRank, LeetCode에 있는 CASE WHEN 기본 예제를 풀어보겠습니다.

(1) Type of Triangle

Problem
Write a query identifying the type of each record in the TRIANGLES table using its three side lengths. Output one of the following statements for each record in the table:

• Equilateral: It’s a triangle with sides of equal length.
• Isosceles: It’s a triangle with sides of equal length.
• Scalene: It’s a triangle with sides of differing lengths.
• Not A Triangle: The given values of A, B, and C don’t form a triangle.

Solution

SELECT CASE
           WHEN A+B <= C OR A+C <= B OR B+C <= A THEN 'Not A Triangle' --(1)
           WHEN A=B AND B=C THEN 'Equilateral' --(2)
           WHEN A=B OR A=C OR B=C THEN 'Isosceles' --(3)
           ELSE 'Scalene'
       END
FROM triangles

이 문제의 데이터는 삼각형 세 변의 길이를 $A, B, C$ 라는 세 변수로 저장합니다.

이 문제를 풀 때 주의할 점은 CASE WHEN 을 먼저 쓸수록 우선조건이 된다 라는 것입니다. 앞에 있는 WHEN 절부터 차례로 처리하는 것이죠!

output은 Equilateral, Isosceles, Scalene, Not A Triangle 이렇게 세 가지 삼각형 종류 혹은 삼각형이 아니다를 출력합니다. 이 문제를 효율적으로 풀기 위해 다음과 같은 단계로 진행합니다.

1. Not A Triangle 먼저 분류하기
2. Equilateral 분류
3. Isosceles 분류
4. Scalene 분류

1번의 경우, 삼각형이 성립되지 않는 경우를 먼저 처리해야 나머지 삼각형의 종류를 분류하는데 쉽기 때문에 먼저 처리해줍니다. 삼각형이 성립되지 않는 경우는 두 변의 길이의 합 < 나머지 한 변의 길이 인 경우이므로 WHEN 조건절에 (1) 과 같이 쿼리를 작성합니다.

이제 삼각형 분류로 넘어가보겠습니다. 삼각형의 포함관계는 정삼각형(Equilateral) < 이등변삼각형(Isosceles) < 기타 삼각형(Scalene) 입니다. 만약 기타 삼각형(Scalene) 먼저 처리한다면, 이 삼각형의 조건은 정삼각형, 이등변삼각형의 조건까지 만족하기 때문에 정삼각형, 이등변삼각형에 해당하는 데이터도 모두 기타 삼각형(Scalene)으로 출력합니다. 따라서, 조건이 가장 까다로운 정삼각형 먼저 처리하고, 그 다음으로는 이등변삼각형, 기타 삼각형 순서대로 처리합니다. 정삼각형에 대한 조건은 (2), 이등변삼각형에 대한 조건은 (3)과 같이 설정하고 나머지 경우는 모두 기타 삼각형이므로 ELSE에 지정합니다.

(2) Reformat Department Table

Problem
Write an SQL query to reformat the table such that there is a department id column and a revenue column for each month.

Department table:
| id | revenue | month | |:— | :— | :— | | 1 | 8000 | Jan | | 2 | 9000 | Jan | | 3 | 10000 | Feb | | 1 | 7000 | Feb | | 1 | 6000 | Mar |

Result table:
| id | Jan_Revenue | Feb_Revenue | Mar_Revenue | … | Dec_Revenue | |:— | :— | :— |:— | :— | :— | | 1 | 8000 | 7000 | 6000 | … | null | | 2 | 9000 | null | null | … | null | | 3 | null | 10000 | null | … | null |

Solution

SELECT id, SUM(CASE WHEN month = 'Jan' THEN revenue ELSE NULL END) AS Jan_Revenue,
           SUM(CASE WHEN month = 'Feb' THEN revenue ELSE NULL END) AS Feb_Revenue,
           SUM(CASE WHEN month = 'Mar' THEN revenue ELSE NULL END) AS Mar_Revenue,
           SUM(CASE WHEN month = 'Apr' THEN revenue ELSE NULL END) AS Apr_Revenue,
           SUM(CASE WHEN month = 'May' THEN revenue ELSE NULL END) AS May_Revenue,
           SUM(CASE WHEN month = 'Jun' THEN revenue ELSE NULL END) AS Jun_Revenue,
           SUM(CASE WHEN month = 'Jul' THEN revenue ELSE NULL END) AS Jul_Revenue,
           SUM(CASE WHEN month = 'Aug' THEN revenue ELSE NULL END) AS Aug_Revenue,
           SUM(CASE WHEN month = 'Sep' THEN revenue ELSE NULL END) AS Sep_Revenue,
           SUM(CASE WHEN month = 'Oct' THEN revenue ELSE NULL END) AS Oct_Revenue,
           SUM(CASE WHEN month = 'Nov' THEN revenue ELSE NULL END) AS Nov_Revenue,
           SUM(CASE WHEN month = 'Dec' THEN revenue ELSE NULL END) AS Dec_Revenue
FROM department
GROUP BY id

이 문제는 기존 Department table에 있는 revenue, month를 이용해 Result table 처럼 월별 revenue를 칼럼으로 가지는 table를 출력하는 문제입니다.

먼저 Result table을 보면 id가 1, 2, 3 한 번씩 출력되는데 이는 Department table의 id가 1, 2, 3, 1, 1 처럼 입력되어있는 것과 다르다. Result table 처럼 출력하기 위해서는 id = 1 에 해당하는 데이터끼리, id = 2 에 해당하는 데이터끼리 CASE WHEN 을 지정하여 각각 월별 Revenue를 출려갛게 하면 됩니다. 이를 위해 GROUP BY 를 이용해 id 별로 grouping을 해준 후, CASE WHEN을 진행합니다.

그리고 CASE WHEN을 이용하여 month = ‘JAN’ 처럼 조건을 12번 넣어주면 되는데 여기서 주의할 점은, result table에서 칼럼명이 Jan_Revenue, Feb_Revenue 같이 출력되어야 한다는 것입니다. 이러한 경우 우리는 ALIAS 를 사용해 칼럼명을 지정해줍니다. 또 해당 월을 만족하면 그 월에 해당하는 revenue를 출력하고, 그게 아니면 result table에 나와있듯이 NULL을 출력합니다.
$\Rightarrow$ SUM(CASE WHEN month = ‘Jan’ THEN revenue ELSE NULL END) AS Jan_Revenue