2024-02-07 AI 코딩 TIL

3 주차 수업

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

--서울 지역의 음식 타입별 평균 음식 주문금액 구하기 (출력 : ‘서울’, ‘타입’, ‘평균 금액’) SELECT SUBSTR(addr, 1,2) '지역', cuisine_type '음식타입', AVG(price) '평균 긍액' FROM food_orders fo WHERE addr like '서울%' GROUP BY 2

서울 지역만 자르고 음식 타입을 그룹하고 음식 주문금액의 평균을 구하기

---

-- 이메일 도메인별 고객 수와 평균 연령 구하기 SELECT SUBSTR(email, 10) '도메인', COUNT(1) '고객수', AVG(age) '평균연령' FROM customers c GROUP BY 1

도메인 주소부분만 자르고 그룹화 전체 카운트를 넣어주고 각 그룹에 나이를 평균

---

-- [지역(시도)] 음식점이름 (음식종류)’ 컬럼을 만들고, 총 주문건수 구하기 SELECT CONCAT('[', SUBSTR(addr, 1, 2), ']', restaurant_name, '(', cuisine_type, ')') '음식점 주문', COUNT(1) '주문 횟수' FROM food_orders fo GROUP BY 1 ORDER BY 2 DESC

concat을 써서 들어갈 내용을 그룹으로 합쳐주고 카운트를 써서 해당내용 카운트를 해주고 오더를 써서 오름차순으로 정렬

---

