SQL - 값의 제외, 값의 변경, Pivot Table

SQL 5주차 강의내용 정리

 

 

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

1. 없는 값이나 잘못된 값을 제외해주는 방법

select restaurant_name,
       avg(rating) avg_rating,
       avg(if(rating<>'Not given'),rating,null)
from food_orders

if 문을 사용해서 특정조건일때와 아닐때를 구분하고, 제외할 땐 null 을 사용한다.

null 을 사용할 때는, Not given 일때도 0 이라고 간주하고 계산한다. 그러면 총 계산해야할 데이터 수가  달라지기 때문에, 평균값이라고 해도 결과가 다를 수 있다.

예를들어 총 데이터가 6개 중에 2개가 Not given 이면 2개의 0 을 포함하고 6개를 나누는데,  null 을 사용하여 제외하면 4개를 나누게 된다.

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

이런 경우에는 where 절에서 is not null 을 붙이면 null이 아닌 데이터만 조회하게 된다.

2. 없는 값이나 잘못된 값을 변경해주는 방법

if 문을 사용해서 대체하는 방법 과 coalesce를 사용하는 방법 2가지가 있다.

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) "null 제거" 이 부분은, b.age 가 값이  없을경우에는 20이라는 값을 넣어주고 별명을 붙인 구문이다.

 

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

상식적이지 않은 데이터란?

음식을 주문한다는 행위는 20세 이상인 성인인 경우가 많다. 그런데 데이터에선 91세나 2세같은 주문했다기에 상식적이지 않은 경우가 있다
또는 결제일자의 경우 비교적 최근인 결제일이 있어야 하는데, 1970년대에 계산한 데이터가 있으면 상식적이지 않은 경우이다.

이런것을 해결하려면, 조건문을 통해서 값의 범위를 지정할 수 있다.

select name,
       age,
       case when age<15 then 15
            when age>=80 then 80
            else age end re_age
from customers

위와 같이 작성하면, re_age 라는 이름으로 새로운 컬럼을 만들어 Subquery 방식 등으로 좀더 현실성 있는 데이터를 연산에 사용할 수 있다.

 

4강. [실습] SQL로 Pivot Table 만들어보기

Pivot Table

실습예제1
음식점별 시간별 주문건수 Pivot Table 뷰 만들기 (15~20시 사이, 20시 주문건수 기준 내림차순)

food_orders 에서는 restaurant_name 을 가져오고 payments 에서는 time 에서 '시간'에 해당하는 부분만 필요하기 때문에
time 의 앞에서 2글자만 가져온다.

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

Pivot Table 예시

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

max(if(hh='15', cnt_order, 0)) "15" 15시 일때, 주문 건수 조회한다. 피벗테이블을 사용할때는 max를 붙여준다. order by는 마지막 행을 기준으로 내림차순으로 정렬을 해달라는 내용이다.

실습예제2
성별, 연령별 주문건수 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