부트캠프 SQL 강의 4일차

3-1. 3주 차 오늘 배울 것

저번 주차에서는  SQL의 기본 구조에 대해 배웠다.

SELECT
FROM
WHERE
GROUP BY
ORDER BY

이번 주차에서는 문자열 가공, 조건에 대해 알아보자.

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

쿼리 결과를 바로 사용할 수 없을 때 우리는 SQL로 데이터를 가공할 수 있다.

다음과 같은 상황이 있을 수 있다.

• 데이터를 보니 잘못된 값이 있는데 이전에 사용하던 값이어서 다른 문자로 수정해줘야 하는데, 하나하나 수동으로 하기는 너무 많을 때
• 주소 전체가 아닌 '시도' 정보만 필요하거나, 전체 주소가 아닌 '서울'로 문자를 변경할 때
• 사업장 [지역]과 같은 형태로 문자 포맷을 변경할 때

이럴 때 사용하는 함수는 REPLACE, SUBSTRING, CONCAT이 있다. 실습을 해보자

특정 문자를 다른 문자로 바꾸기

SQL에서는 REPLACE() 함수를 통해 특정 문자를 다른 것으로 바꿀 수 있는 기능을 제공한다.

REPLACE(바꿀 컬럼, 현재 값, 바꿀 값)

실습

식당 명의 Blue Ribbon을 Pink Ribbon으로 바꿔보자.

SELECT restaurant_name "원래 상점명", REPLACE(restaurant_name, 'Blue', 'Pink') "바뀐 상점명"
FROM food_orders f
WHERE restaurant_name LIKE "Blue%"

원하는 문자만 남기기

SQL에서는 SUBSTRING()함수를 통해 특정 문자만 골라서 조회할 수 있는 기능을 제공한다.

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

실습

서울 음식점들의 주소를 전체가 아닌 '시도'만 나오도록 수정해 보자.

SELECT addr "원래 주소", SUBSTRING(addr, 1, 2) "시도" 
FROM food_orders f
WHERE addr LIKE "서울%"

여러 컬럼의 문자를 합치기

SQL에서는 CONCAT() 함수를 통해 여러 컬럼의 값을 하나로 합칠 수 있는 기능을 제공한다.

CONCAT(붙이고 싶은 값1, 붙이고 싶은 값2, 붙이고 싶은 값3, ...)

붙일 수 있는 문자의 종류는 다음과 같다.

• 컬럼
• 한글
• 영어
• 숫자
• 기타 특수문자

실습

서울시에 있는 음식점은 [서울] 음식점명이라고 수정해 보자.

SELECT addr "원래 주소", CONCAT("[서울] ",restaurant_name) "바뀐 이름" 
FROM food_orders f
WHERE addr LIKE "서울%"

3-3. 실습) 문자 데이터를 바꾸고, GROUP BY 사용하기

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

SELECT SUBSTRING(addr, 1, 2) as "지역", cuisine_type as "타입", AVG(price) as "평균 금액"
FROM food_orders f
WHERE addr LIKE "서울%"
GROUP BY 1, 2

집계함수 앞에 두 개의 컬럼이 있으니, 그룹 바이에서 첫 번째 컬럼, 두 번째 컬럼으로 묶어준다는 의미인 GROUP BY 1, 2를 써준다.

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

SELECT SUBSTRING(email, 10) as "도메인", COUNT(*) as "고객 수", FLOOR(AVG(age)) as "평균 연령"
FROM customers c
GROUP BY 1

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

SELECT CONCAT("[지역(", SUBSTRING(addr, 1, 2), ")]", restaurant_name, " (", cuisine_type ,")") as info, COUNT(*) 
FROM food_orders fo 
GROUP BY 1

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

조건에 따라 포맷을 변경하는 법을 배워보자.

SQL은 조건에 따라 연산을 적용할 수 있는 기능을 제공한다. "내가 원하는 범주"를 조건으로 주고, 해당 범주에 적용하고 싶은 것을 지정해 주는 방식이다.

조건에 따라 다른 방법을 적용하고 싶을 때 - IF

IF문을 사용해서 조건을 지정해 줄 수 있다.

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

실습

1. 음식 타입을 'Korean' 일 때는 '한식', 아닌 경우에는 '기타'라고 지정하자.

SELECT restaurant_name, cuisine_type "원래 음식 타입", IF(cuisine_type = "Korean", "한식", "기타") "음식 타입"
FROM food_orders fo

조건을 여러 가지 지정하고 싶을 때 - CASE

