250206 TIL

수이·2025년 2월 6일
Data_6기sql내일배움캠프
0

🟡 TIL

목록 보기
5/41

개인스터디

엑셀보다 쉽고 빠른 SQL 4주차

3주차 복습

문자 변경

REPLACE : 지정한 문자 다른 문자로 변경
SUBSTRING(SUBSTR) : 특정 문자만 추출
CONCAT : 여러 문자 합하여 포맷팅

조건문

IF(조건, 조건 충족시, 미충족시)
CASE WHEN 조건1 THEN 값(수식)1
	 WHEN 조건2 THEN 값(수식)2
     ELSE 값(수식)3
     END

1) 서브쿼리(Subquery)

  • 여러번의 연산 수행 시
  • 조건문에 연산 결과 사용 시
  • 조건에 Query 결과 사용 시

기본 구조

select column1, special_column
from
    ( /* subquery */
    select column1, column2 special_column
    from table1
    ) a
select column1, column2
from table1
where column1 = (select col1 from table2)

실습

음식 준비시간이 25분보다 초과한 시간을 가져오기

나눠서 생각하기
1) 서브쿼리문 작성

(SELECT order_id, restaurant_name, food_preparation_time-25 over_time
FROM food_orders) over_time_calculate # 초과한 시간 계산

2) 결과값 불러오기

SELECT order_id, restaurant_name, if(over_time >= 0, over_time, 0) # 초과한 시간이 0보다 크면 초과시간, 아니면 0 
FROM
(SELECT order_id, restaurant_name, food_preparation_time-25 over_time
FROM food_orders) over_time_calculate

음식점의 평균 단가별 segmentation 을 진행하고, 그룹에 따라 수수료 연산하기
수수료 구간

  • ~5000원 미만 0.05%
  • ~20000원 미만 1%
  • ~30000원 미만 2%
  • 30000원 초과 3%

나눠서 생각하기
1) 평균 단가별 segmentation

SELECT restaurant_name, AVG(price/quantity) AS unit_price -- 이름 붙여주기
FROM food_orders
GROUP BY 1

2) 수수료 구하기 

SELECT restaurant_name, unit_price,
	   CASE WHEN unit_price < 5000 THEN 0.005
			WHEN unit_price < 20000 THEN 0.01
			WHEN unit_price < 30000 THEN 0.02
			ELSE 0.03
			END AS charge -- 이름 붙여주기 
FROM  -- 서브쿼리로 사용
(
SELECT restaurant_name, AVG(price/quantity) AS unit_price 
FROM food_orders
GROUP BY 1
) A

3) 평균 단가 * 수수료 계산 / 완성✨ 

SELECT restaurant_name, 
	   unit_price*charge AS "수수료"
FROM -- 서브쿼리로 사용2 > B그룹
(
SELECT restaurant_name, unit_price,
	   CASE WHEN unit_price < 5000 THEN 0.005
			WHEN unit_price < 20000 THEN 0.01
			WHEN unit_price < 30000 THEN 0.02
			ELSE 0.03
			END AS charge
FROM  -- 서브쿼리로 사용1 > A그룹 
(
SELECT restaurant_name, AVG(price/quantity) AS unit_price 
FROM food_orders
GROUP BY 1
) A 
) B

음식점의 지역과 평균 배달시간으로 segmentation 하기
결과값

나눠서 생각하기

1) 시도, 평균 배달시간 그룹화

SELECT restaurant_name, 
	   SUBSTR(addr, 1, 2) AS sido, 
	   AVG(delivery_time) AS avg_time 
FROM food_orders
GROUP BY 1,2

2) 평균 배달시간 범주화 / 완성✨ 

SELECT restaurant_name, sido, 
	   CASE WHEN avg_time <= 20 THEN '<=20'
	   		WHEN avg_time BETWEEN 20 AND 30 THEN '20<x<=30'
	   		ELSE '>30'
	   		END AS time_segment
FROM
(
SELECT restaurant_name, 
	   SUBSTR(addr, 1, 2) AS sido, 
	   AVG(delivery_time) AS avg_time 
FROM food_orders
GROUP BY 1,2
) A

음식 타입별 총 주문수량과 음식점수를 연산하고, 주문수량과 음식점수 별 수수료율을 산정하기

  • 음식점수 5개 이상, 주문수 30개 이상 → 수수료 0.5%
  • 음식점수 5개 이상, 주문수 30개 미만 → 수수료 0.8%
  • 음식점수 5개 미만, 주문수 30개 이상 → 수수료 1%
  • 음식점수 5개 미만, 주문수 30개 미만 → 수수료 2%)

