SQL - 4주차

 복습

문자, 포멧 변경과 조건문

문자 변경

REPLACE :  지정한 문자를 다른 문자로 변경
SUBSTRING : 특정 문자만 추출
CONCAT : 여러 문자를 합하여 포맷팅

조건문

IF : if(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)

CASE WHEN END :
case when 조건1 then 값(수식)1
     when 조건2 then 값(수식)2
     else 값(수식)3
end

 

Subquery 를 활용하여 복잡한 연산을 수행

from에 사용할 데이터 가공해서 넣어놓고 그걸로 쿼리문 만들기.

그냥 실습 많이 해서 익숙해지자

02. 여러 번의 연산을 한 번의 SQL 문으로 수행하기 (Subquery)

문자 변경한 다음에 숫자 연산 하고 그 결과로 또 연산을 해야할 때, 긴긴 쿼리문 보다는 조금 더 효율적이고 알아보기 쉽게 사용해봅시다

1) Subquery 가 필요한 경우

여러번의 연산을 수행해야 할 때
조건문에 연산 결과를 사용해야 할 때

조건에 Query 결과를 사용하고 싶을 때 

2) Subquery 문의 기본 구조

Sub 라는 명칭에서 알 수 있듯이, Query 안에 sub 로 들어간 구문이라고 생각해주시면 됩니다. 

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

select column1, column2
from table1
where column1 = (select col1 from table2)

 

3) [실습] Subquery 문을 이용하여 연산문 적어보기

주문 테이블에서  주문 번호, 음식점명, 음식 준비시간을 가져오기 
   select 기본문
   가져올 컬럼 적기
   subquery 문으로 추가

-- select 기본문
select order_id, restaurant_name, food_preparation_time

from 
(--subquery 문으로 추가
-- 가져올 컬럼 적기
select order_id, restaurant_name, food_preparation_time
from food_orders
) a

SELECT price/quantity -- 위에서 사용
FROM (
select price, quantity -- 여기서 불러오고
from food_orders 
) a -- from에 들어가는 서브쿼리에 'a'라는 이름을 준것

 

Subquery 문 안을 수정해서, 음식 주문시간이 25분보다 초과한 시간을 가져오기

select order_id, restaurant_name, if(over_time>=0, over_time, 0) over_time
from 
(
select order_id, restaurant_name, food_preparation_time-25 over_time
from food_orders
) a

 

03. [실습] User Segmentation 와 조건별 수수료를 Subquery 로 결합해보기

조건문과 Subquery 를 결합하여 user segmentation 과 연산을 해봅시다

1) [실습] 음식점의 평균 단가별 segmentation 을 진행하고, 그룹에 따라 수수료 연산하기
(수수료 구간 - 
  ~5000원 미만 0.05%
  ~20000원 미만 1%
  ~30000원 미만 2%
  30000원 초과 3%)

 

흐름 정리

1. Query 를 적기 전에 흐름을 정리해보기
어떤 테이블에서 데이터를 뽑을 것인가 
어떤 컬럼을 이용할 것인가
어떤 조건을 지정해야 하는가 
어떤 함수 (수식) 을 이용해야 하는가 
1.Query 를 적기 전에 흐름을 정리해보기 (해답)
어떤 테이블에서 데이터를 뽑을 것인가 → 주문 테이블
어떤 컬럼을 이용할 것인가 → 식당 이름, 주문 금액, 주문 수량
어떤 조건을 지정해야 하는가 → X
어떤 함수 (수식) 을 이용해야 하는가 → 평균 구하는 식, 카테고리에 따라 연산, 조건문
2.구문으로 만들기
어떤 테이블에서 데이터를 뽑을 것인가 
어떤 컬럼을 이용할 것인가 
어떤 조건을 지정해야 하는가 
어떤 함수 (수식) 을 이용해야 하는가 
2.구문으로 만들기 (해답)
어떤 테이블에서 데이터를 뽑을 것인가 → from food_orders 
어떤 컬럼을 이용할 것인가 → restaurant_name, price, quantity
어떤 조건을 지정해야 하는가 → X
어떤 함수 (수식) 을 이용해야 하는가 → avg(price/quantity), case when, group by
3.전체 구조로 합치기

select restaurant_name,
       price_per_plate*ratio_of_add "수수료"
