SQL 4주차

윤수빈·2024년 7월 3일

sql 사전캠프 스파르타코딩클럽

[엑셀보다 쉽고 빠른 SQL] 4주차 정리

SQL 3주차 에 이어서 SQL 4주차 마무리까지 정리한 글이다.

이번 주차는 Subquery, Join에 대해서 공부했다.

1. Subquery

이전 주차에서는 문자 포맷팅과 조건에 따른 연산이 필요한 경우를 배우면서 다양한 연산이 필요한 경우 쿼리를 어떻게 작성하는지 배웠었다.

하지만 한 쿼리에 여러 조건과 연산을 모두 넣으니 햇갈리기도 하고 한 번에 정리하기 조금 어려웠다.

이번에 배운 Subquery의 경우 이러한 문제가 직면했을 때 비교적 간단하게 쿼리문을 작성할 수 있도록 도와준다.

💡 Subquery 활용 사례

연산을 여러 번 하여 쿼리문이 길어지는 경우
연산한 결과를 다른 연산이나 조건문에 사용하고 싶은 경우
필요한 데이터가 여러 테이블에 나누어진 경우

💡 Subquery 사용 구조

조회할 테이블에서 서브쿼리를 사용한 경우

select column1, special_column
from
    ( /* subquery */
    select column1, column2 special_column
    from table1
    ) a
    
    => from 에서 조회되는 테이블에서 column1

조건에서 서브쿼리를 사용한 경우

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

실습 내용 중
음식 타입별 총 주문수량과 음식점 수를 연산하고, 주문수량과 음식점수 별 수수료율을 산정하기 로 예를 들면,

음식 타입별 총 주문수량과 음식점 수를 연산하고 -> 서브쿼리
주문수량과 음식점수 별 수수료율을 산정하기 -> 메인쿼리로 예상할 수 있다.

이렇게 하면 서브쿼리에서 조회된 데이터를 토대로 메인쿼리에서 조회하여 산정(조건식 사용)이 가능한 것이다.

즉,

음식 타입별 총 주문수량과 음식점 수를 연산 - 서브쿼리

select cuisine_type, sum(quantity) sum_quantity, count(DISTINCT restaurant_name) count_restaurant
from food_orders
group by 1, 2

주문수량과 음식점수 별 수수료율을 산정 - 메인쿼리
(음식점수 5개 이상, 주문수 30개 이상 → 수수료 0.05%
음식점수 5개 이상, 주문수 30개 미만 → 수수료 0.08%
음식점수 5개 미만, 주문수 30개 이상 → 수수료 1%
음식점수 5개 미만, 주문수 30개 미만 → 수수로 2%)

select case when count_restaurant>=5 then if(sum(quantity)>=30, 0.05, 0.08)
			else if(sum(quantity)>=30, 0.1, 0.2)
            end "수수료율"

이 되고, 이제 합치면 된다.
만약 음식 타입, 주문수량, 음식점 수, 수수료율을 조회하고 싶다면

select cuisine_type,
	sum_quantity,
	count_restaurant,
	case when count_restaurant>=5 then if(sum_quantity>=30, 0.05, 0.08)
	else if(sum_quantity>=30, 0.1, 0.2)
    end "수수료율"
from 
(select cuisine_type, sum(quantity) sum_quantity, count(DISTINCT restaurant_name) count_restaurant
from food_orders
group by 1) a

으로 작성하면 되고,
주의해야 할 점은 서브쿼리 from절에서 조회된 데이터와 컬럼들에 한해서만 메인쿼리에서 조회가 가능하기 때문에 위 구문에서 food_orders의 price를 조회하고 싶다면 서브쿼리에도 추가를 해줘야 한다.

2. Join (Left, Right, Inner)

Join 은 테이블끼리 관계가 형성되어 있을때 사용이 가능하다.
관계란 서로 다른 테이블에 연결되는 키값이 있는 경우를 말한다.

예를 들어,
customers 테이블에 있는 customers_id 란 컬럼이
food_orders 테이블에도 customers_id 란 컬럼이 있을 때,

서로 공통되는 키 값이 있기 때문에 customers_id 를 기준으로 데이터를 조회가 가능하다.

이렇게 서로 다른 테이블에 있을 때 조회하기 위해 JOIN 을 사용한다.

💡 기본 사용구조

(조회할 테이블1) JOIN (조회할 테이블2) ON (조회할 테이블1).(공통컬럼)=(조회할 테이블2).(공통컬럼)

=> food_orders left join payments on food_orders.order_id=payments.order_id

여기서 조회할 테이블에 이름을 지정해 주면 더 간단하게 작성할 수 있다.

(조회할 테이블1) a JOIN (조회할 테이블2) b ON a.(공통컬럼)=b.(공통컬럼)

