SQL - 3주차

[수업 목표]
◆  Query 결과를 업무에 바로 사용할 수 있도록 문자 (워딩) 를 다듬는다

( 저장된 데이터를 수정하진 않고 출력정보에서만 바뀝니다.)

◆  문자열 자르기

 SUBSTR("문자열", "시작위치", "길이")
SUBSTR(ename, 2): 문자열의 두 번째 위치("O")부터 문자열 끝까지 자른다.
SUBSTR(ename, 2, 3): 문자열의 두 번째 위치("O")부터 세 자리("ONE")까지 자른다.

◆  replace  : 특정 문자를 다른 문자로 바꾸는 방법 

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

(저장된 데이터를 바꾼 것이 아니라 출력결과 수정한 것입니다.)

select 바꿀컬럼 바꿀컬럼임시이름,
       replace(바꿀컬럼, '원래 단어', '바꿀 단어') 수정한컬럼의 임시명
from 테이블
where 바꿀컬럼 like '%포함된 문자%' -- like...는 특정문자로 데이터 찾기

 

◆ 식당 명의 ‘Blue Ribbon’ 을 ‘Pink Ribbon’ 으로 바꾸기

 

◆ 주소의 ‘문곡리’ 를 ‘문가리’ 로 바꾸기

 

◆ substring : 데이터에서 특정한 문자만 추출하는 구문

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

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

select 바꿀컬럼 바꿀컬럼의임시명,
       substr(바꿀컬럼, 1, 2) 바뀐컬럼임시명  -- substr(조회 할 컬럼, 시작 위치, 글자 수)
from 테이블
where 바꿀컬럼 like '%특정할단어%'

 

◆ 서울 음식점들의 주소를 전체가 아닌 ‘시도’ 만 나오도록 수정

 

◆ concat : 여러 컬럼의 문자 합치기  (콘캣)

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

 

▷붙일 수 있는 문자의 종류 ( 컬럼명을 제외한 글자에 앞뒤로 작은 따음표를 붙입니다.)
   컬럼, 한글, 영어, 숫자, 기타 특수문자

 

◆  서울시에 있는 음식점은 ‘[서울] 음식점명’ 이라고 수정

select restaurant_name "원래 이름",   
       addr "원래 주소",
       concat('[', substring(addr, 1, 2), '] ', restaurant_name) "바뀐 이름"
from food_orders
where addr like '%서울%'

 

 

실습

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

◆  서울 지역의 음식 타입별 평균 음식 주문금액 구하기

SELECT substr(addr, 1,2) "지역" -- addr 컬럼의 앞의 두글자만(1,2) 따와서 '지역'컬럼으로 만들기--
GROUP by 1,2 -- substr(addr, 1,2) 랑 같은 뜻

◆ 이메일 도메인별 고객 수와 평균 연령 구하기

◆  ‘[지역(시도)] 음식점이름 (음식종류)’ 컬럼을 만들고, 총 주문건수 구하기

 

 

◆ 수치계산 및 문자를 다듬을 때, 조건별로 다르게 적용한다

◆ If 문 : 조건에 따라 다른 방법을 적용하고 싶을 때 

  if(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)조건은 컬럼안의 데이터를 기준으로 작성하세요.

◆ 음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Korean’ 이 아닌 경우에는 ‘기타’ 라고 지정

      if(cuisine_type='Korean', '한식', '기타') "음식 타입"

      만약(if)  cuisine_type(컬럼명)이 'Korean'이 맞으면 '한식', 아니면  '기타'로

          "음식 타입" 컬럼을 만들어서 표시하시오.

      삼항문 같다. 

 

◆ 실습에서 ‘문곡리’ 가 평택에만 해당될 때, 평택 ‘문곡리’ 만 ‘문가리’ 로 수정
       if(addr like '%평택군%', replace(addr, '문곡리', '문가리'), addr) "바뀐 주소"

     만약 addr컬럼의 데이터가 '평택군'을 포함한다면 데이터를 문곡리에서 문가리로 바꾸고, 아니면 그대로 "바뀐주소"컬럼에 표시하세요.

 

◆ 실습에서 잘못된 이메일 주소 (gmail) 만 수정을 해서 사용

 

◆ Case 문 :조건을 여러가지 지정하고 싶을 때

case when 조건1 then 값(수식)1
     when 조건2 then 값(수식)2
     else 값(수식)3 -- 혹사 모를 값
end

