엑셀보다 쉽고 빠른 SQL 3주차
1. 업무에 필요한 문자 포맷이 다를 때, SQL로 가공하기
(REPLACE, SUBSTRING, CONCAT)
2. [실습] 문자데이터를 바꾸고 GROUP BY 사용하기
3. 조건에 따라 포맷을 다르게 변경하기(IF, CASE)
4. [실습] SQL로 간단한 User Segmentation 해보기
5. [실습] 조건문으로 서로 다른 식을 적용한 수수료 구해보기
6. Data Type 오류 해결하기
7. 숙제 및 후기
1. 업무에 필요한 문자 포멧이 다를 때, SQL로 가공하기
(REPLACE, SUBSTRING, CONCAT)
- 특정 문자를 다른 문자로 바꾸기(REPLACE)
replace(컬러명, '이전문자', '바꿀문자')
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 '%문곡리%';
- 원하는 문자만 남기(SUBSTRING)
substr(컬럼명, 시작인덱스(1부터), 끝 인덱스)
substring(컬럼명, 시작인덱스(1부터), 끝 인덱스)
select addr "원래 주소",
substr(addr, 1, 2) "시도"
from food_orders
where addr like '%서울특별시%'
- 여러 컬럼의 문자를 합치기(CONCAT)
concat(컬럼명1, 컬럼명2 ..... )
예)
select restaurant_name "원래 이름",
addr "원래 주소",
concat('[', substring(addr, 1, 2), '] ', restaurant_name) "바뀐 이름"
from food_orders
where addr like '%서울%'
2. [실습] 문자데이터를 바꾸고 GROUP BY 사용하기
1) 서울 지역의 음식 타입별 평균음식 주문금액 구하기 ( 출력 : '서울','타입','평균금액')
- 조건1 평균음식 주문금액(AVG() )
- 조건2 서울 지역 (WHERE) , '서울'로 출력 (SUBSTR(addr ,1,2)
- 조건 3 음식 타입별(GROUP BY)
SELECT cuisine_type '음식종류',SUBSTR(addr,1,2) '주소', AVG(price)
FROM food_orders
WHERE addr LIKE "서울%"
GROUP BY cuisine_type;
2) 이메일 도메인별 고객 수와 평균 연령 구하기
- 조건1 고객 수 (SUM) ,평균 연령 구하기(AVG)
- 조건2 이메일 도메인 별 (GROUP BY)
SELECT SUBSTR(email,10) ,sum(1) 고객수, AVG(age) 평균연령
FROM customers
GROUP BY 1;
-- SUBSTR(컬럼명,시작) 끝 인덱스를 생략하면 문자열의 마지막까지!
3) '[지역(시도)] 음식점이름 (음식종류)’ 컬럼을 만들고, 총 주문건수 구하기
- 조건1 지역+음식점이름+(음식종류) 컬럼 만들기(CONCAT)
- 조건2 총 주문 건수 구하기 (SUM() )
SELECT CONCAT('[',SUBSTR(addr,1,2),'] ',restaurant_name,' (',cuisine_type,')') '음식점',COUNT(1) '총 주문'
FROM food_orders fo
GROUP BY 1;
3. 조건에 따라 포맷을 다르게 변경하기(IF,CASE)
- IF 문
IF( 조건, true,false)
select restaurant_name,
cuisine_type "원래 음식 타입",
if(cuisine_type='Korean', '한식', '기타') "음식 타입"
from food_orders
- 평택군의 문곡리를 문가리로 주소 바꾸기
조건 1. 문곡리의 모든 주소 (WHERE) 중 평택군이 포함된 주소의 문곡리를 문가리로 변경(IF,SUBSTR)
select addr "원래 주소",
if(addr like '%평택군%', replace(addr, '문곡리', '문가리'), addr) "바뀐 주소"
from food_orders
where addr like '%문곡리%'
- 도메인별 고객수 와 평균연령 조회하기
조건 1 도메인 그룹(SUBSTR,GROUP BY)
조건 2 gmail은 앞에 '@'가 없기 때문에 '@gmail'로 변경(IF)
조건 3 고객 수(SUM), 평균연령(AVG)
select substring(if(email like '%gmail%', replace(email, 'gmail', '@gmail'), email), 10) "이메일 도메인",
count(customer_id) "고객 수",
avg(age) "평균 연령"
from customers
group by 1
만약 email중 gmail이 들아간 값에 한에 '@gmail'로 바꿈.
10/15 8:55
-------------------
- CASE문
: 조건을 여러가지 지정하고 싶을 때 사용
CASE WHEN 조건1 THEN 값(수식)2
WHEN 조건2 THEN 값(수식)2
ElSE 값(수식)3
END
음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Japanese’ 혹은 ‘Chienese’ 일 때는 ‘아시아’, 그 외에는 ‘기타’ 라고 지정
SELECT CASE WHEN cuisine_type='Korean' THEN '한식'
WHEN cuisine_type IN ('Japanese','Chinese') THEN '아시아'
ELSE '기타' END '음식타입', cuisine_type
FROM food_orders;
- 각 주문 별 음식 단가 구하기
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
4. [실습] SQL로 간단한 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대 남성'
WHEN (age BETWEEN 20 AND 29) AND gender='female' THEN '10대 여성' END '고객 분류',
name, age, gender
FROM customers
WHERE age BETWEEN 10 AND 29;
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
: SQL문이 길지만 반복되는 구간이 많아 한줄씩 읽으면 쉽다.
(price/quantity <5000) and cuisine_type='Korean' then '한식1'
음식 단가가 5000원 미만이고 음식 타입이 한식이라면 '한식1'
price/quantity between 5000 and 15000) and cuisine_type='Korean' then '한식2'
음식 단가가 5000이상 15000이하 이면서 한식이라면 '한식2'
:
5. [실습] 조건문으로 서로 다른 식을 적용한 수수료 구해보기
1) 지역과 배달 시간을 기반으로 배달 수수료 구하기(식당 이름, 주문번호 함께 출력)
- (지역 : 서울, 기타 - 서울일 때는 수수료 계산 * 1.1, 기타일 때는 곱하는 값 없음
- 시간 : 25분, 30분 - 25분 초과하면 음식 가격의 5%, 30분 초과하면 음식 가격의 10%)
SELECT CASE WHEN delivery_time>30 then price*0.1*IF(addr LIKE '%서울%',1.1, 1)
WHEN delivery_time>25 then price*0.05*IF(addr LIKE '%서울%',1.1, 1)
ELSE 0 END "수수료",restaurant_name, order_id,price,delivery_time,addr
FROM food_orders;
: 배달 시간을 이용하여 수수료를 구한 후 IF문을 사용하여 지역에 따라 추가 수수료를 곱해준다.
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)
ELSE 0 END "배달 할증료",restaurant_name, order_id,day_of_the_week ,quantity
FROM food_orders;
: 1번 문제와 비슷하다! 평일과 주말의 따라 배달 할증료를 먼저 구한 후 IF문을 사용하여 음식 수량에 따라 수수료를 추가 한다.
6. Data Type 오류 해결하기
문자형 값을 숫자처럼 계산하려고 할 때 오류가 발생한다.
계산을 하고자 할 때는 자료형에 맞게 바꿔 준 후 계산을 해야한다.
--숫자로 변경
cast(if(rating='Not given', '1', rating) as decimal)
--문자로 변경
concat(restaurant_name, '-', cast(order_id as char))
7. 숙제 및 후기
다음의 조건으로 배달시간이 늦었는지 판단하는 값을 만들어주세요.
- 주중 : 25분 이상
- 주말 : 30분 이상
SELECT order_id,restaurant_name, day_of_the_week, delivery_time ,
CASE WHEN delivery_time>=30 AND day_of_the_week ='Weekend' THEN 'Late'
WHEN delivery_time>=25 AND day_of_the_week ='Weekday' THEN 'Late'
ELSE 'On-time' END '지연여부'
FROM food_orders;
후기: 앞의 강의 까지는 쉬웠지만 조건문이 나오기 시작하면서 많은 집중이 필요했다. 어려운 조건문은 아니지만
문제를 해석하기에 시간이 조금 걸렸고 혼자 문제를 보고 SQL문을 작성하기에 시간이 오래걸렸다.
정답SQL문을 본 후 문제를 보면 쉽게 이해가 갔지만 문제만 보고SQL문이 쉽게 작성되진 않았다.
많은 문제를 보고 푸는 연습을 해야할 것 같다!