Day3. REPLACE,SUBSRTING,CONCAT / GROUP BY / IF,CASE / TIL. 20240417

SQL 공부 3일차.

 

 

To do list.

-SQL 코드카타 문제풀이

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

-문자데이터 바꾸고GROUP BY 사용하기

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

-SQL로 간단한 유저 분할하기

-조건문으로 서로 다른 식을 적용한 수수료 구해보기

-SQL에 문제가 없는데 나는 오류들

 

---

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

 

replace 문

 

1) 식당 명의 ' Blue Bibbon'을 'Pink Bibbon' 으로 바꾸기

 

SELECT restaurant_name "원래 상점명",

               replace(restaurant_name, 'Blue', 'Pink') "바뀐 상점명"

from food_orders

WHERE restaurant_name LIKE '%Blue Bibbon%'

 

2)주소의'문곡리'를 '문가리'로 바꾸기

 

SELECT addr,

              REPLACE (addr,'문곡리','문가리') "바뀐주소"

FROM food_orders

WHERE addr LIKE '%문곡리%'

 

substr /substring 문 

 

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

 

SELECT addr "원래주소",

               SUBSTR(addr,1,2) "시도"

from food_orders

WHERE addr LIKE '%서울특별시%'

 

 

concat 문

1) 서울시에 있는 음식점은 '[서울] 음식점명'이라고 수정

 

SELECT restaurant_name "원래 이름",

               addr "원래 주소",

              CONCAT(restaurant_name, '-', cuisine_type) "음식타입별 음식점",

              CONCAT('[', SUBSTRING(addr,1,2), ']', restaurant_name) "바뀐 이름"

from food_orders

WHERE addr LIKE '%서울%'

 

---

문자데이터 바꾸고GROUP BY 사용하기

 

실습

 

1)서울 지역의 음식 타입별 평균 음식 주문금액 구하기

 

SELECT SUBSTR(addr,1,2) "지역",

               cuisine_type ,

               AVG(price) "평균 금액"

FROM food_orders

WHERE addr like '서울%'

GROUP by 1,2  ( 첫번째 행과 두번째행 집계한다는 뜻에서 1,2)

 

2)이메일 도메인 별로 고객 수와 평균 연령 구하기

 

SELECT SUBSTR(email,10) "도메인",

               COUNT(1) "고객수",

               AVG(age) "평균연령"

FROM customers

group by 1

 

 

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

 

SELECT CONCAT('[',SUBSTR(addr,1,2),']',restaurant_name, '(',cuisine_type,')') "음식점" ,

               count(1) "총 주문건수"

FROM food_orders

group by 1

 

---

 

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

 

if문

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

 

1) 음식 타입을 'Korean' 일 때는 '한식', 'Korean'이 아닌 경우에는 '기타' 라고 지정

 

SELECT restaurant_name,

               cuisine_type "원래 음식 타입",

IF (cuisine_type='Korean', '한식', '기타') "음식 타입"

from food_orders

 

2) '문곡리' 가 평택에만 해당될 때, 평택 '문곡리'만 '문가리' 로 수정

 

SELECT addr "원래 주소",

               IF (addr like '%평택군%',replace(addr,'문곡리','문가리'),addr) "바뀐 주소"

from food_orders

WHERE addr LIKE '%문곡리%'

 

3) 잘못된 이메일 주소(gmail)만 수정을 해서 사용

 

SELECT SUBSTR(if(email like '%gmail%', REPLACE(email,'gmail','@gmail'), email),10) "이메일도메인",

              count(customer_id) "고객 수" ,

              AVG(age) "평균 연령"

from customers

group by 1

 

 

case문

case로 시작해서 end로 끝난다 중간에 조건들은 when으로 연결

 

1) 음식 타입을 'Korean'일때는 '한식', 'Japanese'혹은 'Chinese'일 때는 '아시아', 그외에는 '기타'라고 지정

 

SELECT CASE when cuisine_type ='Korean' THEN '한식'

               WHEN cuisine_type in ('Japanese', 'Chinese') then '아시아'

               ELSE '기타' end "음식타입" ,

from food_orders

 

---

 

SQL로 간단한 유저 분할하기

 

실습

 

1) 10세 이상, 30세 미만의 고객의 나이와 성별로 그룹 나누기 (이름도 같이 출력)

 

SELECT case when age between 10 and 29 and gender='male' then '10대 남성'

                        when age between 10 and 29 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 "고객분류",

                                name,

                                age,

                                gender

FROM customers

WHERE age BETWEEN 10 and 29

 

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

 

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

 

---

 

조건문으로 서로 다른 식을 적용한 수수료 구해보기

 

1) 지역과 배달시간을 기반으로 배달수수료 구하기 (식당 이름, 주문 번호 함께 출력)

 

SELECT CASE when delivery_time >30 then price* 0.1*if(addr like '%서울',1.1, 1)

                         when delivery_time >25 then price* 0.05*if(addr like '%서울',1.1, 1)

                         else 0 end "수수료",

                   restaurant_name ,

                   order_id ,

                   price,

                   delivery_time ,

                   addr

FROM food_orders

 

2)주문시기와 음식 수를 기반으로 배달 할증료 구하기

 

SELECT CASE when day_of_the_week='weekday' then 3000 *if(quantity >3,1.2, 1)

                          when day_of_the_week='weekend' then 3500 *if(quantity >3,1.2, 1)

                          end "배달할증료",

                          restaurant_name ,

                          order_id ,

                         day_of_the_week ,

                          quantity

FROM food_orders

---

Check point.

강의 3-3 실습 매우어렵다.. 예*복습 필요

having 절은 group by 절과 묶어서 사용

having 절 : 그룹으로 지정된 자료들의 속성의 값을 집계 - WHERE절에는 사용이 불가하다!

limit n,m: m번째 row부터 n개까지 보여줘

limit n : n개의 row까지만 보여줘if문 안에는 여러가지 함수를 결합하여 사용가능

 

cast ~ as decimal => 문자를 숫자로 변경cast ~ as char => 숫자를 문자로 변경

 

null 처리 -> IFNULL(A,B)

 

 

---

회고

 

3일차 공부량과 집중력이 조금씩 올라가고 있는 듯하다.. 하지만 코드카타를 풀때면 어김없이 백지비슷한 상태가 될때가 많다. 충분히 생각하고 내가 생각해내서 풀어보려고 노력하자.