from 
(-- 레스토랑 평균 단가를 범위로 나누어 ratio_of_add컬럼으로 보이기 (수수료 비율)
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

 

2) [실습] 음식점의 지역과 평균 배달시간으로 segmentation 하기

1. Query 를 적기 전에 흐름을 정리해보기 -> 2. 구문으로 적용해보기

   어떤 테이블에서 데이터를 뽑을 것인가 
   어떤 컬럼을 이용할 것인가
   어떤 조건을 지정해야 하는가 
   어떤 함수 (수식) 을 이용해야 하는가 

   

◆ Query 를 적기 전에 흐름을 정리해보기
어떤 테이블에서 데이터를 뽑을 것인가 → 주문 테이블   from food_orders 
어떤 컬럼을 이용할 것인가 → 식당 이름, 주소, 배달 시간   restaurant_name, addr, delivery_time
어떤 조건을 지정해야 하는가 → X
어떤 함수 (수식) 을 이용해야 하는가 

→ 평균 구사는 수식, 조건문, 카테고리별 연산, 문자의 특정 부분만 추출

   avg(delivery_time), substring(addr, 1, 2), case when, group by

 

◆ 전체 구조로 합치기

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 -- else '>30 end ~로 적오도 됨.
from 
(
select restaurant_name,
       substring(addr, 1, 2) sido,
       avg(delivery_time) avg_time
from food_orders
group by 1, 2
) a

 

04. [실습] 복잡한 연산을 Subquery 로 수행하기

1) [실습] 음식 타입별 지역별 총 주문수량과 음식점 수를 연산하고, 주문수량과 음식점수 별 수수료율을 산정하기   
(음식점수 5개 이상, 주문수 30개 이상 → 수수료 0.5%
 음식점수 5개 이상, 주문수 30개 미만 → 수수료 0.8%
 음식점수 5개 미만, 주문수 30개 이상 → 수수료 1%
 음식점수 5개 미만, 주문수 30개 미만 → 수수로 2%)

흐름을 정리하기

   어떤 테이블에서 데이터를 뽑을 것인가 
   어떤 컬럼을 이용할 것인가
   어떤 조건을 지정해야 하는가 
   어떤 함수 (수식) 을 이용해야 하는가 

 

◆ Query 를 적기 전에 흐름을 정리해보기

어떤 테이블에서 데이터를 뽑을 것인가 → 주문 테이블  from orders 
어떤 컬럼을 이용할 것인가 → 음식 타입, 주소, 주문 수량, 식당 이름  price, cuisine_type, addr
어떤 조건을 지정해야 하는가 → X
어떤 함수 (수식) 을 이용해야 하는가

  → 합계 구하는 기능, 갯수 수하는 기능  sum(quantity), count(distinct restaurant_name)

 

◆ 전체 구조로 합치기

select cuisine_type, 
total_quantity,
count_res,
       case when count_res>=5 and total_quantity>=30 then 0.005
            when count_res>=5 and total_quantity<30 then 0.008
            when count_res<5 and total_quantity>=30 then 0.01
            when count_res<5 and total_quantity<30 then 0.02 end rate
from
(
select cuisine_type,
       sum(quantity) total_quantity,
       count(distinct restaurant_name) count_res
from food_orders
group by 1
) a

 

서브쿼리 - 음식점 타입별 가게 수, 주문 수 합계 구하기

※ 코드 중간 중간에 테스트 하면서 작성하기

   길어지면 헛갈립니다.

2) [실습] 음식점의 총 주문수량과 주문 금액을 연산하고, 주문 수량을 기반으로 수수료 할인율 구하기 

(할인조건
  수량이 5개 이하 → 10%
  수량이 15개 초과, 총 주문금액이 300000 이상 → 0.5%
   이 외에는 일괄 1%)

 
◆ Query 를 적기 전에 흐름을 정리해보기
-어떤 테이블에서 데이터를 뽑을 것인가

    주문 테이블  from food_orders 
-어떤 컬럼을 이용할 것인가

   음식점 이름, 주문 수량, 주문 금액   restaurant_name, quantity, price

-어떤 함수 (수식) 을 이용해야 하는가 

    합계를 구하는 기능, 조건문  sum(quantity), sum(price), case when 

 

◆ 전체 구조로 합치기

select restaurant_name,
       case when sum_of_quantity<=5 then 0.1
            when sum_of_quantity>15 and sum_of_price>=300000 then 0.005
            else 0.01 end ratio_of_add
