[SQL] SQL 문자열 가공, 조건식, 서브쿼리

• [TIL] 2024-10-15

  • SQL 문자열 가공, 조건식, 서브 쿼리

• 문자열 가공, 조건식

• Replace

  • 데이터의 기존 값을 새로운 값으로 대체하고 싶은 경우

  • 기본 구조

    replace(컬럼명, 기존 값, 변경할 값)

  • 예시)

    select addr, replace(addr, '문곡리', '문가리') "바뀐주소"
    from food_orders
    where addr like '%문곡리%'

  • 실행 결과

  • 아래와 같이 기존 addr 열의 ‘문곡리’가 → 바뀐주소 열의 ‘문가리’로 변경되어 출력된 것을 확인할 수 있음

    

• Substring (substr)

  • 데이터의 일부 문자열만 뽑아내는 경우

  • 기본 구조

    substr(컬럼명, 시작 위치 (e.g. 1 -> 첫번째 글자부터), 글자 수)

  • 예시)

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

• Concat

  • 문자열 합치기

  • 기본 구조

    concat(붙일 값 1, 붙일 값 2, 붙일값 3 ...)

  • 예시)

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

• if

  • 기본 구조

    if(조건, 조건 충족 시 값, 조건 미충족시 값)

  • 예시)

    
    select restaurant_name, cuisine_type "원래 음식 타입", if(cuisine_type='Korean', '한식', '기타') "음식 타입"
    from food_orders

• case

  • 기본 구조

    case when 조건1 then 값(수식)1
    		 when 조건2 then 값(수식)2
    		 else 값(수식)3
    end

  • 예시)

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

  • 활용 케이스

    • 새로운 카테고리 생성할 경우
    • 고객들의 분류를 생성할 경우
      • 10대 여성, 10대 남성, 20대 여성, 20대 남성 등..
    • 연산식 적용 조건 지정이 별도로 필요한 경우

      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 "고객 분류"
      from customers c
      where age between 10 and 29

      • 현금/카드 수수료가 각각 달라서 계산 방식 적용이 별도로 필요한 상황

      • 배달 시간 별 수수료 차등 계산 등

        select case when delivery_time>30 then price*0.1*if(addr like '%서울%', 1.1, 1)
        						when delivery_time>25 then price*0.05
        						else 0 end "수수료",
        						restaurant_name ,
        						order_id,
        						price,
        						delivery_time,
        						addr
        from food_orders

    • if, case문 모두 해당 문 안에 여러 문법, 연산을 넣을 수 있음 (반대도 가능)

• Data Type 에러 처리

  • 데이터 타입 변경

    --숫자로 변경하기
    cast(if(rating='Not given', '1', rating as decimal))
    
    --문자로 변경하기
    cast(restaurant_name, '-', cast(order_id as char))

  • 타입 변경은 짧게만 나왔는데 써먹을 일이 많을 것 같다 기본 구조는 아래처럼 쓰는 듯 하다

  • 기본 구조

    CAST(컬럼명, 값 AS 변경하려는 데이터타입)

  • 데이터 타입은 다른 블로그에서 검색해서 가져옴. 아래 참조

    • 출처 : https://bramhyun.tistory.com/56

      BINARY	BINARY (1,0)
      CHAR	문자열
      DATE	YYYY-MM-DD (날짜 형식)
      DATETIME	YYYY-MM-DD hh:mm:ss
      TIME	hh:mm:ss
      DECIMAL	고정 소수점 타입 ;DECIMAL(M,D)
      NCHAR	국가별 문자 세트 문자열
      SIGNED	부호 있는 64비트 정수
      UNSIGNED	부호 없는 64비트 정수

• 3주차 완강 ! 🤠

---

• SQL에서의 복잡한 연산 수행
• Subquery
  • 활용케이스
    • 여러 번의 연산을 수행할 때
    • 조건문에 연산 결과를 사용해야 할 때
  • 기본 구조

    select special_col_result
    from
    	(select col1, col2
    	from table1
    	) a

  • 예시)

    select price/quantity
    from
    	(select price, quantity
    	from food_orders
    	) a
    	
    	
    	
    	------
    	
    	
    	SELECT restaurant_name,
    		sido,
    		avg_delivery_time,
    		case when avg_delivery_time<=20 then '<=20'
    		when avg_delivery_time>20 and avg_delivery_time<=30 then '20<x<=30'
    		else '>30' end delivery_time_segment
    from
    (
    select restaurant_name,
    		substr(addr, 1, 2) sido, 
    		avg(delivery_time) avg_delivery_time
    from food_orders
    group by 1,2
    ) a
    
    ------
    select cuisine_type,
    	total_quantity,
    	count_res,
    	case when count_res>=5 and total_quantity>=30 then 0.005
    	when count_res>=5 and total_quantity<30 then 0.008
    	when count_res<5 and total_quantity>=30 then 0.01
    	when count_res<5 and total_quantity<30 then 0.02 end rate
    from 
    (
    select cuisine_type, 
    		sum(quantity) total_quantity,
    		count(distinct restaurant_name) count_res
    from food_orders
    group by 1
    ) a

• Join

  • 활용 케이스

    • 필요한 데이터가 여러 테이블에 흩어져 있을 경우
    • 엑셀의 vlookup 과 유사
    • LEFT JOIN
      • A B 테이블 중 A의 인덱스 기준으로 테이블을 병합함
    • INNER JOIN
      • A B 테이블의 교집합 인덱스를 기준으로 테이블을 병합함

  • 기본 구조

  • 예시)

    --- LEFT JOIN 
    SELECT * FROM food_orders left join payments on food_orders.order_id=payments.order_id 
    
    --- INNER JOIN
    SELECT * FROM food_orders inner join payments on food_orders.order_id=payments.order_id 
    
    --- 실습 데이터 활용 예시
    SELECT f.order_id,
    f.customer_id,
    f.restaurant_name,
    f.price,
    c.name,
    c.age,
    c.gender
    FROM food_orders f left join customers c on f.customer_id=c.customer_id

• 중복된 것을 제외하고 조회

  select distinct col1
  from tableA

• 실습
• Subquery & Join 예시)

select cuisine_type,
				sum(price) price,
				sum(price*discount_rate) discounted_price
from
(
select f.cuisine_type,
				f.price,
				c.age,
				(c.age-50)*0.005 discount_rate
from food_orders f left join customers c on f.customer_id=c.customer_id
where c.age>=50
) a 
group by 1
order by sum(price*discount_rate) desc