[D+5] 엑셀보다 쉬운 SQL(5) - 기준 밖의 데이터 처리, 피벗 테이블 제작, 윈도우 함수(rank, sum)

🎉 (경) 엑셀보다 쉬운 SQL 완강!! (축)  🎉

 

✏️ STUDY INDEX

1. 기준 밖의 데이터 처리 (NULL 값, 비상식적인 데이터) 
2. SQL 피벗테이블(Pivot Table) 제작하기 
3. 윈도우 함수 Window Function 
  - ① N번째까지 대상을 조회하고 싶을 때 rank() over() 
  - ② 전체에서 차지하는 비율, 누적합을 구하고 싶을 때 sum() over()

 

1. 기준 밖의 데이터 처리 (NULL 값, 비상식적인 데이터) 

데이터를 조회 또는 집계하다 보면

null(데이터 없음) 값이나 비상식적인 데이터(ex. 고객 나이 2000살)가 존재하는 경우가 있습니다.

이렇게 기준 밖에 있는 데이터 처리하는 방법을 소개해드릴게요. 

방법	구체적인 해결법	예시
① 없는 값 제거하기	없는 데이터 null 값으로 반환하기 IF (컬럼명 <> 'no data', 컬럼명, NULL)	select gender,  avg(if (age <> 'nodata', age, null)) avg_age from user_info group by 1
	NULL 값 자체를 제외하기 WEHRE 컬럼명 IS NOT NULL	select name, age, gender  from user_info where age is not null
② 다른 값으로 대체하기	NULL 값에 다른 데이터 넣기  COALESCE (컬럼명, 대체값)	select name, price, coalresce (age, 20) 'null을 대체한 나이' from user_info
③ 비상식적 값 해결하기	조건문을 활용한 기준 범위 지정 CASE WHEN 기준 THEN 결과값 ...END	select name,  case when age < 15 then 15           when age > 80 then 80          else age end '범위를 지정해준 나이' from user_info

 

* <> 는 '같지 않다'라는 뜻입니다.  

* 1-1번 해결방법은 연산할 때 많이 사용한다고 합니다.

  My sql은 연산시 사용할 수 없는 값은 0으로 간주하기 때문에 null로 연산에서 제외해 주려고요! 

 

2. SQL 피벗 테이블(Pivot Table) 제작하기 

엑셀에서 자주 사용하는 피벗 테이블, SQL에서도 만들 수 있습니다! 

피벗 테이블은 2개 이상의 기준으로 데이터를 집계할 때 보기 쉽게 배열하여 보여주는 것인데요. 

열(세로)에는 집계 기준이, 행(가로)에는 구분 컬럼이 들어가는 형태입니다. 글로 쓰여있으니깐, 이해하기 어렵죠?

아래 제가 강의 실습에서 짰던 쿼리를 소개드리며, 어떻게 만드는지 알려드릴게요! 

 

✔️ 피벗 테이블을 만드는 순서 

• ① 베이스 데이터 만들기 
• ② 피벗 뷰 제작해 주기 

위 순서에 맞춰 [성별, 나이별 주문건수를 집계한 피벗 테이블]을 만들어 보겠습니다! (*10~59세 사이만 집계)

① 피벗 테이블 안에 들어가는 베이스 데이터를 만들어주겠습니다. 

-- step 1. 베이스 데이터 제작하기 
-- 1) 다른 테이블에 있는 데이터를 조회하기 위한 jion 사용 
-- 2) 성별, 주문건수(count()), 연령대별로 구간 나누기 
-- 3) 10~59세로 조건 주기 where, 그룹별에 맞춰 데이터 집계하기 group by

SELECT c.gender as gender, 
	count(*) as cnt_orders,
    case when c.age between 10 and 19 then 10
    	when c.age between 20 and 29 then 20
        when c.age between 30 and 39 then 30
        when c.age between 40 and 49 then 40
        when c.age between 50 and 59 then 50 end age 
