2024-03-20

1. 오늘 배운 것

 

 

Subquery

연산이 여러번 필요할 경우, 연산결과를 반복해서 적어줘야 할 경우에 사용할수 있는 구문

 

 

• 음식 주문시간이 25분보다 초과한 시간을 가져오기

select order_id, restaurant_name, if(over_time>=0, over_time, 0) over_time 
-- 25분-25분=0분으로 25분초과 이기 때문에 >=
from 
(
select order_id, restaurant_name, food_preparation_time-25 over_time 
-- 25분 초과 이기 때문에 -25
from food_orders
) a

 

+

주문 시간이 25분보다 초과 이기 때문에 주문시간 컬럼에서 25분을 빼준다

서브쿼리내로만 할 경우 초과되지 않는 주문건들은 - 값을 추출하기 때문에 

IF 함수를 사용하여 초과되지 않는 주문건들을 0 으로 변환해준다.

초과 이니까 > 부호가 맞지 않을까 했는데, 25분-25분=0분 으로 25분 초과 이기 때문에 >= 부호가 맞다

 

 

* 위와 같이 서브쿼리를 사용 하지 않을 경우 어떻게 작성해야할지 작성해보았다

select order_id
     , restaurant_name
     , IF((food_preparation_time-25)>=0, food_preparation_time-25, 0) over_time
FROM food_orders

 

3번째 조회할 칼럼에 연산함수가 반복적으로 들어가 쿼리문이 지저분해보인다.

그리고 서브쿼리 쓰는게 훨씬 멋있어보임..ㅎ

 

 

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

( 수수료 구간 -

~5000원 미만 0.05%   ~20000원 미만 1%   ~30000원 미만 2%   30000원 초과 3%)

select restaurant_name,
       price_per_plate*ratio_of_add "수수료" -- //3번
from 
(-- //2번
select restaurant_name,
       case when price_per_plate<5000 then 0.005
            when price_per_plate between 5000 and 19999 then 0.01 -- 2만원 미만
            when price_per_plate between 20000 and 29999 then 0.02 -- 3만원 미만
            else 0.03 end ratio_of_add,
       price_per_plate
from 
( -- 가장 가운데에 있는 괄호 먼저 보기 // 1번
select restaurant_name, avg(price/quantity) price_per_plate
from food_orders
group by 1
) a
) b

 

+

5000원 미만 < 5000

20000원 미만 BETWEEN 5000 AND 19999

30000원 미만 BETWEEN 20000 AND 29999

그 외

 

 

CASE 문에서는 위에서부터 순서대로 처리함

초과~ 미만 조건에서는 BETWEEN 함수를 사용해주는 것이 용이해보인다.

 

이렇게 쿼리문이 장문일 경우에는 가장 가운데에 있는 괄호를 먼저 보고 판단한다.

 

< 실행순서 > 

1번. 음식점의 이름과 평균 단가

2번. 1번에서 구한  평균 단가에 따른 수수료

3번. 음식점의 수수료를 연산

 

 

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

평균배달시간 : 20분, 30분, 30분 초과

 

* segmentation : 세분화, 분할

 

① 지역, 평균 배달 시간 뽑아오기

SELECT restaurant_name 
     , SUBSTR(addr, 1, 2) sido
     , AVG(delivery_time) avg_delivery_time
FROM food_orders
GROUP BY 1,2

 

+

음식점의 지역과, 평균 배달시간을 추출

이후 조회하기 편하도록 별칭을 붙여둔다

 

② 서브쿼리로 사용

SELECT restaurant_name
     , sido
     , avg_delivery_time
     , CASE WHEN avg_delivery_time <= 20 THEN '<=20' -- 20분
            WHEN avg_delivery_time > 20 AND avg_delivery_time <=30 THEN '20 < x <= 30' -- 30분
            ELSE '>30' END delivery_time_segment-- 30분초과
FROM
(
SELECT restaurant_name 
     , SUBSTR(addr, 1, 2) sido
     , AVG(delivery_time) avg_delivery_time
FROM food_orders
GROUP BY 1,2
) a

 

