[내일배움캠프-사전캠프 06일차] SQL 3주차, 4주차 강의

---

01. 3주차 3강의

1) replace

replace(col,'현재값','바꿀값')

- col 열의 '현재값'을 '바꿀값'으로 바꾸어달라라는 뜻

2) substring

substr(col, 시작위치, 글자수)

- 여기서 시작위치는 보통의 코딩과 다르게 1부터 시작이다. (다른 코드에서는 0부터 카운트 되지만 sql은 다름)

3) concat

concat('a','b','c', ...)

- a와 b, c, ... 를 이어주는 함수.

4) 실습

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

SELECT SUBSTR(addr,1,2) as "시도", cuisine_type ,AVG(price)
FROM food_orders fo 
WHERE addr LIKE '%서울%'
GROUP BY cuisine_type // 1,2 라고 해도 됨

실습 2) 이메일 도메인별 고객 수와 평균 연령 구하기

SELECT SUBSTR(email, 10) "도메인",
    COUNT(1) "고객 수",
    AVG(age) "평균연령"
from customers c 
group by 1

실습 3) '[지역(시도)]' 음식점 이름 (음식종류)' 컬럼을 만들고 총 주문 건수 구하기.

SELECT CONCAT( '[', SUBSTR(addr, 1,2),'] ' , restaurant_name, ' (',cuisine_type ,')') "바뀐이름",
        COUNT(*)
FROM food_orders
GROUP BY 1

---

2. 3주차 4강의

1) if문

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

2) case문

• 여러가지 케이스를 주는 것임.
  case when 조건 then 조건에 부합할때
else 조건에 부합하지 않을 때 end 컬럼이름
• else는 생략 가능하다.

3) 새로운 카테고리

• 조건문들을 이용하여 다양한 카테고리를 만들 수 있음.

---

3. 3주차 5강의 - 실습

• SQL 에서 고민해야할 순서는. 어떤 테이블에서 데이터를 뽑을 것인지? 어떤 컬럼을 이용할 것인지? 어떤 조건을 이용할 것인지? 어떤 함수 및 수식을 이용할 것인지? 를 순으로 고민하면 된다.

1) 10세 이상, 30세 미만의 고객의 나이와 성별로 그룹 나누기.

/ 이름도 같이 출력

SELECT name, age, gender,
	   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대 여성'
		    WHEN (age BETWEEN 30 and 39) and gender ='male' THEN '30대 남성'
		    WHEN (age BETWEEN 30 and 39) and gender ='female' THEN '30대 여성'
		    END "그룹"
FROM customers 
WHERE age BETWEEN 10 AND 39

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

- (Korean : 한식, Japanese, Chinese, Thai, Vietnamese, Indian = 아시아식, 그외 = 기타)

- (가격= 5000,15000, 그 이상)

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

---

4. 3주차 6강의 - 실습

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

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

SELECT restaurant_name, order_id ,delivery_time, price, addr,
	   CASE WHEN delivery_time > 30 THEN price*1.1*(IF(addr LIKE '서울%', 1.1, 1))
	   		WHEN delivery_time > 25 THEN price*1.05*(IF(addr LIKE '서울%', 1.1, 1)) 
	   		else 0 END "수수료"
FROM food_orders

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

• 주문시기: 평일 기본료 3000, 주말 기본료 3500
• 음식수: 3개 이하면 할증 X / 3개 초과이면 기본료 * 1.2

SELECT order_id, price, quantity, day_of_the_week,
	CASE WHEN day_of_the_week = 'Weekend' THEN 3500*(if(quantity > 3,1.2,1))
		 WHEN day_of_the_week = 'Weekday' THEN 3000*(if(quantity > 3,1.2,1)) END "할증료"
FROM food_orders

---

5. 3주차 7강의 / 데이터 타입

- 숫자로 변경

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

- 문자로 변경

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

---

6.  3주차 숙제

- 배달시간이 늦었는지 판단하는 값을 만들어주세요.

- 주중 : 25분 이상 / 주말 : 30분 이상

SELECT order_id,restaurant_name,day_of_the_week, delivery_time,
	   CASE WHEN day_of_the_week = 'Weekend' THEN if(delivery_time>=30,'Late','On-time')
		 	WHEN day_of_the_week = 'Weekday' THEN if(delivery_time>=25,'Late','On-time') END "지연여부"
FROM food_orders

SELECT order_id, restaurant_name, day_of_the_week, delivery_time,
       IF(day_of_the_week='Weekend', if(delivery_time>=30,'Late','On-time'), if(delivery_time>=25,'Late','On-time')) "지연여부"
FROM food_orders

---

7. 4주차 3 강의 실습

1) 음식점의 평균 단가별 Segmentation을 진행하고, 그룹에 따라 수수료 연산

• 수수료 구간
• ~ 5000 미만 0.05%
• ~ 20000 미만 1% 
• ~ 30000 미만 2%
• 30000 초과 3%

SELECT restaurant_name, price_avgs*ratio_add "수수료"
FROM (
SELECT restaurant_name, price_avgs,
CASE WHEN price_avgs < 5000 THEN 0.005
			WHEN price_avgs BETWEEN 5000 AND 19999 THEN 0.01
			WHEN price_avgs BETWEEN 20000 AND 29999 THEN 0.02
			WHEN price_avgs > 30000 THEN 0.03 END ratio_add
FROM 
(SELECT restaurant_name, avg(price/quantity) price_avgs
FROM food_orders
GROUP BY 1 
)AS a) AS b

- 파생 테이블(derived table)을 사용할 때 별칭(alias)을 반드시 지정해야 한다.

2) 음식점의 지역과 평균 배달시간으로 Segmentation 하기.

는 내일하는 걸로 ;ㅡ;

---

오늘의 회고

- 오늘은 sql 강의를 들었다. 학교 다닐 때만 해도 나름 어려운 부분은 다 수강했다 생각했는데 CASE THEN 구문은 처음 접해본다.

- 나름 지금부터 어려운 부분?인 거 같다. 1,2주차 강의는 실습은 쳐다보기만 했다.(웬만하면 다 아는 부분이니까) 그렇지만 이제부터는 나름 생소한 부분이니 따라하면서 생각하면서 강의를 따라갔다.

- 물론 서브쿼리를 배우는 과정이니, 이렇게 서브쿼리를 from 안에 넣고 도 from 안의 from에 서브쿼리 넣고.. 이러는 거 같긴한데 비효율적이당 ㅠㅡㅠ..