키워드
null문법의 활용
max함수를 이용한 pivot table
rank() over(partition by order by ) as ranking
sum() over()
date() 함수
date format(컬럼,%Ymdw)함수
coalesce () 함수 null이아닌값을 반환 모든값이 null이면 null 반환 반환값 지정가능.
5-1
수업목표
데이터에서 예상하지 못한 값이 나왔을 때 (이상한 값, 값이 없음 등), 분석에 적절하게 처리한다
SQL 로 엑셀에서 자주 사용하는 형태로 데이터를 만든다 엑셀pivot
업무에 활용할 수 있는 다양한 SQL 심화 문법을 익힌다
복습
subquery 수학에서의 ()연산
join 공통컬럼으로 두개이상의 테이블을 결합하는것. join on
5-2 유용한 null 문법
없는값이나 잘못된값 제외하기
select restaurant_name, avg(rating) avg_rating,
avg(if(rating<>'Not given',rating,null)) avg_rating2
from food_orders
group by 1
첫번째 구문은 mysql이 계산할수없는값을 0처리한다.
두번째 구문은 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
customer_id가 null이 아닌것들만 불러온다.
ㄴ 오류 common column 은 select할 때 별칭지정
다른값으로 대처하기
1 if (rating>=1, rating, 대체값)
2 null 값일때 : coalesce(age,대체값)
coalesce () 함수 null이아닌값을 반환 모든값이 null이면 null 반환 반환값 지정가능.
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
coalesce(b.age, 20) 컬럼이 값을 가지고있지않다면 20으로 대체한다.
5-3
조회한데이터가 상식적이지 않은값일때
주문한고객나이가 2살, 결제일이 1930년대
조건문으로범위지정
select name, age,
case when age<15 then 15
when age>=50 then 80
else age end as re_age
from customers
age = '15' 15와 같은지 여부를 판단하는 조건으로 해석된다. 반환값을 설정할때는 적합하지 않다.
false인 0을 반환한다. 그냥 반환값 15를 써주어야한다.
5-4 pivot table 만들기 실습
excel 의 pivot table 을 sql로 구현하기
한줄 띄고 커서에 실행넣어주면 다른쿼리로 인식한다.
실습) 음식점별 시간별 주문건수 Pivot Table 뷰 만들기 (15~20시 사이, 20시 주문건수 기준 내림차순)
1.베이스데이터만들기, 2.pivot view 만들기
select f.restaurant_name, substr(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 substr(p.time, 1, 2) between 15 and 20
group by 1,2
select restaurant_name,
max(if(hh='15', cnt_order, 0)) "15",
max(if(hh='16', cnt_order, 0)) "16",
max(if(hh='17', cnt_ord er, 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 a.restaurant_name,
substring(b.time, 1, 2) hh,
count(1) cnt_order
from food_orders a inner join payments b on a.order_id=b.order_id
where substring(b.time, 1, 2) between 15 and 20
group by 1, 2
) a
group by 1
order by 7 desc
실습)성별, 연령별 주문건수 Pivot Table 뷰 만들기 (나이는 10~59세 사이, 연령 순으로 내림차순)
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
male이 아닐때 0을 반환하는 이유?
조건이 충족되지 않을 때 기본값을 제공하기위해. 제대로 동작할 수 있도록 값을 채워주는 역할.
기본값은 집계함수 (max)와 함께 사용되며 결과 테이블을 구성할 때 데이터를 일관되게 표현하도록 돕는다
max 함수는 그룹내 가장 큰값을 반환 하기에 조건을 충족하지 않는경우 최솟값인 0
결과 모든 age 그룹과 gender 조합에서 값이 반드시 반환되도록 보장한다. 누락된 데이터 없이 설계된다.
그룹 데이터의 일관성을 유지.
5-5 window 함수 순위 매기기, 누적 합계 구하기
Window Function 은 각 행의 관계를 정의하기 위한 함수로 그룹 내의 연산을 쉽게 만들어준다.
window function 기본구조 over와 한세트
window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)
rank() over 한세트(어떤것에 랭킹 매길것인지 파티션 by 요리타입 = 음식타입별로 랭킹을 구하겠다. 순서는 cnt_order순으로 내림차순한다)
rank() over(partition by cuisine_type order by cnt_order desc) ranking
실습) n번째까지의 대상을 조회하고 싶을 때
음식 타입별, 음식점별 주문 건수 집계하기 > Rank 함수 적용하기 >3위까지 조회하고, 음식 타입별, 순위별로 정렬하기
rank() 함수는 대상이되는 값이 있는게 아니라 빈괄호만쓴다.
함수명rank와 over는 절대 빠지지 않고 경우에 따라 partition, order생략가능
rank함수 적용하기
select cuisine_type, a.restaurant_name , cnt_order,
rank() over(partition by cuisine_type order by cnt_order desc) ranking
from
(
select cuisine_type, restaurant_name, count(1) cnt_order
from food_orders
group by 1,2
) a
3위까지 적용하기
select b.cuisine_type, restaurant_name, cnt_order, ranking
from
(
select cuisine_type, a.restaurant_name , cnt_order,
rank() over(partition by cuisine_type order by cnt_order desc) ranking
from
(
select cuisine_type, restaurant_name, count(1) cnt_order
from food_orders
group by 1,2
) a
) b
where ranking<=3
주문건수별 랭킹구하기는? 아래처럼 partition 생략 할 수도 있다.
실습) 누적합 구하기 + 전체에서 데이터가 차지하는 비율
실습) sum에 대한 윈도우 함수로 각 음식점의 주문건이 해당 음식 타입에서 차지하는 비율을 구하고, 주문건이 낮은 순으로 정렬했을 때 누적 합 구하기
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
부족하다. 추가학습
5-6
sql에는 숫자,문자열 타입 외에도 날짜타입이 있다.
yyyy-mm-dd 형식의 컬럼을 data type 으로 변경하기
select date,
date(date) change_date
from payments
날짜형식으로 바꿔주는 함수 date(대상이되는컬럼명) 함수명date(컬럼명date)
컬럼명에 시계모양 날짜타입으로 데이터가 바뀌었다.
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함수 데이트타입의컬럼을 포멧팅해준다 형식을 지정해준다.
date_format(대상date_type의 함수) %y는 십의자리까지 Y는 천의자리까지보여준다
날짜형식데이터는 손쉽게 포메팅이 가능하다 몇주차인지 무슨요일인지
%w 0일요일 1월요일 ~6토요일
실습) 년도, 월을 포함하여 데이터 가공하기
년도, 월별 주문건수 구하기
select date_format(date(date), '%Y') "년",
date_format(date(date), '%m')"월",
date_format(date(date), '%Y%m') "년월",
count(1) "주문건수"
from food_orders f join payments p on f.order_id = p.order_id
group by 1, 2 ,3
3월 조건으로 지정하고, 년도별로 정렬하기
select date_format(date(date), '%Y') "년",
date_format(date(date), '%m')"월",
date_format(date(date), '%Y%m') "년월",
count(1) "주문건수"
from food_orders f join payments p on f.order_id = p.order_id
where date_format(date(date), '%m')='03'
group by 1, 2 ,3
order by date_format(date(date), '%Y')
숙제
음식 타입별, 연령별 주문건수 pivot view 만들기 (연령은 10~59세 사이)
SQL 기본구조 작성하기
Pivot view 를 만들기 위해 필요한 데이터 가공하기
Pivot view 문법에 맞추어 수정하기
내쿼리
서브쿼리
select cuisine_type, a.age2, count(1)
from
(
select cuisine_type,
case when age between 10 and 20 then '10대'
when age between 20 and 30 then '20대'
when age between 30 and 40 then '30대'
when age between 40 and 50 then '40대'
else '50대'end "age2"
from food_orders f inner join customers c on f.customer_id=c.customer_id
order by 1
)a
group by 1, 2
order by 1
pivot view 화하기
select cuisine_type,
max(if(age2='10대',order_count,0)) "10대",
max(if(age2='20대',order_count,0)) "20대",
max(if(age2='30대',order_count,0)) "30대",
max(if(age2='40대',order_count,0)) "40대",
max(if(age2='50대',order_count,0)) "50대"
from(
select cuisine_type, a.age2, count(1) order_count
from
(
select cuisine_type,
case when age between 10 and 20 then '10대'
when age between 20 and 30 then '20대'
when age between 30 and 40 then '30대'
when age between 40 and 50 then '40대'
else '50대'end "age2"
from food_orders f inner join customers c on f.customer_id=c.customer_id
where age between 10 and 59
order BY 1
) a
group by 1, 2
)b
group by 1
연령 조건 10~59 ..
정답 쿼리
select cuisine_type,
max(if(age=10, order_count, 0)) "10대",
max(if(age=20, order_count, 0)) "20대",
max(if(age=30, order_count, 0)) "30대",
max(if(age=40, order_count, 0)) "40대",
max(if(age=50, order_count, 0)) "50대"
from
(
select a.cuisine_type,
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) order_count
from food_orders a inner join customers b on a.customer_id=b.customer_id
where age between 10 and 59
group by 1, 2
) t
group by 1
서브쿼리가 문자열 컬럼이라서 오류가 안뜨긴 했지만 가능한 메인쿼리밖에선 정수로 간결히 표현하자.
추가학습 필요
window function
sum() over(partition by )
pivot view - max 함수