+

긴 쿼리를 작성할 때에는 중간중간 실행하여 맞게 작성되었는지 확인해볼 것!

 

 

JOIN 

엑셀의 vlookup 과 유사하다.

어떤 테이블에서 뽑아올지, 어떤 컬럼에서 묶을지 중요

 

 

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

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

*할인 : 나이-50*0.005

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

 

SELECT cuisine_type 
-- 서브쿼리 내에 하나의 컬럼명으로 지정이 된 것이기 때문에 a. 을 붙이거나, 생략가능
     , 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 c.age >= 50
) a
GROUP BY 1
ORDER BY 3 DESC

 

+

메인쿼리에서 서브쿼리의 컬럼을 사용할때에는 

기존 테이블이 아닌, 서브쿼리 테이블의 별칭을 붙이거나 생략이 가능하다

 

 

 

---

 

2. 문제풀이

• 음식점의 총 주문수량과 주문금액을연산하고, 주문 수량을 기반으로 수수료할인율 구하기

수량이 5개이하 > 10%

수량이 15개 초과, 총 주문금액이 300000 이상 > 0.5%

이 외 1%

 

-- 내가 작성한 쿼리문

SELECT cuisine_type
     , 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 cuisine_type 
     , SUM(quantity) total_quantity
     , SUM(price) total_price
FROM food_orders
GROUP BY 1
) a

-- 학습자료의 쿼리문

SELECT restaurant_name
     , total_quantity
     , total_price
     , CASE WHEN total_quantity <= 5 THEN 0.1
            WHEN total_quantity > 15 AND total_price >= 300000 THEN 0.005
            ELSE 0.01 END discount_rate
FROM
(
SELECT restaurant_name -- 음식 타입이 아니라 음식점별이기 때문에 이 컬럼 사용해야함
     , SUM(quantity) total_quantity
     , SUM(price) total_price
FROM food_orders
GROUP BY 1
) a

 

+

음식점'의' 주문수량과 수수료 할인율 이기 때문에

음식타입이 아닌 음식점이름을 사용해서 출력해야함

 

---

• 고객의 주문 식당 조회

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

 

-- 내가 작성한 쿼리문

SELECT DISTINCT c.name
     , c.age 
     , c.gender 
     , f.restaurant_name 
FROM customers c LEFT JOIN food_orders f ON c.customer_id = f.customer_id 
ORDER BY c.name

-- 학습자료의 쿼리문

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

 

+

고객'의' 주문 식당을 조회하기 때문에 

고객 테이블을 기준으로 left join 을 했으나

강의에서는 주문이력 테이블 기준으로 join 을 했다.

 

 

Q.

왜 고객이 아닌 주문이력 테이블이 기준인건지?

 

A.

고객을 기준으로 주문이력을 가져올 경우 고객테이블에 데이터가 없는 경우에는 가져 오지 않는다.

위의 실습문제의 경우 고객테이블을 전부 출력해야하기 때문에 

주문이력 테이블을 기준으로 조인을 해야 한다.

 

 

 

---

 

 

4주차는 강의영상과 학습자료, 디비버에 있는 데이터가 맞지 않는 문제가 있었다.

강의 속 쿼리를 그대로 따라해도 결과 데이터가 다르거나,

학습자료 속 쿼리를 복붙했는데도 결과 데이터가 다른 경우가 있었는데

그래서 더 궁금증이 폭발 했던 것 같다

 

디비버로 이것저것 쿼리를 짜서 실행해볼 수 있는 작업이 굉장히 재미있다.

 

4주차 강의를 끝내고 숙제를 제출하려고 하는데

답 쿼리는 알았으나 추가 궁금증이 해결되지 않아 따로 포스팅 했다.

튜터님께 여쭤보고 해결이 되면 바로 수정할 예정!

 

 

 

 

팀원들과 이야기 중에 

NOT NULL  이 LIKE && 

과 동일한 결과를 추출한다는 것을 얼핏 들었는데 

잘 이해가 가지 않았다.

다음에 다시 이야기 해 본 후 추가 할 것