SQL 6일차

조회한 데이터에 아무 값이 없을 때

있어야 할 데이터가 없을 때 처리할 수 있는 방법은 어떤 것이 있을까

1. 없는 값을 제외해주기

   • Mysql 에서는 사용할 수 없는 값일 때 해당 값을 연산에서 제외해준다. > 0으로 간주한다.
   • 평균 rating을 구하는 쿼리를 아래와 같이 작성했을 때 avg_rating 과 avg_rating2 의 값이 각각 다르다.

   select restaurant_name,
       avg(rating) as avg_rating,
       avg(if(rating<>'Not given', rating, null)) as avg_rating2
   from food_orders
   group by restaurant_name;

   • avg_rating과 같이 사용할 수 없는 값인 Not given 을 따로 처리해 주지 않으면 Mysql 은 해당 데이터의 값을 0으로 간주하기 때문에 1, 2, 3, Not given 의 평균값은 (1+2+3+0) / 4 가 된다.
   • avg_rating2의 경우처럼 Not given 인 데이터의 값을 null로 처리해주면 해당 값을 평균 계산에서 제외해준다. 따라서 1, 2, 3, Not given 의 평균값은 (1+2+3) / 3 이 된다.

2. 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 

   • is not null 구문을 명시함으로써 inner join과 같은 데이터를 뽑아냄

3. 다른 값을 대신해서 사용하기

   • 데이터 분석 시에는 평균값 혹은 중앙값 등 대표값을 이용해서 대체해주기도 한다.
   • 다른 값으로 변경하고 싶을 때, 다음 두 개의 문법을 이용할 수 있다.
     • 다른 값이 있을 때 조건문 이용하기 : 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

   • age 항목이 null인 데이터를 조회하여 null 제거 컬럼에서 20으로 치환된 값을 보여주는 쿼리

---

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

분석을 하다보면 이해 할 수 없는 값의 데이터가 나올 경우가 있다.

• 예시

  • 음식을 주문한 고객의 나이가 2세 인경우
  • 결제 일자가 1970년대로 너무 오래된 경우

• 조건문으로 값의 범위를 지정하기

  • 조건문으로 가장 큰 값과 작은 값의 범위를 지정해 줄 수 있다.
    • 상식적인 수준 안에서 범위를 지정해 준다.
  • 나이의 경우

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

  • 15세 미만과 80세 초과인 경우를 제외

---

SQL로 Pivot Table 만들기

1. Pivot table이란

   • 2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여주는것
   • Pivot table의 예시
     • 일자별 시간별 주문건수
     • 집계기준 : 일자, 시간

   	1시	2시	3시	4시
   10월 1일	5	3	5	2
   10월 2일	7	10	1	8
   10월 3일	3	3	9	4
   10월 4일	9	16	10	1

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 f.restaurant_name,
       substr(p.time, 1, 2) as hh,
       count(1) as cnt_order
   from food_orders as f inner join payments as p
   on f.order_id = p.order_id
   where substr(p.time, 1, 2) between 15 and 20
   group by f.restaurant_name, substr(p.time, 1, 2)
   ) as a
   group by restaurant_name
   order by max(if(hh='20', cnt_order, 0)) desc;

   • 음식점별, 시간별 주문건수를 집계한 서브 쿼리문을 통해 작성

3. 성별, 연령별 주문건수 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) as 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
   ) as t
   group by age
   order by age desc;

   • 성별, 연령별 주문건수를 집계한 서브 쿼리문을 통해 작성