[SQL 기초] 3주차 강의

 
2024.06.21
앗 잠시만요,, 갑자기 너무 어려워지는데요?
생각이 많은 감자는.. 벌써부터 나중에 내가 이런 걸 혼자서 데이터를 다룰 때 능숙하게 잘 할 수 있을지 걱정해요...^ㅠ^
일단 파이팅....! (분명 오늘 본 건데 왜 기억에 없지...)
 

 

[이론 1]

✳️ REPLACE, SUBSTRING, CONCAT - 데이터 가공하기
       • 쿼리 결과를 바로 사용할 수 없는 경우
          ex) 데이터의 잘못된 다량의 값을 수정해줄 때,
                 칼럼 내의 내용 중 특정 정보만 필요할 때,
                 칼럼 1의 a 내용과 칼럼 2의 b 내용이 'a b' 형태로 문자 포맷을 변경하고 싶을 때

✳️ REPLACE - 특정 문자를 다른 문자로 바꾸기
       - 예전에 저장되어 있었던 이름을 최신 이름으로 변경
      • 함수 사용 방법 : replace(바꿀 컬럼, 현재 값, 바꿀 값)
        💠 주소의 '문곡리'를 '문가리'로 바꾸기

        💭 나의 예시 - bacteria_info 테이블에서 genus_name 컬럼에서 'Propionibacterium'을 'Cutibacterium'으로 변경?

select genus_name "속명"
       replace(genus_name, 'Propionibacterium', 'Cutibacterium')
from bacteria_info
where genus_name like 'Propionibacterium%'

 
✳️ SUBSTRING - 원하는 문자만 남기기
       - 전체 데이터가 아니라 특정한 문자만 필요할 때, 필요한 부분만 조회
       • 함수 사용 방법 : substr(조회 할 컬럼, 시작 위치, 글자 수)
        💠 서울 음식점들의 주소를 전체가 아닌 '시나 도'만 나오도록 수정

           💭 나의 예시 - 서울 음식점들의 주소를 전체가 아닌 '구'만 나오도록 수정

✳️ CONCAT - 여러 컬럼의 문자를 합치기
       - 원하는 문자가 여러 컬럼에 나뉘어 있을 때, 하나로 합쳐서 업무에 필요한  형태로 제작
       • 함수 사용 방법 : concat(붙이고 싶은 값1, 붙이고 싶은 값2, 붙이고 싶은 값3, ........)
        💠 [서울] 음식점명 이라고 수정

           💭 나의 예시 - cosmetics 테이블에서 brand_name, product_name, product_type이 있다고 가정
                                        1)  [에스쁘아] product_name으로 수정

select brand_name "원래 브랜드명", product_name "원래 상품명"
concat('[', substr(brand_name, 1, 2), ']', product_name "바뀐 이름"
from cosmetics
where brand-name like '에스쁘아%'

                                        2)  종류별 상품명으로 수정

select product_name "원래 상품명", product_type "원래 상품종류"
concat(product_type, '-', product_name) "종류별 상품명"
from cosmetics

 

[실습 1]

☑️ 문자 데이터를 바꾸고, GROUP BY 이용
       💠서울 지역의 음식 타입별 평균 음식 주문 금액 구하기 (출력 : ‘서울’, ‘타입’, ‘평균 금액’) 
             group by를 이용할 때 카테고리별로 묶어서 보여 컬럼의 명칭을 적어줘도 되지만 컬럼의 순번을 적어줘도 됨 
             (여기서는 서울 지역과 음식 타입별의 컬럼을 group by해서 데이터를 보여줌)

       💠이메일 도메인별 고객 수와 평균 연령 구하기
             substr(email, 10) 이라고 쓴 것은 10번째 글자부터 마지막 글자까지 모두 불러올 것이라서,
              글자수를 특정해줄 필요가 없었기 때문에 생략해준 것이다.
             customers 테이블내에서 모든 고객들의 수를 카운팅해달라고 한 것이기 때문에 ,
              count에서는  *, 1을 이용해주고 컬럼을 특정하지 않아도 된다고 했었다.

 

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

[이론 2]

✳️ IF, CASE - 조건에 따라 포맷을 다르게 변경
      • 조건에 따라 다른 연산(계산, 문자 바꾸기)을 하기 위한 방법
          ex) a컬럼에서 '2024' 일 때는 '올 해', '2024'가 아닌 경우에는 '기타' 라고 지정,
                주소의 시도를 ‘경기도’ 일때는 ‘경기도’, 아닐 때는 앞의 두 글자만 사용,
                음식 단가를 주문 수량이 1일 때는 음식 가격, 주문 수량이 2개 이상일 때는 음식가격/주문수량 으로 지정
✳️ IF
      - 조건 충족과 불충족을 지정
       • 함수 사용 방법 : if(조건, 조건 충족, 조건 불충족)
         💠 음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Korean’ 이 아닌 경우에는 ‘기타’ 라고 지정

         💠 ‘문곡리’ 가 평택에만 해당될 때, 평택 ‘문곡리’ 만 ‘문가리’ 로 수정

         💠 이전에 도메인별 고객 수와 평균 연령 구하기에서 잘못된 이메일 주소 (gmail) 만 수정을 해서 사용

 