나눠서 생각하기

1) 총 주문수량과 음식점수 연산

SELECT cuisine_type,
	   sum(quantity) AS total_quantity, -- 주문수량 합계
	   COUNT(DISTINCT restaurant_name) AS count_rest -- 중복된 값 제거한 음식점수 합계
FROM food_orders
GROUP BY 1

2) 수수료율 산정 / 완성✨

SELECT cuisine_type,
	   total_quantity, -- 서브쿼리에서 사용했던 거 불러오기1
	   count_rest, -- 서브쿼리에서 사용했던 거 불러오기2
	   CASE WHEN count_rest >= 5 AND total_quantity >= 30 THEN 0.005
	   		WHEN count_rest >= 5 AND total_quantity < 30 THEN 0.008
	   		WHEN count_rest < 5 AND total_quantity >= 30 THEN 0.01
	   		WHEN count_rest < 5 AND total_quantity < 30 THEN 0.02
	   		END AS rate
FROM -- 서브쿼리 시작
(
SELECT cuisine_type,
	   sum(quantity) AS total_quantity,
	   COUNT(DISTINCT restaurant_name) AS count_rest
FROM food_orders
GROUP BY 1
) A

음식점의 총 주문수량과 주문 금액을 연산하고, 주문 수량을 기반으로 수수료 할인율 구하기

  • 수량이 5개 이하 → 10%
  • 수량이 15개 초과, 총 주문금액이 300000 이상 → 0.5%
  • 이 외에는 일괄 1%

나눠서 생각하기

1) 총 주문수량과 총 주문 금액 연산

SELECT restaurant_name,
	   SUM(quantity) AS total_quantity,  -- 총 주문수량
	   SUM(price) AS total_price -- 총 주문금액
FROM food_orders
GROUP BY 1

2) 수수료 할인율 계산 / 완성✨

SELECT restaurant_name,
	   CASE WHEN total_quantity <= 5 THEN 0.1
	   		WHEN total_quantity > 15 AND total_price >= 300000 THEN 0.005
	   		ELSE 0.01
	   		END AS rate
FROM -- 서브쿼리 시작
(
SELECT restaurant_name,
	   SUM(quantity) AS total_quantity, 
	   SUM(price) AS total_price
FROM food_orders
GROUP BY 1
) A

2) JOIN 기능

  • 엑셀 VLOOKUP과 유사한 기능
    • LEFT JOIN : 공통 컬럼 (키값) 을 기준으로, 하나의 테이블에 값이 없더라도 모두 조회
    • NNER JOIN : 공통 컬럼 (키값) 을 기준으로, 두 테이블 모두에 있는 값만 조회

기본 구조

-- 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.공통컬럼명

실습

한국 음식의 주문별 결제 수단과 수수료율을 조회하기
(조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 결제 수단, 수수료율)
*결제 정보가 없는 경우도 포함하여 조회

나눠서 생각하기

1) 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

2) "한국 음식"조건 추가 

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'

고객의 주문 식당 조회하기
(조회 컬럼 : 고객 이름, 연령, 성별, 주문 식당)
*고객명으로 정렬, 중복 없도록 조회

나눠서 생각하기

1) JOIN함수 작성 

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 -- 중복 없도록 조회해야하므로 INNER JOIN 사용

2) "고객명 정렬" 조건 추가 

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 c.name

주문 가격과 수수료율을 곱하여 주문별 수수료 구하기

(조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 수수료율, 수수료)
*수수료율이 있는 경우만 조회

나눠서 생각하기

1) JOIN함수 작성 

SELECT f.order_id,
	   f.restaurant_name,
	   f.price,
	   p.vat
FROM food_orders f INNER JOIN payments p ON f.order_id = p.order_id

2) 수수료 연산 

SELECT f.order_id,
	   f.restaurant_name,
	   f.price,
	   p.vat
       f.price * p.vat "수수료"
FROM food_orders f INNER JOIN payments p ON f.order_id = p.order_id

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

  • 할인 : (나이-50)*0.005
  • 고객 정보가 없는 경우도 포함하여 조회, 할인 금액이 큰 순서대로 정렬

나눠서 생각하기

1) JOIN함수 작성 

SELECT f.cuisine_type,
	     f.price,
	     (c.age - 50) * 0.005 AS discount_rate -- 수수료 계산 
FROM food_orders f LEFT JOIN customers c ON f.customer_id = c.customer_id
WHERE c.age >= 50

2) 원래가격 합 / 할인 적용가격 합 구하기

SELECT cuisine_type,
       SUM(price) AS price,
	   SUM(price * discount_rate) AS discount_price
