SQL 정리 2

subquery

• 여러 번의 연산을 한 번의 sql 문으로 수행해야할 때
• 조건문에 연산 결과를 사용해야 할 때
• 조건에 Query 결과를 사용하고 싶을 때

ex1) 음식점의 평균 단가별 segmentation 을 진행하고, 그룹에 따라 수수료 연산하기.

(수수료 구간 - 

~5000원 미만 0.05%

~20000원 미만 1%

~30000원 미만 2%

30000원 초과 3%)

select restaurant_name,
       price_per_plate*ratio_of_add "수수료"
from 
(
select restaurant_name,
       case when price_per_plate<5000 then 0.005
            when price_per_plate between 5000 and 19999 then 0.01
            when price_per_plate between 20000 and 29999 then 0.02
            else 0.03 end ratio_of_add,
       price_per_plate
from 
(
select restaurant_name, avg(price/quantity) price_per_plate
from food_orders
group by 1
) a
) b

 

ex2) 음식점의 지역과 평균 배달시간으로 segmentation 하기

select restaurant_name,
       sido,
       case when avg_time<=20 then '<=20'
            when avg_time>20 and avg_time <=30 then '20<x<=30'
            when avg_time>30 then '>30' end time_segment
from 
(
select restaurant_name,
       substring(addr, 1, 2) sido,
       avg(delivery_time) avg_time
from food_orders
group by 1, 2
) a

 

중요! 중첩 쿼리문을 적은 후 중첩 쿼리문의 별명을 적어줘야 한다. (a) 안 적어주면 오류 발생.
서브 쿼리 안에서 사용한 별명은 밖에서 사용할 수 없다. 그냥 적거나 서브 쿼리의 별명(a). 으로 적어줘야 한다.

 

 

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.공통컬럼명

 

공통 컬럼은 묶어주기 위한 '공통 값'이기 때문에 두 테이블의 컬럼명은 달라도 상관없다.

 

 

ex) 고객의 주문 식당 조회하기

(조회 컬럼 : 고객 이름, 연령, 성별, 주문 식당)

- 고객명으로 정렬, 중복 없도록 조회

select distinct c.name,
       c.age,
       c.gender,
       f.restaurant_name
from food_orders f left join customers c on f.customer_id=c.customer_id
order by c.name

 

 

- null 값 제거

 

null 값을 제거하면 아예 없는 데이터인 샘 치기 때문에 계산에 포함시키지 않는다.

반대로 is null 을 사용하면 null인 것만 보여주게 된다. 

 

 

ex2) 50세 이상 고객의 연령에 따라 경로 할인율을 적용하고, 음식 타입별로 원래 가격과 할인 적용 가격 합을 구하기

(조회 컬럼 : 음식 타입, 원래 가격, 할인 적용 가격, 할인 가격)

* 할인 : 나이 - 50 * 0.005

* 고객 정보가 없는 경우도 포함하여 조회, 할인 금액이 큰 순서대로 정렬

select cuisine_type,
       sum(price) "원래 가격",
       sum(price)-sum(discount_price) "할인 적용 가격",
       sum(discount_price) "할인 가격" 
from 
(
select a.cuisine_type,
       price,
       price*((b.age-50)*0.005) discount_price
from food_orders a inner join customers b on a.customer_id=b.customer_id
where b.age>=50
) t
group by 1
order by 4 desc

 

 

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

• 테이블에 잘못된 값이 들어있을 때
• Join을 했을 때 값이 없는 경우
• 사용할 수 없는 데이터가 들어있는 경우

1. 없는 값을 제외해주기

• Mysql에서는 사용할 수 없는 값일 때 해당 값을 연산에서 제외해준다 -> 0으로 간주

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

 

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

 

coalesce(b.age, 20) => b.age가 만약 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

-> 15세 미만이거나 80세 초과인 경우 15, 80으로 각각 대체.

 

 

Pivot Table 만들기

• pivot table : 2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여주는 것
• 기본 구조

 

 

 

 

 

 

 

 

 

 

• 예시

 

 

ex1) 음식점별 시간별 주문건수 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

 

 

ex2) 성별, 연령별 주문건수 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 Function - Rank, Sum

• 각 행의 관계를 정의하기 위한 함수로 그룹 내의 연산 쉽게 만들어줌.
• 기본 구조

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

 

• 함수명과 over은 한 세트
• argument : 함수에 따라 작성하거나 생략
• paritition by : 그룹을 나누기 위한 기준. 어떤 단위로 묶어줄 것인지. group by 절과 유사.
• order by : window function을 적용할 때 정렬 할 컬럼 기준. 

 

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

- Rank 함수 적용

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

 

- 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

 

 

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

• 전체에서 차지하는 비율, 누적합을 구할 때 - 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 으로 변경하기

select date(date) date_type,
       date
from payments

 

 

2. date type 을 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') "요일"
from payments

 

 

 

연습문제

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