[2024/12/20]내일배움캠프 QA/QC 1기 - 5일차

온보딩 주차 마지막 날, 팀 발표

커리어 스터디 : Data Scientist와 Quality Control Engineer, 공정별 데이터 분석

---

 

온보딩 주차 다면평가 조사 실시

---

 

온보딩 주차 KPT 회고 제출(https://essay2892.tistory.com/23)

---

 

SQL 2강

업무 필요한 문자 포맷이 다를 때, SQL로 가공하기 (REPLACE, SUBSTRING, CONCAT)

select restaurant_name "원래 상점명",

replace(restaurant_name, 'Blue', 'Pink') "바뀐 상점명"

from food_orders

where restaurant_name like '%Blue Ribbon%'

 #

 

select addr '원래 주소', replace(addr,'문곡리','문가리') '바뀐 주소'

from food_orders

where addr like '%문곡리%'

#

 

select addr "원래 주소",

substr(addr, 1, 2) "시도"

from food_orders

where addr like '%서울특별시%'

#

 

select restaurant_name "원래 이름",

addr "원래 주소",

concat('[', substring(addr, 1, 2), '] ', restaurant_name) "바뀐 이름"

from food_orders

where addr like '%서울%'

#

SQL 3강

문자 데이터를 바꾸고, GROUP BY 사용하기

서울 지역의 음식 타입별 평균 음식 주문금액 구하기 (출력 : ‘서울’, ‘타입’, ‘평균 금액’)

select substr(addr,1,2) "지역",

cuisine_type "음식 타입",

avg(price) "평균 금액"

from food_orders

where addr like '%서울%'

group by cuisine_type

#

 

‘[지역(시도)] 음식점이름 (음식종류)’ 컬럼을 만들고, 총 주문건수 구하기

select concat('[',substr(addr,1,2),'] ',restaurant_name, ' (', cuisine_type ,')') "음식점",

count(1) "총 주문건수"

from food_orders

group by 1

#

SQL 4강

조건에 따라 포맷을 다르게 변경해야한다면 (IF, CASE)

select restaurant_name,

cuisine_type "원래 음식 타입",

if(cuisine_type='Korean', '한식', '기타') "음식 타입"

from food_orders

#

 

select addr "원래 주소",

if(addr like '%평택군%', replace(addr, '문곡리', '문가리'), addr) "바뀐 주소"

from food_orders

where addr like '%문곡리%'

#

 

음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Japanese’ 혹은 ‘Chienese’ 일 때는 ‘아시아’, 그 외에는 ‘기타’ 라고 지정

SELECT restaurant_name,

cuisine_type "원래 음식 타입",

case when cuisine_type = 'Korean' then "한식"

when cuisine_type = 'Japanese' or cuisine_type = "Chienese" then "아시아"

else "기타"

end "음식 타입"

FROM food_orders

#

 

음식 단가를 주문 수량이 1일 때는 음식 가격, 주문 수량이 2개 이상일 때는 음식가격/주문수량 으로 지정

SELECT order_id,

price,

quantity,

if(quantity = 1, price, price/quantity) "음식 단가"

FROM food_orders

 

SELECT order_id,

price,

quantity,

case when quantity = 1 then price

else price/quantity

end "음식 단가"

FROM food_orders

#

 

주소의 시도를 ‘경기도’ 일때는 ‘경기도’, ‘특별시’ 혹은 ‘광역시’ 일 때는 붙여서, 아닐 때는 앞의 두 글자만 사용

SELECT restaurant_name,

addr,

case when addr like '경기도%' then "경기도"

when addr like '%특별시%' or addr like '%광역시%' then substr(addr,1,5)

else substr(addr,1,2)

end "변경된 주소"

FROM food_orders

#

SQL 5강

SQL로 간단한 User Segmentation 해보기

10세 이상, 30세 미만의 고객의 나이와 성별로 그룹 나누기 (이름도 같이 출력)

SELECT age,

gender,

name,

case when age between 10 and 19 and gender = 'male' then "10대 남자"

when age between 10 and 19 and gender = 'female' then "10대 여자"

when age between 20 and 29 and gender = 'male' then "20대 남자"

when age between 20 and 29 and gender = 'female' then "20대 여자"

end "그룹"

from customers

where age >= 10 and age < 30

#

 

음식 단가, 음식 종류 별로 음식점 그룹 나누기

select restaurant_name,

price/quantity "단가",

cuisine_type,

order_id,

case when (price/quantity <5000) and cuisine_type='Korean' then '한식1'

when (price/quantity between 5000 and 15000) and cuisine_type='Korean' then '한식2'

when (price/quantity > 15000) and cuisine_type='Korean' then '한식3'

when (price/quantity <5000) and cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '아시아식1'

when (price/quantity between 5000 and 15000) and cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '아시아식2'

when (price/quantity > 15000) and cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '아시아식3'

when (price/quantity <5000) and cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '기타1'

when (price/quantity between 5000 and 15000) and cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '기타2'

when (price/quantity > 15000) and cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '기타3' end "식당 그룹"

from food_orders

 #

SQL 6강

조건문으로 서로 다른 식을 적용한 수수료 구해보기

지역과 배달시간을 기반으로 배달수수료 구하기 (식당 이름, 주문 번호 함께 출력)

(지역 : 서울, 기타 - 서울일 때는 수수료 계산 * 1.1, 기타일 때는 곱하는 값 없음 시간 : 25분, 30분 - 25분 초과하면 음식 가격의 5%, 30분 초과하면 음식 가격의 10%)

select order_id "주문 번호",

if(addr like '서울%', "서울", "기타") "지역",

restaurant_name "식당 이름",

price "가격",

delivery_time "배달 시간",

case when delivery_time > 25 and delivery_time <= 30 then price*0.05*if("지역"="서울",1.1,1)

when delivery_time > 30 then price*0.1*if("지역"="서울",1.1,1)

else 0

end "배달수수료"

from food_orders

#

 

주문 시기와 음식 수를 기반으로 배달할증료 구하기

(주문 시기 : 평일 기본료 = 3000 / 주말 기본료 = 3500 음식 수 : 3개 이하이면 할증 없음 / 3개 초과이면 기본료 * 1.2)

select day_of_the_week "주문 시기",

quantity "음식수",

case when day_of_the_week = 'Weekday' then 3000*if(quantity <= 3, 1, 1.2)

when day_of_the_week = 'Weekend' then 3500*if(quantity <= 3, 1, 1.2)

end "배달할증료"

from food_orders

#

SQL 7강

SQL문에 문제가 없는 것 같은데 왜 오류가 나나요_ (Data Type 오류 해결하기)

우리가 실습하는 Mysql 과 다르게, 다른 SQL 문법에서는 data type 이 다를 때 연산이 되지 않을 수 있습니다.

1) 번에서 봤던 예시를 보면, rating 은 숫자가 포함되어 있지만 문자 형으로 저장이 되어있습니다 (출력 결과 컬럼명 옆의 ‘ABC’ 혹은 ‘123’ 을 확인해주세요. ‘ABC’ 는 문자로 저장이 되어있다는 의미입니다.)

