TIL 8일차 : 복습, subquery, join

 

도전한 점

• SQL 1~2주차 복습 및 정리
• SQL 3주차 복습(TIL 5일~)
• SQL 4주차~5주차 연강 듣기

 

---

 

[기본 구조]

1. SELECT : '데이터 조회'의 명령어

2. FROM : 어떤 테이블에서 가져올래

3. WHERE : 조건을 주고 싶어

4. GROUP BY : 범주별 계산

5. ORDER BY : 쿼리결과 정렬

 

[조건을 지정하는 방법]

1. 비교연산자 : <, >, =, <>(같지않다)

2. 범위 지정 : IN, BETWEEN, LIKE

3. 여러가지 조건의 적용 : AND, OR, NOT

 

＊IN : '포함' 하는 조건 주기. (=조건 안에 있는 모든 목록을 불러와 줘)

예시) where age in (21, 25, 27), where name in ('윤주아', '정현준')

 

[엑셀 함수 적용]

SUM, AVG, COUNT, MIN, MAX

 

＊COUNT(1) FROM A : A 테이블 안에 있는 모든 데이터의 개수를 세준다.

＊COUNT(DISTINCT 고객명)와의 차이 : 주문 건은 10개, 주문한 사람은 5명일 때

예시)  select count(distinct pay_type) from payments : 값은 2가 뜬다. (cash, card)

 

[문자 변경]

1. REPLACE : 지정한 문자를 다른 문자로 변경

2. SUBSTRING : 특정 문자만 추출

3. CONCAT : 여러 문자를 합하여 포맷팅

 

[조건문]

1. IF (조건, 조건O, 조건X)

2. CASE WHEN ELSE END

 

---

 

[1] 여러번의 연산을 보기 쉽게 정리하는 서브쿼리(Subquery)문

 

• 실습 1. 음식점의 평균 단가별 segmentation을 진행하고, 그룹에 따라 수수료 연산하기

 

 

＊쿼리문에서 괄호는 가장 중간의 값부터 계산한다.

 

• 실습 2. 음식점의 지역과 평균 배달시간으로 segmentation(세분화) 하기

 

작성 코드

select 지역, restaurant_name, avg(delivery_time) "평균 배달시간" from ( select substr(addr, 1, 2) "지역", restaurant_name, delivery_time from food_orders ) a group by 1

코드 예제

select restaurant_name, sido, avg_delivery_time, case when avg_delivery_time<=20 then '<=20' when avg_delivery_time between 21 and 30 then '20<x<=30' else '>30' end delivery_time_segment from (select restaurant_name, substr(addr, 1, 2) sido, avg(delivery_time) avg_delivery_time from food_orders group by 1, 2 ) a

 

- 잘못된 점 : '지역에 따른 배달시간' 값을 구하는 게 아니라 세분화를 해야 한다. 😂😂😂

 

＊group by 완벽 이해하기 : 중간 괄호 2만 넣었을 때, 결과값이 유일한 값들로 출력되는 것을 볼 수 있다.

 

 

• 실습 3. 음식 타입별 지역별 총 주문수량과 음식점 수를 연산하고, 주문수량과 음식점수 별 수수료율을 산정하기. (조건 생략)

 

 

＊여기서 지역별이란 말은 cuisine_type에 포함된 것으로 addr를 의미하는 건 아니다. 그러므로 addr 미사용.

＊count/sum/count(distinct)의 차이를 알아두자. count(quantity)가 아니고 sum이다.

＊count(distinct restaurant_name) : 레스토랑 수의 유일 값을 연산해 줘. = 중복이 없도록 계산해.

＊숫자값은 문자 그대로 '0.5%' 쓰기 보다 숫자로 '0.005' 적어두면 나중에 편하겠지.

 

• 실습 4. 음식점의 총 주문수량과 주문 금액을 연산하고, 주문 수량을 기반으로 수수료 할인율 구하기. (조건 생략)

 