FROM -- 서브쿼리
(
SELECT f.cuisine_type,
	     f.price,
	     (c.age - 50) * 0.005 AS discount_rate 
FROM food_orders f LEFT JOIN customers c ON f.customer_id = c.customer_id
WHERE c.age >= 50
)

3) 음식 타입별 그룹화 / 정렬 추가

SELECT cuisine_type,
       SUM(price) AS price,
	   SUM(price * discount_rate) AS discount_price
FROM 
(
SELECT f.cuisine_type,
	     f.price,
	     (c.age - 50) * 0.005 AS discount_rate 
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 -- 할인가격 내림차순 정렬

숙제

식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation 하기

  • 평균 음식 주문 금액 기준 : 5,000 이하 / ~10,000 / ~30,000 / 30,000 초과
  • 평균 연령 : ~ 20대 / 30대 / 40대 / 50대 이상
  • 두 테이블 모두에 데이터가 있는 경우만 조회, 식당 이름 순으로 오름차순 정렬

나눠서 생각하기

1) JOIN함수 작성 / 평균 금액,연령 구하기

SELECT f.restaurant_name,
	   AVG(f.price) AS avg_price, --평균금액
	   AVG(c.age) AS avg_age --평균연령
FROM food_orders f INNER JOIN customers c ON f.customer_id = c.customer_id
GROUP BY 1

2) 서브쿼리 활용, segmentation

SELECT restaurant_name,
	   CASE WHEN avg_price <= 5000 THEN 'price_group1'
	   	    WHEN avg_price <= 10000 THEN 'price_group2'
	   	    WHEN avg_price <= 30000 THEN 'price_group3'
	   	    ELSE 'price_group4'
	   	    END AS price_group,
	   CASE WHEN avg_age < 30 THEN 'age_group1'
	   	    WHEN avg_age < 40 THEN 'age_group2'
	   	    WHEN avg_age < 50 THEN 'age_group3'
	   	    ELSE 'age_group4'
	   	    END AS age_group	   
FROM --서브쿼리 시작
(SELECT f.restaurant_name,
	   AVG(f.price) AS avg_price,
	   AVG(c.age) AS avg_age
FROM food_orders f INNER JOIN customers c ON f.customer_id = c.customer_id
GROUP BY 1
) A

3) 식당 이름순 오름차순 정렬 

SELECT restaurant_name,
	   CASE WHEN avg_price <= 5000 THEN 'price_group1'
	   	    WHEN avg_price <= 10000 THEN 'price_group2'
	   	    WHEN avg_price <= 30000 THEN 'price_group3'
	   	    ELSE 'price_group4'
	   	    END AS price_group,
	   CASE WHEN avg_age < 30 THEN 'age_group1'
	   	    WHEN avg_age < 40 THEN 'age_group2'
	   	    WHEN avg_age < 50 THEN 'age_group3'
	   	    ELSE 'age_group4'
	   	    END AS age_group	   
FROM 
(SELECT f.restaurant_name,
	   AVG(f.price) AS avg_price,
	   AVG(c.age) AS avg_age
FROM food_orders f INNER JOIN customers c ON f.customer_id = c.customer_id
GROUP BY 1
) A
ORDER BY 1

데일리퀘스트

SQL 문법을 연습해요 7 - 랭크게임 하다가 싸워서 피드백 남겼어요…

일기

전 직장에 다녀와서 조금 늦게 공부 시작했지만 무사히 참여완료!
매니저님께서 매번 연락 주시고, 챙겨주셔서 정말정말 감사하다
(ㄱㅇㅇ 매니저님 ... 이 자리를 빌어.. 다시 한번 감사합니다.. 감사합니다..)

SQL 강의가 끝났다! 내일부터는 본격적인 자격증 시험 준비를 위해 5강 복습을 마치고 ADSP에 집중할 예정🤩 강의는 몇 개 듣긴 했는데 알아보니 기출 문제를 풀어보는 게 좋다고 해서 책을 추가로 하나 더 살까 싶다. 외워야 하는 부분이 많아서 걱정이다.

4주차 강의 들을때도 서브쿼리, JOIN이 어려워서 애먹었는데 다시 보니까 그래도 잘 활용할 수 있을듯 하다. 다만 연습용 DB보다도 실전에서 사용하는 DB 데이터의 양은 훨씬 방대할 것이라 쿼리문을 간결하고 가독성 있게 작성하는 버릇 들여놓는 걸 잊지 말아야겠다!

profile
수이
이전 포스트

250205 TIL

다음 포스트

250207 TIL

0개의 댓글

관련 채용 정보