FROM food_orders f inner join customers c on f.customer_id = c.customer_id
WHERE c.age BETWEEN 10 and 59  
group by 1,3

 

② 베이스 데이터는 서브 쿼리로 넣어주고, 이제 피벗 뷰를 만들어볼게요. 

-- STEP 2. 피벗뷰 만들어주기 
-- 1) 가장 첫번째 컬럼 = 행 데이터, 그 외는 열 데이터가 됩니다. 
-- 2) 중요한 것은 열 데이터는 max()로 감싸줘야 합니다. 

SELECT gender, 
	   max(if(age = 10, cnt_orders, 0)) '10대', 
	   max(if(age = 20, cnt_orders, 0)) '20대', 
	   max(if(age = 30, cnt_orders, 0)) '30대', 
	   max(if(age = 40, cnt_orders, 0)) '40대', 
	   max(if(age = 50, cnt_orders, 0)) '50대' 
FROM 
	(SELECT c.gender as gender, 
		   count(*) as cnt_orders,
		   case when c.age between 10 and 19 then 10
		   	    when c.age between 20 and 29 then 20
		   	    when c.age between 30 and 39 then 30
		   	    when c.age between 40 and 49 then 40
		   	    when c.age between 50 and 59 then 50 end age 
	FROM food_orders f inner join customers c on f.customer_id = c.customer_id
	WHERE c.age BETWEEN 10 and 59  
	group by 1,3)a

 

그럼 아래와 같은 피벗 테이블을 얻으실 수 있습니다.

이제 와서 생각하지만 행을 나이대로 구분했어도 괜찮았을 것 같네요. 

성별, 나이대별 주문건수를 나타내는 피벗테이블

 

3. 윈도우 함수 Window function (rank, sum 활용) 

윈도우 함수(Window function)는 관련 행의 그룹 연산을 용이하게 하는 함수로

데이터 분석, 집계, 순위 매기는 등의 작업을 수행할 때 사용합니다.  

 

group by 절은 열을 기준으로 여러 행의 데이터를 한 번에 집계하여 보여준다면,

윈도우 함수는 정의한 그룹 기준(범위)에 따른 집계 데이터를 볼 수 있어요. 

윈도우하면 생각나는 창문🪟과 같이 각 그룹의 연산을 수행한다고 이해해 주시면 됩니다!

윈도우 함수 사용법은 아래와 같고요. 무조건 over() 와 쌍을 지어 주셔야 해요. 

-- 윈도우 함수 사용법 
함수() OVER (PARTITION BY 그룹 기준 컬럼 ORDER BY 정렬 기준 컬럼)

 

윈도우 함수의 종류는 SUM(), MAX(), RANK(), LAG() 등 매우 다양한데요, 

이 중 많이 활용하는 SUM()과 RANK() 예제를 통해 어떤 식으로 사용되는지 소개해드릴게요. 

어떤 순서로 쿼리를 짰는지는 코드 블럭 안에 함께 정리해 두었습니다! 

 

① N번째까지 대상을 조회하고 싶을 때 - Rank 

-- 음식 타입별로, 준비 및 배달 평균 시간이 가장 빠른 상점 1~3순위를 뽑아보자!
-- 1) 베이스 데이터 제작 : 음식타입, 음식점, 평균 제작 시간 
-- 2) 음식 타입에 따라 평균 제작시간이 적은 것의 순위 매기기 rank() over() 
-- 3) 최종적으로 where 절을 활용하여 3순위 이내의 데이터만 조회하기 

SELECT cuisine_type,restaurant_name, time_rank, avg_time
from (
	SELECT cuisine_type,restaurant_name, 
		rank() over (partition by cuisine_type order by avg_time) as time_rank,
		avg_time
	from(
		select cuisine_type, restaurant_name, 
			avg(food_preparation_time+delivery_time) as avg_time
		from food_orders
		group by 1, 2)a )b