작성 코드

select restaurant_name, total_quantity, total_price, case when total_quantity<=5 then 0.1 when total_quantity>15 and total_price>=300000 then 0.005 else 0.01 end rate from ( select restaurant_name, sum(quantity) total_quantity, sum(price) total_price from food_orders group by 1 ) a

코드 예제

sum_price, discount_rate 별칭을 제외하고 위와 같음👍

 

[2] 필요한 데이터가 서로 다른 테이블에 있을 때 조회하는 법 : JOIN=묶어주다

 

1. LEFT JOIN ON : A집합을 기준으로 붙여줘. B는 있는 거만 가져와도 됨.

2. INNER JOIN ON : A와 B에 공통된 데이터만 가져와줘. 비어 있으면 안돼.

 

 

• 실습 1. 주문 테이블과 고객 테이블을 coustomer_id를 기준으로 left join 으로 묶어보기

 

 

＊컬럼, 서브쿼리문 뿐만 아니라 테이블에도 별칭을 지어줄 수 있다!

＊JOIN 테이블 사용할 때, 어느 테이블인지 온점(.)으로 명시해준다.

 

 

＊중복되는 컬럼만 명시해주면 값은 똑같이 출력된다😎

 

• 실습 2. 한국 음식의 주문별 결제 수단과 수수료율 조회하기 (조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 결제 수단, 수수료율)

- 결제 정보가 없는 경우도 포함하여 조회한다. = left join

 

작성 코드

select f.order_id, f.restaurant_name, f.price, p.pay_type, p.vat from food_orders f left join payments p on f.order_id=p.order_id - 잘못된 점 : 조건 누락

코드 예제

select f.order_id, f.restaurant_name, f.price, p.pay_type, p.vat from food_orders f left join payments p on f.order_id=p.order_id where cuisine_type='Korean'

 

＊where절은 from 다음에 쓰이는데 테이블 명시 안해도 됨. 명시해도 값은 나온다.

 

• 실습 3. 고객의 주문 식당 조회하기 (조회 컬럼 : 고객 이름, 연령, 성별, 주문 식당)

- 고객 명으로 정렬하고, 중복 없도록 조회한다.

 

작성 코드

select c.name, c.age, c.gender, f.restaurant_name from customers c inner join food_orders f on c.customer_id=f.customer_id👎 order by name - 잘못된 점 : left join을 사용해야 하며, 중복값 제거조건 누락됨.

코드 예제

[1] select distinct c.name, c.age, c.gender, f.restaurant_name from customers c left join food_orders f on c.customer_id=f.customer_id order by 1(=c.name, =name) [2] select distinct c.age, c.name, c.gender, f.restaurant_name from customers c left join food_orders f on c.customer_id=f.customer_id order by 2

 

＊중복이 없도록 조회한다. <> inner join : group by 도 안먹히고, distinct를 사용한다.

＊누락정보 조건이 없으니까 그냥 묶어준다는 의미의 left join을 사용한다.

＊distinct는 반드시 select 옆에 쓰여야 출력된다. [1]의 age옆에 하니까 안됨.

＊[1]번과 [2]번의 값은 같다. 꼭 name으로 distinct할 필요는 없다.

 

• 실습 4. 주문 가격과 수수료율을 곱하여 주문별 수수료 구하기 (조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 수수료율, 수수료)

- 수수료율이 있는 경우만 조회한다.

 

select f.order_id,

f.restaurant_name,

f.price,

p.vat,

f.price*p.vat "fee"👍

from food_orders f inner join payments p on f.order_id=p.order_id

 

• 실습 5. 50세 이상 고객의 연령에 따라 경로 할인율을 적용하고, 음식 타입별로 원래 가격과 할인 적용 가격합을 구하기 (조회 컬럼 : 음식 타입, 원래 가격, 할인 적용 가격, 할인 가격(?))

- 할인(율) : 나이-50＊0.005

