3주차 복습하기
문자 변경
- REPLACE : 지정한 문자를 다른 문자로 변경
- SUBSTRING : 특정 문자만 추출
- CONCAT : 여러 문자를 합하여 포맷팅
조건문
- IF : if(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)
- CASE WHEN END:
case when 조건1 then 값(수식)1
when 조건2 then 값(수식)2
else 값(수식)3
end
4주차에는 여러번의 연산을 한번의 SQL 문으로 실행할수 있는 Subquery와 Join 에 대해서 알아보자
서브 쿼리(Subquery)
서브 쿼리가 필요한이유
1.여러번의 연산을 수행해야 할때
2.조건문에 연산 결과를 사용해야 할 때
3.조건에 Query 결과를 사용하고 싶을때
서브쿼리의 기본구조
ex1)
select column1, special_column
from
(
select column1, column2 special_column
from table1
)
ex2)
select column1, column2
from table1
where column1 = (select col1 from table2)
이해를 돕기 위해 실습으로 진행해보자
1. [실습] 음식점의 평균 단가별 segmentation 을 진행하고, 그룹에 따라 수수료 연산하기
(수수료 구간 -
~5000원 미만 0.05%
~20000원 미만 1%
~30000원 미만 2%
30000원 초과 3%)
select restaurant_name ,
avgprice*add_avgprice as '수수료'
from (select restaurant_name,
case when avgprice<5000 then 0.005
when avgprice between 5000 and 19999 then 0.01
when avgprice between 20000 and 29999 then 0.02
else 0.03
end add_avgprice,avgprice
from (select restaurant_name, avg(price/quantity) as avgprice
from food_orders
group by restaurant_name) a)b
※코드분석
restaurant_name을 그룹별로 묶어 평균 단가를 측정 -> 평균단가를 수수료 기준에 따라 다르게 계산한다 -> 평균단가와 기준에따른 수수료를 곱하여 값을 출력한다.
2. [실습] 음식 타입별 총 주문수량과 음식점 수를 연산하고, 주문수량과 음식점수 별 수수료율 산정하기
(음식점수 5개 이상, 주문수 30개 이상 → 수수료 0.005%
음식점수 5개 이상, 주문수 30개 미만 → 수수료 0.008%
음식점수 5개 미만, 주문수 30개 이상 → 수수료 0.01%
음식점수 5개 미만, 주문수 30개 미만 → 수수료 0.02%)
select cuisine_type,ord_count,res_count,
Case when res_count >=5 and ord_count>=30 then 0.005
when res_count >=5 and ord_count<30 then 0.008
when res_count <5 and ord_count>=30 then 0.01
when res_count <5 and ord_count<30 then 0.02
end as ratio_price
from (select cuisine_type,sum(quantity) as ord_count,count(distinct restaurant_name) as res_count
from food_orders
group by 1)a
3. [실습] 음식점의 총 주문수량과 주문 금액을 연산하고, 주문 수량을 기반으로 수수료 할인율 구하기
(할인조건 수량이 5개 이하 → 10% 수량이 15개 초과, 총 주문금액이 300000 이상 → 0.5% 이 외에는 일괄 1%)
select restaurant_name,
Case when total_order<=5 then 0.1
when total_order>15 and total_price>=300000 then 0.005
else 0.01
end '수수료'
from(select restaurant_name,SUM(quantity) as total_order,SUM(price) as total_price
from food_orders
group by 1)a
조인(JOIN)
조인이 필요한 이유
ex 1) 주문 가격은 주문테이블에 있지만, 어떤 수단으로 결제를 했는지는 결제테이블에 있을때
ex 2) 주문을 한 사람을 확인하기 위해, 주문 테이블과 고객 테이블에서 각각 정보를 가져와서 엑셀에서 합쳐야 할때
위 그림과 같이 다른 테이블에 같은 컬럼(고객 id)가 있을때 한 테이블로 묶어줄 수 있다.
조인의 종류(LEFT JOIN,INNER JOIN,FULL JOIN)
LEFT,RIGHT JOIN : 공통 컬럼 (키값) 을 기준으로, 하나의 테이블에 값이 없더라도 모두 조회되는 경우를 의미함.
INNER JOIN : 공통 컬럼 (키값) 을 기준으로, 두 테이블 모두에 있는 값만 조회함.
FULL 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.공통컬럼명
JOIN 으로 두 테이블의 데이터 조회하기
1) [실습] 한국 음식의 주문별 결제 수단과 수수료율을 조회하기
(조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 결제 수단, 수수료율)
*결제 정보가 없는 경우도 포함하여 조회
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
where cuisine_type='Korean'
2) [실습] 고객의 주문 식당 조회하기
(조회 컬럼 : 고객 이름, 연령, 성별, 주문 식당)
*고객명으로 정렬, 중복 없도록 조회
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
JOIN 으로 두 테이블의 값을 연산하기
1) [실습] 주문 가격과 수수료율을 곱하여 주문별 수수료 구하기
(조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 수수료율, 수수료)
*수수료율이 있는 경우만 조회
select f.order_id,
f.restaurant_name,
f.price,
p.vat,
f.price * p.vat vat2
from food_orders f inner join payments p on f.order_id=p.order_id
2) [실습] 50세 이상 고객의 연령에 따라 경로 할인율을 적용하고, 음식 타입별로 원래 가격과 할인 적용 가격 합을 구하기
(조회 컬럼 : 음식 타입, 원래 가격, 할인 적용 가격, 할인 가격)
*할인 : (나이-50)*0.005 * 고객 정보가 없는 경우도 포함하여 조회, 할인 금액이 큰 순서대로 정렬
select a.cuisine_type,
sum(price),
sum(price*discount_rate) discounted_price
from
(select f.cuisine_type,
f.price,
c.age,
(c.age-50)*0.005 discount_rate
from food_orders f left join customers c on f.customer_id = c.customer_id
where c.age>=50)a
group by 1
order by SUM(price - (price * discount_rate)) desc
코드해석
우선 from 절안에서 food_orders 와 cutomers 테이블에 동일한 cutomer_id 를 join 을 통해 합친후 할인율을 구하고 음식타입별로 그룹을 묶어 가격의 합계와 할인가격의합을 구한다
4주차 마무리 문제
식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation 하기
- 평균 음식 주문 금액 기준 : 5,000 이하 / ~10,000 / ~30,000 / 30,000 초과
- 평균 연령 : ~ 20대 / 30대 / 40대 / 50대 이상
- 두 테이블 모두에 데이터가 있는 경우만 조회, 식당 이름 순으로 오름차순 정렬
select f.restaurant_name,
Case when avg(f.price)<=5000 then 'price group1'
when avg(f.price) between 5000 and 10000 then 'price group2'
when avg(f.price) between 10000 and 30000 then 'price group3'
when avg(f.price) >=30000 then 'price group4'
end 'price group',
Case when avg(c.age) <=29 then 'age group1'
when avg(c.age) between 30 and 39 then 'age group2'
when avg(c.age) between 40 and 49 then 'age group3'
when avg(c.age) >=50 then 'age group4'
end 'age group'
from food_orders f inner join customers c on f.customer_id =c.customer_id
group by 1
order by 1
위는 안좋은 예시다(내가 썻던 방법)
이유: 나이와 가격을 평균내는것을 일일이 입력하여 쓸대없이 코드가 복잡해졌다.
from 절을 함수처럼 사용하면 아래와 같이 바꿀수 있을것같다
select restaurant_name,
case when price <=5000 then 'price_group1'
when price >5000 and price <=10000 then 'price_group2'
when price >10000 and price <=30000 then 'price_group3'
when price >30000 then 'price_group4' end price_group,
case when age <30 then 'age_group1'
when age between 30 and 39 then 'age_group2'
when age between 40 and 49 then 'age_group3'
else 'age_group4' end age_group
from
(
select a.restaurant_name,
avg(price) price,
avg(age) age
from food_orders a inner join customers b on a.customer_id=b.customer_id
group by 1
) t
order by 1
'SQL' 카테고리의 다른 글
| [SQL]CSV 파일을 이용해 데이터 가져오기(MY SQL) (0) | 2025.01.20 |
|---|---|
| [SQL]불필요한 Query 처리방법(coalesce,<>), Pivot Table (0) | 2025.01.17 |
| [SQL]REPLACE, SUBSTRING, CONCAT,IF, CASE (2) | 2025.01.16 |
| [SQL]SELECT,WHERE,ORDER BY (0) | 2025.01.14 |
| [SQL]SQL의 개요 (0) | 2025.01.14 |