=> food_orders fo left join payments p on fo.order_id=p.order_id

💡 JOIN 방식
join 은 여러 방식으로 데이터를 조회할 수 있다. (배운건 inner / left)

inner join
: 두 테이블을 조인할 때, 두 테이블에 모두 지정한 열의 데이터가 있어야 한다. (교집합)
outer join
: 두 테이블을 조인할 때, 1개의 테이블에만 데이터가 있어도 결과가 나온다.
Left - 왼쪽 테이블의 모든 값이 출력
Right - 오른쪽 테이블의 모든 값이 출력
FULL - 테이블의 모든 값이 출력
cross join
: 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인하는 기능이다.
상호 조인 결과의 전체 행 개수는 두 테이블의 각 행의 개수를 곱한 수만큼 된다.
예를 들어, 테이블1과 2에 각 3행씩 있다면 (테이블1 행 개수 * 테이블2 행 개수) = 9
self join
: 자신이 자신과 조인한다는 의미로, 1개의 테이블을 사용한다.
한 테이블에 있는 값이 서로 어떤 연관이 있어 추가적인 데이터를 가공하여 조회하고 싶을때 사용한다. (배우자 정보 등)

💡 JOIN 활용한 실습

Join으로 서로 다른 테이블의 데이터를 가져와 연산식을 적용하고 조회하는 것을 해보았다.

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

일단 할인율 적용에 대한 데이터를 먼저 조회하기 위해 서브쿼리를 사용한다.
하지만 그 전에 고객과 음식주문에 대한 정보가 있어야 하기 때문에 join을 사용하여 고객 테이블, 주문 테이블을 조회해야 하고 고객 정보가 없는 경우도 포함 해야한다.

서브쿼리

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

메인 쿼리에서는 음식 타입별 가격 합과 할인적용가, 할인 가격을 구해주면 된다.
또한, 할인 금액이 큰 순서대로 정렬해야 한다.

메인쿼리

select cuisine_type,
       sum(price) "타입별 가격 합",
       sum(price)-sum(discount_price) "할인 적용 가격",
       sum(discount_price) "할인 가격" 
from (서브쿼리)
group by 1
order by 4 desc

합치면 아래 결과가 나온다.

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

3. 4주차 숙제 정리

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

식당 정보와 주문자 정보는 별도 테이블로 있기 때문에 JOIN 방식을 사용
평균 음식 주문 금액과 주문자의 평균 연령 구하는 식 사용 - AVG()
식당별로 구분 - GROUP BY
두 테이블에 데이터가 모두 있는 경우 - INNER JOIN
식당 이름 순으로 오름차순 정렬 - ORDER BY
평균 연령 및 평균 주문 금액 기준으로 나누어야 함 - 조건문과 함께 비교연산자 혹은 BETWEEN 사용

일단 식당별이라는 공통분모가 있기 때문에 서브쿼리로 두었다.
그 다음 메인 쿼리에서 각 평균 주문 금액과 평균 연령에 따른 구분을 나누기로 했다.

서브쿼리

select fo.restaurant_name, 
	   avg(fo.price) avg_price, 
       avg(c.age) avg_age
from food_orders fo inner join customers c on fo.customer_id=c.customer_id
group by 1

두 테이블에 데이터가 모두 있는 경우로 inner join 해준다
식당별을 그룹으로 묶어준다
avg를 사용하여 각 평균 금액/나이를 구해준다

메인쿼리

select restaurant_name, 
	case when avg_price<=5000 then 'price_group1'
		 when avg_price<=10000 then 'price_group2'
		 when avg_price<=30000 then 'price_group3'
		 else 'price_group4' end price_group,
	case when avg_age<=29 then 'age_group1'
		 when avg_age<=39 then 'age_group2'
		 when avg_age<=49 then 'age_group3'
		 else 'age_group4' end age_group
from (서브쿼리)
order by restaurant_name

금액별 그룹과 나이별 그룹을 나눠주기 위해 조건문과 비교연산자 사용
레스토랑 이름을 오름차순으로 나열해야하므로 ORDER BY 사용
레스토랑 이름, 금액별 그룹, 나이별 그룹을 SELECT

결과

select restaurant_name, 
	case when avg_price<=5000 then 'price_group1'
		 when avg_price<=10000 then 'price_group2'
		 when avg_price<=30000 then 'price_group3'
		 else 'price_group4' end price_group,
	case when avg_age<=29 then 'age_group1'
		 when avg_age<=39 then 'age_group2'
		 when avg_age<=49 then 'age_group3'
		 else 'age_group4' end age_group
from
	(select fo.restaurant_name, avg(fo.price) avg_price, avg(c.age) avg_age
		from food_orders fo inner join customers c on fo.customer_id=c.customer_id
		group by 1
	) a
order by restaurant_name

로 SQL문을 작성하였다.