SQL

문자 데이터 형태 가공해서 조회

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

REPLACE(바꿀 컬럼, 현재 값, 바꿀 값)

원하는 문자만 뽑아오기 SUBSTR

SUBSTR(조회 할 컬럼, 시작 위치, 글자 수)

여러 컬럼의 문자를 합치기 CONCAT

CONCAT(붙이고 싶은 값1, 붙이고 싶은 값2, 붙이고 싶은 값3, .....)

• 붙일 수 있는 문자의 종류
  • 컬럼, 한글, 영어, 숫자, 기타 특수문자

실습 문제 - 문자 데이터를 바꾸고, GROUP BY 사용하기

1) 서울 지역의 음식 타입별 평균 음식 주문금액 구하기 (출력 : ‘서울’, ‘타입’, ‘평균 금액’)

SELECT 
	# 서울 까지만 보이게 가공
    SUBSTR(addr, 1, 2) '시도',
    cuisine_type '타입',
    AVG(price) '평균 금액' 
FROM food_orders
WHERE addr LIKE "서울%"
# 서울시, 타입별. 두 가지 카테고리로 묶기
#GROUP BY에 컬럼명을 그대로 쓰지 않아도 된다
#SELECT 에 작성된 순서대로 숫자로 인식한다
GROUP BY 1, 2;

혼자 작성한 쿼리는 GROUP BY cuisine_type; 인데
답지랑 답이 똑같이 나와서 혼란스러웠다. GPT를 통해 이해 완료.

집계 함수를 제외하면 SELECT 에 포함된 모든 컬럼이 GROUP BY로 그룹화되어야 한다.
그러나 MySQL은 SELECT 에 사용된 컬럼을 암묵적으로 GROUP BY에 포함한다.

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

SELECT 
	# 의도적으로 앞 글자를 8자로 통일해서 가능함
	SUBSTR(email, 10) '도메인',
	COUNT(customer_id) '고객 수',
	AVG(age) '평균 연령'
FROM customers
GROUP BY 1;

내용은 쉬운데 문제가 너무 억지다.
SUBSTR를 써야해서 일부러 이렇게 만든 거겠지만,
차라리 @랑 .를 기준으로 문자를 분할하는 방법이 무조건 있을텐데 그게 궁금했다.
아마도 뒤에 나오겠지..!

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

SELECT 
	CONCAT(
		'[',
		SUBSTR(addr,1,2),
		'] ',
		restaurant_name,
		' (',
		cuisine_type,
		')') 
		AS "[지역] 음식점이름 (음식종류)",
    # 전체를 전부 세라, COUNT(1) 도 같은 뜻!
	COUNT(*) "주문건수"
FROM food_orders
GROUP BY 1;

점점 문제가 복잡해질수록, 더 천천히 차근차근 풀어야겠다.

조건에 따라 포멧을 다르게 변경, 연산

조건 지정 IF

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

조건을 여러가지 지정 CASE

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

조건을 사용할 수 있는 경우

• 새로운 카테고리 만들기
• 조건에 따른 연산식을 적용하기
• 다른 문법 안에서 적용하기

실습 문제

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

SELECT 
	name "이름",
	age '나이',
	gender '성별',
	CASE
		WHEN age BETWEEN 10 AND 19 AND gender = "female" THEN "10대 여성"
		WHEN age BETWEEN 10 AND 19 AND gender = "male" THEN "10대 남성"
		WHEN age BETWEEN 20 AND 29 AND gender = "female" THEN "20대 여성"
		WHEN age BETWEEN 20 AND 29 AND gender = "male" THEN "20대 남성"
	END 
		AS "고객 그룹"
FROM customers
WHERE age BETWEEN 10 AND 29;

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

SELECT 
    cuisine_type AS '음식 종류',
    price/quantity AS '음식 단가',
    CASE 
        WHEN cuisine_type = 'Korean' AND price/quantity < 5000 THEN '저가 한식'
        WHEN cuisine_type = 'Korean' AND price/quantity BETWEEN 5000 AND 14999 THEN '중가 한식'
        WHEN cuisine_type = 'Korean' AND price/quantity > 15000 THEN '고가 한식'
        WHEN cuisine_type IN ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') AND price/quantity < 5000 THEN '저가 아시아식'
        WHEN cuisine_type IN ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') AND price/quantity BETWEEN 5000 AND 14999 THEN '중가 아시아식'
        WHEN cuisine_type IN ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') AND price/quantity > 15000 THEN '고가 아시아식'
        WHEN price/quantity < 5000 THEN '기타 저가식'
        WHEN price/quantity BETWEEN 5000 AND 14999 THEN '기타 중가식'
        WHEN price/quantity > 15000 THEN '기타 고가식'
    END AS '음식 그룹'
FROM food_orders;

길지만 어렵진 않고 복사 잘 하면 된다.
타이핑 하기 너무 귀찮아서 마지막 조건은 cuisine_type 빼고 했는데, 위에 조건 처리 이후에 남은 것들로 처리하기 때문에 문제없다고 한다.

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

# 나의 풀이
SELECT 
    order_id '주문 번호',
    restaurant_name '식당 이름',
    CASE 
        WHEN addr LIKE '%서울%' AND delivery_time BETWEEN 25 AND 29 THEN price*0.05*1.1
        WHEN addr LIKE '%서울%' AND delivery_time > 30 THEN price*0.1*1.1
        WHEN addr NOT LIKE '%서울%' AND delivery_time BETWEEN 25 AND 29 THEN price*0.05
        WHEN addr NOT LIKE '%서울%' AND delivery_time > 30 THEN price*0.1
        ELSE 0
    END AS '배달수수료'
FROM food_orders;

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

나는 CASE로 WHEN을 네개 썼는데
답지는 CASE 안에 IF를 또 넣어서 반으로 줄였다
지금은 네줄이니까 상관없지만
나중에 줄이 길어지면 답지같은 방식이 훨씬 효율적일 것 같다.

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

SELECT 
order_id '주문 번호',
day_of_the_week '주문 시기',
quantity '음식 수',
CASE 
	WHEN day_of_the_week = 'Weekday' THEN IF (quantity <= 3, 3000, 3000*1.2)
	WHEN day_of_the_week = 'Weekend' THEN IF (quantity <= 3, 3500, 3500*1.2)
END AS '배달할증료'
FROM food_orders;

그래서 위에거 보고 따라해봤다.
근데 답지는

if(day_of_the_week='Weekday', 3000, 3500)
*(if(quantity<=3, 1, 1.2)) "할증료"

이렇게 쓰더라
간단한 코드라도 응용법이 참 다양한 것 같다

Data Type 오류 해결

데이터 타입 변경 CAST
문자, 숫자를 혼합하여 함수에 사용 할 때에는 데이터 타입을 변경해야 함

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

느낀점

실습하면서 많이 틀렸는데 아주 사소한 실수도 많았다.
쉼표 띄어쓰기 줄바꿈 괄호 잘 신경쓰자.
그리고 문제 풀면서 기억이 나지 않는 부분이 있었는데,
구글링을 하지 않고 내 벨로그 보면서 다시 공부했다.
자세한 공부 기록의 필요성을 느꼈다. 꾸준히 쌓아가고 싶다.
아직 완전 기초지만 점점 진도가 나가니까 재밌다.
내일은 3주차 숙제를 풀고 4주차 바로 나가야겠다.
이번주안에 인강 끝낼 수 있을 것 같다.