[백문이불여일타] 데이터 분석을 위한 중급 SQL - 2부

 

조건문 CASE

 

 

예시 1) 카테고리 id 값이 1이면 음료, 2면 조미료, 그 외의 카테고리는 기타로 조회

SELECT CASE
		WHEN categoryid = 1 THEN '음료'
        	WHEN categoryid = 2 THEN '조미료'
        	ELSE '기타'
       END
FROM Products

 

 

예시 2) 카테고리 별 평균 가격 조회

SELECT CASE
		WHEN categoryid = 1 THEN '음료'
       		WHEN categoryid = 2 THEN '소스'
            	ELSE '이외'
       END AS "new category"
       , AVG(Price)
FROM Products
GROUP BY "new category"

 

 

 

CASE를 활용한 테이블 피봇

 

예시 1) CategoryId = 1인 상품의 가격에 대해서 평균 값을 알고 싶은 경우

SELECT AVG(CASE WHEN categoryId = 1 THEN price ELSE NULL END) "category1 price"
FROM Products

 

예시 2) 카테고리 별로 평균 가격을 조회

SELECT AVG(CASE WHEN categoryId = 1 THEN price ELSE NULL END) "category1 price" 
	,AVG(CASE WHEN categoryId = 2 THEN price ELSE NULL END) "category2 price" 
        ,AVG(CASE WHEN categoryId = 3 THEN price ELSE NULL END) "category3 price" 
FROM Products

이렇게 평균값을 조회할 수 있는데, 결과값이 가로로 펼쳐져 있는 것을 pivoting 이라 한다.

 

예시 이미지는 더보기에 링크 참고

https://docs.tibco.com/pub/spotfire/6.5.1/doc/html/data/data_pivoting_data.htm

Pivoting Data

 

 

 

---

 

 

HACKER RANK

 

 

1. Type of Triangle

 

 

풀이과정은 아래 더보기 클릭

제일 쉬운 정사각형을 잡아내는 것 부터 시작하면 수월함

 

SELECT CASE
			WHEN a = b AND b = c THEN 'Equilateral'
			ELSE 'Other'
			END, a, b, c
FROM Triangles

 

이 질의문으로 먼저 정사각형이 잘 조회되고 있는지 3 변의 길이와 비교하며 확인

 

 

 

이등변 삼각형은 정삼각형과 겹치는 조건이 있기 때문에 Not A Triangle을 먼저 작성해주는 것이 좋음

즉, a = b AND b = c THEN 'Equilateral' 이라고 했을 때, a = b OR b = c OR a = c THEN 'Isosceles' 를 이등변 삼각형의 조건으로 넘겨주면 이등변 삼각형도 정삼각형에 포함된다.

 

그걸 방지하기 위해서 순서를 Equilateral을 먼저 조회로 필터링을 마치고, 그 다음에 Isosceles를 조회해주어야 한다.

CASE 절의 순서도 매우 중요!!

 

SELECT CASE
		WHEN a = b AND b = c THEN 'Equilateral'
            	WHEN a = b OR b = c OR a = c THEN 'Isosceles'
                ELSE 'Other'
       END, a, b, c
FROM Triangles

 

결과 값을 확인해보니 테이블에 20 20 40 이 이등변 삼각형에 포함된 것을 확인할 수 있다.

수식에 의하면 20 + 20 > 40 이 성립하지 않기 때문에 Not A Triangle에 포함되어야 하는 삼각형임을 알 수 있다.

 

이런 현상을 방지하기 위해서는 위에서 언급했듯이 CASE 문의 순서를 조절하여 삼각형이 아닌 것 부터 걸러주어야 한다.

 

<답>

 

SELECT CASE	
	        WHEN a = b AND b = c THEN 'Equilateral'
	        WHEN a + b <= c OR b + c <= a OR a + c <= b THEN 'Not A Triangle'
        	WHEN a = b OR b = c OR a = c THEN 'Isosceles'
        	ELSE 'Scalene'
       END
FROM Triangles

 

 

 

 

 

LEETCODE

 

1179. Reformat Department Table

 

문제 주소 - https://leetcode.com/problems/reformat-department-table/

Reformat Department Table - LeetCode

 

풀이는 더보기 클릭

1179. Reformat Department Table 는 CASE 문제로 각 부서의 월 수익을 pivoting하여 조회하는 것

 

이 문제에서 중요한 포인트는 단순히 수익을 합산하여 나타내는 것도 있겠지만, GROUP BY로 각 부서를 집단화 시키는 것이다.

즉, 부서의 id로 묶어서 집계함수 SUM을 사용하여 월 수익을 조회하면 된다.

 

<답>

 

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

 

 

---

 

< 기초 SQL 시리즈 >

 

2020/07/14 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 기초 SQL - 1부

2020/07/14 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 기초 SQL - 2부

2020/07/14 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 기초 SQL - 3부 完

 

< 중급 SQL 시리즈 >

 

2020/07/15 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 중급 SQL - 1부

2020/07/15 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 중급 SQL - 2부

2020/07/16 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 중급 SQL - 3부

2020/07/16 - [Database/SQL] - [백문이불여일타] 데이터 분석을 위한 중급 SQL - 4부 完

 

 

이 포스팅은 해당 강좌를 수강하며 작성하였습니다.

https://www.inflearn.com/course/%EB%8D%B0%EC%9D%B4%ED%84%B0-%EB%B6%84%EC%84%9D-%EC%A4%91%EA%B8%89-SQL

[백문이불여일타] 데이터 분석을 위한 중급 SQL - 인프런