Subquery란?
SQL에서 subquery(하위 쿼리 또는 서브쿼리)는 다른 쿼리 내에 포함된 또 다른 쿼리를 의미.
사용목적
- 데이터 필터링: subquery를 사용하여 특정 조건을 만족하는 데이터를 선택합니다. 예를 들어, WHERE 절에서 서브쿼리를 사용하여 특정 조건을 만족하는 행을 필터링할 수 있습니다.
- 데이터 집계: subquery를 사용하여 데이터를 집계하거나 계산할 수 있습니다. 예를 들어, 평균값, 합계 등을 계산할 때 사용할 수 있습니다.
- 서브쿼리 비교: 서브쿼리를 사용하여 두 개 이상의 데이터 세트를 비교하거나 연관된 데이터를 조회할 수 있습니다.
- 조인 조건: 조인 시 서브쿼리를 사용하여 조인 조건을 복잡하게 설정할 수 있습니다.
Subquery가 필요한 경우
- 여러번의 연산을 수행해야 할 때
- 조건문에 연산 결과를 사용해야 할 때
- 조건에 Query 결과를 사용하고 싶을 때
subquery 기본구조
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)
ex)
주문 테이블에서 주문 번호, 음식점명, 음식 준비시간을 가져오기
select order_id, restaurant_name, food_preparation_time
from
(
select order_id, restaurant_name, food_preparation_time
from food_orders
) 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
User Segmentation 와 조건별 수수료를 Subquery 로 결합해보기
(수수료 구간 -
~5000원 미만 0.05%
~20000원 미만 1%
~30000원 미만 2%
30000원 초과 3%)
- 어떤 테이블에서 데이터를 뽑을 것인가 → from food_orders
- 어떤 컬럼을 이용할 것인가 → restaurant_name, addr, delivery_time
- 어떤 조건을 지정해야 하는가 → X
- 어떤 함수 (수식) 을 이용해야 하는가 → avg(delivery_time), substring(addr, 1, 2), case when, group by
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
// food_orders 테이블에서 각 레스토랑 별로 평균 price_per_plate를 계산합니다. 이는 주문당 가격을 주문 수량으로 나누어 계산합니다.
결과는 restaurant_name으로 그룹화됩니다.
) b
//서브쿼리 a의 결과를 기반으로 각 레스토랑의 평균 price_per_plate에 따라 다른 조건에 따라 ratio_of_add를 계산합니다. 이는 CASE 문을 사용하여 구체적으로 처리되며, 주문당 가격 범위에 따라 다른 수수료율을 할당합니다.
음식점의 지역과 평균 배달시간으로 segmentation 하기
- 어떤 테이블에서 데이터를 뽑을 것인가 → 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' // <=20: 20분 이하
when avg_time>20 and avg_time <=30 then '20<x<=30' // 20분 초과 30분 이하>30
when avg_time>30 then '>30' end time_segment // 30분 초과
//서브쿼리 a에서 계산된 restaurant_name, sido, 및 time_segment를 선택하여 출력합니다.
time_segment는 평균 배달 시간(avg_time)에 따라 다른 구간으로 분류됩니다.
from
(
select restaurant_name,
substring(addr, 1, 2) sido,
avg(delivery_time) avg_time
from food_orders
group by 1, 2
) a
//food_orders 테이블에서 각 레스토랑 별로 배달 시간의 평균(avg_time)을 계산합니다.
addr에서 첫 두 글자를 추출하여(SUBSTRING(addr, 1, 2)) 시도(sido) 정보로 사용합니다.
결과는 restaurant_name과 sido로 그룹화됩니다.
복잡한 연산을 Subquery 로 수행하기
식 타입별 총 주문수량과 음식점 수를 연산하고, 주문수량과 음식점수 별 수수료율을 산정하기
(음식점수 5개 이상, 주문수 30개 이상 →
수수료 0.05% 음식점수 5개 이상, 주문수 30개 미만 →
수수료 0.08% 음식점수 5개 미만, 주문수 30개 이상 →
수수료 1% 음식점수 5개 미만, 주문수 30개 미만 → 수수로 2%)
- 어떤 테이블에서 데이터를 뽑을 것인가 → from orders
- 어떤 컬럼을 이용할 것인가 → price, cuisine_type, addr
- 어떤 함수 (수식) 을 이용해야 하는가 → avg(price), substring(addr, 1, 2)
select cuisine_type,
total_quantity
count_res
case when count_of_restaurant>=5 and count_of_orders>=30 then 0.0005 // count_res가 5 이상이고 total_quantity가 30 이상인 경우, ratio_of_add는 0.0005입니다.
when count_of_restaurant>=5 and count_of_orders<30 then 0.008 // count_res가 5 이상이고 total_quantity가 30 미만인 경우, ratio_of_add는 0.008입니다.
when count_of_restaurant<5 and count_of_orders>=30 then 0.01 // count_res가 5 미만이고 total_quantity가 30 이상인 경우, ratio_of_add는 0.01입니다.
when count_of_restaurant<5 and count_of_orders<30 then 0.02 end ratio_of_add // count_res가 5 미만이고 total_quantity가 30 미만인 경우, ratio_of_add는 0.02입니다.
from
(
select cuisine_type,
sum(quantity) total_quantity,
count(distinct restaurant_name) count_res
from food_orders
group by 1
) a
//food_orders 테이블에서 각 cuisine_type 별로 주문된 총 수량(total_quantity)을 계산합니다. 이는 quantity의 합계로 구합니다.
각 cuisine_type에 속한 고유한 restaurant_name의 수를 계산하여 (count_res), 그룹화합니다.
필요한 데이터가 서로 다른 테이블에 있을 때 조회하기 (JOIN)
- SQL에서 JOIN은 둘 이상의 테이블에서 데이터를 결합하여 하나의 결과 집합을 생성하는 방법을 제공한다.
- JOIN은 관계형 데이터베이스에서 데이터를 연결하고 관계를 이해하는 데 중요한 기능이다.
JOIN의 유형
- INNER JOIN: 두 테이블 간의 일치하는 행만 반환합니다. 일치하지 않는 행은 제외됩니다.
- LEFT JOIN (또는 LEFT OUTER JOIN): 왼쪽 테이블의 모든 행을 포함하며, 오른쪽 테이블에서 일치하는 행이 있는 경우에만 연결됩니다.
- RIGHT JOIN (또는 RIGHT OUTER JOIN): 오른쪽 테이블의 모든 행을 포함하며, 왼쪽 테이블에서 일치하는 행이 있는 경우에만 연결됩니다. 일부 데이터베이스에서는 RIGHT JOIN을 지원하지 않을 수 있습니다.
- FULL JOIN (또는 FULL OUTER JOIN): 양쪽 테이블의 모든 행을 포함합니다. 일치하는 행이 없는 경우 NULL 값으로 채워집니다.
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.공통컬럼명
공통컬럼은 묶어주기 위한 ‘공통 값’ 이기 때문에 두 테이블의 컬럼명은 달라도 괜찮습니다. 예를 들어 주문정보에는 ‘고객ID’, 고객정보에는 ‘고객아이디’ 라고 컬럼명이 되어있다면, 테이블1.고객ID=테이블2.고객아이디 와 같이 묶어줄 수 있습니다.
ex)
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
JOIN 으로 두 테이블의 데이터 조회하기
한국 음식의 주문별 결제 수단과 수수료율을 조회하기
조건 : (조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 결제 수단, 수수료율) *결제 정보가 없는 경우도 포함하여 조회
- 어떤 테이블에서 데이터를 뽑을 것인가 → from food_orders, payments
- 어떤 컬럼을 이용할 것인가 → order_id, restaurant_name, price, pay_type, vat
- 어떤 조건을 지정해야 하는가 → where cuisine_type=’Korean’
- 어떤 함수 (수식) 을 이용해야 하는가 → left join payments (on order_id)
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
//food_orders 테이블 a와 payments 테이블 b를 order_id 열을 기준으로 조인합니다.
LEFT JOIN은 food_orders 테이블의 모든 행을 포함하고, payments 테이블에서 일치하는 order_id가 있는 경우에만 결합합니다. 일치하지 않는 경우 payments 테이블의 해당 열은 NULL이 됩니다.
where cuisine_type='Korean'
//한식을 주문한 경우에 해당하는 데이터만 필터링합니다.
cuisine_type이 'Korean'인 행만 선택됩니다.
고객의 주문 식당 조회하기
조건 : (조회 컬럼 : 고객 이름, 연령, 성별, 주문 식당) *고객명으로 정렬, 중복 없도록 조회
- 어떤 테이블에서 데이터를 뽑을 것인가 → from customers, food_orders
- 어떤 컬럼을 이용할 것인가 → name, age, gender, restaurant_name
- 어떤 조건을 지정해야 하는가 → X
- 어떤 함수 (수식) 을 이용해야 하는가 → left join orders (on customer_id), order by , distinct
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값을 제거할려면
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
where c.name is not null // null 값 제거
order by c.name
JOIN 으로 두 테이블의 값을 연산하기
주문 가격과 수수료율을 곱하여 주문별 수수료 구하기
조건 : (조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 수수료율, 수수료) *수수료율이 있는 경우만 조회
- 어떤 테이블에서 데이터를 뽑을 것인가 → 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
//food_orders 테이블 a와 payments 테이블 b를 order_id 열을 기준으로 내부 조인합니다.
INNER JOIN은 food_orders 테이블과 payments 테이블에서 모두 일치하는 order_id를 가진 행만 반환합니다.
50세 이상 고객의 연령에 따라 경로 할인율을 적용하고, 음식 타입별로 원래 가격과 할인 적용 가격 합을 구하기
조건 : (조회 컬럼 : 음식 타입, 원래 가격, 할인 적용 가격, 할인 가격) *할인 : 나이-50*0.005
- 어떤 테이블에서 데이터를 뽑을 것인가 → from food_orders, customers
- 어떤 컬럼을 이용할 것인가 → cuisine_type, price, age
- 어떤 조건을 지정해야 하는가 → where age>=50
- 어떤 함수 (수식) 을 이용해야 하는가 → avg(price/quantity), case when, group by
select cuisine_type,
sum(price) "원래 가격",
sum(price)-sum(discount_price) "할인 적용 가격",
sum(discount_price) "할인 가격"
//
SUM(price) AS "원래 가격": 각 요리 종류별 주문의 총 원래 가격을 합산합니다.
SUM(price) - SUM(discount_price) AS "할인 적용 가격": 각 요리 종류별 할인을 적용한 총 가격을 계산합니다.
SUM(discount_price) AS "할인 가격": 각 요리 종류별 총 할인 금액을 계산합니다.
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
//(내부 서브쿼리 ('t')food_orders 테이블(a)과 customers 테이블(b)을 customer_id를 기준으로 조인합니다.
조인된 결과에서 b.age가 50 이상인 고객의 경우에 대해서만 처리합니다.
각 주문의 price와 할인 가격(discount_price)을 계산합니다. 할인 가격은 주문의 가격에 고객의 나이(b.age)에 따라 할인율을 적용하여 계산됩니다.
group by 1
order by 4 desc