from 
(
select restaurant_name,
       sum(quantity) sum_of_quantity,
       sum(price) sum_of_price
from food_orders
group by 1
) a

 

◆ Join 을 활용하여 여러개의 테이블에 있는 데이터를 한 번에 조회하고 연산하기

05. 필요한 데이터가 서로 다른 테이블에 있을 때 조회하기 (JOIN)

1) JOIN 이 필요한 경우

필요한 데이터가 여러 테이블에 있을 때, 여러 테이블에서 데이터를 불러오기 위해 사용합니다.

2) JOIN 의 기본 원리와 종류

JOIN 은 기본적으로 엑셀의 Vlookup 과 유사합니다

예시)

-주문 정보에서 고객 이메일을 알기 위해서는, 고객 정보에서 동일한 고객 ID 의 이메일을 가져와야 합니다
(엑셀에서는 vlookup(고객ID, 고객 정보, 3, False) 라고 적으실 것입니다)
-JOIN 은 동일한 원리를 가집니다. 각각 주문 정보와 고객 정보가 테이블이라고 할 때, 고객 ID 를 기준으로 필요한 값을 가져와 주는 것입니다. 
-이 때 중요한 것은, 두 테이블이 공통으로 갖고 있는 컬럼이 됩니다. 위의 예시에서는 ‘고객 ID’가 되겠죠

-공통 컬럼을 기준으로 묶은 형태는 아래와 같이 됩니다. 
  즉, 공통 컬럼을 기준으로 두 테이블을 합쳐서, 각각 테이블에서 필요한 데이터를 조회할 수 있도록 만들어주는 것입니다.

조인 하는 방법

LEFT JOIN : 공통 컬럼 (키값) 을 기준으로, 하나의 테이블에 값이 없더라도 모두 조회되는 경우를 의미합니다. 

INNER JOIN : 공통 컬럼 (키값) 을 기준으로, 두 테이블 모두에 있는 값만 조회합니다. 
즉, 위의 예시에서 주문번호 14의 경우, 고객에 대한 정보가 고객 정보에 없으므로 조회 시 제외됩니다. 

◆ JOIN 의 기본 구조

-- LEFT JOIN
select 조회 할 컬럼
from 테이블1 a left join 테이블2 b on a.공통컬럼명=b.공통컬럼명
   -- 테이블1 a : 테이블1을 a로 부를거다.   그레서 '테이블1.공통컬러명'을 'a.공통컬럼명'으로 적음.

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

 

food_orders에 있지만 payments에는 없는 컬럼도 출력됩니다.

food_orders과 payments에 공통적인 컬럼만 출력됩니다.

팁
- 공통컬럼은 묶어주기 위한 ‘공통 값’ 이기 때문에 두 테이블의 컬럼명은 달라도 괜찮습니다. 
   예를 들어 주문정보에는 ‘고객ID’, 고객정보에는 ‘고객아이디’ 라고 컬럼명이 되어있다면,  테이블1.고객ID=테이블2.고객아이디 와 같이 묶어줄 수 있습니다. 

3) [실습] JOIN 을 이용하여 두 개의 테이블에서 데이터를 조회해보기

주문 테이블과 고객 테이블을 cusomer_id 를 기준으로 left join 으로 묶어보기
(조회 컬럼 : order_id, customer_id, restaurant_name, price, name, age, gender)

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

06. [실습] JOIN 으로 두 테이블의 데이터 조회하기
✔️
본격적으로 JOIN 으로 두 테이블 데이터를 여러가지 방향으로 조회해봅시다
1) [실습] 한국 음식의 주문별 결제 수단과 수수료율을 조회하기
(조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 결제 수단, 수수료율)
*결제 정보가 없는 경우도 포함하여 조회  <- left join

흐름 정리
어떤 테이블에서 데이터를 뽑을 것인가 
어떤 컬럼을 이용할 것인가
어떤 조건을 지정해야 하는가 
어떤 함수 (수식) 을 이용해야 하는가 

1.Query 를 적기 전에 흐름을 정리해보기 
어떤 테이블에서 데이터를 뽑을 것인가 → 주문 테이블, 지불 테이블
어떤 컬럼을 이용할 것인가 → f.주문 번호, 식당 이름, 주문 가격,    p.결제 수단, 수수료율, 음식 타입
어떤 조건을 지정해야 하는가 → 한국 음식
어떤 함수 (수식) 을 이용해야 하는가 → 두 테이블의 결합

