2024.06.23
ㅠㅠ 결국엔 본강의 시작 전까지 총 5주차 분량에 달하는 기초 강의를 다 못 들었다..
담임 매니저님두 주말까지 다 듣기에 힘들 거라고 하시긴 했다..
그래도 TIL 써가면서 용케 작심삼일 안하고 4주차까지 달린 것에 의의를 둔다.. 만약 4주차를 다 끝내지 않았다면 내 마음가짐은 엉망인 상태로 시작을 했겠지?
[이론 1]
✳️ Subquery
- 영어에서 문장, 수학에서 수식이 길면 효율적인 표현으로 쉽게 표현하고자 하는데 sql에도 이러한 방식이 있다.
• Subquery가 필요한 경우
→ 여러 번의 연산을 수행해야 할 때
→ 조건문에 연산 결과를 사용해야 할 때
→ 조건에 Query 결과를 사용하고 싶을 때
• 기본 구조
select column1, special_column
from
( /* subquery */
select column1, column2 special_column
from table1
) a
select column1, column2
from table1[KDC] 엑셀보다 쉽고 빠른 SQL - 4주차 3
where column1 = (select col1 from table2)
💠 주문 테이블에서 주문 번호, 음식점명, 음식 준비시간을 가져오기
▶ 주문 테이블에서 음식 준비시간이 25분보다 초과한 시간을 가져오고 주문 번호, 음식점명, 음식 준비시간을 보여주기.
▶ 25분 보다 초과한 시간을 얼만큼 초과했는지를 보여줄 것이고, 초과하지 않았다면 0분으로 표시해줄 것임.
▶ subquery 부분인 () 사이 부분을 보면 초과한 시간을 계산하기 위해, 준비시간-25을 계산하고 over_time이라고 별명 달아줌.
▶ 본래 식에서 if 문을 써주며 over_time중 over_time인 것과 아닌 것(0에서 마이너스 값들)을 어떻게 표현해줄지 제시를 한 것, over_time이면 그대로 계산된 결과를 입력하고 아닌 것은 0으로 표시.
[실습 1]
☑️ User Segmentation 와 조건별 수수료를 Subquery 로 결합해보기
💠 음식점의 평균 단가별 segmentation 을 진행하고, 그룹에 따라 수수료 연산하기
(수수료 구간) ~5000원 미만 0.05%
~20000원 미만 1%
~30000원 미만 2%
30000원 초과 3%
▶ 첫 번째 subquery a에는 음식 평균 단가를 계산하고 음식점별로 그룹을 지어줬다.
▶ 두 번째 subquery b에는 음식 평균 단가에 따라 범위에 따른 세분화를 위해 조건문을 이용해줬다.
▶ 마지막으로 본 query에서 그룹에 따라 수수료를 계산했다.
💠 음식점의 지역과 평균 배달시간으로 segmentation 하기
▶ subquery a에는 substr을 이용하여 음식적의 지역을 시와 도를 2글자씩만 표기하도록 적고 평균 배달 시간을 구했다.
▶ 그리고 본 query에서 case문을 이용하여 평균 배달 시간을 범위를 지정하여 세분화했다.
☑️ 복잡한 연산을 Subquery 로 수행하기
💠 음식 타입별 지역별 총 주문수량과 음식점 수를 연산하고, 주문수량과 음식점수 별 수수료율을 산정하기
(음식점수 5개 이상, 주문수 30개 이상 → 수수료 0.05%
음식점수 5개 이상, 주문수 30개 미만 → 수수료 0.08%
음식점수 5개 미만, 주문수 30개 이상 → 수수료 1%
음식점수 5개 미만, 주문수 30개 미만 → 수수로 2%)
▶ 지문에 ,를 기준으로 앞을 subquery로 작성해주고 본 query에서 수수료율을 산정하였다.
💠 음식점의 총 주문수량과 주문 금액을 연산하고, 주문 수량을 기반으로 수수료 할인율 구하기
(할인조건)
수량이 5개 이하 → 10%
수량이 15개 초과, 총 주문금액이 300000 이상 → 0.5%
이 외에는 일괄 1%
▶ 지문에 ,를 기준으로 앞을 subquery로 작성해주고 본 query에서 수수료 할인율을 구했다.
[이론 2]
✳️ JOIN
- 필요한 데이터가 한 테이블이 아닌 여러 테이블에 모여져 있을 때 한 테이블로 데이터를 불러오는 방법.
• JOIN이 필요한 경우
→ 주문 가격은 주문테이블에 있지만, 어떤 수단으로 결제를 했는지는 결제테이블에 있어요
→ 주문을 한 사람을 확인하려면, 주문 테이블과 고객 테이블에서 각각 정보를 가져와서 엑셀에서 합쳐줘야 해요
→ 주문 건별 수수료를 계산하려면 수수료율이 필요한데, 결제 테이블에 있어서 어떻게 연산할 수 있을지 모르겠어요
• 기본 원리 및 종류
- 엑셀의 Vlookup과 유사하다.
• 기본 구조
-- LEFT JOIN
select 조회 할 컬럼
from 테이블1 a left join 테이블2 b on a.공통컬럼명=b.공통컬럼명
-- INNER JOIN
select 조회 할 컬럼
from 테이블1 a inner join 테이블2 b on a.공통컬럼명=b.공통컬럼명
💡 TIP
공통컬럼은 묶어주기 위한 ‘공통 값’ 이기 때문에 두 테이블의 컬럼명은 달라도 괜찮음.
예를 들어 주문정보에는 ‘고객ID’, 고객정보에는 ‘고객아이디’ 라고 컬럼명이 되어있다면,
테이블1.고객ID=테이블2.고객아이디 와 같이 묶어줄 수 있다.
💠 JOIN 을 이용하여 두 개의 테이블에서 데이터를 조회해보기
주문 테이블과 고객 테이블을 cusomer_id 를 기준으로 left join 으로 묶어보기
(조회 컬럼 : order_id, customer_id, restaurant_name, price, name, age, gender)
▶ food_orders와 customers 테이블의 customer_id를 가지고 left join을 이용하여 food_order의 customer_id 데이터와 겹치는 customers의 customer_id 데이터 및 겹치지 않는 데이터를 불러옴.
▶ 여기서 food_orders는 f로 별명, customers는 c로 별명을 지어주고 간편하게 작성할 수 있도록 하였다.
▶ select 뒤에 쓰인 컬럼은 각각 어떤 테이블의 컬럼인지를 같이 명시해준 것이다.
[실습 2]
☑️ JOIN 으로 두 테이블의 데이터 조회하기
💠 한국 음식의 주문별 결제 수단과 수수료율을 조회하기
(조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 결제 수단, 수수료율)
*결제 정보가 없는 경우도 포함하여 조회
💠 고객의 주문 식당 조회하기
(조회 컬럼 : 고객 이름, 연령, 성별, 주문 식당)
*고객명으로 정렬, 중복 없도록 조회
▶ 이 부분은.. 선생님께서 하라는대로 left로 했는데 null(포함 되지 않는 데이터라 표시되는 값) 이 나와서 강의 자료의 코드를 따라 inner로 대체하여 만들었다.
▶ Distinct는 데이터의 갯수를 세는 것뿐만 아니라 중복된 것을 제외하고 값을 보여주는 역할을 해주기도 한다.
☑️ JOIN 으로 두 테이블의 값을 연산하기
💠주문 가격과 수수료율을 곱하여 주문별 수수료 구하기
(조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 수수료율, 수수료)
*수수료율이 있는 경우만 조회
▶ Join 구문으로 묶어줬기 때문에 두 테이블의 값을 연산할 수 있게 된다.
💠50세 이상 고객의 연령에 따라 경로 할인율을 적용하고, 음식 타입별로 원래 가격과 할인 적용 가격 합을 구하기
(조회 컬럼 : 음식 타입, 원래 가격, 할인 적용 가격, 할인 가격)
* 할인 : 나이-50*0.005
* 고객 정보가 없는 경우도 포함하여 조회, 할인 금액이 큰 순서대로 정렬
▶ Subquery가 계산이 된다면 하나의 데이터 결과가 되는 것이다. 그래서 본 쿼리에서 동일한 컬럼을 적어줄 때 subquery에 대한 명칭.컬럼 혹은, 명칭은 생략하고 그냥 컬럼명을 적어 줘도 된다. (a.cuisine_type or cuisine_type)
[숙제]
✏️ 식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation 하기
- 평균 음식 주문 금액 기준 : 5,000 / 10,000 / 30,000 / 30,000 초과
- 평균 연령 : ~ 20대 / 30대 / 40대 / 50대 이상
select *
from food_orders
select *
from customers
처음에 내가 필요한 테이블이 무엇인지 파악하고 각각의 테이블을 열어 공통된 컬럼을 확인하였다.
그 결과 customers_id라는 컬럼을 join하기로 하였다.
(
select restaurant_name,
avg(price) price,
avg(age) age
from food_orders f inner join customers c on f.customer_id=c.customer_id
group by 1
) t
다음과 같이 음식가격과 고객나이의 평균을 구하기 위한 쿼리를 작성하고 이것을 subquery처리를 하였다.
select restaurant_name,
case when price<=5000 then 'price_group1'
when price>5000 and price<=10000 then 'price_group2'
when price>10000 and price<=30000 then 'price_group3'
when price>30000 then 'price_group4' price_group,
case when age<30 then 'age_group1'
when age between 31 and 39 then 'age_group2'
when age between 40 and 49 then 'age_group3'
else 'age_group4' end age_group
from
(
select restaurant_name,
avg(price) price,
avg(age) age
from food_orders f inner join customers c on f.customer_id=c.customer_id
group by 1
) t
order by 1
그리고 본 query에서 case 문을 사용하여 가격과 나이에 해당하는 세분화 조건을 세워 세분화 하고 마지막으로 정렬하였다.
[고찰]
지난 시간에 궁금했던 대문자 구분에 대한 내용이 학습질문에 실려 있어서 캡쳐해왔다.
그리고 별명을 지을 때 as를 이용해주는 걸 권장하는 것에 대한 실전적인 이유를 적어주셔서 도움이 되었다.
이번 시간에는 JOIN 문을 작성하면서 어떤 테이블을 기준으로 하여 쿼리를 작성하는지에 따라 결과가 달라질 수도 있다라는 걸 알게 되었다.
5주차 강의까지 들을 수 있는 시간이 좀 더 있었으면 좋았을테지만 혼자서 해보는 4일간의 사전 기초 SQL 공부는 여기서 마감하지만! 본 수업으로 4개월을 달린다!! 아자아자ㅠㅠ! 글을 쓰다보니 날짜가 넘어가면서 오늘이 시작 날이 되었는데 졍말.. 설렘뽕짝하면서도 어려움이 있을거라 너무 손가락이 떨린다..^-^
'📒 Today I Learn > 🐬 SQL' 카테고리의 다른 글
[SQL] REGEXP, 날짜 추출 함수, 올림/반올림/내림/버림 (0) | 2024.06.26 |
---|---|
[SQL 기초] 5주차 강의 (0) | 2024.06.24 |
[SQL 기초] 3주차 강의 (1) | 2024.06.22 |
[SQL 기초] 2주차 강의 (0) | 2024.06.21 |
[SQL 기초] 1주차 강의 (0) | 2024.06.21 |