개인스터디
엑셀보다 쉽고 빠른 SQL 3주차
2주차 복습
기본 구조
SELECT # 데이터 조회 - 필수 구문 FROM # 어디에서 데이터를 조회할까 - 필수 구문 WHERE # 조건 지정 GROUP BY # 카테고리별 조회 NEW! ORDER BY # 정렬 NEW!
문자포맷 가공
1) 특정 문자를 다른 문자로
replace(바꿀 컬럼, 현재 값, 바꿀 값)- 예시1) 최근에 상점 이름이 바뀌었지만 과거 데이터에는 옛날 이름으로 저장되어있어요
- 예시2) 예전에 ‘문곡리’ 라는 지명이 ‘문가리’ 로 바뀌었어요
2) 원하는 문자만 남기기
substr(조회할 컬럼, 시작 위치, 글자 수)
or
substring(조회할 컬럼, 시작 위치, 글자 수)- 예시) 전체 주소에서 앞부분인 ‘시도’ 부분만 필요해요
- 공백도 글자 수로 친다!
3) 여러 컬럼의 문자 합치기
concat(붙이고 싶은 값1, 붙이고 싶은 값2, 붙이고 싶은 값3, .....)-
예시) 서울시에 있는 음식점은 ‘[서울] 음식점명’ 이라고 수정하고 싶어요
-
붙일 수 있는 문자의 종류
- 컬럼
- 한글
- 영어
- 숫자
- 기타 특수문자
실습1
서울 지역의 음식 타입별 평균 음식 주문금액 구하기 (출력 : ‘서울’, ‘타입’, ‘평균 금액’)
나눠서 생각하기
1) 평균 음식 주문금액 구하기
SELECT AVG(price) -- 평균 주문 금액
FROM food_orders 2) 서울 지역의 음식 타입별
SELECT AVG(price)
FROM food_orders
WHERE addr LIKE '서울%' -- 서울이 들어간 주소
GROUP BY cuisine_type -- 음식 타입별 3) 출력 : ‘서울’, ‘타입’, ‘평균 금액’ / 완성✨
SELECT SUBSTR(addr, 1, 2) "지역", --서울시
cuisine_type "음식 종류", -- 타입
AVG(price) "평균 금액" -- 평균금액
FROM food_orders
WHERE addr like '서울%'
GROUP BY 2 -- 음식 타입별 = 2번째 컬럼 기준으로 묶기 이메일 도메인별 고객 수와 평균 연령 구하기
나눠서 생각하기
1) 고객 수와 평균 연령
SELECT COUNT(customer_id) "고객 수",
AVG(age) "평균 연령"
FROM customers2) 이메일 도메인별로 묶기 / 완성✨
SELECT SUBSTR(email, 10) "도메인",
COUNT(customer_id) "고객 수",
AVG(age) "평균 연령"
FROM customers
GROUP BY 1 -- 첫번째 컬럼으로 묶기여기서 엄청나게 고민을 많이 했는데, 아이디를 8글자로 맞춰놓으셨다고 해서 10번째 자릿수로 불러오면 gmail이 mail로 잘려버리는 현상 발생..!
실제 고객 리스트를 불러오면 글자수가 다 다를텐데 그럼 그땐 어떻게 컬럼 지정을 하는 건지 궁금했는데 이후 3-4에서 방법을 알려주셨다!
SELECT SUBSTR(if(email like '%gmail%', replace(email, 'gmail', '@gmail'), email), 10) "도메인",
count(customer_id) "고객 수",
avg(age) "평균 연령"
from customers
group by 1‘[지역(시도)] 음식점이름 (음식종류)’ 컬럼을 만들고, 총 주문건수 구하기
나눠서 생각하기
1) ‘[지역(시도)] 음식점이름 (음식종류)’ 컬럼 만들기
SELECT CONCAT('[',SUBSTR(addr, 1, 2),']', restaurant_name,'(',cuisine_type,')') "[지역]음식점이름(음식종류)"
FROM food_orders2) 주문건수 추가 / 완성✨
SELECT CONCAT('[',SUBSTR(addr, 1, 2),']', restaurant_name,'(',cuisine_type,')') "[지역]음식점이름(음식종류)",
COUNT(1) "주문 건수"
FROM food_orders
GROUP BY 1 -- 첫번째 컬럼 기준으로 묶기 조건에 따라 포맷 변경
1) 조건에 따라 다른 방법을 적용
if(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)- 예시)음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Korean’ 이 아닌 경우에는 ‘기타’ 라고 지정하고 싶어요
2) 조건을 여러가지 지정
case when 조건1 then 값(수식)1
when 조건2 then 값(수식)2
else 값(수식)3
end- 예시) 음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Japanese’ 혹은 ‘Chienese’ 일 때는 ‘아시아’, 그 외에는 ‘기타’ 라고 지정
실습2
10세 이상, 30세 미만의 고객의 나이와 성별로 그룹 나누기 (이름도 같이 출력)
나눠서 생각하기
1) 10세 이상 30세 미만 고객
SELECT *
FROM customers
WHERE age BETWEEN 10 AND 29 2) 그룹 나누기
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 = 'female' THEN "20대 남성"
WHEN (age BETWEEN 20 and 29) and gender = 'female' THEN "20대 여성"
END "그룹"
FROM customers
WHERE age BETWEEN 10 AND 29 3) 이름, 나이, 성별 함께 출력 / 완성✨
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 = 'female' 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 음식 단가, 음식 종류 별로 음식점 그룹 나누기
- 조건 1
Korean = 한식
Japanese, Chinese, Thai, Vietnamese, Indian = 아시아식
그외 = 기타- 조건 2
가격 = 5000 미만, 5000 이상 15000 미만, 15000 이상
나눠서 생각하기
1) 음식점 이름, 단가, 음식타입 불러오기
SELECT restaurant_name,
price/quantity AS "단가",
cuisine_type
FROM food_orders2) 조건1 적용
SELECT restaurant_name,
price/quantity AS "단가",
cuisine_type,
CASE WHEN cuisine_type = 'Korean' THEN '한식'
WHEN cuisine_type IN ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') THEN '아시아식'
ELSE "기타"
END "음식점 그룹"
FROM food_orders3) 조건 2 적용 / 완성✨
SELECT restaurant_name,
price/quantity AS "단가",
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 "음식점 그룹"
FROM food_orders중복되는 부분이 있기도 하고 코드가 너무 길어서 가독성이 떨어진다고 생각해서, 축약할 수 있는 방법은 없는 건지 챗GPT한테 물어봤더니 이렇게 답해줬다!
SELECT
restaurant_name,
price / quantity AS "단가",
cuisine_type,
CASE
WHEN cuisine_type = 'Korean' THEN
CASE
WHEN price / quantity < 5000 THEN '한식1'
WHEN price / quantity BETWEEN 5000 AND 15000 THEN '한식2'
ELSE '한식3'
END
WHEN cuisine_type IN ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') THEN
CASE
WHEN price / quantity < 5000 THEN '아시아식1'
WHEN price / quantity BETWEEN 5000 AND 15000 THEN '아시아식2'
ELSE '아시아식3'
END
ELSE
CASE
WHEN price / quantity < 5000 THEN '기타1'
WHEN price / quantity BETWEEN 5000 AND 15000 THEN '기타2'
ELSE '기타3'
END
END AS "음식점 그룹"
FROM
food_orders;
조건 1을 적용하면서 ELSE 그룹을 묶은 뒤, 조건 2를 적용하면서는 다시 풀어주게 되어 불편했는데 이렇게 하면 가독성도 좋고 쓰기도 좋은 것 같다.
CASE를 이중으로 사용하는 방법은 아직 배운 적이 없는 것 같은데 추후에 나오려나?~
지역과 배달시간을 기반으로 배달수수료 구하기
- 조건 1
식당 이름, 주문 번호 함께 출력- 조건 2
지역 : 서울, 기타
서울일 때는 수수료 계산 * 1.1,
기타일 때는 곱하는 값 없음- 조건 3
시간 : 25분, 30분
25분 초과하면 음식 가격의 5%
30분 초과하면 음식 가격의 10%
나눠서 생각하기
1) 조건 1 적용
SELECT restaurant_name,
order_id,
price,
addr,
delivery_time
FROM food_orders2) 조건 3부터 적용 / 조건 2의 '수수료'가 아직 없기 때문에 배달 시간에 따른 수수료부터 만들어줘야한다.
SELECT restaurant_name,
order_id,
price,
addr,
delivery_time,
CASE WHEN delivery_time BETWEEN 26 AND 30 THEN price * 0.05
WHEN delivery_time > 30 THEN price * 0.1
ELSE 0
END "수수료"
FROM food_orders3) 조건 2 적용 / 완성✨
SELECT restaurant_name,
order_id,
price,
addr,
delivery_time,
CASE WHEN delivery_time BETWEEN 26 AND 30 THEN price * 0.05 * (IF (SUBSTR(addr,1,2) = '서울', 1.1, 1)) -- 서울이면 1.1배 반환, 아니면 1배 반환
WHEN delivery_time > 30 THEN price * 0.1 * (IF (SUBSTR(addr,1,2) = '서울', 1.1, 1))
ELSE 0
END "수수료"
FROM food_orders이것도 조금 복잡한 것 같아서 간소화할 수 있는 코드를 챗gpt한테 작성해달라고 했다.
SELECT restaurant_name,
order_id,
price,
addr,
delivery_time,
price *
CASE
WHEN delivery_time > 30 THEN 0.1
WHEN delivery_time > 25 THEN 0.05 -- 30분 초과가 이미 걸러졌으므로 between 절 사용할 필요가x
ELSE 0
END *
IF(SUBSTR(addr,1,2) = '서울', 1.1, 1) AS "수수료"
FROM food_orders코드를 입력하세요짱GPT...... 훨씬 간소화되어 보인다😂
주문 시기와 음식 수를 기반으로 배달할증료 구하기
- 조건 1
주문 시기
- 평일 기본료 = 3000
- 주말 기본료 = 3500
- 조건 2
음식 수
- 3개 이하이면 할증 없음
- 3개 초과이면 기본료 * 1.2
나눠서 생각하기
1) 조건 1 적용
SELECT order_id,
day_of_the_week AS "평일/주말",
quantity AS "수량",
CASE WHEN day_of_the_week = 'Weekday' THEN 3000
ELSE 3500
END "배달할증료"
FROM food_orders2) 조건 2 적용 / 완성 ✨
SELECT order_id,
day_of_the_week AS "평일/주말",
quantity AS "음식수",
CASE WHEN day_of_the_week = 'Weekday' THEN 3000 * IF(quantity > 3 , 1.2, 1)
ELSE 3500 * IF(quantity > 3 , 1.2, 1)
END "배달할증료"
FROM food_orders답이 잘 나오긴 했지만, 해설에서는 IF문을 사용해서 더 간단하게 표현했다.
select order_id,
price,
quantity,
day_of_the_week,
if(day_of_the_week='Weekday', 3000, 3500)*(if(quantity<=3, 1, 1.2)) "할증료"
from food_orders챗gpt한테도 다시 물어보니 해설에서 더 간소화하기는 어려워보인다고 한다. 너무 CASE 절만 사용하려고 하지 말고 IF절로도 표현할 수 있다는 것 잊지 말기🤓
숙제
다음의 조건으로 배달시간이 늦었는지 판단하는 값을 만들어주세요.
주중 : 25분 이상
주말 : 30분 이상
나눠서 생각하기
1) 주중/주말 구분
SELECT order_id,
restaurant_name,
day_of_the_week,
delivery_time,
CASE WHEN day_of_the_week = 'Weekend' THEN '주말'
WHEN day_of_the_week = 'Weekday' THEN '주중'
END AS "주중/주말"
FROM food_orders2) 배달시간 조건 넣기 / 완성✨
SELECT order_id,
restaurant_name,
day_of_the_week,
delivery_time,
CASE WHEN day_of_the_week = 'Weekend' AND delivery_time >= 30 THEN 'Late'
WHEN day_of_the_week = 'Weekday' AND delivery_time >= 25 THEN 'Late'
ELSE 'On-time'
END AS "지연여부"
FROM food_orders데일리퀘스트
SQL 문법을 연습해요 6 - 팀 프로젝트 열심히 했으니 다시 놀아볼까요?!
일기
복습 + 강의 듣기 하려니까 상당히 힘들다..! 다행히 오늘은 4주차 강의 듣기 완료
서브쿼리랑 JOIN 함수 나오니까 이해하는 데 시간이 좀 걸려서 실습 한 문제 푸는데 30분씩 걸리고 그렇다🥲 쉽지않네 쉽지않아
특히 코드를 가독성 있게 작성하는 버릇을 들여야하는데 아직까지는 그게 잘 안되는 것 같아서 아쉽다. 한 가지 방법으로만 생각하지 말고, 다른 함수를 사용할 수는 없는지 열어두고 공부하는 것도 잊지 말자!
본캠프 들어가기 전에 체력도 쌓고 어쩌고저쩌고 계획은 많았는데 그나마 잘 유지하고 있는 습관은 영양제 챙겨먹기💊 요일별로 나뉘어있는 통을 사서 오메가3 + 유산균 + 종합비타민 + 마그네슘 이렇게 먹고 있다. 잠 잘자고 내일도 화이팅해야지❇️
