SQL

[스파르타코딩클럽] 엑셀보다 쉽고 빠른 SQL - 3주차

zzheng 2024. 11. 20. 22:30

배운 내용을 작성하고 있습니다. 혹여 해당 포스팅에서 잘못된 부분이 있을 경우, 알려주시면 빠르게 수정 조치하도록 하겠습니다. 감사합니다.

 

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

1) Query 결과를 바로 사용할 수 없는 경우

  • 데이터를 보니 잘못된 값이 있어요. 이전에 사용하던 값이어서 다른 문자로 수정을 해줘야하는데, 하나하나 수동으로 하기는 너무 많아서 SQL 로 바꿀 수 있을까요?
  • 저는 주소 전체가 아닌 ‘시도’ 정보만 필요해요. 서울의 통계만 구하고 싶은데, 전체 주소가 아닌 ‘서울’ 로 문자를 변경할 수는 없을까요?
  • 저는 보고서를 작성할 때 사업장 명과 함께 지역이 같이 나와야해요. ‘사업장 [지역]’ 과 같은 형태로 문자 포맷을 변경할 수 있을까요?

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

  • SQL 에서는 특정 문자를 다른 것으로 바꿀 수 있는 기능을 제공합니다.
  • 예시1) 최근에 상점 이름이 바뀌었지만 과거 데이터에는 옛날 이름으로 저장되어있어요
  • 예시2) 예전에 ‘문곡리’ 라는 지명이 ‘문가리’ 로 바뀌었어요
  • 함수명 : replace
  • 사용 방법
replace(바꿀 컬럼, 현재 값, 바꿀 값)
  • [실습1] (식당 명의 ‘Blue Ribbon’ 을 ‘Pink Ribbon’ 으로 바꾸기)
select restaurant_name "원래 상점명",
       replace(restaurant_name, 'Blue', 'Pink') "바뀐 상점명"
from food_orders
where restaurant_name like '%Blue Ribbon%'

3) 원하는 문자만 남기기

  • SQL 에서는 특정 문자만 골라서 조회할 수 있는 기능을 제공합니다
  • 예시) 전체 주소에서 앞부분인 ‘시도’ 부분만 필요해요
  • 함수명 : substring (substr)
  • 사용 방법
substr(조회 할 컬럼, 시작 위치, 글자 수)
  • [실습] (서울 음식점들의 주소를 전체가 아닌 ‘시도’ 만 나오도록 수정)
select addr "원래 주소",
       substr(addr, 1, 2) "시도"
from food_orders
where addr like '%서울특별시%'

4) 여러 컬럼의 문자를 합치기

  • SQL 에서는 여러 컬럼의 값을 하나로 합칠 수 있는 기능을 제공합니다.
  • 예시) 서울시에 있는 음식점은 ‘[서울] 음식점명’ 이라고 수정하고 싶어요
  • 함수명 : concat
  • 사용 방법
concat(붙이고 싶은 값1, 붙이고 싶은 값2, 붙이고 싶은 값3, .....)
  • 붙일 수 있는 문자의 종류
    • 컬럼
    • 한글
    • 영어
    • 숫자
    • 기타 특수문자
  • [실습] (서울시에 있는 음식점은 ‘[서울] 음식점명’ 이라고 수정)
select restaurant_name "원래 이름",   
       addr "원래 주소",
       concat('[', substring(addr, 1, 2), '] ', restaurant_name) "바뀐 이름"
from food_orders
where addr like '%서울%'

 

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

1) Group by 처럼 조건도 카테고리별로 줄 수 있을까?

💡 범주별로 값을 구할 때는 group by 를 썻죠. 범주별로 다른 연산 (계산, 문자 바꾸기) 을 적용할 수도 있을까요?

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

💡 개념을 이해하기 어렵다면 아래의 예시를 참고해봅시다

  • 음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Korean’ 이 아닌 경우에는 ‘기타’ 라고 지정하고 싶어요
  • 주소의 시도를 ‘경기도’ 일때는 ‘경기도’, 아닐 때는 앞의 두 글자만 사용하고 싶어요
  • 음식 단가를 주문 수량이 1일 때는 음식 가격, 주문 수량이 2개 이상일 때는 음식가격/주문수량 으로 지정하고 싶어요 

2) 조건에 따라 다른 방법을 적용하고 싶을 때 - If 문 기초 (실습 포함)

💡 조건을 지정해주는 가장 기초 문법은 ‘If’ 문입니다 (엑셀의 기능과 유사합니다)

  • IF 문은 원하는 조건에 충족할 때 적용할 방법과 아닌 방법을 지정해 줄 수 있습니다
  • 예시) 음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Korean’ 이 아닌 경우에는 ‘기타’ 라고 지정하고 싶어요
  • 함수명 : if
  • 사용 방법
