1. 여러번의 연산을 한 번의 SQL문으로 수행하기 (SubQuery)
2. User Segmentation와 조건별 수수료를 Subquery로 결합해보기
3. 복잡한 연산을 Subquery로 수행하기
4.필요한 데이터가 서로 다른 테이블에 있을 때 (JOIN)
5. [실습] JOIN으로 두 테이블의 데이터 조회하기
6. [실습] JOIN으로 두 테이블의 값을 연산하기
7. 숙제 및 후기
1. 여러번의 연산을 한 번의 SQL문으로 수행하기 (SubQuery)
Subquery가 필요한 경우
여러번의 연산을 수행해야 할 때
조건문에 연산 결과를 사용해야 할 때
조건에 Query결과를 사용하고 싶을 때
서브퀴리 예시
1) FROM문에서
select column1, special_column
from
( /* subquery */
select column1, column2 special_column
from table1
) a
2) WHERE 문에서
select column1, column2
from table1
where column1 = (select col1 from table2)
- 음식 단가 조회 하기
(2) SELECT price/quantity
FROM
(
(1) SELECT price, quantity
FROM food_orders
) a;
(1) 에서 가격과 음식 수량을 먼저 조회 후
조회된 결과 값을 이용하여 (2) 에서 연산을 수행한다.
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를 먼저 한 후 오버 타임을 구할 수 있다. 25 분보다 준비시간이 적을경우 if문을 사용하여 0분으로!
2. User Segmentation와 조건별 수수료를 Subquery로 결합해보기
- 음식점의 평균 단가별 segmentation 을 진행하고, 그룹에 따라 수수료 연산하기
~5000원 미만 0.05% ~20000원 미만 1% ~30000원 미만 2% 30000원 초과 3%)
select restaurant_name,
price_per_plate*ratio_of_add "수수료"
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
when price_per_plate between 20000 and 29999 then 0.02
else 0.03 end ratio_of_add,
price_per_plate -- (1)결과에 따라 수수료 계산
from
(
(1) select restaurant_name, avg(price/quantity) price_per_plate
from food_orders
group by 1 -- 식당별 단가 평균
) a
) b
1) 음식점별 평균 단가를 조회
2) (1)에서 조회 된 값(평균 음식단가)을 (2)에서 조건문을 사용하여 수수료를 계산
3) 음식점별 평균음식단가*수수료를 계산하여 조회
- 음식점의 지역과 평균 배달시간으로 segmentation 하기
(2) SELECT restaurant_name, sido,avg_delivery_time,
CASE WHEN avg_delivery_time<=20 THEN "<=20"
WHEN avg_delivery_time>20 and avg_delivery_time<=30 THEN '20<x<=30'
ELSE '>30' END delivery_time_segment
FROM
(
(1) SELECT restaurant_name, SUBSTR(addr,1,2) sido,
AVG(delivery_time) avg_delivery_time
FROM food_orders fo
GROUP BY 1,2
) a
1) 음식점,지역별 평균배달시간과, 주소를 조회
2) (1)에서 조회된 평균 배달시간을 이용하여 segmentation
3. 복잡한 연산을 Subquery로 수행하기
1)음식 타입별 총 주문수량과 음식점 수를 연산하고, 주문수량과 음식점수 별 수수료율을 산정하기
- (음식점수 5개 이상, 주문수 30개 이상 → 수수료 0.5%
- 음식점수 5개 이상, 주문수 30개 미만 → 수수료 0.8%
- 음식점수 5개 미만, 주문수 30개 이상 → 수수료 1%
- 음식점수 5개 미만, 주문수 30개 미만 → 수수로 2%)
SELECT cuisine_type,total_quantity,count_res,
CASE WHEN count_res>=5 AND total_quantity>=30 THEN 0.005
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
2)음식점의 총 주문수량과 주문 금액을 연산하고, 주문 수량을 기반으로 수수료 할인율 구하기
- (할인조건
- 수량이 5개 이하 → 10%
- 수량이 15개 초과, 총 주문금액이 300000 이상 → 0.5%
- 이 외에는 일괄 1%)
SELECT restaurant_name, sum_price,sum_quantity,
CASE WHEN sum_quantity<=5 THEN 0.1
WHEN sum_quantity>15 AND sum_price>=300000 THEN 0.005
ELSE 0.01 END discount_rate
FROM
(
SELECT restaurant_name ,SUM(price) sum_price,SUM(quantity) sum_quantity
FROM food_orders
GROUP BY 1
) a;
-------------------10/16 4-4
4.필요한 데이터가 서로 다른 테이블에 있을 때 (JOIN)
두 개의 서로 다른 테이블에서 원하는 값을 가져와 하나의 테이블로 조회를 하고싶을 때 JOIN 을 사용
LEFT JOIN: A테이블에 모든 정보와 B테이블에도 있는 정보를 합침.
INNER JOIN : A테이블과 B테이블에 같은 정보를 가져옴.
SELECT *
FROM food_orders LEFT JOIN payments ON food_orders.order_id=payments.order_id;
SELECT *
FROM food_orders INNER JOIN payments ON food_orders.order_id=payments.order_id;
주문 테이블과 고객 테이블을 cusomer_id 를 기준으로 left join 으로 묶어보기 (조회 컬럼 : order_id, customer_id, restaurant_name, price, name, age, gender)
SELECT fo.order_id ,fo.customer_id , fo.restaurant_name ,fo.price ,c.name ,c.gender ,c.age
FROM food_orders fo LEFT JOIN customers c ON fo.customer_id =c.customer_id;
5. [실습] JOIN으로 두 테이블의 데이터 조회하기
1) 한국 음식의 주문별 결제 수단과 수수료율을 조회하기
- (조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 결제 수단, 수수료율) *결제 정보가 없는 경우도 포함하여 조회
SELECT fo.order_id , fo.restaurant_name , fo.price ,p.pay_type ,p.vat
FROM food_orders fo LEFT JOIN payments p ON fo.order_id=p.order_id
WHERE cuisine_type ='Korean';
2) 고객의 주문 식당 조회하기
- (조회 컬럼 : 고객 이름, 연령, 성별, 주문 식당) *고객명으로 정렬, 중복 없도록 조회
SELECT DISTINCT c.name, c.age, c.gender, fo.restaurant_name
FROM food_orders fo LEFT JOIN customers c ON fo.customer_id=c.customer_id
ORDER BY c.name;
6. [실습] JOIN으로 두 테이블의 값을 연산하기
1)주문 가격과 수수료율을 곱하여 주문별 수수료 구하기
- (조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 수수료율, 수수료) *수수료율이 있는 경우만 조회
SELECT fo.order_id, fo.restaurant_name, fo.price,p.vat,fo.price*p.vat '수수료'
FROM food_orders fo INNER JOIN payments p ON fo.order_id=p.order_id
2) 50세 이상 고객의 연령에 따라 경로 할인율을 적용하고, 음식 타입별로 원래 가격과 할인 적용 가격 합을 구하기
- (조회 컬럼 : 음식 타입, 원래 가격, 할인 적용 가격, 할인 가격) 할인 : 나이-500.005
-
- 고객 정보가 없는 경우도 포함하여 조회, 할인 금액이 큰 순서대로 정렬
SELECT cuisine_type,SUM(price) price,
sum(price*discount_rate) discounted_price
FROM
(
SELECT fo.cuisine_type, fo.price,c.age,(c.age-50)*0.005 discount_rate
FROM food_orders fo LEFT JOIN customers c ON fo.customer_id=c.customer_id
WHERE c.age>=50
) a
GROUP BY 1
ORDER BY discounted_price DESC;
7. 숙제 및 후기
1. 식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation 하기
- 평균 음식 주문 금액 기준 : 5,000 / 10,000 / 30,000 / 30,000 초과
- 평균 연령 : ~ 20대 / 30대 / 40대 / 50대 이상
- 두 테이블 모두에 데이터가 있는 경우만 조회(INNER JOIN) , 식당 이름 순으로 오름차순 정렬
5,000 (price_group1) / 10,000(price_group2).....
~20(age_group1) / 30 (age_group2)....
먼저 식당별 평균 음식가격과 고객의 평균 나이를 조회(customers_id를 이용하여 두 테이블 join)
그 후, 조회된 결과를 이용하여 평균가격과 평균나이를 Segmentation
SELECT restaurant_name,
CASE WHEN price<=5000 THEN 'price_group1'
WHEN price<=10000 THEN 'price_group2'
WHEN price<=30000 THEN 'price_group3'
ELSE 'price_group4'END AS 'price_group',
CASE WHEN age<30 THEN 'age_group1'
WHEN age<40 THEN 'age_group2'
WHEN age<50 THEN 'age_group3'
ELSE 'age_group4' END AS 'age_group'
FROM
(
SELECT fo.restaurant_name,AVG(fo.price) price ,AVG(c.age) age
FROM food_orders fo INNER JOIN customers c ON fo.customer_id=c.customer_id
GROUP BY 1
) a
ORDER BY 1;
후기:
SQL문이 점점 길어져 복잡해보이고 읽기도 싫었지만 한줄한줄 천천히 해석하면 쉽게 이해할 수있다!
서브퀴리에 이어 조인까지 하니 이제 모르는게 없는 것 같았지만 초반에는 문제 푸는데 해설이 필요했다..
하지만 여러번 문제를 보다보니 문제만으로 SQL문을 작성할 수 있게 되었따!(나좀 쩌는듯)
'엑셀보다 쉽고 빠른 SQL' 카테고리의 다른 글
| 엑셀보다 쉽고 빠른 SQL 5주차 (4) | 2024.10.21 |
|---|---|
| 엑셀보다 쉽고 빠른 SQL 3주차 (2) | 2024.10.15 |
| 엑셀보다 쉽고 빠른 SQL 2주차 (0) | 2024.10.14 |
| 엑셀보다 쉽고 빠른 SQL 1주차 (3) | 2024.10.11 |