-- in 여러 데이터 중 하나를 만족하면 되는 경우 
case when 컬럼 in ('경우1', '경우2', '경우3') then 값(수식)
-- not in 여러 데이터 중 하나도 만족하지 않는 경우 
     when 컬럼 not in ('경우1', '경우2', '경우3') then 값(수식)

select restaurant_name,
       price/quantity "단가",
       case 
            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 <5000) and cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '기타1'
from food_orders

 

Case 문은 각 조건별로 적용 할 값을 지정해 줄 수 있습니다. 
조건별로 지정을 해주기 때문에 아래와 같이 if 문을 여러번 쓴 효과를 낼 수 있습니다. 

   case(조건1, 값1, case(조건2, 값2, 값3))

 조건이 여러개일 경우 'and'로 이어주세요

 

▷조건을 사용할 수 있는 경우 
   - 새로운 카테고리 만들기
   -  연산식을 적용할 조건 지정하기 
   -  다른 문법 안에서 적용하기

 

◆ 음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Japanese’ 혹은 ‘Chienese’ 일 때는 ‘아시아’, 그 외에는 ‘기타’ 라고 지정

 

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

 

실습

✔️ 새로운 카테고리 만들기 - 조건문과 수식을 이용하여 간단한 User Segmentation 을 해봅시다

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

 ◆ 음식 단가, 음식 종류 별로 음식점 그룹 나누기

(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

price/quantity : 단가

✔️ 조건문을 이용하여 다른 수식을 적용해보기

◆  [실습] 지역과 배달시간을 기반으로 배달수수료 구하기 (식당 이름, 주문 번호 함께 출력)
(지역 : 서울, 기타 - 서울일 때는 수수료 계산 * 1.1, 기타일 때는 곱하는 값 없음
 시간 : 25분, 30분 - 25분 초과하면 음식 가격의 5%, 30분 초과하면 음식 가격의 10%)

 

◆ 주문 시기와 음식 수를 기반으로 배달할증료 구하기
(주문 시기 : 평일 기본료 = 3000 / 주말 기본료 = 3500
 음식 수 : 3개 이하이면 할증 없음 / 3개 초과이면 기본료 * 1.2)

 

◆ 수치계산과 문자 연산이 되지 않는 경우를 배우고, 에러를 수정한다

◆ 데이터 타입에 따른 오류

Mysql 과 다르게, 다른 SQL 문법에서는 data type 이 다를 때 연산이 되지 않을 수 있습니다

컬럼명 좌측상단에 데이터 타입이 적혀있습니다.

abc, A-Z 등은 문자타입

123 은 숫자타입 입니다.

 

◆  데이터 타입 바꾸기

문자 -> 숫자
cast(변경하고자하는 커럼or 값 as decimal) 

숫자 -> 문
cast( 변경하고자하는 커럼or 값 as char)

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

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

 

---

 

숙제

다음의 조건으로 배달시간이 늦었는지 판단하는 값을 만들어주세요.
주중 : 25분 이상
주말 : 30분 이상
SQL 문의 기본 구조로 시작
조건을 여러번 적용할 때 if, case 문 중 어떤 것을 이용할지 결정
조건에 ‘주중, 주말’ 조건과 ‘배달시간’ 조건을 동시에 줄 때 사용 할 논리연산자 결정

예상 결과

 

답

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

 

 

---

※ 요약

◆ 업무 필요한 문자 포맷이 다를 때, SQL로 가공하기
▷  REPLACE 특정 문자를 다른 문자로 바꾸기
▷  SUBSTRING 원하는 문자만 남기기 
▷  CONCAT 여러 컬럼의 문자를 합치기 

◆  조건에 따라 포맷을 다르게 변경하기
▷  If 조건에 따라 다른 방법을 적용하고 싶을 때 
▷  Case 조건을 여러가지 지정하고 싶을 때 

 

◆  cast 데이터 타입 바꾸기

▷ 문자 -> 숫자
cast(변경하고자하는 커럼or 값 as decimal) 

▷ 숫자 -> 문
cast( 변경하고자하는 커럼or 값 as char)

 

※ 기억할 것

◆ 특정 문자를 다른 문자로 바꾸는 방법 : replace

replace(바꿀컬럼, '원래 단어', '바꿀 단어')

◆ 데이터에서 원하는 부분만 출력하는 방법입니다.

--substr (바꿀컬럼, 위치 숫자)
subster(addr,1,2)--자리값은 1부터 시작합니다.

※Tip

◆ 'GROUP BY 1 '은 첫번 째 컬럼으로 묶어준다는 뜻