1. 데이터 가공하기
우리가 실제로 마주하는 데이터들 중에는 테이블에 잘못된 값이 들어와있거나 조인을 했을 때 값이 비어있는(NULL값) 경우가 존재할 수 있다. 또는 도메인적으로 상식적으로 말이 되지 않는 값들이 들어있는 경우가 생길 수 있다. 이러한 경우 해결할 수 있는 쿼리문에 대해 예시와 함께 알아볼 것이다.
1.1 데이터가 없는 경우 제외(IF)
데이터가 없는 경우 IF문을 사용하여 데이터가 없을 경우 제외하는 쿼리문을 이용해 결측치를 제거할 수 있다
select restaurant_name,
avg(rating) avg_rating,
avg(if(rating <> "Not given",rating, NULL)) avg_rating2
from food_orders
group by 1
avg_rating이 결측치를 제외하지 않고 계산한 평균, avg_rating2이 결측치를 제외하고 계산한 평균이다.
결측치를 제외한 경우 평균 계산시 분모가 작아져 값이 커지는 모습을 볼 수 있다.
1.2 데이터가 없는 경우 다른 값 대체(COALESCE)
데이터가 없다고 해서 제거 할 경우 문제가 생길 수 있다. 이때 COALECSE를 이용해 원하는 값으로 대체가 가능하다
select a.order_id,
a.customer_id,
a.restaurant_name,
a.price,
b.name,
b.age,
coalesce(b.age, 20) "null 제거",
b.gender
from food_orders a left join customers b on a.customer_id=b.customer_id
where b.age is null
위는 두 테이블을 조인한 후 나이가 결측치인 값들에 대해서 데이터를 추출하는 쿼리문을 작성한 것이다. 이때 원래 b.age는 전부 null갑으로 되어있는데 'null 제거' 컬럼의 경우 결측치를 전부 20으로 대체하여 처리하였다.
1.3 상식적이지 않은 값을 가진 데이터의 처리(CASE WHEN)
위의 경우와 같이 보통 어떤 결제를 하는 고객의 나이는 20세 이상인데, 위 4번째 데이터와 같이 2세의 고객이주문을 하는 경우는 거의 없다고 할 수 있다. 이러한 경우 마찬가지로 조건문을 사용하여 처리할 수 있다
SELECT name, age,case when age < 15 then 15
when age >= 80 then 80
else age end as re_age
from customers
위와 같이 15세 미만이거나 80세 이상의 고객의 나이를 CASE WHEN을 이용해 각각 15와 80으로 처리할 수 있다.
2. PIVOT TABLE
PIVOT TABLE : 2개 이상의 기준으로 데이터를 집계할 때 보기 쉽게 배열하여 보여주는 것
2.1 기본 구조
2.2 예제
PIVOT TABLE을 만들 땐 피벗 테이블의 기본이 되는 BASE DATA를 만들고 진행하는 것이 좋다
BASE DATA의 컬럼은 집계 기준 컬럼, 구분컬럼, 데이터 카운트 컬럼으로 구성되어 있다.
# 1. base data
select gender,
case when age between 10 and 19 then 10
when age between 20 and 29 then 20
when age between 30 and 39 then 30
when age between 40 and 49 then 40
when age between 50 and 59 then 50 end age,
count(1) cnt_order
from food_orders f inner join customers c on f.customer_id = c.customer_id
where age between 10 and 59
group by 1,2
위와 같이 집계 기준을 남/녀, 구분 컬럼을 나이(10,20,30,40,50대)로 한 후 각 기준에 해당하는 데이터의 개수 순서대로 base data를 구성한다.
# 2. pivot table
SELECT age,
max(if(gender = 'male', cnt_order,0)) 'male',
max(if(gender = 'female', cnt_order,0)) 'female'
FROM
(
select gender,
case when age between 10 and 19 then 10
when age between 20 and 29 then 20
when age between 30 and 39 then 30
when age between 40 and 49 then 40
when age between 50 and 59 then 50 end age,
count(1) cnt_order
from food_orders f inner join customers c on f.customer_id = c.customer_id
where age between 10 and 59
group by 1,2
) a
group by 1
order by 1 desc
위에서 구한 base data를 쿼리문의 from문에 넣고, 각각 성별에 따라 max(if(조건, 데이터 수,0))의 순서를 이용해 각 컬럼을 설정한다.
3. Window Function(RANK, SUM)
Window Function : 각 행의 관계를 정의하기 위한 함수, 그룹 내 연산을 쉽게 만들어줌
기본 SQL구조로 해결하기 위해 복잡하기 서브쿼리 문을 이용하거나 여러번 연산을 할때 편리하다.
3.1 기본 구조
window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)필요에 따라 다른 구문은 생략될 수 있으나, window _function과 over는 무조건 필수적으로 넣어줘야 한다!
3.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. 날짜 데이터
select date, # 문자형
date(date) change_date # 날짜형
from payments
위의 date와 change_date는 같은 날짜를 나타내는 데이터임에도 불구하고 컴퓨터는 date를 문자열로, change_date를 시간 데이터로 받아들인다. 이와 같이 날짜 형태로 인식하게 되면 날짜간의 계산도 가능하다
4.1 기본 형태
- 날짜형 변환 : date(문자로 된 날짜 데이터)
- 원하는 년/월/일/요일 추출 : date_format(날짜 데이터, "%Y"/"%m"/"%d"/"%w")
4.2 예제
select date(date) date_type,
date_format(date(date), '%Y') "년",
date_format(date(date), '%m') "월",
date_format(date(date), '%d') "일",
date_format(date(date), '%w') "요일"
from payments
숙제
음식 타입별, 연령별 주문건수 pivot view 만들기 (연령은 10~59세 사이)
select cuisine_type,
max(if(age_group = 10, cnt_orders,0)) "10",
max(if(age_group = 20, cnt_orders,0)) "20",
max(if(age_group = 30, cnt_orders,0)) "30",
max(if(age_group = 40, cnt_orders,0)) "40",
max(if(age_group = 50, cnt_orders,0)) "50"
FROM
(
SELECT f.cuisine_type,
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_group,
count(1) as cnt_orders
FROM food_orders f inner join customers c on f.customer_id = c.customer_id
where c.age between 10 and 59
group by cuisine_type, age_group
) a
group by cuisine_type
