[TIL] 230508 <SQL> 없는 값 제외, 다른 값으로 대체, 범위지정, Pivot table, Window함수(Rank,Sum), 날짜포맷

 

 

>> sql문 실행 단축키 : ctrl + enter

 

 

사용할 수 없는 데이터가 들어있거나, 값이 없는 경우

 

 방법1) 없는 값 제외

   ● if(rating<>'Not given', rating, null)

select restaurant_name,
       avg(rating) average_of_rating,
       avg(if(rating<>'Not given', rating, null)) average_of_rating2
from food_orders
group by 1

ㄴ 위의 코드를 실행 시 null인 row를 아예 제외하고 계산함

원래 mySql에서는 사용할 수 없는 값(Not given)을 0으로 간주하여 (0+0+5+3+4+3)/6으로 avg를 계산하는데,위의 코드에서는 null인 데이터를 아예 제외하고 (5+3+4+3)/4 로 avg를 계산함

 

   ● where 컬럼 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

ㄴ join 시 inner join과 동일한 결과값을 갖게 됨

 

 

 방법2) 다른 값으로 대체

   ● 다른 값이 있을 때 :   if(rating>=1, rating, 대체값)

   ● null값 일 때 :   coalesce(age,대체값)

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

ㄴ null을 20으로 대체

 

 

 

 

데이터가 상식적이지 않은 값을 가지고 있다면 값의 범위를 지정해주기

select customer_id, name, email, gendor, age,
       case when age<15 then 15
            when age>80 then 80
            else age end "범위를 지정해준 age"
from customers

 

 

---

SQL로 Pivot Table 만들기

Pivot Table의 기본 구조 (2개 이상의 기준으로 데이터 집계 시, 보기 쉽게 배열하여 보여줌)

 

// 음식점별 시간별 주문건수 Pivot Table 뷰 만들기 
// (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

// 성별, 연령별 주문건수 Pivot Table 뷰 만들기  
// (나이는 10~59세 사이, 연령 순으로 내림차순)

select age,
       max(if(gender='male', order_count, 0)) male,
       max(if(gender='female', order_count, 0)) female
from 
(
select b.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) order_count
from food_orders a inner join customers b on a.customer_id=b.customer_id
where b.age between 10 and 59
group by 1, 2
) t
group by 1
order by age

 

 

---

Window 함수

각 행의 관계를 정의하기 위한 함수 (주문건수 순위, A가 차지하는 비율 등)

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

 

1) Rank : 특정 기준으로 순위 매김

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

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

 

2) Sum (누적합 구하기 가능)

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

select cuisine_type,
       restaurant_name,
       order_count,
       sum(order_count) over (partition by cuisine_type) sum_cuisine_type,
       sum(order_count) over (partition by cuisine_type order by order_count, restaurant_name) cumulative_sum
from
(
select cuisine_type, restaurant_name, count(1) order_count
from food_orders
group by 1, 2
) a

 

 

---

날짜 데이터의 포맷

1. yyyy-mm-dd 형식의 컬럼을 date type으로 변경 ( date() )

select date(date) date_type,
       date
from payments

 

2. date type을 date_format을 이용하여 년, 월, 일, 요일 로 조회가능

• 년 : Y (4자리), y(2자리)
• 월 : M, m
• 일 : d, e
• 요일 : w
  
  

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

 

 

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

select date_format(date(date), '%Y') y,
       date_format(date(date), '%m') m,
       count(1) order_count
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
order by 1

 

 

// 음식 타입별, 연령별 주문건수 pivot view 만들기 
// (연령은 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 f.cuisine_type,
	   case when age < 20 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 f inner join customers c on f.customer_id=c.customer_id
group by 1,2
) a
group by 1