TIL 2024-08-30 (SQL - 4주차)

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

 

Subquery? 문자 변경 후 숫자 연산을 하고 그 결과로 또 연산을 해야하거나, Query문이 너무 길 때 조금 더 효율적이고 알아보기 쉽게 하기 위해 사용.

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

말 그대로 Query문이 너무 복잡해지거나 길어질 때 사용한다.

 

사용 방법을 쉽게 실습을 통해 알아보자

 

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

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

 

위 Query 문을 보면서 설명한다면, 먼저 음식 주문시간이 25분을 초과하는 지

확인하는 수식과 컬럼의 이름을 지정해준다.

그리고 그 컬럼을 over_time으로 컬럼명을 지어주고, ()를 닫은 다음 새로운 select를 만들어 if문을 사용해 over_time이 0보다 크면 over_time이 결과에 나오게 하고 그것이 아니면 0이라고 나오게 한다.

결과 사진

 

Subquery를 사용하는 순서는 문제를 먼저 읽고 문제에서 요구하는 것 중 가장 먼저 필요로 하는 것을 해결한 후,

()로 묶어 작성하고 그 다음 조건을 Query문에 작성해주는 것이 좋다.

 

또한 나는 문제에서 요구하는 마지막 결과값을 마지막에 작성하는 Query문이라고 생각하니 이해하기 더욱 수월했다.

 

더욱 이해하기 쉽게 실습 문제를 더 풀어보자.

 

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

문제를 보며 천천히 Query문을 작성하면 어렵지 않게 작성할 수 있고 중간 중간 Query문을 실행하여 에러가 나는지 확인하는 습관도 좋은 습관이다.

 

먼저 음식 타입별 총 주문수량과 음식점 수를 연산해야 하므로,

 

select cuisine_type,
       sum(quantity) total_quantity,
       count(distinct restaurant_name) count_res
from food_orders
group by 1

•  

•  

음식 타입별 이기 때문에 cuisine_type 컬럼을 불러오고 주문 수량을 확인하기 위해 sum 연산을 사용해준다.

음식점 수를 연산하기 위해 count 함수를 사용하고 중복되지 않게 distinct를 사용한다.

 

음식 타입별 구별하기 위해 group by를 사용하면 문제 앞 부분을 해결했다고 볼 수 있다.

이제 subquery문을 사용하여 나머지 조건들을 계산해주면 되는데,

 

select cuisine_type, 
       total_quantity,
       count_res,
       case when count_res>=5 and total_quantity>=30 then 0.0005
            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

 

 먼저 작성한 Query문을 ()로 묶어줘고 위에 필요한 컬럼들을 작성해준다.

그리고 case 를 사용하여 조건을 붙여주면 되는데 () 안에 있는 미리 계산한 컬럼들을 가져와 사용하면 수월하게 조건문을 작성할 수 있다.

 

결과 사진

---

 

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

 

JOIN은 주문 가격은 주문 테이블에 있지만, 어떤 수단으로 결제 했는지는 결제 테이블에 있는 경우 둘 다 한 번에 확인하려고 할 때 사용한다.

 

JOIN은 엑설의 Vlookup과 유사하다고 하는데 나는 엑셀의 Vlookup을 모르기에....

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

 

형태는 똑같고 left, inner 자리만 요구에 따라 바꿔주면 된다.

 

간단한 실습을 해보자면,

 

select fo.order_id,
       fo.customer_id,
       fo.restaurant_name,
       fo.price,
       c.name,
       c.age,
       c.gender
from food_orders fo left join customers c on fo.customer_id = c.customer_id

 

처음에 작성할 때 select 보단 from을 먼저 작성해주는 게 편하다.

먼저 두 테이블에 공통 되는 컬럼을 찾은 뒤 형태에 맞게 작성해주면 되는데,

 

테이블명이 너무 길면 food_orders fo, 이런 식으로 fo라는 별명을 줄 수 있고 두 개의 공통된 컬럼을 연결해줌으로서 두 테이블의 컬럼을 모두 불러 올 수 있으며 특정 컬럼을 불러오고 싶을 땐 테이블의 별명을 붙여야 한다.  ex) fo.order_id

 

결과 사진

 

---

 

앞에 배운 subquery문 까지 사용하는 실습문제를 풀어보자.

 

• [실습] 50세 이상 고객의 연령에 따라 경로 할인율을 적용하고, 음식 타입별로 원래 가격과 할인 적용 가격 합을 구하기
• 고객 정보가 없는 경우도 포함하여 조회, 할인 금액이 큰 순서대로 정렬
• (조회 컬럼 : 음식 타입, 원래 가격, 할인 적용 가격, 할인 가격)
• 할인 : 나이-50*0.005

마지막에 결과에 나타내야하는 컬럼은 음식 타입, 원래 가격, 할인적용가격, 두 가격의 합 이렇게 4개이다.

 

그러면 먼저 작성해야하는 것은 음식가격과 고객의 나이를 한 번에 계산해야하기 때문에 두 테이블의 컬럼을 합쳐야한다.

 

select fo.cuisine_type,
       fo.price,
       fo.price*((c.age-50)*0.005) discount_price
from food_orders fo inner join customers c on fo.customer_id = c.customer_id
where c.age >= 50

 

이러면 나라별 음식 가격과 할인율이 나오고 나머지 조건은 이제 Subquery를 사용하여 작성하면 된다.

 

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

 

필요한 컬럼 순서대로 작성을 한 후 먼저 적은 컬럼들을 이용해서 원래 가격, 할인 가격, 할인 된 가격을 작성해준다.

음식 타입별로 해야하기 때문에 group by 1(cuisine_type)을 작성하고 할인 금액이 큰 순서 이므로 order by 4 desc를 작성해준다.