if(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)
  • [실습1] 음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Korean’ 이 아닌 경우에는 ‘기타’ 라고 지정
select restaurant_name,
       cuisine_type "원래 음식 타입",
       if(cuisine_type='Korean', '한식', '기타') "음식 타입"
from food_orders

 

3) 조건을 여러가지 지정하고 싶을 때 - Case 문 기초 (실습 포함)

💡 조건을 지정하다보면, 두 개 이상 지정을 해야 할 경우가 생깁니다. 이 때는 case 문을 이용하여 여러번의 if 문을 적용 한 효과를 낼 수 있습니다

  • Case 문은 각 조건별로 적용 할 값을 지정해 줄 수 있습니다.
  • 조건별로 지정을 해주기 때문에 아래와 같이 if 문을 여러번 쓴 효과를 낼 수 있습니다. case(조건1, 값1, case(조건2, 값2, 값3))
  • 예시) 음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Japanese’ 혹은 ‘Chienese’ 일 때는 ‘아시아’, 그 외에는 ‘기타’ 라고 지정
  • 함수명 : case
  • 사용 방법
case when 조건1 then 값(수식)1
     when 조건2 then 값(수식)2
     else 값(수식)3
end
  • [실습1] 음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Japanese’ 혹은 ‘Chienese’ 일 때는 ‘아시아’, 그 외에는 ‘기타’ 라고 지정
select restaurant_name,
       cuisine_type AS "원래 음식 타입",
       case when (cuisine_type='Korean') then '한식'
       else '기타'
       end as " 음식 타입"
from food_orders

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

  • 새로운 카테고리 만들기
    • 음식 타입과 같이 새로운 카테고리를 만들 수 있습니다.
      • 한국 음식, 아시아 음식, 미국 음식, 유럽 음식 이런 식의 새로운 cuisine_category 를 생성할 수 있죠
    • 고객들의 분류도 만들 수 있습니다.
      • 10대 여성, 10대 남성, 20대 여성, 20대 남성 등, 이런 식의 성별과 나이별로 새로운 고객 군 카테고리를 생성할 수 있죠
  • 연산식을 적용할 조건 지정하기
    • 수수료를 계산할 때 흔히들 현금 사용, 카드사용을 나누고는 하죠
    • 현금일 때의 수수료율과 카드일 때의 수수료율이 다르다면, 연산식을 만들 때 if 문 혹은 case 문으로 각각 다른 수수료율 혹은 수수료 계산 방식을 적용할 수 있습니다
  • 다른 문법 안에서 적용하기
    • if, case 문 안에 다른 문법이나 연산을 넣을 수도 있지만, 다른 문법 안에 조건문을 넣을 수도 있습니다
    • 예를 들어 concat 문으로 여러 컬럼을 합칠 때, rating 이 있을 때는 rating 을 넣어주고 없을 때는 아무것도 넣지 않도록, concat 안에 if 문을 넣어줄 수 있죠

 

3. SQL문에 문제가 없는 것 같은데 왜 오류가 나나요_ (Data Type 오류 해결하기)

  • 문자/숫자 계산을 했더니 오류가 났어요
    • SQL 문을 회사에서 써봤는데요, avg, substring 등 함수를 썼더니 에러메세지에 ‘data type’ 단어가 뜨면서 실행되지 않아요
    • 우리가 실습하는 Mysql 과 다르게, 다른 SQL 문법에서는 data type 이 다를 때 연산이 되지 않을 수 있습니다.
    • rating 은 숫자가 포함되어 있지만 문자 형으로 저장이 되어있습니다 (출력 결과 컬럼명 옆의 ‘ABC’ 혹은 ‘123’ 을 확인해주세요. ‘ABC’ 는 문자로 저장이 되어있다는 의미입니다.)

  • 따라서 문자, 숫자를 혼합하여 함수에 사용 할 때에는 데이터 타입을 변경해주어야 합니다
--숫자로 변경
cast(if(rating='Not given', '1', rating) as decimal) 

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

 

정리

👉 문자 변경

  1. REPLACE : 지정한 문자를 다른 문자로 변경
  2. SUBSTRING : 특정 문자만 추출
  3. CONCAT : 여러 문자를 합하여 포맷팅

👉 조건문

  1. IF : if(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)
  2. CASE WHEN END : case when 조건1 then 값(수식)1 when 조건2 then 값(수식)2 else 값(수식)3 end