스파르타 코딩클럽 DAY-4

원래 우리 팀원들끼리 정한 시간은 

5시 30분까지 강의와 숙제, 블로그까지 완료하고 서로 만나기로 했지만..

SQL 3주차 강의부터 점점 내용이 많아지고 어려워지면서 이제서야 겨우 블로그를 쓰게 된다 ㅜㅜ 

매번 강의 내용에서 하는 것들을 실습해보려고 하지만 

내용들이 점점 복잡해지면서 혼자하는게 힘들어지는게 느껴진다.... 😭

그래도 복습 꾸준히 해야지. 

 

오늘 배운 내용.

1. 서브쿼리 

서브쿼리란 쿼리 안에 쿼리를 말하는 것으로 ,

주로 여러번의 연산을 수행해야 할 때, 조건문에 연산결과를 사용할 때, 조건에 쿼리 결과를 사용하고 싶을 때 

많이 사용한다. 

서브 쿼리문은 

SELECT 컬럼 1, 스페셜 컬럼
FROM (SELECT 컬럼 1, 컬럼 2, 스페셜 컬럼
      FROM 테이블)

형식으로 이루어져 있다. 

서브쿼리문에 별명을 붙이면 그냥 간단하게 괄호가 끝 난 부분에 별명(나의 경우 a 로 만듬) 을 붙여주면 된다.

 

예시 1 : 음식주문시간이 25분을 초과한 시간 가져오기

 - 들어갈 컬럼 : order_id, restaurant_name, food_preeparation_time

 - 들어갈 함수 : 서브쿼리문, if(시간을 초과했는지 확인하기 위해서) 

SELECT order_id,
       restaurant_name,
       if(oveer_time>=0,over_time,0) over_time
FROM
(
SELECT order_id,
       restaurant_name,
       food_preparation_time-25 over_time
FROM food_orders
)a

음식준비 시간-25 가  0이상인 경우 기존의 over_time 으로 나오는 결과가 나온다. 

 

실습 1 : 음식별 평균 단가별 segmentation 을 진행

수수료 구간 : 5000원 미만-0.5% / 20000원 미만-1% / 30000원 미만-2% / 30000원 초과 - 3%

 - 들어갈 컬럼 : cuisine_type, price, restaurant_name

 - 들어갈 함수 : avg, case when 

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

이건 쿼리안에 쿼리 가 있는 쿼리문이기 때문에 ...

문장이 엄청엄청 길어진다.

어제한 concat 보다 훨씬 길다..

하지만 이렇게 서브쿼리문을 작성하면서 

복잡하고 어려운 연산 결과들을 포함한 결과를 훨씬 더 깔끔하게 낼 수 있다는 장점이 있다.

 

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

조건 : 20분 이하, 20분 초과 30분 이하, 30분 초과

 - 들어갈 컬럼 : restaurant_name, addr, delivery_time

 - 들어갈 함수 : substr, avg

SELECT restaurant_name,
       sido,
       case when avg_time<=20 then '<=20'
            when avg_time>20 and avg_time<=30 then '20<x<=30'
            else '>30' end time_segment
FROM
(
SELECT restaurant_name,
       substr(addr,1,2) sido,
       avg(delivery_time) avg_time
FROM food_orders
GROUP BY 1,2
)a

 

 

실습 3 : 음식점의 총 주문수량과 주문금액을 연산하고, 주문수량을 기반으로 수수료 할인률 구하기 

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

 - 들어갈 컬럼 : restaurant_name, quantity, price

 - 들어갈 함수 : sum

SELECT restaurant_name ,
       case when total_quantity <=5 then 0.1
            when total_quantity>15 and total_price>=300000 then 0.005
            else 0.01 end "할인률"
FROM
(
SELECT restaurant_name ,
       sum(quantity) total_quantity,
       sum(price) total_price
FROM food_orders 
GROUP BY 1
)a

case when 을 통해서 각각의 조건들을 붙여주고 계산해주면 된다. 

 

2. JOIN 구문 

JOIN 은 필요한 데이터가 다른 테이블에 있을 때 가져와서 연결할 수 있는 함수 

JOIN 의 종류(다양한 종류가 있지만 간단하게 2개만)

 1) LEFT JOIN : 공통 컬럼을 기준으로 하나의 테이블에 값이 없더라도 모두 조회되는 경우 / 왼쪽 테이블이 기준

     a 테이블이 왼쪽에 있고, b 테이블이 오른쪽에 있을 때 LEFT JOIN을 하면 

     a 테이블에 있는 컬럼은 전부, b 테이블에서는 a 테이블과 공통되는 컬럼만 가져오게 됨

 2) INNER JOIN : 공통 컬럼을 기준으로 두 테이블에 모두 있는 값만 조회= 교집합 생각하기 

 

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

 

INNER JOIN 과 LEFT JOIN 구문의 형식은 같고 그냥 JOIN 앞에 있는 문자만 바꿔주면 된다. 

 

