4주차

Suhyeon Lee·2024년 8월 29일
엑셀보다 쉽고 빠른 SQL
0

[수업 목표]

  • Subquery 활용: 복잡한 연산 수행
  • Join 활용: 여러 개의 테이블에 있는 데이터를 한 번에 조회하고 연산하기

[복습]

  • 문자 변경
    1. REPLACE: 지정한 문자를 다른 문자로 변경
    2. SUBSTRING: 특정 문자만 추출
    3. CONCAT: 여러 문자를 합하여 포맷팅
  • 조건문
    1. IF: IF(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)
    2. CASE WHEN END: 
		CASE        
			WHEN 조건1 THEN 값(수식)1        
			WHEN 조건2 THEN 값(수식)2        
			ELSE 값(수식)3        
		END

A. 여러 번의 연산을 한 번의 SQL문으로 수행하기

1. Subquery가 필요한 경우

  • 여러 번의 연산을 수행해야 할 때
    • (예) 수수료를 부과할 수 있는 시간을 구하고 구해진 시간에 주문 금액별로 가중치를 두고 가중치를 적용한 결과로 최종 예상 배달비를 계산
      • 조건문에 연산 결과를 사용해야 할 때
        (예) 음식 타입별 평균 음식 주문 금액 따라 음식비 상, 중, 하 나누기
      • 조건에 Query 결과 사용하고 싶을 때
        (예) 30대 이상이 주문한 결과만 조회

2. Subquery문의 기본 구조

  • Sub라는 명칭에서 알 수 있듯 Query 안에 sub로 들어간 구문임 
SELECT column1, special_column
FROM
	(/*subquery*/
    SELECT column1, column2 special_column
    FROM table1
    ) a

※ 단일행 서브쿼리(SingleRow Subquery): 하나의 행만 리턴하기 때문에 단일행 비교 연산자(=, <, >, <>)만 사용 

SELECT column1, column2
FROM table1
WHERE column1 = (SELECT col1 FROM table2)

3. [실습] Subquery문을 이용하여 연산문 적어보기

  • 주문 테이블에서 주문번호, 음식점명, 음식 준비시간을 가져오기
SELECT order_id, restaurant_name, food_preparation_time
FROM (
     SELECT order_id, restaurant_name, food_preparation_time
     FROM food_orders
     ) a
  • 음식 주문시간이 25분보다 초과한 시간 가져오기
SELECT order_id, restaurant_name, IF(over_time>0, over_time, 0) over_time
FROM (
     SELECT order_id, restaurant_name, food_preparation_time-25 over_time
     FROM food_orders
     ) a

B. [실습] User Segmentation, 조건별 수수료를 Subquery로 결합 (조건문과 Subquery 결합)

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

  • 수수료 구간
    • ~5000원 미만: 0.05%
    • ~20000원 미만: 1%
    • ~30000원 미만: 2%
    • 30000원 초과: 3%

a. 어떤 테이블에서 데이터를 뽑을 것인가 → FROM food_orders
b. 어떤 컬럼을 이용할 것인가 → restaurant_name, price, quantity
c. 어떤 조건을 지정해야 하는가 → 조건 없음
d. 어떤 함수(수식) → AVG(price/quantity), CASE, GROUP BY

SELECT restaurant_name,
       price_per_plate*ratio_of_add "수수료"
FROM (
     SELECT restaurant_name,
            CASE
              WHEN price_per_plate < 5000 THEN 0.0005
              WHEN price_per_plate BETWEEN 5000 AND 19999 THEN 0.01
              WHEN price_per_plate BETWEEN 20000 AND 29999 THEN 0.02
              ELSE 0.03
            END ratio_of_add,
    FROM (
  		 SELECT restaurant_name,
         		AVG(price/quantity) price_per_plate
    	 FROM food_orders
    	 GROUP BY 1
    	 ) a
    ) b

💡 price_per_plate between 5000 and 19999 대신에 price_per_plate >= 5000 AND price_per_plate < 20000 라고 쓰면 안 되나? → 됨!
price BETWEEN a AND b는 price >= a AND price <= b 와 동일한 조건임
강의에서는 두 개의 방식 모두 보여주려는 것으로 실제로는 한 개의 방식으로 통일해서 작성하면 됨 (다만 작성할 때에는 조건 범위에 유의할 것)

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

  • 평균 배달 시간: 20분 이하, 20분 초과 30분 이하, 30분 초과
    a. 어떤 테이블 → FROM food_orders
    b. 어떤 컬럼 → restaurant_name, addr, delivery_time
    c. 어떤 조건 → 없음
    d. 어떤 함수 → AVG(delivery_time), SUBSTRING(addr, 1, 2), CASE, GROUP BY
