#SQL
- SQL 기초 구문 복습 및 데이터 조회 조건과 매칭해보기
- SQL 문의 기본 구조
SELECT FROM WHERE GROUP BY ORDER BY - 조회 조건 매칭
- 주문 테이블에서 → FROM
- 주문 수량이 1건인 주문건의 → WHERE
- 음식 가격의 평균을 음식 종류별로 조회하여 → AVG, GROUP BY, SELECT
- 음식 가격이 높은 순서대로 정렬하기 → ORDER BY
- SQL 문의 기본 구조
- 3주차
1. 문자 포맷 가공하기
- 특정 문자를 다른 문자로 바꾸기
REPLACE(바꿀 칼럼, 현재 값, 바꿀 값)
# 주소의 '문곡리'를 '문가리'로 바꾸기
SELECT addr "원래 주소",
REPLACE(addr, '문곡리', '문가리') "바뀐 주소"
FROM food_orders
WHERE addr LIKE '%문곡리%'
- 원하는 문자만 남기기
SUBSTRING 또는 SUBSTR (조회할 칼럼, 시작 위치, 글자 수)
# 서울 음식점들의 주소를 전체가 아닌 '시도'만 나오도록 수정
SELECT addr "원래 주소",
SUBSTR(addr, 1, 2) "시도"
FROM food_orders
WHERE addr LIKE '%서울특별시%'
- 여러 칼럼의 문자를 합치기
CONCAT(붙이고 싶은 값1, 붙이고 싶은 값2, 붙이고 싶은 값3, ...)
+) 붙일 수 있는 문자의 종류: 컬럼, 한글, 영어, 숫자, 기타 특수문자
# 서울시에 있는 음식점은 '[서울]음식점 이름' 이라고 수정
SELECT restaurant_name "원래 이름",
addr "원래 주소",
CONCAT('[', substring(addr, 1, 2), '] ', restaurant_name) "바뀐 이름"
FROM food_orders
WHERE addr LIKE '%서울%'
2. 문자 데이터를 바꾸고, GROUP BY 사용 (실습)
가. 이메일 도메인별 고객 수와 평균 연령 구하기
가-1. QUERY 적기 전 흐름 정리하기
#어떤 테이블에서 데이터를 뽑을 것인가 > 고객테이블
#어떤 컬럼을 이용할 것인가 > 이메일 주소, 나이, 고객번호
#어떤 조건을 지정해야 하는가 > 없음
#어떤 함수(수식)을 이용해야 하는가 > 특정 문자 뽑아내기, 평균 구하기, 갯수 구하기
가-2. 구문으로 만들기
#어떤 테이블에서 데이터를 뽑을 것인가 > FROM customers
#어떤 컬럼을 이용할 것인가 > email, age, customer_id
#어떤 조건을 지정해야 하는가 > 없음
#어떤 함수(수식)을 이용해야 하는가 > SUBSTR(email, 10), AVG(age), COUNT(1)
가-3. 전체 구조로 합치기
SELECT SUBSTR(email, 10) "이메일 도메인",
COUNT(1) "고객 수",
AVG(age) "평균 연령"
FROM customers
GROUP BY 1
나. '[지역(시도)] 음식점 이름 (음식종류)' 컬럼을 만들고, 총 주문건수 구하기
나-1. QUERY 적기 전 흐름 정리하기
#어떤 테이블에서 데이터를 뽑을 것인가 > 주문테이블
#어떤 컬럼을 이용할 것인가 > 주소, 음식점 이름, 음식 종류, 주문 번호
#어떤 조건을 지정해야 하는가 > 없음
#어떤 함수(수식)을 이용해야 하는가 > 특정 문자 뽑아내기, 문자 합치기, 갯수 구하기
나-2. 구문으로 만들기
#어떤 테이블에서 데이터를 뽑을 것인가 > FROM food_orders
#어떤 컬럼을 이용할 것인가 > addr, restaurant_name, cuisine_type, order_id
#어떤 조건을 지정해야 하는가 > 없음
#어떤 함수(수식)을 이용해야 하는가 > SUBSTR(addr, 1, 2), CONCAT('[',추출문자,']', restaurant_name, '(', cuisine_type,')'), COUNT(1)
나-3. 전체 구조로 합치기
SELECT CONCAT('[', SUBSTR(addr, 1, 2), '] ', restaurant_name, ' (', cuisine_type, ')') "바뀐이름",
COUNT(1) "주문건수"
FROM food_orders
GROUP BY 1
3. 조건에 따라 포맷 변경
- 조건을 지정하는 기초 문법
IF(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)
# 음식 타입을 'Korean'일 때는 '한식', 아닌 경우에는 '기타'라고 지정하기
SELECT restaurant_name,
cuisine_type "원래 음식 타입",
IF(cuisine_type='Korean', '한식', '기타') "음식 타입"
FROM food_orders
- 여러 조건 지정하기
CASE WHEN 조건 THEN 값(수식) ~~ ELSE 값(수식) END
# 주소의 시도를 '경기도'일때는 '경기도',
'특별시' 혹은 '광역시'일 때는 붙여서,
아닐 때는 앞의 두 글자만 사용하여 나타내기
SELECT restaurant_name,
addr,
CASE WHEN addr LIKE '%경기도%' THEN '경기도'
WHEN addr LIKE '%특별%' OR addr LIKE '%광역%' THEN SUBSTR(addr, 1, 5)
ELSE SUBSTR(addr, 1, 2)
END "변경된 주소"
FROM food_orders
- 조건문 활용 케이스
-
새로운 카테고리 만들기
EX)
음식타입과 같은 새로운 카테고리 생성 가능
고객들의 분류 생성 가능 -
연산식을 적용할 조건 지정하기
EX)
현금일 때와 카드일 때 수수료율을 조건문을 활용해 각각 수수료 계산 방식 적용 가능 -
다른 문법 안에서 적용하기
EX)
CONCAT문으로 여러 칼럼을 합칠 때,
rating이 있을 때는 넣어주고, 없을 때는 아무것도 넣지 않도록,
CONCAT 안에 IF 문 사용 가능
4. SQL로 간단한 USER SEGMENTATION 해보기 (실습)
가. 10세 이상, 30세 미만의 고객의 나이와 성별로 그룹 나누기 (이름도 같이 출력)
가-1. QUERY 적기 전 흐름 정리하기
#어떤 테이블에서 데이터를 뽑을 것인가 > 고객테이블
#어떤 컬럼을 이용할 것인가 > 나이, 성별, 이름
#어떤 조건을 지정해야 하는가 > 나이 (10세 이상, 30세 미만)
#어떤 함수(수식)을 이용해야 하는가 > 조건문
가-2. 구문으로 만들기
#어떤 테이블에서 데이터를 뽑을 것인가 > FROM customers
#어떤 컬럼을 이용할 것인가 > age, gender, name
#어떤 조건을 지정해야 하는가 > WHERE age BETWEEN 10 AND 29
#어떤 함수(수식)을 이용해야 하는가 > CASE WHEN THEN END
가-3. 전체 구조로 합치기
SELECT name,
age,
gender,
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='male' THEN "20대 남자"
WHEN (age BETWEEN 20 AND 29) AND gender='female' THEN "20대 여자"
END "그룹"
FROM customers
WHERE age BETWEEN 10 AND 29
나. 음식 단가, 음식 종류별로 음식점 그룹 나누기
- (Korean = 한식 / Japanese, Chinese, Thai, Vietnamese, Indian = 아시아식 / 그 외 = 기타)
(가격 = 5000, 15000, 그 이상)
나-1. QUERY 적기 전 흐름 정리하기
#어떤 테이블에서 데이터를 뽑을 것인가 > 주문테이블
#어떤 컬럼을 이용할 것인가 > 주문 금액, 주문 수량, 음식 종류
#어떤 조건을 지정해야 하는가 > 없음
#어떤 함수(수식)을 이용해야 하는가 > 조건문
나-2. 구문으로 만들기
#어떤 테이블에서 데이터를 뽑을 것인가 > FROM food_orders
#어떤 컬럼을 이용할 것인가 > price, quantity, cuisine_type
#어떤 조건을 지정해야 하는가 > 없음
#어떤 함수(수식)을 이용해야 하는가 > CASE WHEN THEN END
나-3. 전체 구조로 합치기
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
5. 조건문으로 서로 다른 식을 적용한 수수료 구하기 (실습)
- 주문 시기와 음식 수를 기반으로 배달할증료 구하기
(주문 시기: 평일 기본료 = 3000, 주말 기본료 = 3500)
(음식 수: 3개 이하 = 할증 없음, 3개 초과 = 기본료 * 1.2)
-
QUERY 적기 전 흐름 정리하기
#어떤 테이블에서 데이터를 뽑을 것인가 > 주문테이블
#어떤 컬럼을 이용할 것인가 > 주문 수량, 주문 시기
#어떤 조건을 지정해야 하는가 > 없음
#어떤 함수(수식)을 이용해야 하는가 > 조건문 -
구문으로 만들기
#어떤 테이블에서 데이터를 뽑을 것인가 > FROM food_orders
#어떤 컬럼을 이용할 것인가 > quantity, day_of_the_week
#어떤 조건을 지정해야 하는가 > 없음
#어떤 함수(수식)을 이용해야 하는가 > 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
6. DATA TYPE 오류 해결하기
- 문자 / 숫자 계산 시 오류가 나는 경우
- 문자, 숫자를 혼합하여 함수에 사용할 때에는 데이터 타입을 변경해줘야 함.
--숫자로 변경
CAST(IF(rating='Not given', '1', rating) AS DECIMAL)
--문자로 변경
CONCAT(restaurant_name, '-', CAST(order_id AS CHAR))- 과제풀이
다음의 조건으로 배달시간이 늦었는지 판단하는 값 만들기.
- 주중: 25분 이상 / 주말: 30분 이상
☑️
☑️ 작성 쿼리
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☑️ POINT!
기존 데이터에 조건문을 사용해서
필요한 형태의 데이터를 추출할 수 있다.
IF 와 CASE (WHEN THEN ELSE END) 조건문을
다양하게 활용할 수 있도록 연습해보기.