2.구문으로 만들기 
어떤 테이블에서 데이터를 뽑을 것인가 → from food_orders, payments
어떤 컬럼을 이용할 것인가 → order_id, restaurant_name, price, pay_type, vat
어떤 조건을 지정해야 하는가 → where cuisine_type=’Korean’
어떤 함수 (수식) 을 이용해야 하는가 → left join payments (on order_id)

3.전체 구조로 합치기

select a.order_id,
       a.restaurant_name,
       a.price,
       b.pay_type,
       b.vat
from food_orders a left join payments b on a.order_id=b.order_id
where cuisine_type='Korean'

​

2) [실습] 고객의 주문 식당 조회하기

(조회 컬럼 : 고객 이름, 연령, 성별, 주문 식당) 
*고객명으로 정렬, 중복 없도록 조회

 

 흐름을 정리

어떤 테이블에서 데이터를 뽑을 것인가 
어떤 컬럼을 이용할 것인가
어떤 조건을 지정해야 하는가 
어떤 함수 (수식) 을 이용해야 하는가 

1.Query 를 적기 전에 흐름을 정리해보기 
어떤 테이블에서 데이터를 뽑을 것인가 → 고객테이블, 주문 테이블
어떤 컬럼을 이용할 것인가 → 고객 이름, 연령, 성별, 주문 식당
어떤 조건을 지정해야 하는가 → X
어떤 함수 (수식) 을 이용해야 하는가 → 두 테이블의 결합, 정렬, 중복 제거

2.구문으로 만들기
어떤 테이블에서 데이터를 뽑을 것인가 → from customers, food_orders 
어떤 컬럼을 이용할 것인가 → name, age, gender, restaurant_name
어떤 조건을 지정해야 하는가 → X
어떤 함수 (수식) 을 이용해야 하는가 → left join orders (on customer_id), order by , distinct

3.전체 구조로 합치기

select distinct c.name,  -- distinct : 중복 없애기
       c.age,
       c.gender,
       f.restaurant_name
from food_orders f left join customers c on f.customer_id=c.customer_id
where c.name is not null -- null 값 제거
order by c.name

07. [실습] JOIN 으로 두 테이블의 값을 연산하기

1) [실습] 주문 가격과 수수료율을 곱하여 주문별 수수료 구하기

(조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 수수료율, 수수료)
*수수료율이 있는 경우만 조회  <- inner join

흐름을 정리
어떤 테이블에서 데이터를 뽑을 것인가 
어떤 컬럼을 이용할 것인가
어떤 조건을 지정해야 하는가 
어떤 함수 (수식) 을 이용해야 하는가 

Query 를 적기 전에 흐름을 정리
어떤 테이블에서 데이터를 뽑을 것인가 → 주문 테이블, 결제 테이블
어떤 컬럼을 이용할 것인가 → 주문 번호, 식당 이름, 주문 가격, 수수료율
어떤 조건을 지정해야 하는가 → X
어떤 함수 (수식) 을 이용해야 하는가 → 두 테이블의 결합, 곱하기

 

구문으로 만들기
어떤 테이블에서 데이터를 뽑을 것인가 → from food_orders, payments
어떤 컬럼을 이용할 것인가 → order_id, restaurant_name, price, vat
어떤 조건을 지정해야 하는가 → X
어떤 함수 (수식) 을 이용해야 하는가 → inner join, price*vat

 

전체 구조로 합치기

select a.order_id,
       a.restaurant_name,
       a.price,
       b.vat,
       a.price*b.vat "수수료율"
from food_orders a inner join payments b on a.order_id=b.order_id

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

(조회 컬럼 : 음식 타입, 원래 가격, 할인 적용 가격, 할인 가격)
*할인 : 나이-50*0.005
* 고객 정보가 없는 경우도 포함하여 조회, 할인 금액이 큰 순서대로 정렬  <- left join

흐름을 정리
어떤 테이블에서 데이터를 뽑을 것인가 
어떤 컬럼을 이용할 것인가
어떤 조건을 지정해야 하는가 
어떤 함수 (수식) 을 이용해야 하는가 