따라서 문자, 숫자를 혼합하여 함수에 사용 할 때에는 데이터 타입을 변경해주어야 합니다

--숫자로 변경

cast(if(rating='Not given', '1', rating) as decimal)

 

--문자로 변경

concat(restaurant_name, '-', cast(order_id as char))

SQL 3주차 숙제 제출(https://essay2892.tistory.com/20)

 

엑셀보다 쉽고 빠른 SQL 4주차 강의

SQL 1강

복습 및 맛보기

 

SQL 2강

여러 번의 연산을 한 번의 SQL 문으로 수행하기 (Subquery)

Subquery 가 필요한 경우

• 여러번의 연산을 수행해야 할 때
• 조건문에 연산 결과를 사용해야 할 때
• 조건에 Query 결과를 사용하고 싶을 때

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

#

SQL 3강

User Segmentation 와 조건별 수수료를 Subquery 로 결합해보기

음식점의 평균 단가별 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.0005

when price_per_plate >= 5000 and price_per_plate < 20000 then 0.01

when price_per_plate >= 20000 and price_per_plate < 30000 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

#

 

음식점의 지역과 평균 배달시간으로 segmentation 하기

select restaurant_name, sido "지역",

case when avg_time <= 20 then '<=20'

when avg_time > 20 and avg_time <= 30 then '20<x<30'

when avg_time > 30 then '>30'

end '평균배달시간'

from

(select restaurant_name, substr(addr,1,2) sido, avg(delivery_time) avg_time

from food_orders

group by 1, 2

) a

#

 

SQL 4강

복잡한 연산을 Subquery 로 수행하기

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

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

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

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

음식점수 5개 미만, 주문수 30개 미만 → 수수료 2%)