WHERE time_rank <= 3

 

② 전체에서 차지하는 비율, 누적합을 구하고 싶을 때 - Sum 

sum 은 합계를 구하는 함수지만, 누적합이나 카테고리별 합계를 구하고자 할 때 윈도우 함수 형태로 사용할 수 있습니다. 

-- 음식타입별, 음식점별 매출 집계하고, 누적 매출합 구해보기
-- 1) 데이터 베이스 제작 : 음식 타입, 음식점, 매출 sum(price * quantity) - group by
-- 2) 누적 합계 집계해보기 sum() over() *정렬기준을 설정할때 누적합을 구할 수 있음. 

SELECT cuisine_type '음식 타입', 
	restaurant_name '매장명', 
	rev '매장 매출', 
	sum(rev)over(partition by cuisine_type) '음식 타입별 총매출',
	sum(rev)over(partition by cuisine_type order by rev, restaurant_name) '음식 타입별 누적 매출'
FROM 
	(SELECT cuisine_type, 
		restaurant_name,
		sum(price*quantity) as rev
	FROM food_orders 
	GROUP BY 1, 2) a

 

sum() over()에 정렬 기준 적용하면 누적합을 구할 수 있는데요! 정렬을 적용하지 않으면 타입별 총매출을 집계합니다. 아래 쿼리 결과 이미지를 첨부해 드릴게요. 참고하시면 어떻게 결과가 나왔는지 이해하실 수 있을 거예요! 

 

---

 

오늘의 공부는 여기까지 입니다. 

 

🔥드.디.어 [ 엑셀보다 쉬운 SQL ] 강의를 완강했습니다🔥

확실히 마지막 챕터이다 보니 이전 내용보다 훨씬 어렵네요.

오늘 블로그를 작성하며 복습했지만 여전히 공부가 더 필요한 개념이라고 생각됩니다. 

주말에 한번 더 강의를 돌려봐야 할 것 같아요! 어제는 서브 쿼리가 어려웠지만, 윈도우 함수나 피벗 테이블 등 더 어려운 개념이 생기니 서브쿼리는 상대적으로 쉽고 귀여운 개념이 되었네요 😂 

 

저는 처음에 기초 강의를 수강하면서 '대체 이렇게 추출한 데이터를 어떻게 실무에 적용한다는 거지?' 많은 고민이 되었습니다. 쿼리를 짜서 데이터를 집계하는 것도 힘들지만, 이걸 대체 어떻게 활용할 수 있을지 상상이 되지 않았거든요. 그런데 오늘 다른 분들이 진행한 데이터 분석 프로젝트를 보면서, 이 스킬을 앞으로 어떻게 실무에 적용할 수 있을지 감이 잡히기 시작했습니다! 데이터 전처리, 분석을 토대로 비즈니스에 적용할 수 있는 내용을 도출한 프로젝트였거든요! 

 

강의를 들으면서 생각했던 것은 '필요한 데이터를 명확히 정의 및 집계하고, 가공해서 유의미한 인사이트를 도출하는 게 가장 중요하다'라고 느낍니다. 지금은 강사님이 어떤 데이터를 어떤 형태로 추출해 보라고 명확히 안내해 주시지만, 실무에서는 그 역할 또한 스스로 해야 할 일이 될 테니까요!

 

하루가 다르게 SQL 난이도가 급증가 한다고 느끼는 요즘입니다.  

하지만 어제는 서브쿼리가 어려웠지만 오늘은 윈도우 함수가 제일 어려워진 것처럼, 

더 어려운 상위 개념으로 '이건 아직 어려운게 아니란다^^' 잘 속여가며

어렵게만 느껴지는 SQL을 잘 정복해보고 싶습니다! SQL 공부하시는 모든 분들 화이팅하세요🍀

 

*<엑셀보다 쉬운 SQL> 온라인 강의 5주 차 자발적 학습 노트입니다.