실습 1 : 주문테이블과 고객 테이블을 customer_id를 기준으로 LEFT JOIN으로 묶기 

조회 컬럼 : order_id, customer_id, restaurant_name,price,name,age,gender

SELECT f.order_id ,
       f.customer_id,
       f.restaurant_name,
       f.price,
       c.name,
       c.age,
       c.gender
FROM food_orders f LEFT JOIN customers c on f.customer_id =c.customer_id

SELECT 문에서 조회하고자 하는 컬럼을 그 테이블의 별칭을 이용해서 간단하게 조회하면 된다. 

 

실습 2 : 한국 음식의 주문별 결제 숟단과 수수로율 조회하기 

조회 컬럼 : order_id, restaurant_name, price / pay_type, vat

SELECT f.order_id,
       f.restaurant_name,
       f.price,
       p.pay_type ,
       p.vat
FROM food_orders f LEFT JOIN payments p on f.order_id = p.order_id
WHERE cuisine_type = 'Korean'

 

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

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

할인 : (나이-50)*0.005 (고객정보가 없는 경우도 포함, 할인 금액이 큰 순서대로 정렬)

SELECT cuisine_type ,
       sum(price) price,
       sum(price*discount_rate) discounted_price
FROM
(
SELECT f.cuisine_type,
       f.price,
       c.age,
       (c.age-50)*0.005 discount_rate
FROM food_orders f LEFT JOIN customers c on f.customer_id = c.customer_id
WHERE age>=50 
)a
GROUP BY cuisine_type
ORDER BY discounted_price desc

고객정보가 없는 경우도 포함하라고 한건 food_orders 테이블을 기준으로 LEFT JOIN 을 하면 된다. 

이후에 할인금액이 큰 순서대로 정렬이니까 내림차순으로 정렬하면 되는데...

이상하게 결과가 정렬이 안되는 것같다..

페이지에 있는 정답을 복붙에도 저렇게 나오네... ? 

 

 

3. 오늘의 숙제 및 느낀 점 

 1) 오늘의 숙제 :

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

- 평균 음식 주문 금액 기준 : 5,000 이하 / ~10,000 / ~30,000 / 30,000 초과 - 평균 연령 : ~ 20대 / 30대 / 40대 / 50대 이상

 두 테이블 모두에 데이터가 있는 경우만 조회, 식ㄷ당 이름 순으로 오름차순 정렬-

 - 들어갈 테이블 : food_orders / customers

 - 들어갈 컬럼 : restaurant_name, price , customer_id  / age

SELECT restaurant_name ,
       case when avg_price <=5000 then 'price_gruop1'
            when avg_price>5000 and avg_price<=10000 then 'price_group2'
            when avg_price>10000 and avg_price<=30000 then 'price_group3'
            else 'price_group4'end avg_price,
       case when avg_age <30 then 'age_group1'
            when avg_age >=30 and avg_age<40 then 'age_group2'
            when avg_age>=40 and avg_age<50 then 'age_group3'
            else 'age_group4' end avg_age 
FROM
(
SELECT f.restaurant_name,
       avg(f.price) avg_price,
       avg(c.age) avg_age
FROM food_orders f INNER JOIN customers c on f.customer_id = c.customer_id 
GROUP BY 1
)a
ORDER BY restaurant_name

제시된 문장에 '두테이블 모두에 데이터가 있는 경우만 조회' 라고 했으니 

INNER JOIN 으로 두 테이블을 연결해야 한다. 

그리고 조건들이 길게 나열되어 있지만, 결론적으로 따지자면 

price 를 평균 내서 제시된 기준으로 그룹을 나누고, 

age 도 평균을 내서 제시된 기준으로 그룹을 나누면 

price_group 과 age_group 으로 나눌 수 있다. 

 

긴 문장이지만 차근차근 

어떤 서브쿼리를 먼저 진행해야 하는지를 생각하면서 하다보면 

나름의 재미도 느끼면서 풀수 있었다. 

 

 

2) 느낀점

사실 SQL 강의를 들을 때 부터 가장 긴장했던 부분이 

오늘 배운 서브쿼리와 INNER JOIN 이었다. 

SQLD를 풀때 가장 많이 틀린 부분이기도 하고, 문제 자체가 길기도 하다 보니 

매번 겁을 먹으면서 풀었던 기억이 있는데 

막상 실제로 MY SQL 로 풀어보니 

긴 문장이어도 하나하나 접근하고, 쿼리를 적었을 때 결과값이 나오는거에 대한 뿌듯함과 희열도 있었다.

어제는 강의에서 제시하는 실습들을 강의를 보면서 막 적느라 정신이 없었는데 

오늘은 그래도 강의를 멈추고, 먼저 풀어본 뒤 답을 맞출 수 있는 여유는 생긴 것 같다 ㅎㅎ 

이제 SQL 은 하루 남았는데 마무리까지 완벽하게 하자 !!! 

화이팅 ><