✳️ CASE
      - 각 조건별로 적용할 값을 지정해줄 수 있음
      - if 문을 여러 번 쓴 효과가 있음 if(조건1, 값1, if(조건2, 값2, 값3))
      - else는 조건이 부합하지 않는 경우에 이용, 이런 경우가 없으면 생략
      - end는 case 문을 끝내주는 것으로 명령
       • 함수 사용 방법 : case when 조건1 then 값(수식)1
                                                  when 조건2 then 값(수식)2
                                                  else 값(수식)3 end
         💠 음식단가를 주문 수량이 1일 때는 음식 가격, 주문 수량이 2개 이상일 때는 음식가격/주문수량 으로 지정
                 ✔️ if 문으로도 작성 해보자

case 문 이용

if 문 이용

                 💠 주소의 시도를 ‘경기도’ 일때는 ‘경기도’, ‘특별시’ 혹은 ‘광역시’ 일 때는 붙여서, 아닐 때는 앞의 두 글자만 사용

 
✳️ 조건을 사용할 수 있는 경우
       • 새로운 카테고리 만들기
          - 음식 타입 : 한국음식, 아시아음식, 미국음식, 유럽음식 등 cuisine_category 생성
          - 고객 분류 : 10대 여성, 10대 남성, 20대 여성, 20대 남성 등 성별 및 나이별 새로운 고객 카테고리 생성

       • 연산식을 적용할 조건 지정하기
          - 수수료 계산 시, 카드와 현금의 수수료율이 다르면 if 문으로 각각 다른 수수료율 혹은 case 문으로 수수료 계산 방식 적용

       • 다른 문법 안에서 적용하기
          - if와 case 문 내에 문법이나 연산을 넣는 것처럼 반대로 문법 내에 조건문을 넣어줄 수 있음

[실습 2]

☑️ 간단한 User Segmentation (사용자 분할) 해보기
      💠10세 이상, 30세 미만의 고객의 나이와 성별로 그룹 나누기 (이름도 같이 출력)

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

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

 

[실습 3]

☑️ 조건문으로 서로 다른 식을 적영한 수수료 구해보기
      💠지역과 배달시간을 기반으로 배달수수료 구하기 (식당 이름, 주문 번호 함께 출력)
            (지역 : 서울, 기타 - 서울일 때는 수수료 계산 * 1.1, 기타일 때는 곱하는 값 없음
             시간 : 25분, 30분 - 25분 초과하면 음식 가격의 5%, 30분 초과하면 음식 가격의 10%)

조건에 배달시간이 30분 초과, 25분-30분 초과라서 굳이 비트윈을 써주지 않아도 30분 초과 조건만으로도 충족이 되어 25분 초과라 명시해줬다.

비트윈을 쓰면 이런식으로 활용도 가능하다

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

 

[이론 3]

✳️ SQL문에 문제가 없는 것 같은데 왜 오류가 나나요_ (Data Type 오류 해결하기)
       • 다른 데이터들을 다루면서 일어나는 오류일 때 참고하면 좋음

3주차 강의자료 일부

 

[숙제]

✏️ 다음의 조건으로 배달시간이 늦었는지 판단하는 값을 만들어주세요.
       - 주중 : 25분 이상 / - 주말: 30분 이상

select *
from food_orders

처음에는 어떤 컬럼을 이용해야 할지 알아보기 위해 food_orders의 모든 정보를 불러옴.
delivery_time과 day_of_the_week 컬럼을 이용 할 것.

select order_id, 
       restaurant_name, 
       day_of_the_week, 
       delivery_time,
       case when day_of_the_week='weekday', if(delivery_time>=25) then 'late'
            when day_of_the_week='weekend', if(delivery_time>=30) then 'late'
            else 'on-time' END 
from food_orders

처음에 조건을 두 개로 두고 day_of_the_week이 주중 혹은 주말일 때를 구분해 case 문을 써줌.
근데 delivery_time은 어떻게 조건을 걸어주지..!? 계속 if 문을 같이 쓴는 걸 봐서 그런지 이런 식으로 쿼리를 작성했는데 역시나 오류가 뜸..

select order_id,
       restaurant_name,
       day_of_the_week,
       delivery_time,
       case when day_of_the_week='weekday' and delivery_time>=25 then 'late'
            when day_of_the_week='weekend' and delivery_time>=30 then 'late'
            else 'on-time' end "지연 여부"
from food_orders

다시 생각해보고 쿼리를 다시 작성하는데 꼭 if가 들어갈 필요 없이 case 문 안에 2개의 조건이 나란히 나열되면 되겠다 싶어서 and를 활용해주자 하고 작성함.

그랬더니 성공✨ 아휴 뿌듯〰️
 

[고찰]

 case 문과 if문을 구별하는 것에 대해서 좀 더 익숙해지고 어떨 때 이런 조건문을 쓴다! 라는 나만의 명확한 기준이 생겨야 할 것 같다.
다음 강의를 들으면 또 이 내용을 금방 잊어버릴까 두렵지만.. 까먹지 않도록 내가 정리한 TIL을 보며 나의 것으로 만들기 위해 반복하고 데이터로 연습해볼 필요가 있다고 생각한다.