select cuisine_type, total_quantity '총 주문량', number_of_restaurant '음식점 수',

case when number_of_restaurant >= 5 and total_quantity >= 30 then "0.5%"

when number_of_restaurant >= 5 and total_quantity < 30 then "0.8%"

when number_of_restaurant < 5 and total_quantity >= 30 then "1%"

when number_of_restaurant < 5 and total_quantity < 30 then "2%"

end '수수료율'

from

(

SELECT cuisine_type , sum(quantity) total_quantity, count(DISTINCT restaurant_name) number_of_restaurant

from food_orders

group by 1

) a

#

 

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

(할인조건 수량이 5개 이하 → 10%

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

이 외에는 일괄 1%)

select restaurant_name,

total_quantity '총 주문수량',

total_price '총 주문금액',

if (total_quantity <= 5, "10%", if((total_quantity > 15 and total_price >= 300000), "0.5%","1%")) '수수료 할인율'

from

(

SELECT restaurant_name, sum(quantity) total_quantity, sum(price) total_price

FROM food_orders

group by restaurant_name

) a

#

SQL 5강

필요한 데이터가 서로 다른 테이블에 있을 때 조회하기 (JOIN)

LEFT JOIN : 공통 컬럼 (키값) 을 기준으로, 하나의 테이블에 값이 없더라도 모두 조회

INNER JOIN : 공통 컬럼 (키값) 을 기준으로, 두 테이블 모두에 있는 값만 조회

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

 

select f.order_id,

f.customer_id,

f.restaurant_name,

f.price,

c.name,

c.age,

c.gender

from food_orders f left join customers c on f.customer_id = c.customer_id

#

SQL 6강

JOIN 으로 두 테이블의 데이터 조회하기

한국 음식의 주문별 결제 수단과 수수료율을 조회하기

(조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 결제 수단, 수수료율)

*결제 정보가 없는 경우도 포함하여 조회

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 fo.cuisine_type = 'Korean'

#

 

고객의 주문 식당 조회하기

(조회 컬럼 : 고객 이름, 연령, 성별, 주문 식당)

*고객명으로 정렬, 중복 없도록 조회

 

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

where c.name is not null

order by c.name

#

SQL 7강

JOIN 으로 두 테이블의 값을 연산하기

주문 가격과 수수료율을 곱하여 주문별 수수료 구하기

(조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 수수료율, 수수료)

*수수료율이 있는 경우만 조회

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

#

 

50세 이상 고객의 연령에 따라 경로 할인율을 적용하고, 음식 타입별로 원래 가격과 할인 적용 가격 합을 구하기

(조회 컬럼 : 음식 타입, 원래 가격, 할인 적용 가격, 할인 가격)

*할인 : (나이-50)*0.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 age >= 50

) a

group by 1

order by discounted_price desc

#

SQL 4주차 숙제 제출(https://essay2892.tistory.com/22)