엑셀보다 쉽고 빠른 SQL 3주차(포맷 변경, 조건문)

기본 구조

• SELECT : 데이터를 줘야 하는 가장 기본문
• FROM : 어떤 테이블에서 가져올지 정함
• WHERE : 조건을 줄때 사용
• GROUP BY : 범주 별로 계산을 해줄때 사용
• ORDER BY : 쿼리 결과를 정리

---

실제 업무에 사용 가능하게 문자 데이터를 다듬을 수 있을지

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

 

실습1: 특정 문자를 다른 문자로 바꾸기

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

• Blue를 Pink로 변경
• like는 해당 문자열을 포함한 데이터만 보여줌

select restaurant_name "원래 상정명",
	replace(restaurant_name, "Blue", "Pink") "바뀐 상점명"
from food_orders
where restaurant_name like '%Blue Ribbon%'

 

[문자데이터 바꾸고, groupy by]

• 서울 지역의 음식 타입별 평균 음식 주문 금액

SELECT substr(addr, 1, 2) "지역",
	cuisine_type,
	avg(price) "평균 금액"
FROM food_orders
WHERE addr like "%서울%"
GROUP BY substr(addr, 1, 2), cuisine_type	
# 편의를 위해 SELECT에서 사용한 컬럼 위치를 사용해도됨
# substr(addr, 1, 2), cuisine_type를 1, 2로 작성해도 됨

• 이메일 도메인별 고객수와 평균 연령

# email 부분의 10번째 문자부터 끝까지 가져옴
SELECT substr(email, 10) "도메인",
	count(1) "고객 수",
    avg(age) "평균 연령"
FROM customers
GROUP BY 1

• 지역 음식점이름 음식종류 컬럼 만들고, 총 주문건수

SELECT concat('[', substr(addr, 1, 2), ']', restaurant_name, ' (', cuisine_type, ')') "음식점",
	count(1) "주문 건수"
FROM food_orders
GROUP BY 1

 

실습2: 조건별로 다르게 계산을 할 수 있을지

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

• 특정 문자만 골라 조회할 수 있는 기능 제공
• addr에서 데이터를 뽑아오는데 첫번쨰 문자부터 두 글짜만 가져옴

SELECT addr "원래 주소",
	substr(addr, 1, 2) "시도"
FROM food_orders
WHERE aadr like "%서울특별시%"

 

실습 3: 여러 컬럼의 문자열 합치기

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

• [ 와 substring(addr, 1, 2)와 ] 와 restaurant_name을 결합

SELECT restaurant_name "원래 이름",
	addr "원래 주소",
    concat('[', substring(addr, 1, 2), ']', restaurant_name)
FROM food_orders
WHERE addr like '%서울%'

 

실습[조건에 따라 포맷을 다르게 변경, IF CASE]

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

# 각 조건별로 적용 할 값을 지정
case when 조건 1 then 값(수식)1
	when 조건2 then 값(수식)2
    else 값(수식)3
end

 

• 음식 타입을 Korean일 때는 한식, Korean이 아닌 경우에는 기타라고 지정하고 싶을 때
  • Korean이면 한식이라고 지정, Korean이 아니면 기타로 지정

SELCET 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 '%문곡리%'

• 잘못된 이메일 주소만 수정
  • GROUP BY 1 : 첫 번째 열을 기준으로 그룹화한다는 의미

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

• Korean 일 때는 한식, Japanese 또는 Chinese이면 아시아, 나머지는 기타로 지정

SELECT case when cuisine_type="Korean" then "한식"
	when cuisine_type in ('Japanese', 'Chinese') then '아시아'
    else '기타' end "음식 타입",
    cuisine_type
FROM food_orders

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

SELECT order_id,
	price,
    quantity,
    case when quantity=1 then price
    when quantity>=2 then price/quantity and "음식 단가"
FROM

 

실습[User Segmentation]

• 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 '20대 여성' end '고객 분류',
    name
FROM customers
WHERE age between 10 and 29

 

실습[조건문으로 서로 다른 식 적용한 수수료 구하기]

• 지역과 배달시간을 기반으로 배달 수수료 구하기

# 25분 초과하면 음식 가격의 5%, 30분 초과하면 음식 가격의 10% 수수료 발생
# 서울일때는 시간 계산 * 1.1
SELECT case when dilivery_time>30 then price*0.1*if(addr like '서울', 1.1, 1)
	when dilivery_time>25 then price*0.05*if(addr like '서울', 1.1, 1)
    else 0 end "수수료"
FROM food_orders

---

수업에서 배운대로 하였는데 오류가 나는 이유

• data type 에러
  • 1, 2, 3이라고 저장되어있을지라도 그게 숫자형이 아닌 문자형일수 있고, 이런 것을 연산하려고 하면 에러 발생
  • 데이터를 볼 때 컬럼명 옆에 abc가 적혀있으면 문자, 123이 적혀있으면 숫자
  • 문자, 숫자 혼합하여 함수에 사용할 때는 데이터 타입을 변경해줘야 함

# 숫자로 변경
cast(if(rating='Not given', '1', rating) as decimal)

# 문자로 변경
concat(restaurant_name, '-', cast(order_id as char))

---

과제

[다음의 조건으로 배달시간 늦었는지 판단하는 값 생성]

• 주중: 25분 이상, 주말: 30분 이상

SELECT case when day_of_the_week='Weekend' and delivery_time>=30 then "Late"
	when day_of_the_week='Weekend' and delivery_time<30 then "On-time"
	when day_of_the_week='Weekday' and delivery_time>=25 then "Late"
	when day_of_the_week='Weekday' and delivery_time<25 then "On-time" end "지연여부",
	order_id,
	restaurant_name,
	day_of_the_week,
	delivery_time 
FROM food_orders