조건에 따라 포맷을 다르게 변경해야 한다면 (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 '%문곡리%'

문자 안에 글자를 바꿔주는 replace문을 사용할 때에도 조건문을 넣어서

특정 상화에서만 변경을 할 수 있도록 할 수 있다.

---

select substring(if(email like '%gmail%', replace(email, 'gmail', '@gmail'), email), 10) "이메일 도메인", count(customer_id) "고객 수", avg(age) "평균 연령" from customers group by 1

substr를 사용할 때에 gmail이 포함되어 있는 칼럼의 gmail을 @gmail로 변경하여 넣어주고 10번째 글자에서부터 잘라서 표기할 수 있도록 한다.

---

SELECT CASE when cuisine_type = 'korean' THEN '한식' WHEN cuisine_type IN ('Japanese', 'Chinese') then '아시아' ELSE '기타' END '음식타입', COUNT(1) '갯수' FROM food_orders fo group by 1

음식타입 별로 조건 실행하고 in으로

일본과 중국을 합쳐서 변경

---

select order_id, price, quantity, case when quantity=1 then price when quantity>=2 then price/quantity end "음식 단가" from food_orders

케이스 문을 이용하여 가격에 맞추어 음식의 단가를 계산해주는

---

select restaurant_name, addr, case when addr like '%경기도%' then '경기도' when addr like '%특별%' or addr like '%광역%' then substring(addr, 1, 5) else substring(addr, 1, 2) end "변경된 주소" from food_orders

경기도는 경기도로 표기하고

광역시와 특별시는 5글자까지 표기하며

나머지 직할시 들은 2글자만 표기하여 준다.

---

SQL로 간단한 User Segmentation 해보기

SELECT 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 '고객분류', name, age, gender FROM customers c WHERE age BETWEEN 10 AND 29

각 나이별 성별에 맞추어 10대20대 나누어 보기

---

-- 음식 단가, 음식 종류 별로 음식점 그룹 나누기 -- (Korean = 한식 Japanese, Chinese, Thai, Vietnamese, Indian = 아시아식 그외 = 기타) -- (가격 = 5000, 15000, 그 이상) SELECT restaurant_name , CASE when cuisine_type = 'korean' THEN '한식' WHEN cuisine_type in('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') THEN '아시아식' ELSE '기타' end '음식 종류', case when price/quantity <= 5000 then '5000원' WHEN price/quantity between 5001 and 15000 THEN '15000원' ELSE '15000원 이상' END '단가', cuisine_type , price/quantity '가격' FROM food_orders fo

각 단가에 맞추어 작성

---

-- [실습] 지역과 배달시간을 기반으로 배달수수료 구하기 (식당 이름, 주문 번호 함께 출력) -- (지역 : 서울, 기타 - 서울일 때는 수수료 계산 * 1.1, 기타일 때는 곱하는 값 없음 -- 시간 : 25분, 30분 - 25분 초과하면 음식 가격의 5%, 30분 초과하면 음식 가격의 10%) SELECT CONCAT(restaurant_name, ' - ', order_id) '주문내역', addr '지역', price '가격', CASE when addr like '%서울%' THEN price * 0.1 else 0 end '지역 수수료', CASE when delivery_time <= 25 THEN '없음' when delivery_time between 26 and 30 THEN '5%' when delivery_time > 30 THEN '10%' end '배달비용', CASE when addr like '%서울%' and delivery_time <= 25 THEN price * 0.1 when addr like '%서울%' and delivery_time between 26 and 30 THEN price * 0.15 when addr like '%서울%' and delivery_time > 30 THEN price * 0.2 when addr not like '%서울%' and delivery_time <= 25 THEN price * 0.0 when addr not like '%서울%' and delivery_time between 26 and 30 THEN price * 0.05 when addr not like '%서울%' and delivery_time > 30 THEN price * 0.1 end '총 수수료', CASE when addr like '%서울%' and delivery_time <= 25 THEN price * 1.1 when addr like '%서울%' and delivery_time between 26 and 30 THEN price * 1.15 when addr like '%서울%' and delivery_time > 30 THEN price * 1.2 when addr not like '%서울%' and delivery_time <= 25 THEN price * 1.0 when addr not like '%서울%' and delivery_time between 26 and 30 THEN price * 1.05 when addr not like '%서울%' and delivery_time > 30 THEN price * 1.1 end '총 비용' FROM food_orders fo

지역 수수료와 배달 수수료를 합쳐서 계산하는 법

---

select restaurant_name, order_id, delivery_time, price, addr, case when delivery_time>25 and delivery_time<=30 then price*1.05*(if(addr like '%서울%', 1.1, 1)) when delivery_time>30 then price*1.1*(if(addr like '%서울%', 1.1, 1)) else 0 end "수수료" from food_orders

case와 if를 같이 사용 할 수도 있다.

---

-- [실습] 주문 시기와 음식 수를 기반으로 배달할증료 구하기 -- (주문 시기 : 평일 기본료 = 3000 / 주말 기본료 = 3500 -- 음식 수 : 3개 이하이면 할증 없음 / 3개 초과이면 기본료 * 1.2) SELECT order_id '주문 번호', day_of_the_week '주말/평일', quantity '주문 수량', price '가격', CASE WHEN quantity > 3 then (price * 1.2) + IF(day_of_the_week = 'Weekend', 3500, 3000) else IF(day_of_the_week = 'Weekend', 3500, 3000) end '배달할증료' FROM food_orders fo

주문수량과 주말 평일에 맞추어 변하는 수수료 값

---

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

​

--숫자로 변경 cast(if(rating='Not given', '1', rating) as decimal) --문자로 변경 concat(restaurant_name, '-', cast(order_id as char))

---

◎ 숙제 ◎

-- 다음의 조건으로 배달시간이 늦었는지 판단하는 값을 만들어주세요. -- - 주중 : 25분 이상 -- - 주말 : 30분 이상 SELECT order_id '주문번호', if(day_of_the_week = 'Weekend', '주말', '평일') '주말/평일', CONCAT(delivery_time, ' 분')'배달 시간', CASE when day_of_the_week = 'Weekend' THEN IF(delivery_time >= 30, '배달 늦음', '신속 배달') else IF(delivery_time >= 25, '배달 늦음', '신속 배달') end '배송상태' FROM food_orders fo

배송시간과 주말평일에 2조건으로 배송상태를 확인

---

4 주차

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

select column1, special_column from ( /* subquery */ select column1, column2 special_column from table1 ) a

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

---

-- [실습] 음식점의 평균 단가별 segmentation 을 진행하고, 그룹에 따라 수수료 연산하기 -- (수수료 구간 - -- ~5000원 미만 0.05% -- ~20000원 미만 1% -- ~30000원 미만 2% -- 30000원 초과 3%) SELECT restaurant_name '음식점', price '가격', quantity '주문 수량', total_avg '평균단가', CASE when total_avg < 5000 then 0.005 when total_avg < 20000 then 0.01 when total_avg < 30000 then 0.02 when total_avg >= 30000 then 0.03 end '수수료' FROM ( SELECT price , quantity , restaurant_name, AVG(price/quantity) total_avg FROM food_orders fo GROUP BY restaurant_name ) a

주문수량에 맞추어 개당 가격의 평균을 계산하고

거기에 맞추어 나온 평균값에 case문을 이용하여 조건을 걸어준다.

---

추가적으로 이렇게 할 수 있다.

SELECT restaurant_name '음식점', price '가격', quantity '주문 수량', total_avg '평균단가', vat '수수료 률', total_avg*vat '수수료 비용' FROM ( SELECT restaurant_name , price , quantity , total_avg , CASE when total_avg < 5000 then 0.005 when total_avg < 20000 then 0.01 when total_avg < 30000 then 0.02 when total_avg >= 30000 then 0.03 end vat FROM ( SELECT price , quantity , restaurant_name, AVG(price/quantity) total_avg FROM food_orders fo GROUP BY restaurant_name ) a ) b

---

-- [실습] 음식점의 지역과 평균 배달시간으로 segmentation 하기 SELECT restaurant_name '음식점이름', addr_s '수도', CASE when avg_delivery <= 20 then '20분 이하' when avg_delivery <= 30 then '30분 이하' ELSE '30분 초과' END '시간 경과' FROM ( SELECT restaurant_name , SUBSTR(addr, 1,2) addr_s, AVG(delivery_time) avg_delivery FROM food_orders fo group by 1,2 ) a

이름과 지역으로 분류를 두 번 해주고 거기에 맞는 평균 배달시간을 구해준다

그다음에 평균시간에 맞추어서 묶음을 해준다.

---

-- [실습] 음식 타입별 지역별 총 주문수량과 음식점 수를 연산하고, -- 주문수량과 음식점수 별 수수료율을 산정하기 SELECT addr_s, cuisine_type, store, total_order, CASE when store >= 5 then if(total_order >= 30, 0.05,0.08) else if(total_order >= 30, 0.1,0.2) end '수수료 율(%)' from ( SELECT cuisine_type , SUBSTR(addr,1,2) addr_s, COUNT(1) store, SUM(quantity) total_order FROM food_orders fo group by 1, 2 ) a ORDER BY 1, 2

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

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

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

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

각 수수료에 맞추어 값 계산

---

-- [실습] 음식점의 총 주문수량과 주문 금액을 연산하고, 주문 수량을 기반으로 수수료 할인율 구하기 -- 할인조건 -- 수량이 5개 이하 → 10% -- 수량이 15개 초과, 총 주문금액이 300000 이상 → 0.5% -- 이 외에는 일괄 1% SELECT restaurant_name '음식점', sum_q '총 주문 갯수', sum_p '총 주문 금액', sum_p*dc '할인 금액', sum_p+(sum_p*dc) '총 금액' FROM ( SELECT restaurant_name , sum_q , sum_p , CASE WHEN sum_q <= 5 THEN 0.1 WHEN sum_q > 15 THEN 0.005 ELSE 0.01 END dc FROM ( SELECT restaurant_name , SUM(quantity) sum_q, SUM(price) sum_p FROM food_orders GROUP BY restaurant_name ) a ) b

그룹바이를 사용할 때 숫자를 너무 쓰면 빨간 글씨가 뜨므로 적절하게 사용하자.

​