Day5. 피벗테이블 / Rank,Sum / 날짜 / TIL.20240419

To do list.

-조회한 데이터에 아무 값이 없다면 어떻게 해야할까?

-조회한 데이터가 상식적이지 않은 값을 가지고 있다면 어떻게 해야할까?

-피벗테이블

-랭크,SUM

-날짜 포맷

 

 

 

---

조회한 데이터에 아무 값이 없다면 어떻게 해야할까?

 

없는 값을 제외해주기 => null 사용

 

SELECT restaurant_name ,

               avg(rating) avg_rating,

               AVG(IF(rating <>'Not given',rating,NULL)) avg_rating2

FROM food_orders

group by 1

 

 

 

is not NULL 사용

SELECT a.order_id,

a.customer_id,

a.price,

b.name,

b.age,

b.gender

from food_orders a left join customer b on a.customer_id=b.customer_id

WHERE b.customer_id is not NULL

 

 

 

다른 값을 대신 사용하기

 

값의 변경

COALESCE (b.age,20) "null 제거", 

COALESCE 사용

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

 

---

조회한 데이터가 상식적이지 않은 값을 가지고 있다면 어떻게 해야할까?

 

 -방법- 조건문으로 값의 범위를 지정하기

EX) 성인인데 나이가 2세라고 데이터가 나왔을경우 

 

SELECT name,

               age,

              case when age<15 then 15

                       when age>=80 then 80

else age end re_age

FROM customers

 

---

SQL로 피벗 테이블 만들어보기

 

베이스데이터 만들기 -> 베이스데이터 이용하여 피벗뷰 만들기

 

1) 음식점별 시간별 주문건수 피벗 뷰 만들기 (15-20시 사이, 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 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

 

2) 성별, 연령별 주문건수 피벗 뷰 만들기 (나이는 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

 

---

업무 시작을 단축시켜 주는 마법의 문법(Window Function - RANK, SUM)

 

rank() over 한쌍

 

1) N번째까지의 대상을 조회하고 싶을 때, Rank (랭크함수는 특정 기준으로 순위를 매겨줌)

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

 

SELECT cuisine_type,

               restaurant_name ,

               cnt_order,

               ranking

from

(

SELECT cuisine_type,

               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

 

전체에서 차지하는 비율, 누적합을 구할 때, Sum

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

 

---

날짜 포맷과 조건까지 SQL로 한 번에 끝내기 (포맷함수)

 

SELECT date,

               date(date) change_date

FROM payments

 

=> 숫자에서 날짜 형식으로 바꾼 것.

 

 DATE_FORMAT 사

 

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') "요일"                 => 0일요일 ~7토요일

FROM payments

 

년도, 월을 포함하여 데이터 가공하기

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

 

SELECT DATE_FORMAT(date(date), '%Y') "년",

               DATE_FORMAT(date(date), '%m') "월",

               DATE_FORMAT(date(date), '%Y%m') "년월",

               COUNT(1) "주문건수"

FROM food_orders f inner join payments p on f.order_id =p.order_id

where DATE_FORMAT(date(date), '%m')='03'

group by 1,2,3

order by 1

 

---

 

과제

음식 타입별, 연령별 주문건수 pivot view 만들기

 

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

 

 

 

---

Check point.

 

코드카타를 보면서 = null 이 아니라 is null 이라고 표기하는 이유

 

=>SQL 문법 자체가 저렇게 정의되어있습니다.

   NAME=NULL이라고 하면 말 그대로 이름이 NULL인 경우가 되지만,

   여기서 말하는 NULL은 데이터가 정의되어있지 않은 경우를 찾는 것이라서,

   IS NULL을 사용하는 것입니다.

 

코드카타 24번 문제 어려움

 

피벗뷰를 깔끔하게 만들어 주기위해선 max값을 넣어준다

max 등 계산하는 집계함수 사용시 그룹바이 넣어줌

 

 

 

---

회고

5주차 SQL강의를 끝마치며 .. 처음엔 쉽게 문제가 풀렸지만 5주차인 피벗테이블 조인문 등 쿼리문이 길어지니까 난이도가 갑자기 확 높아진느낌이다. 좀 더 열심히 공부해야겠다.