❗️데이터 연결하기
- Subquery가 필요한 경우
1. 여러번의 연산을 수행해야 할 때
ex) 수수료를 부과할 수 있는 시간을 구하고 -> 구해진 시간에 주문 금액별로 가중치를 주고 -> 가중치를 적용한 결과로 최종 예상 배달비를 계산할 때
2. 조건문에 연산 결과를 사용해야 할 때
ex) 음식 타입별 평균 음식 주문금액 따라 음식비 상/중/하를 나누고 싶을 때
3. 조건에 Query 결과를 사용하고 싶을 때
ex) 30대 이상이 주문한 결과만 조회하고 싶을 때
* Subquery문의 기본 구조
- Sub라는 명칭에서 알 수 있듯이, Query안에 sub로 들어간 구문이라고 생각하면 됨.
[실습1]
주문 테이블에서 주문번호, 음식점명, 음식 준비시간을 가져오기 -> 음식 준비시간이 25분보다 초과한 시간을 가져오기
- 조건문과 Subquery를 결합하여 usersegmentation과 연산하기
[실습1] 음식점의 평균 단가별 segmentation을 진행하고, 그룹에 따라 수수료 연산하기
(수수료 구간 - 5000원 미만 0.5%, 5000원이상 ~ 20,000원 미만 1%, 20,000원이상 ~ 30,000원 미만 2%, 30,000원 이상 3%)
* 나눠서 결과 보기 *
1. 가운데에 있는 괄호 결과값
2. 밖에 있는 괄호에서 안에 있는 괄호를 이용하여 가격에 따른 수수료 계산
3. 최종적으로 price_per_plate에 ratio_of_add를 곱하여 수수료를 계산하는 결과가 나옴
[실습2] 음식점의 지역과 평균 배달시간으로 segmentation하기
[실습3] 음식 타입별 지역별 총 주문수량과 음식점 수를 연산하고, 주문수량과 음식점수 별 수수료율을 산정하기
음식점수 5개 이상, 주문수 30개 이상 -> 수수료 0.05%
음식점수 5개 이상, 주문수 30개 미만 -> 수수료 0.08%
음식점수 5개 미만, 주문수 30개 이상 -> 수수료 1%
음식점수 5개 미만, 주문수 30개 미만 -> 수수료 2%
[실습4] 음식점의 총 주문수량과 주문 금액을 연산하고, 주문 수량을 기반으로 수수료할인율 구하기
할인조건 : 수량이 5개 이하 -> 10%, 수량이 15개 초과, 총 주문금액이 300,000 이상 -> 0.5%, 이 외에는 일괄 1%
- 함수 : JOIN
필요한 경우
ex) 주문 가격은 주문테이블에 있지만, 어떤 수단으로 결제 했는지는 결제테이블에 있음.
주문 건 별 수수료를 계산하려면 수수료율이 필요한데, 결제 테이블에 있어서 어떻게 연산할 수 있을지 모르겠음.
* 기본 원리와 종류
- 엑셀의 VLOOKUP과 유사함!!
- 주문 정보에서 고객 이메일을 알기 위해서는, 고객 정보에서 동일한 고객 ID의 이메일을 가져와야되는데 엑셀에서는 VLOOKUP(고객ID, 고객정보, 3, False)라고 적음.
- JOIN은 동일한 원리를 가짐. 가각 주문 정보와 고객 정보가 테이블이라고 할 때, 고객 ID를 기준으로 필요한 값을 가져와 줌
-> 두 테이블이 공통으로 갖고 있는 컬럼이 됨. ex) 고객 ID
공통 컬럼을 기준으로 두 테이블을 합쳐서, 각각 테이블에서 필요한 데이터를 조회할 수 있도록 만들어줌.
+ 최 종 +
- JOIN 하는 방법
1. LEFT JOIN : 공통 컬럼(키값)을 기준으로, 하나의 테이블에 값이 없더라도 모두 죄회되는 경우를 의미함.
ex)
2. INNER JOIN : 공통 컬럼(키값)을 기준으로, 두 테이블 모두에 있는 값만 조회함.
즉, 위의 예시에서 주문번호 14의 경우, 고객에 대한 정보가 고객 정보에 없으므로 조회시 제외됨.
- JOIN의 기본 구조
💡TIP
공통컬럼은 묶어주기 위한 '공통 값'이기 때문에 두 테이블의 컬럼명은 달라도 상관없음.
ex) 주문정보에는 '고객ID", 고객정보에는 '고객아이디'라고 컬럼명이 되어있다면, [테이블1. 고객ID=테이블2. 고객아이디]와 같이 묶어줄 수 있음.
[실습] JOIN을 이용하여 두개의 테이블에서 데이터를 조회해보기
주문 테이블과 고객테이블을 customer_id를 기준으로 left join으로 묶어보기
(조회컬럼 : order_id, customer_id, restaurant_name, price, name, age, gender)
[실습] 고객의 주문 식당 조회하기
(조회 컬럼 : 고객 이름, 연령, 성별, 주문 식당)
* 고객명으로 정렬, 중복 없도록 조회
2. null 값 제거
where c.name is not null 추가
[실습] JOIN으로 연산에 필요한 값을 불러오고, 연산하기
50세 이상 고객의 연령에 따라 경로 할인율을 적용하고, 음식 타입별로 원래 가격과 할인 적용 가격 합을 구하기
(조회 컬럼 : 음식 타입, 원래 가격, 할인 적용 가격)
*할인 : (나이-50)*0.005
*고객 정보가 없는 경우도 포함하여 조회, 할인 금액이 큰 순서대로 정렬
[실습문제] 식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation 하기
(조건)
1. 평균 음식 주문 금액 기준 : 5,000이하/~10,000/~30,000/30,000 초과
2. 평균 연령 : ~20대/30대/40대/50대 이상
3. 두 테이블 모두에 테이터가 있는 경우만 조회, 식당 이름 순으로 오름 차순 정렬
1. 먼저 평균 금액과 평균 연령을 INNER JOIN으로 묶어준다. (조건3에 의해 inner join 사용)
2. 조건1과 조건2를 case when으로 각각 분류함.
3. 오름차순이기 때문에 desc 사용하지 않음.
- 학습하면서 겪었던 문제점&에러
이번 수업에서는 Subquery랑 JOIN을 활용해서 데이터를 연결하고 원하는 조건에 맞게 조회하는 방법을 배웠다. 공부하면서 가장 헷갈렸던 부분은 서브쿼리 결과를 바깥 쿼리에서 다시 사용하는 방식이랑 LEFT JOIN, INNER JOIN을 언제 써야 하는지 구분하는 점이었다. 그래서 쿼리를 한 번에 작성하지 않고 안쪽 쿼리부터 먼저 실행해서 결과를 확인했고, JOIN도 직접 결과를 비교해보면서 차이를 익혔다. 그 덕분에 복잡해 보이던 쿼리도 조금씩 이해됐고, 다음에 비슷한 문제가 나오면 작은 단위부터 하나씩 확인하면서 풀 수 있을 것 같다.