SQL 4주차 강의 시작
1. 여러 번의 연산을 한 번에 Subquery 문을 통해 구성해보자.
select column1,
special_column
from
(/* subquery */
select column1,
column2 special_column
from table1
) a
# Subquery의 기본 구조
위 구조를 이용해서 작성하면 길디 긴 쿼리문을 더 효율적으로 보기 쉽게 구성이 가능함!
바로 예제 풀러 Go
ex) 음식점의 지역과 평균 배달시간(20분 이하/20분 초과 30분 이하/30분초과)으로 segmentation 해보기
select restaurant_name,
place,
avg_delivery_time,
case when avg_delivery_time <= 20 then '<= 20'
case when avg_delivery_time > 20 and avg_delivery_time <=30 then '20 < x <= 30'
else '> 30' end delivery_time_segment
from
(
select restaurant_name,
substr(addr, 1, 2) place,
avg(delivery_time) avg_delivery_time
from food_orders
group by 1, 2
) a
오늘 처음 서브 쿼리를 배웠는데, 위 예제를 서브 쿼리문을 사용하지 않고 하나의 select / from 문에 다 때려 넣어서 만들었을 생각을 해보니 머리가 아파온다.. ㅠ
2. 서로 다른 테이블에 있는 데이터를 JOIN 을 사용해 조회해보자.
LEFT JOIN : 공통 컬럼 기준, 한 테이블에 값이 없어도 모두 조회
INNER JOIN : 공통 컬럼 기준, 두 테이블 모두 있는 값만 조회
-- LEFT JOIN
select 조회 할 컬럼
from table1 a left join table2 b on a.공통컬럼명=b.공통컬럼명
-- INNER JOIN
select 조회 할 컬럼
from table1 a inner join table2 b on a.공통컬럼명=b.공통컬럼명
그래도 조인은 서브쿼리보단 비교적 구성이 쉬운 편
예제를 풀어보자
ex)
- 한국 음식의 주문별 결제 수단과 수수료율을 조회하기
- (조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 결제 수단, 수수료율) 결제 정보가 없는 경우도 포함하여 조회
select f.order_id,
f.restaurant_name,
f.price,
f.cuisine_type,
p.pay_type,
p.vat
from food_orders f left join payments p on f.order_id=p.order_id
where cuisine_type='Korean'
# JOIN으로 테이블 연결 후 필요한 컬럼 조회
LEFT JOIN으로 결합했으니, 정보가 없는 데이터 NULL 출력도 확인
마지막으로, 숙제 Go
Q) 식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation 하기
- 평균 음식 주문 금액 기준 : 5,000 / 10,000 / 20,000 / 30,000 초과
- 평균 연령 : ~ 20대 / 30대 / 40대 / 50대 이상
- 두 테이블 모두에 데이터가 있는 경우만 조회, 식당 이름 순으로 오름차순 정렬
select restaurant_name,
avg_price,
avg_age
case when avg_price<=5000 then 'x<=5000'
case when avg_price<=10000 then '5000<x<=10000'
case when avg_price<=20000 then '10000<x<=20000'
case when avg_price<=30000 then '20000<x<=30000'
else '30000<x' end price_group,
case when avg_age<30 then '20대'
case when avg_age<40 then '30대'
case when avg_age<50 then '40대'
else '50대 이상' end age_group
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
# 먼저 JOIN으로 테이블을 묶고, CASE별로 그룹별 정리
각 CASE별 범위가 잘 지정되었는지 확인하기 위해, 2개의 컬럼을 더 가져와 대조해보았다.
잘 된거 같으니 제출하자. GOOD!
이번 숙제는 꽤 오래 걸렸다.
복잡할수록 필요한 조건문이 무엇인지, 어떤 컬럼을 도출해야하는지 잘 판단해야할 것 같다.
뭐든 많이 해보면 늘겠지 연습만이 살길