[SQL] SQL로 가공하기(REPLACE,SUBSTRING,CONCAT)
1. 2주차 복습
- SQL문의 기본 구조
SELECT
FROM
WHERE
GROUP BY
ORDER BY- 복습용 예시
- 주문 테이블에서 → FROM
- 주문 수량이 1건인 주문건의 → WHERE
- 음식 가격의 평균을 음식 종류별로 조회하여 → GROUP BY, AVG
- 음식 가격이 높은 순서대로 정렬하기 → ORDER BY
2. SQL로 가공하기
- Query 결과를 바로 사용할 수 없는 경우
- 데이터를 보니 잘못된 값이 있어서, 수정해야하는 경우
- 다른 문자로 수정을 해야하는데, 하나하나 수동으로 하기는 너무 많은경우
- 주소 전체가 아닌 ‘시도’ 정보만 필요한 경우
- 서울의 통계만 구하고 싶은데, 전체 주소가 아닌 ‘서울’ 로 문자를 변경하고 싶은 경우
- 사업장 명과 함께 지역이 같이 나와야하는데,. ‘사업장 [지역]’ 과 같은 형태로 문자 포맷을 변경하고 싶은 경우
2-1. 특정 문자를 다른 문자로 바꾸기(REPLACE)
- 특정 문자를 다른 것으로 바꿀 수 있는 기능
- 사용방법
replace(바꿀 컬럼, 현재 값, 바꿀 값)- 예시: 예전에 ‘문곡리’ 라는 지명이 ‘문가리’ 로 바뀌었어
SELECT addr "원래 주소",
REPLACE(addr, '문곡리', '문가리') "바뀐 주소"
FROM food_orders
WHERE addr LIKE '%문곡리%'내가 한 해석 : food_orders 테이블에서 addr('원래 주소') 컬럼의 값 중에서 '문곡리'가 포함된 데이터를 '문가리'로 변경한 값을 '바뀐 주소' 컬럼명으로 출력한다.
2-2. 원하는 문자만 남기기(SUBSTRING (substr))
- 특정 문자만 골라서 조회할 수 있는 기능
- 사용 방법
substr(조회 할 컬럼, 시작 위치, 글자 수)- 예시 : 서울 음식점들의 주소를 전체가 아닌 ‘시도’ 만 나오도록 수정
SELECT addr "원래 주소",
SUBSTR(addr, 1, 2) "시도"
FROM food_orders
WHERE addr LIKE '%서울특별시%'내가 한 해석 : food_orders 테이블에서 addr 컬럼의 값 중 '서울특별시'가 들어가는 데이터를 찾고 addr의 첫 번째 문자부터 2개의 문자를 추출해서 '시도' 열에 표시한다.
2-3. 여러 컬럼의 문자를 합치기(CONCAT)
- 여러 컬럼의 값을 하나로 합칠 수 있는 기능
- 붙일 수 있는 문자의 종류
- 컬럼
- 한글
- 영어
- 숫자
- 기타 특수문자
- 사용 방법
concat(붙이고 싶은 값1, 붙이고 싶은 값2, 붙이고 싶은 값3, .....)- 예시 : 서울시에 있는 음식점은 '[서울] 음식점명'이라고 수정
SELECT restaurant_name "원래 이름",
addr "원래 주소",
CONCAT('[', SUBSTRING(addr, 1, 2), '] ', restaurant_name) "바뀐 이름"
FROM food_orders
WHERE addr like '%서울%'내가 한 해석 : food_orders 테이블에서 '서울'이 포함된 행을 찾는다, 그리고 각 행에 대해 addr 값에서 첫번째 문자부터 2개의 문자를 추출한다 그리고 그 추출한 값을 [ ] 로 감싸고 그 뒤에 restaurant_name 값을 붙인다. 그리고 이렇게 만들어진 문자열을 '바뀐 이름'이라는 열로 결과에 포함시킨다.
3. [실습] 문자 데이터를 바꾸고, GROUP BY 사용하기
3-1. 서울 지역의 음식 타입별 평균 음식 주문금액 구하기(출력 : '서울', '타입', '평균 금액')
- 구문으로 만들기
- 어떤 테이블에서 데이터를 뽑을 것인가 - food_orders
- 어떤 컬럼을 이용할 것인가 - price,cuisine_type,addr
- 어떤 조건을 지정해야 하는가 - addr LIKE '%서울%'
- 어떤 함수 (수식) 을 이용해야 하는가- GROUP BY, AVG(price), substr
- 전체 구조로 합치기
SELECT substr(addr, 1, 2) '시도',
cuisine_type '음식 종류',
AVG(price) '평균 금액'
FROM food_orders
WHERE addr LIKE '%서울%'
GROUP BY 1, 2여기는 아직도 이해가 잘 안된다 group by가 대체 뭘 뜻하는지 ㅠ ...
이리저리 많이 알아봤는데 숫자 1은 SELECT문에서 첫번째로 나열된 컬럼을 의미하고, 숫자 2는 두번째로 나열된 컬럼을 의미한다, 따라서 GROUP BY 1, 2는 첫번째 컬럼인 '시도'와 두번째 컬럼인 '음식 종류'를 기준으로 결과를 그룹화 하겠다는 의미다
라고한다 ^^ .. 그래도 이해가 잘 되질 않아서 시간 남을때 group by 부분은 다시 공부를 해봐야 할 것 같다. .....
3-2. 이메일 도메인별 고객 수와 평균 연령 구하기
- 구문으로 만들기
- 어떤 테이블에서 데이터를 뽑을 것인가 - customers
- 어떤 컬럼을 이용할 것인가 - email,customer_id,age
- 어떤 조건을 지정해야 하는가
- 어떤 함수 (수식) 을 이용해야 하는가 - avg(age), count,substring
- 전체 구조로 합치기
SELECT SUBSTR(email,10,12) '이메일 도메인',
COUNT(customer_id) '고객 수',
AVG(age) '평균 연령'
FROM customers
GROUP BY 1이건 조금 이해했다!! 근데 substring에서 12는 결국 끝 글자를 뜻하는거라서 굳이 칠 필요는 없었던 것 같다(지금은 수정함)
여기서의 group by 1 도 아까 예시로 봤던 것 때문에 왜 사용되엇는지는 알 것같다( 결과를보면 알게된다 ! )
3-3. [지역(시도)] 음식점 이름(음식종류) 컬럼을 만들고, 총 주문건수 구하기
- 구문으로 만들기
- 어떤 테이블에서 데이터를 뽑을 것인가 - food_orders
- 어떤 컬럼을 이용할 것인가 - restaurant_name,quantity,cuisine_type,addr
- 어떤 조건을 지정해야 하는가
- 어떤 함수 (수식) 을 이용해야 하는가- count(quantity),concat,substr
- 전체 구조로 합치기
SELECT concat('[', SUBSTR(addr,1,2), ']', restaurant_name,'(',cuisine_type,')') '바뀐이름',
count(quantity) '주문건수'
FROM food_orders
GROUP BY 1결과창만 띄워놓고 혼자 만들어보는 연습을 하는중이다 ,, 아직 당장 저 문장 하나만 보고 만들기는 어려운 것 같다..
실습은 시간날 때마다 매번 풀어보는 수 밖에 없는 것 같다 어려웡