SELECT 	restaurant_name,
    	CASE
    		WHEN avg_time <= 20 THEN '20분 이하'
    		WHEN avg_time > 20 AND avg_time <= 30 THEN '20분 초과 30분 이하'
    		WHEN avg_time > 30 THEN '30분 초과'
    	END time_segment
FROM (
     SELECT restaurant_name,
    		SUBSTRING(addr, 1, 2) sido,
    		AVG(delivery_time) avg_time
    FROM food_orders
    GROUP BY 1, 2
    ) a

💡 MySQL에서는 쌍따옴표와 홑따옴표를 엄격하게 구분하지 않는데 다른 데이터베이스에서는 쌍따옴표와 홑따옴표의 사용을 구분해서 사용하므로 구분하는 편이 좋음
→ 홑따옴표: 문자열 값을 나타내는 데 사용. 문자열 안에는 모든 문자가 포함될 수 있음.
→ 쌍따옴표: 식별자(table name, column name 등)에 사용될 때 주로 활용.
※ 쌍따옴표 내부에 있는 문자는 대소문자를 구분하여 처리되며 이는 대소문자를 구분하지 않는 기본적인 데이터베이스 동작과는 다름

C. [실습] 복잡한 연산을 Subquery로 수행: 하나의 쿼리문에서 수행하기 어려운 복잡한 연산

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

a. 음식점 수 5개 이상, 주문 수 30개 이상 → 수수료 0.05%
b. 음식점 수 5개 이상, 주문 수 30개 미만 → 수수료 0.08%
c. 음식점 수 5개 미만, 주문 수 30개 이상 → 수수료 1%
d. 음식점 수 5개 미만, 주문 수 30개 미만 → 수수료 2%

SELECT	cuisine_type,
		total_quantity,
		count_of_restaurant,
        CASE
        	WHEN count_of_restaurant >= 5 AND total_quantity >= 30 THEN 0.0005
        	WHEN count_of_restaurant >= 5 AND total_quantity < 30 THEN 0.0008
        	WHEN count_of_restaurant < 5 AND total_quantity >= 30 THEN 0.01
        	WHEN count_of_restaurant < 5 AND total_quantity < 30 THEN 0.02
		END ratio_of_add
FROM (
     SELECT cuisine_type,
         	SUM(quantity) total_quantity,
         	COUNT(DISTINCT restaurant_name) count_of_restaurant
     FROM food_orders
     GROUP BY 1
     ) a

💡 DISTINCT restaurant_name
→ 주문 내역의 음식점 이름이 같은 경우 한 번만 세는 것
→ “한 번이라도 주문이 들어간 음식점 수” (한 곳에 여러 번 주문했을 때 음식점 수를 count한다면 distinct로 중복값을 제거해주는 게 맞음)

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

  • 할인 조건
    • 수량 5개 이하 → 10%
    • 수량 15개 초과, 총 주문 금액 300,000 이상 → 0.5%
    • 이 외에는 일괄 1%
SELECT restaurant_name,
       CASE
        WHEN sum_of_quantity<=5 THEN 0.1
        WHEN sum_of_quantity>15 AND sum_of_price>=300000 THEN 0.005
        ELSE 0.01
      END ratio_of_add
FROM (
     SELECT restaurant_name,
        	SUM(quantity) sum_of_quantity,
        	SUM(price) sum_of_price
     FROM food_orders
     GROUP BY 1
     ) a

D. 필요한 데이터가 서로 다른 테이블에 있을 때 조회하기 (JOIN)

1. JOIN이 필요한 경우

  • 여러 테이블에서 데이터를 불러와야 할 때

2. JOIN의 기본 원리와 종류

  • 기본적으로 엑셀의 VLOOKUP과 유사
  • 예시
고객 ID주문 번호식당 이름고객 이메일
110A
211B
312C
413D

 

고객 ID전화번호이메일연령
10000-0000abc@naver.com20
21111-1111def@kakao.com25
32222-2222ghi@gmail.com30
43333-3333jkl@naver.com35
  • 위와 같은 경우 주문 정보 테이블에서 고객 이메일을 알기 위해서 고객 정보 테이블에 있는 동일한 고객 ID의 이메일을 가져와야 함
    • 엑셀에서는 VLOOKUP(고객ID, 고객 정보, 3, FALSE)라 적을 것
    • JOIN도 동일한 원리: 고객 ID를 기준으로 필요한 값을 가져오는 것
  • 중요한 것은 두 테이블이 공통으로 갖고 있는 컬럼
  • 공통 컬럼을 기준으로 두 테이블을 합쳐 각각 테이블에서 필요한 데이터를 조회할 수 있도록 만들어주는 것
    • JOIN은 하는 방법에 따라 여러 가지가 있음
  • LEFT JOIN: 공통 컬럼(키값)을 기준으로 하나의 테이블에 값이 없더라도 모두 조회되는 경우