- 고객 정보가 없는 경우도 포함하여 조회, 할인 큼액이 큰 순서대로 정렬한다.

 

작성 코드

[1] select f.cuisine_type, f.price, c.age, (c.age-50)*0.005 discount_price, f.price-(c.age-50)*0.005 "할인 적용 가격" from food_orders f left join customers c on f.customer_id=c.customer_id where c.age>=50 order by 3 - 할인적용가격 연산식 문제, 그리고 할인율임. - 큰 금액부터 : 내림차순 정렬 필요함. - 결과적으로 가격의 합을 구해야 함. [2] select f.cuisine_type, sum(f.price) price, sum(f.price*((c.age-50)*0.005)) discounted_price from food_orders f left join customers c on f.customer_id=c.customer_id where c.age>=50 group by 1 order by 3 desc

코드 예제

select cuisine_type, sum(price) price, sum(price*discount_rate) discounted_price from (select f.cuisine_type, f.price, c.age, (c.age-50)*0.005 discount_rate from food_orders f left join customers c on f.customer_id=c.customer_id where c.age>=50 ) a group by 1 order by 3 desc

 

- 서브쿼리 없는 [2]와 서브쿼리있는 [코드 예제] 둘의 연산 값이 같기에 결과 값도 같다.

 

• 실습 6. 식당별 평균 주문 금액과 주문자의 평균 연령을 기반으로 segmentation 하기

- 평균 음식 주문 금액 기준 : 5,000 / 10,000 / 30,000 / 30,000 초과

- 평균 연령 : ~ 20대 / 30대 / 40대 / 50대 이상

- 두 테이블 모두 데이터가 있는 경우만 조회, 식당 이름 순으로 오름차순 정렬

 

작성 코드

select restaurant_name, average_price, average_age, case when average_price<5000 then 'price_1' when average_price>=5000 and average_price<10000 then 'price_2' when average_price>=10000 and average_price<30000 then 'price_3' else 'price_4' end seg_price, case when average_age<30 then '20대이하' when average_age>=30 and average_age<40 then '30대' when average_age>=40 and average_age<50 then '40대' else '50대이상' end seg_age from ( select restaurant_name, avg(price) average_price, avg(age) average_age from (select f.restaurant_name, f.price, c.age from food_orders f inner join customers c on f.customer_id=c.customer_id ) a group by 1 order by 1 ) b

코드 예제

select restaurant_name, case when average_price<5000 then 'price_1' when average_price>=5000 and average_price<10000 then 'price_2' when average_price>=10000 and average_price<30000 then 'price_3' else 'price_4' end seg_price, case when average_age<30 then '20대이하' when average_age between 30 and 39 then '30대' when average_age between 40 and 49 then '40대' else '50대이상' end seg_age from ( select f.restaurant_name, avg(price) average_price, avg(age) average_age from food_orders f inner join customers c on f.customer_id=c.customer_id group by 1 ) a order by 1

 

- 같은 문자가 반복되는 걸 줄이려면 'between'을 활용한다.
- JOIN문을 쓰면서 동시에 avg로 평균내는게 빠른데 뻘짓함.
- 단, JOIN문이라도 연산식 사용할 때 avg(c.price) 안되고 avg(price) 해야됨.

 

좋았던 점

• 실습 5번에서 서브쿼리를 작성하지 않아도 되는 코드를 작성해보니 재미있다.

아쉬운 점

• 실습 문제 가끔 이해하기 어렵게 적어 놨음.

잊지말 점

• 작성한 쿼리문을 드래그 하여 영역을 지정하거나 or 단독으로 쓴 쿼리문 위에 커서를 올리고 실행시키면 해당 영역만 출력해준다. 많이 사용함😎

시도할 점

• 데이터 분석 강의 듣기 하루 1주차씩 (화/5, 수/1, 목/2, 금/3, 토/4, 일5)
• 그러므로 화요일 5주차 완강, 코드카타 시작