대망의 SQL 마지막 5주차 강의 시작
먼저 MySQL에서는 사용할 수 없는 값을 0으로 간주한다.
1) 데이터가 없는 값을 연산에서 제외하거나 대체하는 방법을 3가지로 알아보자.
1-1) 사용할 수 없는 값을 NULL로 지정해 제외하기
위 데이터에서 rating 컬럼의 평균을 구한다고 생각해보자.
Not given은 사용할 수 없는 값 => 0으로 간주함. 고로
0(Not given) + 0(Not given) + 5 + 3 + 4 + 3 / 6 = 2.5
하지만 SQL에서 Not given을 null로 지정해주면 연산에서 제외되므로
5 + 3 + 4 + 3 / 4 = 3.75 로 값이 달라진다.
1-2) 빈 데이터인 [NULL]을 없애고 싶을 때 is not null 명령어로 제외하기
select a.order_id,
a.customer_id,
a.restaurant_name,
a.price,
b.name.
b.age,
b.gender
from food_orders a left join customers b on a.customer_id=b.customer_id
where b.customer_id is not null
# SQL에서 is not null 작성 예시
1-3) 사용할 수 없는 값을 다른 값으로 대체하기
select b.age,
coalesce(b.age, 20) "null값 대체"
food food_orders a left join customers b on a.customer_id=b.customer_id
where b.age is null
# coalesce 사용 예시위 예시와 같이 coalesce를 사용해 age 컬럼의 null 값을 20으로 대체할 수 있다.
2) SQL로 Pivot table을 만들어보자.
피벗 테이블을 만들때는 2가지 단계를 거친다.
먼저 베이스 데이터를 만들고, 이 데이터를 이용해 피벗 뷰를 만든다.
ex) 성별 / 연령별 주문건수 Pivot Table 뷰 만들기 (나이는 10~59세 사이, 연령 순으로 내림차순)
2-1) 베이스 데이터 만들기
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 customer c on f.customer_id=c.customer_id
where age between 10 and 59
group by 1, 2
모든 연령을 표기하면 데이터가 너무 길어져서 case로 범위를 지정해서 연령별로 표기
2-2) 베이스 데이터를 토대로 피벗 뷰 만들기
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 customer 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
베이스 데이터를 서브 쿼리를 이용해 a 그룹으로 묶어주고 max 함수로 male / female 을 지정해주면 완성
일단 MAX 함수의 원리를 이해없이 피벗 뷰를 만들기 위해 사용한 상태인데, 미래의 내가 더 알아갈 예정
3) Window Function의 구조 이해하기
window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)
# 윈도우 함수의 기본 구조
윈도우 함수의 argument 자리에 각각 rank 또는 sum을 이용해 순위, 비율, 누적합 등 원하는 데이터를 출력할 수 있음
4) 날짜 데이터를 이해하고 여러 포맷 지정하기
엑셀에서 셀 서식을 지정하듯, SQL에서도 문자 타입의 날짜 컬럼을 날짜 형식으로 변경이 가능함
select date,
date(date) date_type
from payments
# date type 설정 예시
왼쪽의 문자 형식이었던 데이터들이 오른쪽 날짜 형식으로 바뀜을 볼 수 있음
이렇게 나눠진 날짜 형식을 년 / 월 / 일 / 요일 단위의 컬럼으로 포맷을 지정해보자
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
# date_format으로 쪼개기
