[TIL] SQL_7일차(문자 포맷 변경&조건문,Subquery)

이전 시간에 이어서 학습합니다.

열공 중🔥

✍🏻배운내용🌞

1. 문자 포맷 변경, 조건문을 복습

2. Subquery문이란?

---

📝1. 문자 포맷 변경, 조건문을 복습

1) 문자 변경 

REPLACE : 지정한 문자를 다른 문자로 변경 

💡replace(바꿀 컬럼, 현재 값, 바꿀 값)

SUBSTRING : 특정 문자만 추출

💡 substr(조회 할 컬럼, 시작 위치, 글자 수)

CONCAT : 여러 문자를 합하여 포맷팅

💡 concat if (붙이고 싶은 값1, 값2, 값3, .....)

 

2) 조건문 

IF :

💡 if(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)

CASE WHEN END :

💡 case when 조건1 then 값(수식)1

             when 조건2 then 값(수식)2

             else 값(수식)3

             end

 

2. Subquery문이란?

: Query 안에 sub 로 들어간 구문. 긴 쿼리문 보다는 조금 더 효율적이고 알아보기 쉽게 하기 위해 사용

1) Subquery 가 필요한 경우

1-1) 여러번의 연산을 수행해야 할 때 

ㄱ) 수수료를 부과할 수 있는 시간을 구하고

ㄴ) 구해진 시간에 주문 금액별로 가중치를 주고

ㄷ) 가중치를 적용한 결과로 최종 예상 배달비를 계산할 때

 

1-2) 조건문에 연산 결과를 사용해야 할 때

→ 음식 타입별 평균 음식 주문금액 따라 음식비 상/중/하 를 나누고 싶을 때

 

1-3) 조건에 Query 결과를 사용하고 싶을 때

→ 30대 이상이 주문한 결과만 조회하고 싶을 때

 

[ 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)

 

[실습] Subquery 문

1. 주문 테이블에서 주문 번호, 음식점명, 음식 준비시간을 가져오기

• select 기본문

•  가져올 컬럼 적기

•  subquery 문으로 추가

Subquery 문 안을 수정해서, 음식 주문시간이 25분보다 초과한 시간을 가져오기

select order_id, restaurant_name, food_preparation_time, if(over_time>=0, over_time, 0) over_time from ( select order_id, restaurant_name,food_preparation_time, food_preparation_time - 25 over_time from food_orders ) a

food_preparation_time-25를 계산한 값이 0보다 크거나 같으면 over_time, 작으면 0

 

2. 음식점의 평균 단가별 segmentation(분할)을 진행하고, 그룹에 따라 수수료 연산하기

(수수료 구간:

~5000원 미만 0.05%,

~20000원 미만 1%,

~30000원 미만 2%,

30000원 초과 3%)

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 ) b

 

 

3. 음식점의 지역(앞 두 글짜)과 평균 배달시간으로 segmentation 하기

(평균 배달시간 조건: 20분, 30분, 30분 초가 →범주 별로=group by)

select restaurant_name, substr(addr, 1, 2) sido, avg(delivery_time) avg_delivery_time from food_orders group by 1,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분과 30분 사이' else '30분보다 큰' end '세분화 배달시간' from (select restaurant_name, substr(addr, 1, 2) sido, avg(delivery_time) avg_delivery_time from food_orders group by 1,2) a

 

4, 음식 타입별 (지역별) 총 주문수량과 음식점 수를 연산하고, 주문수량과 음식점수 별 수수료율을 산정하기

(음식점수 5개 이상, 주문수 30개 이상 → 수수료 0.05%

음식점수 5개 이상 주문수 30개 미만 → 수수료 0.08%

음식점수 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

 

 

5. 음식점의 총 주문수량과 주문 금액을 연산하고, 주문 수량을 기반으로 수수료 할인율 구하기

(할인조건 :

수량이 5개 이하 → 10%

수량이 15개 초과, 총 주문금액이 300000 이상 → 0.5%

이 외에는 일괄 1%)

select restaurant_name, sum_quan, sum_pri,            case when sum_quan<=5 then 0.1                     when sum_quan>15 and sum_pri>=300000 then 0.005                     else 0.01 end '할인' from (select restaurant_name, sum(quantity) sum_quan, sum(price) sum_pri from food_orders group by 1) a

 

 

[ 🚨 주의 ]

파생 테이블( Subquery 문 )에 대한 별칭을 안 적으면 오류 발생!!

🚨 ⚠SQL Error [1248] [42000]: Every derived table must have its own alias

---

💡[ Tip ] SQL생각 흐름 🧠 

1. 기본구조를 적기 : select from where

2. 어떤 데이터를 가져올 테이블을 적기 : from 테이블

 → 만약 조건이 잘 파악이 안 되면 (전체) 테이블만 우선 실행하여 결과를 보고 조건 찾기

3. 어떤 컬럼을 이용할 것인지 선택 : select 컬럼

4. 어떤 조건을 지정할 지 적기 : where 조건, group by / order by

5. 어떤 함수(수식) 을 이용해야 하는가 → 갯수 구하는 수식

 

[🔎복습 ]

💡replace(바꿀 컬럼, 현재 값, 바꿀 값)

💡 substr(조회 할 컬럼, 시작 위치, 글자 수)

💡 concat if (붙이고 싶은 값1, 값2, 값3, .....)

💡 if(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)

💡 case when 조건1 then 값(수식)1

             when 조건2 then 값(수식)2

             else 값(수식)3

             end

---

ADsP 시험 정보가 궁금하신 분들은 여기를 클릭 💨

이전 내용이 궁금하다면 여기를 클릭 💨

replace, substr, concat, if/case에 대해 더 알고싶다면 여기를 클릭🔥

 

🌈 오늘 하루도 열공한 모두 즐거운 하루 되세요🌆

다음 시간에는 JOIN에 대해 학습합니다.