[SQL]불필요한 Query 처리방법(coalesce,<>), Pivot Table

4주차 복습하기

Subquery

쿼리의 결과를 쿼리에 다시 활용하는것

select column1, special_column
from
    ( /* subquery */
    select column1, column2 special_column
    from table1
    ) a

 

JOIN

두 개 이상의 테이블을 결합하여 사용하는 것

JOIN 의 형태에 따라 Left join, Inner join 등이 있음

 

-- LEFT JOIN
select 조회 할 컬럼
from 테이블1 a left join 테이블2 b on a.공통컬럼명=b.공통컬럼명

-- INNER JOIN
select 조회 할 컬럼
from 테이블1 a inner join 테이블2 b on a.공통컬럼명=b.공통컬럼명

 

---

5주차는  예상치못한 Query 결과가 나왔을때 어떻게 처리해야할지 알아보고 SQL 로 Pivot Table 만들어보자.

 

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

 

이와 같이 name , age gender 값이 null 값으로 나와 있는 것들이 있다.

이런 경우에 어떻게 처리해주는 것이 좋을까?

 

 

1.없는 값을 제외하기 

 

1-1

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

 

<>: 아니다(조건문)

null: 제외

 

이 코드를 입력하면 다른 결과 값이 출력 된다.

 

SQL에서 값이 없다면 0으로 간주하기 때문에 코드로 따로 설정을 안해주면 평균을 구할때 없는 값도 포함하기 때문에 원하는 결과값이 나올 수 있다.

그럼으로 값이 없는것은 null로 지정하여 제외시켜줘야한다.

 

1-2

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절을 사용하여 null 이 아닌것만 가져오는 것도 가능하다.

 

 

2.다른 값을 대신 사용하기

2-1

다른 값이 있을 때 조건문 이용하기 : if(rating>=1, rating, 대체값)

 

2-2

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

 

coalesce(b.age,20) : b.age 가 만약 null 이라면 20으로 대체

 

 

✅ 부적절한 데이터가 있을땐 어떻게 처리해야할까?

 

이런식으로 age의 범위가 말도 안되는 2 같은 경우가 있을때는 

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

 

case , when 문을 사용하여 데이터의 범위를 줄여 줄 수 있다.

 

 

✅ SQL 로 Pivot Table 만들어보기

Pivot Table 이란?

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

 

 

Pivot table 의 기본 구조

 

 

 

Pivot table 의 예시

 

 

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

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) betwwen 15 and 20
group by 1,2

 

코드해석

food_orders 와 payments 테이블의 order_id 의 값을 합치고 substr 로 시간만 추출하여 15시와 20 시 사이만 레스토랑과 시간별로 출력한다.

 

여기서 pivot view 를 만드는 코드를 작성해야한다.

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

 

기존에 작성한 코드를 from 절로 묶어준다.

 

 

 

 

 

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

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)
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

 

여기서 pivot view 를 만드는 코드를 작성하면

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 Function - RANK, SUM)

RANK : 이름에서 유추할 수 있듯이 ‘특정 기준으로 순위를 매겨주는’ 기능

 

1. [실습] 음식 타입별로 주문 건수가 가장 많은 상점 3개씩 조회하기

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

 

 rank() over (partition by cuisine_type order by cnt_order desc) 

cuisine_type 별로 cnt_order 의 내림차순으로 랭킹을 매기는 것이다.

 

만약여기서 3개만 조회할려면 위의 코드를 묶고 where ranking <=3 으로 묶으면된다.

 

SUM:앞서 배운 합계를 구하는 기능과 동일

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

 

 

✅ 5주차 마무리 문제

음식 타입별, 연령별 주문건수 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