[내일배움캠프-사전캠프 02일차] SQL 기초-2

SQL에 대한 기본 지식은 갖췄다고 생각했으나 애매했던 부분에 대해서만 정리했습니다.

 

✅전체 데이터의 갯수 구하기

• 함수 종류
  • 데이터 갯수 : COUNT(컬럼) * 컬럼명 대신 1 혹은 * 사용 가능
  • 몇개의 값을 가지고 있는지 구할 때 : DISTINCT
• 예시
  • 데이터 갯수 : 주문 테이블의 전체 주문은 몇건인가요?
  • 몇개의 값을 가지고 있는지 구할 때 : 주문을 한 고객은 몇명인가요?
    • count(DISTINCT cutomer_id)
• 사용 예시 (주문건수와, 주문 한 고객 수 구하기)

select count(1) count_of_orders,
       count(distinct customer_id) count_of_customers
from food_orders

 

 

 

✅SQL 가공하기 (REPLACE, SUBSTRING, CONCAT)

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

💡 데이터를 조회하다보면, Query 결과를 그대로 이용하지 못하는 경우가 있다.

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

 

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

• 예시1) 최근에 상점 이름이 바뀌었지만 과거 데이터에는 옛날 이름으로 저장
• 예시2) 예전에 ‘문곡리’ 라는 지명이 ‘문가리’ 로 바뀌었다.
• 함수명 : replace 
• 사용 방법 
  • replace(바꿀 컬럼, 현재 값, 바꿀 값)
• (식당 명의 ‘Blue Ribbon’ 을 ‘Pink Ribbon’ 으로 바꾸기)

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

 

3) 원하는 문자만 남기기

• 예시) 전체 주소에서 앞부분인 ‘시도’ 부분만 필요
• 함수명 : substring (substr)
• 사용 방법
  • substr(조회 할 컬럼, 시작 위치, 글자 수)
• (서울 음식점들의 주소를 전체가 아닌 ‘시도’ 만 나오도록 수정)

select addr "원래 주소",
       substr(addr, 1, 2) "시도"
from food_orders
where addr like '%서울특별시%'

 

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

• 예시) 서울시에 있는 음식점은 ‘[서울] 음식점명’ 이라고 수정하고 싶어요
• 함수명 : concat
• 사용 방법
  • concat(붙이고 싶은 값1, 붙이고 싶은 값2, 붙이고 싶은 값3, .....)
• 붙일 수 있는 문자의 종류
  • 컬럼, 한글, 영어, 숫자, 기타 특수문자
• (서울시에 있는 음식점은 ‘[서울] 음식점명’ 이라고 수정)

 

 

 

 

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

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

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

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

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

 

1) 조건에 따라 다른 방법을 적용하고 싶을 때 - If 문

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

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

 

 

 

2) 조건을 여러가지 지정하고 싶을 때 - Case 문

• 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
• 음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Japanese’ 혹은 ‘Chienese’ 일 때는 ‘아시아’, 그 외에는 ‘기타’ 라고 지정

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

 

 

 

✅SQL문에 문제가 없는 것 같은데 왜 오류가 날까? (Data Type 오류 해결하기)

문자/숫자 계산을 했더니 오류 발생

• 👉 avg, substring 등 함수를 썼더니 에러메세지에 ‘data type’ 단어가 뜨면서 실행되지 않는다.
• 아래 예시를 보면, rating 은 숫자가 포함되어 있지만 문자 형으로 저장이 되어있다
  (출력 결과 컬럼명 옆의 ‘ABC’ 혹은 ‘123’ 을 확인 ‘ABC’ 는 문자로 저장이 되어있다는 의미.)

 

따라서 문자, 숫자를 혼합하여 함수에 사용 할 때에는 데이터 타입을 변경해주어야 한다.

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

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

 

 

 

 

 

 

✅ 오늘의 회고

• if, case 에서 여러 조건이 걸리면 까다롭다
• 주로 ORM 을 사용했어서 오랜만에 쿼리 학습이라 재밌다.