[SQL복습] 5주차

[5주차 - Pivot Table]

 

[문자가 들어있거나/ 값을 가지고 있는 않은 상황/ join한 테이블이 값을 가지고 있지 않을때]

 

방법 1: 없는 갑을 제외해주기

'NULL은 없다'는 의미 즉, if(----,----, null)->조건에 충족하지 않으면 없는 걸로 취급해줘!

1) avg (rating) average_of_rating    : 'not given'을 0의 값으로 처리 즉, 0,0,5,3,4,3 /6으로 평균구함

2) Null 사용 -> 5,3,4,3/4로 없는 자료값으로 취급

 

>left join사용후, 자료가 없어 null로 표기되는 부분들을 없애고 싶다면, 'is not null'을 사용해 제거

 

 

방법2: 다른 값 대신 사용

              (평균값 혹은 중앙값 등 대표값을 이용하여 대체해주기도 함)

a) 조건문 이용: if( rating>=1, rating, 대체값)   -> 다른 값이 있을 때 유용 b) coalesce (age, 대체값)                                -> null 값일 때

b.age컬럼이 값을 가지고 있지 않다면, 모두 20으로 대체해줘!

[Pivot Table]

: 2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여주는 것

 

[실습]

음식점별 시간별 주문건수 Pivot Table 뷰 만들기 (15-20시 사이, (시간은 앞 두글자만)20시 주문건수 기준 내림차순)

select restaurant_name, .    -> 첫 행            max(if(hh='15', cnt_order, 0)) "15", -> max를 무조건 붙여야함            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 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 ->max/ sum/ avg처럼 계산하는 함수가 들어가면 꼭 그룹해줘야함 order by 7 desc

 

[window 함수]

: 각 행의 관계를 정의하기 위한 함수로 그룹 내의 연산을 쉽게 만들어줌

=> 행은 그대로 유지한 채, 순위, 누적합, 이전값 비교 등을 계산

 

[구조]

window_function (argument) over (partiton by 그룹 기준 컬럼 order by 정렬기준

-window_function: 기능 명 사용 (sum, avg, rank등)

-argument: 함수에 따라 작성하거나 생략

-partiton by: 그룹을 나누기 위한 기준 (group by와 유사)

-order by: window function 을 적용할 때 정렬 할 컬럼 기준

 

[실습]

음식 타입별로 주문 건수가 가장 많은 상점 3개씩 조회하기

select cuisine_type,        restaurant_name,        order_count,        ranking from ( select cuisine_type,        restaurant_name,        rank() over (partition by cuisine_type order by order_count desc) rankig,        order_count from ( select cuisine_type, restaurant_name, count(1) order_count from food_orders group by 1, 2 ) a ) b where ranking<=3 order by 1, 4

 

1.음식타입별, 음식점별 주문 건수 집계

2. Rank 함수 적용 -> 계산 대상이 없고 순위를 메기는 거기 때문에 ()안은 비워두기

3. 3위까지 조회, 음식타입별, 순위별로 정렬

 

[실습]

각 음식점의 주문건이 해당 음식 타입에서 차지하는 비율을 구하고, 주문건이 낮은 순으로 정렬했을 때 누적 합 구하기

SELECT      cuisine_type,     restaurant_name,     cnt_order,     SUM(cnt_order) OVER (PARTITION BY cuisine_type) AS sum_cuisine,     SUM(cnt_order) OVER ( PARTITION BY cuisine_type ORDER BY cnt_order) AS cum_cuisine FROM (     SELECT          cuisine_type,         restaurant_name,         COUNT(1) AS cnt_order     FROM food_orders     GROUP BY 1, 2 ) a ORDER BY cuisine_type, cnt_order;

 

[날짜 데이터]

date (컬럼명): 문자 형식의 데이터를 날짜 형식의 데이터로 변경

date_format(date(컬럼명), (%Y/ %M/ %D/%W))

L데이트를 형식화 해줘!

 

예)

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

요일: 0은 일요일, 1은 월요일을 의미

 

[실습]

년도별 3월의 주문건수 구하기

select date_format(date(date), '%Y') "년",        date_format(date(date), '%m') "월",        date_format(date(date), '%Y%m') "년월",        count(1) "주문건수" 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,3 order by 1