관련 설명
https://velog.io/@dyllis_lin/MySQL-%EA%B8%B0%EB%A1%9D3
대체
- 식당 명의 ‘Blue Ribbon’ 을 ‘Pink Ribbon’ 으로 바꾸기
- 특정 문자를 다른 문자로 대체 : REPLACE(바꿀 컬럼, 현재 값, 바꿀 값)
- 문자열 부분 일치 조회 : LIKE(조회할 문자열/패턴)
'%'나 '_' 사용
SELECT restaurant_name AS "원래 이름"
, REPLACE(restaurant_name, 'Blue', 'Pink') AS "바뀐 이름"
FROM food_orders fo
WHERE restaurant_name LIKE '%Blue Ribbon%';
- LIKE의 사용
% -> 모든 문자 대체
_ -> 언더스코어 개수 만큼 모든 문자 대체(한 글자)
- %Blue Ribbon%
-> Blue Ribbon이 포함된 ROW를 찾을 때
-> Blue Ribbon 앞뒤에 무슨 글자가 오든지 간에 'Blue Ribbon'이 포함된 ROW를 출력하고 싶을 때
- Blue Ribbon%
-> 'Blue Ribbon'으로 시작하는 문자가 있는 ROW를 찾을 때
- %Blue Ribbon
-> 'Blue Ribbon'으로 끝나는 문자가 있는 ROW를 찾을 때
- _Blue Ribbon%
-> 한 문자 뒤에 'Blue Ribbon'이 있는 Row를 찾을 때(Blue Ribbon 뒤에는 어떤 글자가 오든 상관없음)
- _Blue Ribbon__
-> 한 문자 뒤에 'Blue Ribbon'이 오고, 그 'Blue Ribbon'뒤에는 2개의 문자가 오는 Row를 찾을 때
결론 : 조합하기 나름
- 주소의 '문곡리'를 '문가리'로 바꾸기
SELECT addr AS "원래 주소"
, REPLACE(addr, '문곡리', '문가리') AS "바뀐 주소"
FROM food_orders fo
WHERE addr LIKE '%문곡리%'; 
특정 문자만 남기기
- 서울 음식점들의 주소를 전체가 아닌 '서울'만 나오도록 수정하기
- 특정 문자만 조회 : SUBSTRING(조회 할 컬럼, 시작 위치, 시작 위치로부터의 글자 수) <SUBSTR과 동일>
SELECT addr AS "원래 주소"
, SUBSTR(addr, 1, 2) AS "바뀐 주소"
FROM food_orders fo
WHERE addr LIKE '%서울특별시%'; 
합치기
- CONCAT(붙이고 싶은 값1, 붙이고 싶은 값2, 붙이고 싶은 값3, ...)
- 서울시에 있는 음식점을 '[서울] 음식점명' 이라고 수정하기
로직
1. 필요 컬럼 : 식당 이름, 주소
2. 조건 : 서울 지역 -> WHERE addr LIKE '%서울%';
3. 필요 수식 : '[서울] 음식점명' 형태로 만들기
-> CONCAT('[', SUBSTR(addr, 1, 2), ']' ' ', restaurant_name)
SELECT restaurant_name AS "원래 이름"
, addr AS "원래 주소"
, CONCAT('[', SUBSTR(addr, 1, 2), ']' ' ', restaurant_name) AS "바뀐이름"
FROM food_orders fo
WHERE addr LIKE '%서울%'; 
문자 바꾸기 + GROUP BY
- 서울 지역의 음식 타입 별 평균 음식 주문금액 구하기(출력 : '서울', '타입', '평균금액')
로직
1. 필요 컬럼 : 주문 금액, 음식 타입, 주소
2. 조건 : 서울 지역
3. 필요 수식 : AVG, SUBSTR
SELECT SUBSTR(addr, 1, 2) AS "지역"
, cuisine_type AS "음식 종류"
, AVG(price) AS "평균 금액"
FROM food_orders fo
WHERE addr LIKE '%서울%'
GROUP BY SUBSTR(addr, 1, 2), cuisine_type; 
- 이메일 도메인별 고객 수와 평균 연령 구하기
로직
1. 필요 컬럼 : 이메일 주소, 고객 번호, 나이
2. 조건 : 없음
3. 필요 수식 : SUBSTR, AVG, COUNT
SELECT SUBSTR(email, 10) AS "이메일 도메인"
, COUNT(customer_id) AS "고객 수"
, AVG(age) AS "평균 연령"
FROM customers c
GROUP BY SUBSTR(email, 10); 
- 도메인 별로 묶어야 하기 때문에 @ 이후의 도메인 주소(xxx.com이나 xxx.net 등)만 뽑아야 한다.
- 'SUBSTR(조회 할 컬럼, 시작 위치, 시작 위치로부터의 글자 수)' 에서 시작 위치로부터의 글자 수를 생략하면, 시작 위치로부터 해당 문자열의 끝까지 반환한다.
- '[지역] 음식점 이름 (음식 종류)' 컬럼을 만들고 총 주문 건수 구하기
로직
1. 필요 컬럼 : 주소, 음식점 이름, 음식 종류, 주문 번호
2. 조건 : 없음
3. 필요 수식 : SUBSTR, CONCAT, COUNT
SELECT CONCAT('[', SUBSTR(addr, 1, 2), '] ', restaurant_name, ' (', cuisine_type, ')')
, COUNT(1) AS "주문 건수"
FROM food_orders fo
GROUP BY 1; 
조건에 따른 연산
- 음식 타입을 'Korea' 일 때는 한식, 'Korean'이 아닌 경우에는 '기타'라고 지정하기
- IF(조건, 조건을 충족할 경우, 조건을 만족하지 못할 경우)
SELECT restaurant_name AS "식당 이름"
, cuisine_type AS "원래 음식 타입"
, IF(cuisine_type = 'Korean', '한식', '기타') AS "음식 타입"
FROM food_orders fo;
- '문곡리'가 평택에만 해당될 때, 평택 '문곡리'만 '문가리'로 수정하기
SELECT addr AS "원래 주소"
, IF(addr LIKE '%평택군%', REPLACE(addr, '문곡리', '문가리'), addr) AS "바뀐 주소"
FROM food_orders fo
WHERE addr LIKE '%문곡리%'; 
- '문곡리'가 평택에만 해당될 때, 평택 '문곡리'만 '문가리'로 수정하기
-> IF(addr LIKE '%평택군%', REPLACE(addr, '문곡리', '문가리'), addr) AS "바뀐 주소"
- 위쪽의 <이메일 도메인별 고객 수와 평균 연령 구하기>에서 잘못된 이메일 주소 (gmail)만 수정하기
SELECT SUBSTRING(IF(email LIKE '%gmail%',
REPLACE(email, 'gmail', '@gmail'), email), 10) AS "이메일 도메인"
, COUNT(customer_id) AS "고객 수"
, AVG(age) AS "평균 연령"
FROM customers c
GROUP BY 1; - 음식 타입을 'Korean ' 일 때는 '한식', 'Japanese' 혹은 'Chinese' 일 때는 '아시안', 그 외에는 '기타' 라고 지정하기
- 여러가지 조건 지정 : CASE WHEN 조건1 THEN 값1(수식1)
WHEN 조건2 THEN 값2(수식2)
ELSE 값3(수식3)
END
SELECT restaurant_name AS "식당 이름"
, cuisine_type AS "원래 음식 타입"
, CASE WHEN cuisine_type = 'Korean' THEN '한식'
WHEN cuisine_type IN ('Japanese', 'Chinese') THEN '아시안'
ELSE '기타'
END
FROM food_orders fo; IN 대신 OR도 가능핟다.
-> WHEN cuisine_type = 'Japanese' OR cuisine_type = 'Chinese' THEN '아시안'
- 음식 단가를 주문 수량이 1일 때는 음식 가격, 주문 수량이 2개 이상일 때는 음식가격/주문수량으로 지정하기
SELECT order_id
, price AS "가격"
, quantity AS "수량"
, CASE WHEN quantity = 1 THEN price
WHEN quantity >= 2 THEN price/quantity
END "음식 단가"
FROM food_orders fo;
- 주소의 시도를 '경기도' 일 때는 '경기도, '특별시' 혹은' 광역시'일 때는 붙여서, 아닐 때는 앞의 두 글자만 사용하기
select restaurant_name,
addr,
case when addr like '%경기도%' then '경기도'
when addr like '%특별시%' or addr like '%광역시%' then substring(addr, 1, 5)
else substring(addr, 1, 2) end "변경된 주소"
from food_orders; 이 문제 풀 때 진짜 애먹었다.
'특별시' 혹은' 광역시'일 때는 붙여서이 부분에서 생각이 계속 꼬였다. 처음에는 OR은 생각도 못하고 IN을 쓰려 했는데 뭔가 더 복잡해지는 기분이었다.
그리고 '붙인다' 라는 말에 갇혀서 생각의 회로가 계속 진짜 요상한 방향으로 튀어서
-> + 연산자로 계속 문자열 연결 못하나 -> 만약 되도 어떻게 연결할건데? 무한반복..........
게다가, (addr, 1, 5)는 생각지도 못했다.
돌아보고 나니까 당연히(?) IN으로도, OR로도 쓸 수 있었다. 정답 보고 헉 했다. 정말...........
어쨌거나 IN이나 OR 둘다 사용 가능하다.
- IN의 경우
-> WHEN addr IN ('%특별시%', '%광역시%') THEN SUBSTR(addr, 1, 5)- OR의 경우
-> WHEN addr LIKE '%특별시%' OR addr LIKE '%광역시%'%') THEN SUBSTR(addr, 1, 5)
그리고 내가 쓴 코드.........
-> WHEN addr LIKE IN('%특별시%', '%광역시%') THEN CONCAT(SUBSTR(addr, 1, 2), '특별시')
아까 언급했듯이 '붙여서'라는 단어에 매몰돼서 <붙인다 -> 연결 -> CONCAT 사용 해야만한다!> 라는 사고흐름으로 이어졌다.
근데 가만 생각해 보니까 CONCAT쓰면 쿼리가 더 지저분해 질 것 같다.(아마도...?) CONCAT을 사용해서 쿼리를 짤 수 있을것 같기는 한데, 딱 떠오르지는 않는다. 이미 SUBSTR 써서 짠 쿼리를 봐서 그런가 더욱더 생각 안남.
아무튼 진짜 대 혼 종. 그냥 CONCAT이든 SUBSTR이든 LIKE, IN, OR 모두 제대로 된 개념이 안 잡혀있던 것이었다. 그래도 아직 3일차니까 문제 많이 풀어서 체화 시키는 수 밖에 없을 듯....
- 10세 이상, 30세 미만의 고객의 나이와 성별로 그룹 나누기(이름도 같이 출력)
로직
1. 필요 컬럼 : 이름, 나이, 성별
2. 조건 : 나이 10세 이상, 30세 미만 -> WHERE age BETWEEN 10 AND 29
3. 필요 수식 : 조건문 -> CASE WHEN ... END
SELECT name AS "이름"
, gender AS "성별"
, age
, 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 AS "그룹"
FROM customers c
WHERE age BETWEEN 10 AND 29;
- 음식 단가, 음식 종류 별로 음식점 그룹 나누기
(Korean = 한식, Japanese/Thai/Vietnamese/Indian = 아시아식, 그 외 = 기타)
(가격 = 5000, 15000, 그 이상)
로직
1. 필요 컬럼 : 주문 금액, 주문 수량, 음식 종류
2. 조건 : 없음
3. 필요 수식 : 조건문 -> CASE WHEN ... END
SELECT restaurant_name AS "식당 이름"
, price/quantity "단가"
, cuisine_type "식당 타입"
, 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 AS "식당 그룹"
FROM food_orders; 
- 지역과 배달시간을 기반으로 배달수수료 구하기
(식당 이름, 주문 번호 함께 출력)
(지역 : 서울, 기타 - 서울일 때는 수수료 계산 * 1.1, 기타일 때는 곱하는 값 없음, 시간 : 25분, 30분 - 25분 초과하면 음식 가격의 5%, 30분 초과하면 음식 가격의 10%)
로직
1. 필요 컬럼 : 식당 이름, 주문 번호, 지역 , 배달 시간, 주문 금액
2. 조건 : 없음
3. 필요 수식 : 조건문 -> CASE, IF
SELECT
restaurant_name AS "식당 이름"
, order_id AS "주문 번호"
, delivery_time AS "배달 시간"
, price AS "가격"
, addr AS "주소"
CASE
WHEN addr LIKE '%서울%' THEN
CASE
WHEN delivery_time > 25 AND delivery_time <= 30 THEN (price * 1.1) + (price * 0.05)
WHEN delivery_time > 30 THEN (price * 1.1) + (price * 0.10)
ELSE price * 1.1
END
ELSE
CASE
WHEN delivery_time > 25 AND delivery_time <= 30 THEN price + (price * 0.05)
WHEN delivery_time > 30 THEN price + (price * 0.10)
ELSE price
END
END AS "수수료 포함 총 금액"
FROM
food_orders;
구조
CASE1
WHEN (주소가 서울 일 때,)
CASE 1-1
WHEN 배달시간이 25분 초과인 경우
WHEN 배달시간이 30분 초과인 경우
ELSE 그 외의 경우
END
ELSE (주소가 서울이 아닐 때,)
CASE 1-2
WHEN 배달시간이 25분 초과인 경우
WHEN 배달시간이 30분 초과인 경우
ELSE 그 외의 경우
END
END
중첩 CASE문 처음 써봄.... 뭔가 더 깔끔하고 간결하게 표현 할 수 있는 방법은 없나? 싶었는데 나한테는 이게 최선이었다. 사실 이게 맞는지도 모르겠음. 틀렸을 가능성 매우 농후.
- 주문 시기와 음식 수를 기반으로 배달할증료 구하기
주문 시기 : 평일 기본료 = 3000 / 주말 기본료 = 3500
음식 수 : 3개 이하이면 할증 없음 / 3개 초과이면 기본료 * 1.2
로직
1. 필요 컬럼 : 주문 수량, 주문 시기
2. 조건 : 없음
3. 필요 수식 : 조건문 -> CASE, IF
SELECT order_id AS "주문 번호"
, price AS "가격"
, quantity AS "수량"
, day_of_the_week AS "구분"
, IF(day_of_the_week = 'Weekday', 3000, 3500)
* IF(quantity <= 3, 1, 1.2) AS "할증료"
FROM food_orders fo;- 배달시간이 늦었는지 판단하는 값을 만들기
주중 : 25분 이상, 주말 : 30분 이상 -> 배달 지연
SELECT order_id AS "주문 번호"
, restaurant_name AS "식당 이름"
, delivery_time AS "배달 시간"
, day_of_the_week AS "구분"
, 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 AS "지연 여부"
FROM food_orders fo;오늘의 TMI
그래도 아직까지는 SQL에 벽 안느껴서 다행이랄까... 막힐 때는 많아도 그게 엄청 힘들다거나 하지는 않아서 다행. 그냥 매번 다른 사람들 풀이 보고 감탄만 하는 중...