조건을 지정하다 보면 여러 개를 지정해야 할 경우가 생긴다. 이 때는 CASE 문을 사용하자.

CASE WHEN 조건1 THEN 값(수식)1
	WHEN 조건2 THEN 값(수식)2
    ...
    ELSE 값(수식)3
END

CASE를 사용할 때는 꼭 맨 뒤에 END를 붙여줘서 끝났다는 것을 명시해야 한다.

실습

1. 음식 타입을 'Korean'일 때는 '한식', 'Japanese' 혹은 'Chinese' 일 때는 '아시아', 그 외에는 '기타'로 지정하자.

select restaurant_name, CASE WHEN(cuisine_type = "Korean") THEN "한식"
			WHEN(cuisine_type = "Japanese" OR cuisine_type ="Chinese") THEN "아시아"
			ELSE "기타"
END as "타입"
from food_orders fo

조건을 사용할 수 있는 경우 알아보기

조건을 사용할 수 있는 경우는 다음과 같다.

• 새로운 카테고리 만들기
• 연산식을 적용할 조건 지정하기
• 다른 문법 안에서 적용하기

3-5. SQL로 간단한 User Sementation 해보기

새로운 카테고리 만들기 - 조건문과 수식을 이용하여 간단한 User Segmentation을 해보자.

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

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대 남성'
			else '20대 여성' end as '고객 분류',
			name,
			age,
			gender
FROM customers c 
WHERE age >= 10 AND age < 30

실습 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 ='Japanese' OR cuisine_type ='Chinese' OR cuisine_type ='Thai' OR cuisine_type ='Vietnamese' OR cuisine_type ='Indian')) THEN '아시아식1'
		 WHEN(price/quantity BETWEEN 5000 AND 15000 AND (cuisine_type ='Japanese' OR cuisine_type ='Chinese' OR cuisine_type ='Thai' OR cuisine_type ='Vietnamese' OR cuisine_type ='Indian')) THEN '아시아식2'
		 WHEN (price/quantity > 15000 AND (cuisine_type ='Japanese' OR cuisine_type ='Chinese' OR cuisine_type ='Thai' OR cuisine_type ='Vietnamese' OR cuisine_type ='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 as "종류"
FROM food_orders fo

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

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

조건은 다음과 같다.

지역: 서울, 기타 - 서울일 때는 시간 계산 * 1.1, 기타일 때는 곱하는 값없음

시간: 25분, 30분 -25분 초과하면 음식 가격의 5%, 30분 초과하면 음식 가격의 10%

SELECT IF(SUBSTRING(addr,1,2) ='서울', 
CASE WHEN(delivery_time > 30) then price * 0.1 WHEN(delivery_time > 25) THEN price * 0.05 else 0 end * 1.1
, CASE WHEN(delivery_time > 30) then price * 0.1 WHEN(delivery_time > 25) THEN price * 0.05 else 0 end) as "수수료",
restaurant_name , order_id,price ,delivery_time , addr
FROM food_orders fo

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

조건은 다음과 같다.

주문 시기: 평일 기본료 = 3000 / 주말 기본료: 3500

음식 수: 3개 이하이면 할증 없음 / 3개 초과이면 기본료 * 1.2

SELECT CASE WHEN day_of_the_week ='weekday' then 3000*IF(quantity > 3, 1.2, 1)
		WHEN  day_of_the_week ='weekend' then 3500*IF(quantity > 3, 1.2, 1)
		END '배달 할증료',
		restaurant_name, order_id , quantity , day_of_the_week
FROM food_orders fo

3-7. Data Type 오류 해결하기

숫자계산이나 문자 가공 시 자주 발생하는 오류를 알아보자.

위 이미지를 보면 Not given과 숫자들이 있다. 얼핏 보면 숫자 계산을 할 수 있을 것 같지만, rating 컬럼명 옆에 보면 'ABC' 가 있는 것을 볼 수 있는데, 이는 문자열이라는 의미이다. 따라서 문자, 숫자를 혼합하여 함수에 사용할 때에는 데이터 타입을 변경시켜줘야 한다.

-- 숫자로 변경
CAST(IF(rating='Not given', '1', rating) as decimal)

-- 문자로 변경
CONCAT(restaurant_name, '-', CAST(order_id as char))

3-8. 숙제

배달시간이 늦었는지 판단하는 값을 만들어보자.

조건은 다음과 같다.

• 주중: 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 as '지연여부'
FROM food_orders fo