0. Intro
어느덧 마지막 주차 학습까지 왔다. 다음 주부터는 피그마를 배우고자 해서, 이번 주 안에 SQL 강의를 마치는 것이 나의 제1목표였는데, 무사히 성공한 듯싶다. 마지막 학습에서는 크게 3가지를 배웠다. 먼저, 데이터가 없거나 Outlier가 있을 경우에 대처하는 법을 배웠고, 이후 SQL에서도 피벗 테이블을 만드는 방법과 윈도우 함수 2가지, 그리고 날짜포맷함수를 학습했다. 바로 알아보자!
1. 데이터를 교정하는 방법
: 데이터를 교정하는 경우는, 데이터에 값이 없거나, 있어도 이상한 값이 있는 경우이다. 각각의 경우에 대처할 수 있는 다양한 방법을 알아보자.
1-1. 데이터에 아무 값이 없다면?
: 아래와 같이 데이터에 아무 값도 없는 경우가 있을 수 있다([NULL]은 데이터가 없다는 뜻)
- 이럴 때에는 두 가지 방법을 활용할 수 있는데, 첫째는 아예 해당 데이터를 제외해주는 것이고, 둘째는 다른 값으로 데이터를 대체해 주는 것이다.
●[방법 1] 없는 값을 제외해 주기
: 이는 단순하게 if문을 활용하면 된다.
- if(조회 컬럼<>'값 없음 표시', 조회컬럼, null)
-> 이 내용은 조회컬럼에 값이 없는 경우에는 null로 처리해서 이후 연산에서 제외해 주고, 값이 있으면 해당 값을 쓰라는 내용이다.
●[방법 2] 다른 값을 대신 사용하기
: 마찬가지로, if문을 활용해도 되고, 혹은 coalesce() 함수를 사용해도 된다. 마찬가지로, rating과 age라는 열값이 없는 예시로 살펴보자.
1-2. 데이터에 이상치(Outlier)가 있다면?
: 아래와 같이 데이터에 상식적이지 않은 값이 있을 수 있다
●[방법] 조건문으로 값의 범위를 지정하기
: 이건 조건문을 통해, 가장 큰 값 혹은 가장 작은 값의 범위를 지정해 버리는 것이 방법이 된다.
- 예를 들어, 위의 나이의 경우에는 아래와 같이 범위를 지정해 줄 수 있다.
2. SQL로 Pivot Table 만들기
: 엑셀에서만 사용하던 피벗 테이블을 SQL에서도 구현할 수 있는데, 일단 피벗테이블이란 무엇인지부터 정리해 보면 다음과 같다.
- 이를 바로 예시를 통해, 어떻게 피벗테이블을 만들어 내는지 알아보자
● 예제1: 음식점별 시간별 주문건수 Pivot Table 뷰 만들기(15~20시 사이, 20시 주문건수 기준 내림차순)
- 당연히, 피벗테이블을 만들기 전에 '음식점별 시간별 주문건수를 구하는 Base 쿼리를 먼저 짜줘야 한다.
- 음식점별 데이터는 food_orders에, 주문시간 데이터는 paymets 테이블에 있으니, 이를 먼저 Join 해준다.
- 이후, 음식점 주문시간 데이터에서 '주문 시'만 필요하므로, substr을 이용해 시각만 빼온다.
- 완성된 Base 쿼리를 서브쿼리 삼아, 피벗테이블(집계기준: 음식점명, 구분컬럼: 시간)을 만드는 본 쿼리를 짜준다.
- 이후, 위 조건에 따라 범주화 및 정렬해 준다.
위의 로직에 따라, 다음과 같이 쿼리를 작성해 주면 된다.
1) 두 개의 테이블을 inner join(누락되는 데이터는 필요없기 때문)
select *
from food_orders f inner join payments p on f.order_id=p.order_id
2) payments의 time에서 '주문 시'만 가져옴(15시~20시만 필요하므로 where절 이용)
- 나머지 필요열들도 불러오고, 범주화
select restaurant_name,
substring(p.time, 1, 2) hh,
count(1) cnt_order
from food_orders f inner join payments p on f.order_id=p.order_id
where substring(p.time, 1, 2) between 15 and 20
group by 1, 2
3) 피벗테이블을 만드는 쿼리 짜주기
- 집계기준(행축)이 되는 값은 '음식점 이름'이므로, 첫 열로 선택
- 뒤의 각각의 구분컬럼들은 if문으로 만들기(이때, max()함수 앞에 붙이기*)
*이유는 아직 밝혀지지 않음
select restaurant_name,
max(if(hh='15', cnt_order, 0)) "15",
max(if(hh='16', cnt_order, 0)) "16",
max(if(hh='17', cnt_order, 0)) "17",
max(if(hh='18', cnt_order, 0)) "18",
max(if(hh='19', cnt_order, 0)) "19",
max(if(hh='20', cnt_order, 0)) "20"
from
4) 위에 작성한 Base쿼리를 서브쿼리로 삼기
- 마찬가지로 음식점별로 분류할 것이므로 범주화 진행
- '20시'열 기준 내림차순이므로, 정렬 진행
select restaurant_name,
max(if(hh='15', cnt_order, 0)) "15",
max(if(hh='16', cnt_order, 0)) "16",
max(if(hh='17', cnt_order, 0)) "17",
max(if(hh='18', cnt_order, 0)) "18",
max(if(hh='19', cnt_order, 0)) "19",
max(if(hh='20', cnt_order, 0)) "20"
from
(
select restaurant_name,
substring(p.time, 1, 2) hh,
count(1) cnt_order
from food_orders f inner join payments p on f.order_id=p.order_id
where substring(p.time, 1, 2) between 15 and 20
group by 1, 2
) a
group by 1
order by 7 desc
- 그럼 다음과 같은 조회결과를 볼 수 있다.
3. Window Function - (RANK, SUM)
: Window Function은 각 행의 관계를 정의하기 위한 함수로 그룹 내의 연산을 쉽게 만들어준다. 예를 들어, 주문건수 순으로 순위를 매기거나, 전체주문 중 특정 식당주문이 차지하는 비율을 구할 때 사용할 수 있다. 이 중, 2가지 함수만 알아볼 것이기에 바로 예제와 함께 정리해 보자.
3-1. RANK()
: '특정 기준으로 순위를 매겨주는' 기능을 지닌 함수이다.
- 기본구조: rank() over (partiotion by '묶어줄 기준열' order by '순위의 기준열')
● 예제 2: 음식 타입별로 주문 건수가 가장 많은 상점 3개씩(3순위까지) 조회하기
- 먼저, 음식 타입별, 음식점별 주문 건수를 집계한다.
- Rank 함수를 적용하여 순위를 구한 열을 추가한다.
- 3위까지만 조회해서, 음식타입별, 순위별로 정렬한다.
1) 음식타입별, 음식점별 주문 건수 집계하는 쿼리를 짠다.
select cuisine_type, restaurant_name, count(1) order_count
from food_orders
group by 1, 2
2) 위 쿼리를 서브쿼리 삼아, Rank함수를 이용해 순위를 집계하는 열을 추가하는 쿼리를 짠다.
select cuisine_type,
restaurant_name,
rank() over (partition by cuisine_type order by order_count desc) rn,
order_count
from
(
select cuisine_type, restaurant_name, count(1) order_count
from food_orders
group by 1, 2
) a
3) 다시 한번 위 커리를 서브쿼리 삼아서, 3위까지만 조회하고, 재정렬 한다.
select cuisine_type,
restaurant_name,
order_count,
rn "순위"
from
(
select cuisine_type,
restaurant_name,
rank() over (partition by cuisine_type order by order_count desc) rn,
order_count
from
(
select cuisine_type, restaurant_name, count(1) order_count
from food_orders
group by 1, 2
) a
) b
where rn<=3
order by 1,4
- 그럼 다음과 같이 목표했던 결과를 도출할 수 있다.
3-2. SUM()
: 기존에 알던 SUM함수와 다르지 않다. 다만, 누적합을 구하거나 카테고리별 합계컬럼과 원본컬럼을 함께 이용할 때 유용하게 사용할 수 있다.
- 기본구조: sum() over (partition by '묶어줄 기준열' order by '누적순서로 삼을 열')
● 예제 3: 각 음식점의 주문 건이 해당 음식 타입에서 차지하는 비율을 구하고, 주문건이 낮은 순 정렬 시, 누적 합 구하기
- 먼저, 음식 타입별, 음식점별 주문 건수를 집계한다.
- 카테고리별 합, 카테고리별 누적합을 구한다.
-> 카테고리별로 정렬해서, 각 음식점의 주문건이 해당 음식 타입에서 차지하는 비율을 유추할 수 있게 한다.
1) 음식 타입별, 음식점별 주문 건수를 집계하는 Base 쿼리를 짠다.
select cuisine_type,
restaurant_name,
count(1) cnt_order
from food_orders
group by 1,2
2) 위 쿼리를 서브쿼리 삼아, 카테고리별 합, 누적합을 구하는 본 쿼리를 짠다(음식 타입, 주문 건수로 정렬까지).
select cuisine_type,
restaurant_name,
cnt_order,
sum(cnt_order) over (partition by cuisine_type) sum_cuisine,
sum(cnt_order) over (partition by cuisine_type order by cnt_order) cum_cuisine
from
(
select cuisine_type,
restaurant_name,
count(1) cnt_order
from food_orders
group by 1,2
)a
order by cuisine_type, cnt_order
- 그럼 다음과 같은 조회결과를 확인할 수 있다.
4. 날짜 포맷함수
: 정수, 문자열처럼 날짜 데이터도 특정한 타입을 지니고 있다. 기존의 숫자 데이터를 날짜 데이터로 변환하는 함수는 date() 함수이다. 날짜 데이터로 변환뿐만 아니라, 날짜에서 년, 월, 일, 요일 데이터만을 뽑아낼 수도 있다.
4-1. 날짜 포맷
- date('바꿀 컬럼')
4-2. 날짜 구성요소 추출
- date_format('날짜 데이터 컬럼', '%Y')
*이때 %뒤에 오는 값은, 년(Y,y), 월(M,m), 일(d,e), 요일(w)
4-3. 날짜 연산
- 이건 과제를 진행하다 구글링으로 배우게 된 문법인데, Interval과 date_sub()등의 함수를 이용해 날짜 연산을 할 수 있다.
● 예제 4: 연도별 3월의 주문건수 집계하기
1) 먼저, 년도, 월을 포함하여 데이터를 가공한다.
select date_format(date(date), '%Y') y,
date_format(date(date), '%m') m,
order_id
from food_orders a inner join payments b on a.order_id=b.order_id
2) 년도, 월별 주문건수를 구한다.
select date_format(date(date), '%Y') y,
date_format(date(date), '%m') m,
order_id
from food_orders a inner join payments b on a.order_id=b.order_id
group by 1, 2
3) 3월을 조건으로 지정해 주고, 연도별로 정렬한다.
select date_format(date(date), '%Y') y,
date_format(date(date), '%m') m,
count(1) order_count
from food_orders a inner join payments b on a.order_id=b.order_id
where date_format(date(date), '%m')='03'
group by 1, 2
order by 1
- 그럼 다음과 같은 조회결과를 확인할 수 있다.
한 줄 코멘트: 이렇게 SQL 기초강의가 모두 끝났다. 생각보다 후딱 지나갔고, 어려운 부분 없이 잘 마친 것 같다. 데이터를 목적에 맞게 활용하려면, 그 첫 번째는 잘 뽑아내서 쓰는 것이니까 게을리하지 않고 계속 복습해야겠다.
'[내일배움캠프(PM2기_사전캠프)] > SQL기초' 카테고리의 다른 글
| [사전캠프 Day 9] SQL 기초강의 Week4 (0) | 2025.03.20 |
|---|---|
| [사전캠프 Day 8] SQL 기초강의 Week 3 (0) | 2025.03.19 |
| [사전캠프 Day 7] SQL 기초강의 Week 1~2 (0) | 2025.03.18 |