[TIL] 241120 | SQL | REPLACE | SUBSTR | CONCAT | IF | CASE WHEN | CAST | subquery | JOIN

0. 아티클 스터디 2건
1. 엑셀보다 쉽고 빠른 SQL 3주차
2. 엑셀보다 쉽고 빠른 SQL 4주차

엑셀보다 쉽고 빠른 SQL 3주차

특정 문자를 다른 문자로 바꾸기 - REPLACE

• REPLACE(바꿀 칼럼, '현재 값', '바꿀 값')

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

전체 문자열에서 원하는 문자만 남기기 - SUBSTR

• SUBSTR(조회할 칼럼, 시작위치, 글자 수)
• 끝까지 가져오고 싶을 때는 세 번째 파라미터 생략

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 '%서울%'

TIP

• GROUP BY를 사용할 때 컬럼명 대신 SELECT 절에서의 위치를 적어도 됨
  예시:

SELECT SUBSTR(addr, 1, 2) '시도',
	cuisine_type '음식 종류',
	AVG(price) '평균 금액'
FROM food_orders
WHERE addr LIKE '서울%'
GROUP BY 2

여기서 1은 SUBSTR(addr, 1, 2), 2는 cuisine_type 의미.

조건에 따른 포맷 설정 - IF

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

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

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

여러 가지 조건 설정 - CASE WHEN

• CASE WHEN 조건1 THEN 값1
  WHEN 조건2 THEN 값2
  ELSE 값3 END
• ELSE는 생략 가능

SELECT CASE WHEN cuisine_type = 'Korean' THEN '한식'
			WHEN cuisine_type IN ('Japanese', 'Chinese') THEN '아시아'
			ELSE '기타' END '음식타입',
		cuisine_type
FROM food_orders

CASE WHEN, IF 복합적 사용

SELECT restaurant_name, order_id, delivery_time, price, addr,
	CASE WHEN delivery_time > 30 THEN price * 0.1 * IF(addr LIKE '서울%', 1.1, 1)
		WHEN delivery_time BETWEEN 26 AND 30 THEN price * 0.05 * IF(addr LIKE '서울%', 1.1, 1)
		ELSE 0 END '수수료'
FROM food_orders  

같은 결과, 다른 SQL문

• CASE WHEN 사용하는 경우

SELECT order_id, price, quantity, day_of_the_week,
	CASE WHEN quantity > 3 THEN 1.2 * IF(day_of_the_week = 'weekday', 3000, 3500)
		WHEN quantity <= 3 THEN 1 * IF(day_of_the_week = 'weekday', 3000, 3500) END '배달할증료'
FROM food_orders  

• CASE WHEN을 사용하지 않는 경우

SELECT order_id, price, quantity, day_of_the_week,
 	IF(quantity > 3, 1.2, 1) * IF(day_of_the_week = 'weekday', 3000, 3500) '배달할증료'
FROM food_orders  

조건이 2개 이하일 때에는 IF문이 더 간결함.

DataType 오류 해결 - CAST

• 문자, 숫자를 혼합하여 함수에 사용할 때 데이터타입 변경 필요
• 숫자로 변경:
  • CAST(IF(rating = 'Not given', '1', rating) as decimal): 숫자로 변경
• 문자로 변경:
  • CONCAT(restaurant_nme, '-', cast(order_id as char): 문자로 변경

엑셀보다 쉽고 빠른 SQL 4주차

Subquery

Subquery가 필요한 경우

• 여러 번의 연산을 수행해야 할 때
• 조건문에 연산 결과를 사용해야 할 때
• 조건에 query 결과를 사용하고 싶을 때

Subquery문의 기본 구조

• 'Query 안에 sub로 들어간 구문'
  예시

select order_id, restaurant_name, if(over_time>=0, over_time, 0) over_time
from 
(
select order_id, restaurant_name, food_preparation_time-25 over_time
from food_orders
) a

• FROM문 속 () 서브쿼리를 'a'로 명명. 초과된 시간(over_time)을 구함.
• 본 쿼리에서 over_time 칼럼을 활용하여 IF문 계산.

다중 subquery

select restaurant_name,
       price_per_plate*ratio_of_add "수수료"
from 
(
select restaurant_name,
       case when price_per_plate<5000 then 0.005
            when price_per_plate between 5000 and 19999 then 0.01
            when price_per_plate between 20000 and 29999 then 0.02
            else 0.03 end ratio_of_add,
       price_per_plate
from 
(
select restaurant_name, avg(price/quantity) price_per_plate
from food_orders
group by 1
) a
) b

두 테이블의 데이터 조회 - JOIN

LEFT JOIN

• 공통 칼럼(키값)을 기준으로, 하나의 테이블에 값이 없더라도 모두 조회.
• 왼쪽 테이블의 모든 데이터 조회.

SELECT *
FROM food_orders fo LEFT JOIN payments p ON fo.order_id = p.order_id

• table1 LEFT JOIN table2 ON table1.column_name1 = table2.column_name2

INNER JOIN

• 공통 칼럼(키값)을 기준으로 두 테이블 모두에 있는 값만 조회.

SELECT *
FROM food_orders fo INNER JOIN payments p ON fo.order_id = p.order_id