주문 번호식당 이름고객 ID전화번호이메일연령
10A10000-0000abc@naver.com20
11B21111-1111def@kakao.com25
12C32222-2222ghi@gmail.com30
13D43333-3333jkl@naver.com35
14D5
  • INNER JOIN: 공통 컬럼(키값)을 기준으로, 두 테이블 모두에 있는 값만 조회
    • 위의 예시에서 주문번호 14의 경우 고객에 대한 정보가 고객 정보에 없으므로 조회 시 제외됨
  • 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.공통컬럼명

💡 공통 컬럼은 묶어주기 위한 ‘공통 값’이기 때문에 컬럼명 달라도 괜찮음
(예) 주문정보 테이블에는 ‘고객 ID’, 고객정보 테이블에는 ‘고객 아이디’라는 컬럼명일 경우 주문정보.고객ID = 고객정보.고객아이디 와 같이 묶을 수 있음

3. [실습] JOIN을 이용하여 두 개의 테이블의 데이터 조회하기

  • 주문 테이블과 고객 테이블을 customer_id를 기준으로 LEFT JOIN으로 묶기
    • 조회 컬럼: order_id, customer_id, restaurant_name, price, name, age, gender
SELECT 	a.order_id,
		a.customer_id,
        a.restaurant_name,
        a.price,
        b.name,
        b.age,
        b.gender
FROM food_orders a LEFT JOIN customers b ON a.customer_id = b.customer_id

E. [실습] JOIN으로 두 테이블의 데이터 조회하기

1. 한국 음식의 주문별 결제 수단과 수수료율

a. 조회 컬럼: 주문 번호, 식당 이름, 주문 가격, 결제 수단, 수수료율
b. 결제 정보가 없는 경우도 포함하여 조회

SELECT 	a.order_id,
    	a.restaurant_name,
    	a.price,
    	b.pay_type,
    	b.vat
FROM food_orders a LEFT JOIN payments b ON a.order_id = b.order_id

2. 고객의 주문 식당 조회

a. 조회 컬럼: 고객 이름, 연령, 성별, 주문 식당
b. 고객명으로 정렬, 중복 없도록 조회

SELECT 	c.name ,
    	c.age ,
    	c.gender ,
    	fo.restaurant_name 
FROM food_orders fo LEFT JOIN customers c ON fo.customer_id = c.customer_id 
ORDER BY c.name

※ null 값을 제거하려면

SELECT c.name ,
	   c.age ,
       c.gender ,
       fo.restaurant_name 
FROM food_orders fo LEFT JOIN customers c ON fo.customer_id = c.customer_id 
WHERE c.name IS NOT NULL // null값 제거
ORDER BY c.name

F. [실습] JOIN으로 두 테이블의 값 연산하기

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

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

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

2. 50세 이상 고객의 연령에 따라 경로 할인율을 적용하고, 음식 타입별로 원래 가격과 할인 적용 가격 합을 구하기

a. 조회 컬럼: 음식 타입, 원래 가격, 할인 적용 가격, 할인 가격
b. 할인: (나이-50)*0.005
c. 고객 정보가 없는 경우도 포함하여 조회, 할인 금액이 큰 순서대로 정렬

SELECT cuisine_type,
       SUM(price) "원래 가격",
       SUM(price)-SUM(discount_price) "할인 적용 가격",
       SUM(discount_price) "할인 가격" 
FROM
(
SELECT fo.cuisine_type,
       price,
       price*((c.age-50)*0.005) discount_price
FROM food_orders fo INNER JOIN customers c ON fo.customer_id = c.customer_id
WHERE c.age >= 50
) t
GROUP BY 1
ORDER BY 4 DESC

4주차 숙제

  • 식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation하기
    • 평균 음식 주문 금액 기준
      • 5,000
      • 10,000
      • 30,000
      • 30,000 초과
    • 평균 연령
      • ~ 20대
      • 30대
      • 40대
      • 50대 이상
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'
       END price_group,
       CASE 
	       WHEN age <30 THEN 'age_group1'
         WHEN age BETWEEN 30 AND 39 THEN 'age_group2'
         WHEN age BETWEEN 40 AND 49 THEN 'age_group3'
         ELSE 'age_group4'
       END age_group
FROM
(
SELECT fo.restaurant_name,
       AVG(price) price,
       AVG(age) age
FROM food_orders fo INNER JOIN customers c ON fo.customer_id = c.customer_id
GROUP BY 1
) t
ORDER BY 1
profile
Suhyeon Lee
2 B R 0 2 B
이전 포스트

3주차

다음 포스트

1주차(2)

0개의 댓글