SQL : CASE로 pivot하기

CASE 절을 이용해서 테이블을 피벗하는 방법에 대하여!

- 예제 : 리트코드 1179

- 예제 : 리트코드 1193

---

 

LeetCode 1179. Reformat Department Table 문제 이용

 

Table : Department

Column Name	Type
id	int
revenue	int
month	varchar

달별 각 부서 (department)의 revenue 값이 기록되어 있는 테이블이다. 

month의 values are in ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]

 

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

즉, 각 부서별로 행을 만들어서 1월 수익, 2월 수익, ... , 12월 수익이 열로 나열되도록 테이블을 새로 만들으라는 뜻. 

 

---

 

Input & Output 예시

 

 

첫번째 열은 id이니까, 일단 id를 select 해놓고 

그 다음 두번째 열부터 달별 (1월 ~ 12월) 수익의 합을 값으로 입력해야 하는데 

id별로 그 값은 한 번씩 나와야 하니까 

마지막에 'GROUP BY' id를 해야 한다는 것을 알 수 있을 것이다. 

 

월별 수익의 합은

처음 input 테이블에서 

첫번째 행부터 한 칸씩 내려가면서

month열의 값이 해당하는 달 (Jan / Feb / ... )일 때는 revenue(값)을 돌려주고

그렇지 않으면 NULL을 돌려주는 것으로 한다. 

그 다음에 모두 합해주면 ID별, 달별 수익의 합을 나열할 수 있을 것이다. 

 

이것을 쿼리로 작성해보면 아래와 같다. 

중요한 부분은, CASE 절 쓴다는 것과 

GROUP BY 함수와 함께 사용하기 위해 Aggregation 함수로 'SUM'을 같이 이용해준다는 것이다!

 

---

 

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

 

---

 

LeetCode 1193. Monthly Transactions I  문제 이용

 

문제 설명 :

 

 

거래 월 & 나라 별로 그룹지어서, 거래 횟수, **승인된** 거래횟수, 거래 금액, **승인된** 거래금액으로 계산하는 문제 

 

 

문제 접근 :

 

• 일단 거래 일자가 year - month - date로 나와있기 때문에 DATE_FORMAT함수 또는 SUBSTRING 함수를 이용하여 year - month 단위로 끊어줘야 함
• 모든 거래는 그냥 count, sum 해주면 되지만, 
• **승인**된 거래끼리만 따로 aggregate 해주기 위해서는 CASE WHEN pivot을 활용한다. 

 

코드 : 

 

SELECT

 

   DATE_FORMAT(trans_date, '%Y-%m') AS month,

   country,

 

   COUNT(*) AS trans_count,

   SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 END) AS approved_count,

 

   SUM(amount) AS trans_total_amount,

   SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END) AS approved_total_amount

 

FROM Transactions

 

GROUP BY month, country ;