1.Query 를 적기 전에 흐름을 정리
어떤 테이블에서 데이터를 뽑을 것인가 → 주문 테이블, 고객 테이블
어떤 컬럼을 이용할 것인가 → 음식 타입, 주문 금액, 연령
어떤 조건을 지정해야 하는가 → 50세 이상
어떤 함수 (수식) 을 이용해야 하는가 → 두 테이블의 결합, 곱하기, 합계

2.구문으로 만들기
어떤 테이블에서 데이터를 뽑을 것인가 → from food_orders, customers
어떤 컬럼을 이용할 것인가 → cuisine_type, price, age
어떤 조건을 지정해야 하는가 → where age>=50
어떤 함수 (수식) 을 이용해야 하는가 → avg(price/quantity), case when, group by

 

3.전체 구조로 합치기

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) as discount_price
from food_orders a left join customers b on a.customer_id=b.customer_id
where b.age>=50
) t
group by 1
order by 4 desc

​

---

숙제

문제 :  식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation 하기

평균 음식 주문 금액 기준 : 5,000 / 10,000 / 20,000 / 30,000 초과
평균 연령 : ~ 20대 / 30대 / 40대 / 50대 이상
두 테이블 모두에 데이터가 있는 경우만 조회, 식당 이름 순으로 오름차순 정렬

◆ SQL 기본구조 적어보기

1. Query 를 적기 전에 흐름을 정리해보기 -> 2. 구문으로 적용해보기
   어떤 테이블에서 데이터를 뽑을 것인가 
      food_orders, customers 테이블
   어떤 컬럼을 이용할 것인가
      식당 이름, 주문 금액, 주문자 연령
   어떤 조건을 지정해야 하는가 
     X
   어떤 함수 (수식) 을 이용해야 하는가  
      inner join, order by 레스토랑 이름

◆ 각 테이블에서 필요한 컬럼 정리하기

- 공통 컬럼

customer_id
- 출력에 필요한 컬럼

c.age, f.restaurant_name, f.price

 

◆ Join 문으로 데이터 조회문 적어보기

SELECT f.restaurant_name, f.price, c.age
from food_orders f inner join customers c on f.customer_id = c.customer_id

◆ 데이터를 연산해보기

가게 별로 평균 가격, 평균 고객연령 출력

SELECT f.restaurant_name, AVG(f.price) avg_price, AVG(c.age ) avg_ags
from food_orders f inner join customers c on f.customer_id = c.customer_id
GROUP by 1

◆ 연산한 데이터를 segmentation 에 활용하기

SELECT 
    restaurant_name, 
    CASE 
        WHEN avg_price <= 5000 THEN 'price_group1'
        WHEN avg_price > 5000 AND avg_price <= 10000 THEN 'price_group2'
        WHEN avg_price > 10000 AND avg_price <= 20000 THEN 'price_group3'
        WHEN avg_price > 20000 AND avg_price <= 30000 THEN 'price_group4'
        ELSE 'price_group5' 
    END AS price_group,
    CASE 
        WHEN avg_ags < 20 THEN 'age_group1'
        WHEN avg_ags >= 20 AND avg_ags < 30 THEN 'age_group2'
        WHEN avg_ags >= 30 AND avg_ags < 40 THEN 'age_group3'
        WHEN avg_ags >= 40 AND avg_ags < 50 THEN 'age_group4'
        ELSE 'age_group5' 
    END AS age_group
FROM (
    SELECT 
        f.restaurant_name AS restaurant_name, 
        AVG(f.price) AS avg_price, 
        AVG(c.age) AS avg_ags
    FROM 
        food_orders f 
    INNER JOIN 
        customers c 
    ON 
        f.customer_id = c.customer_id
    GROUP BY 
        1
) a
ORDER BY 1

 

---

※ 요약

◆ 서브쿼리는 from 에서

◆ JOIN 의 기본 구조

-- LEFT JOIN
select 조회 할 컬럼
from 테이블1 a left join 테이블2 b on a.공통컬럼명=b.공통컬럼명
   -- 테이블1 a : 테이블1을 a로 부를거다.   그레서 '테이블1.공통컬러명'을 'a.공통컬럼명'으로 적음.

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

※Tip

◆ DBeaver에서  일부 쿼리문만 실행하기

원하는 부분을 선택하고 ctrl + enter

 

 

◆ 쿼리 작성 중간 중간에 실행해서 맞게 하고 있는지 확인하세요