[240914] SQL 사전강의 (~4강)

과제에서 필수 사용되는 문법 요약

• [KDC] 엑셀보다 쉽고 빠른 SQL - 4주차

  • 02. 여러 번의 연산을 한 번의 SQL 문으로 수행하기 (Subquery)

    • [실습] Subquery 문을 이용하여 연산문 적어보기
      • 배달완료 시간이 50분보다 초과한 주문 체크

        select order_id,
        	   restaurant_name,
        	   IF(over_time >= 0, over_time, 0) as sorry
        from
        (select order_id,
        		restaurant_name,
        		(delivery_time + food_preparation_time) - 50 as over_time
        from food_orders fo ) overtime ;

  • 03. [실습] User Segmentation 와 조건별 수수료를 Subquery 로 결합해보기

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

       • 수수료 구간

         ~5000원	0.5%
         ~20000원	1%
         ~30000원	2%
         30000원 ~	3%

         select restaurant_name,
                price_per_plate*ratio_of_add "수수료"
         from 
         (
         select restaurant_name,
                case when price_per_plate<5000 then 0.005
                     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,
                price_per_plate
         from 
         (
         select restaurant_name, avg(price/quantity) price_per_plate
         from food_orders
         group by 1
         ) a
         ) b

         ⇒ 오답

         select restaurant_name,
         	   case when avg < 5000 then 0.05%
         	        when avg between 5000 and 20000 then 1%
         	        when avg between 20001 and 30000 then 2%
         	        else 3% end as "수수료"
         FROM 
         (select restaurant_name, AVG(price) as avg
         from food_orders fo 
         group by restaurant_name) a ;

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

       select restaurant_name,
       			 sido,
       			 avg_time,
       			 case when avg_time <20 then 'fast'
       					  when avg_time between 20 and 30 then 'normal'
       					  else 'slow' end as time_seg
       from
       (select restaurant_name,
       			 substr(addr,1,2) as sido,
       			 avg(delivery_time) as avg_time
       from food_orders
       group by 1,2
       ) a;

       ⇒ 오답

       select case when addr like "%특별%" then substr(addr,1,2)
       			when addr like "%직할%" then substr(addr,1,2)
       			when addr like "%광역%" then substr(addr,1,2)
        			else substr(addr,1,3) end as region,
       			avg(delivery_time) as avg_delivery_time
       from food_orders fo
       group by 1;

  • 04. [실습] 복잡한 연산을 Subquery 로 수행하기

    1. [실습] 음식 타입별 총 주문수량과 음식점 수를 연산하고, 주문수량과 음식점수 별 수수료율을 산정하기

       • 음식점수 5개 이상, 주문수 30개 이상 → 수수료 0.05%
       • 음식점수 5개 이상, 주문수 30개 미만 → 수수료 0.08%
       • 음식점수 5개 미만, 주문수 30개 이상 → 수수료 1%
       • 음식점수 5개 미만, 주문수 30개 미만 → 수수로 2%

       select *,
       	   case when tot_restaurant < 5 and tot_quantity < 30 then 0.02
       	   		when tot_restaurant < 5 and tot_quantity >= 30 then 0.01
       	   		when tot_restaurant >= 5 and tot_quantity < 30 then 0.0008
       	   		when tot_restaurant >= 5 and tot_quantity >= 30 then 0.0005
       	   		end as "수수료율"
       from
       (select cuisine_type,
       	   sum(quantity) as tot_quantity,
       	   COUNT(DISTINCT restaurant_name) as tot_restaurant
       FROM  food_orders fo
       group by 1
       ) a;

       ⇒ 오답

       select *,
       	   case when tot_restaurant < 5 and tot_quantity < 30 then 0.02
       	   		when tot_restaurant < 5 and tot_quantity >= 30 then 0.01
       	   		when tot_restaurant >= 5 and tot_quantity < 30 then 0.0008
       	   		when tot_restaurant >= 5 and tot_quantity >= 30 then 0.0005
       	   		end as "수수료율"
       from
       (select cuisine_type,
       	   sum(quantity) as tot_quantity,
       	   COUNT(restaurant_name) as tot_restaurant
       FROM  food_orders fo
       group by 1
       ) a;

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

       • 할인조건
         • 수량 5개 이하 → 10%
         • 수량 15개 초과, 총 주문금액이 300,000 이상 → 0.5%
         • 이 외에는 일괄 1%

       SELECT *,
       	   case when tot_qty > 15 and tot_price >= 300000 then 0.005
       	   		when tot_qty <= 5 then 0.1
       	   		else 0.01 end as "할인율"
       FROM 
       (SELECT restaurant_name,
       	   sum(quantity) as tot_qty,
       	   sum(price) as tot_price
       FROM food_orders fo 
       group by 1
       ) a;

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

    1. [실습] JOIN 을 이용하여 두 개의 테이블에서 데이터를 조회해보기

       • 주문 테이블과 고객 테이블을 cusomer_id 를 기준으로 left join 으로 묶어보기
         • (조회 컬럼 : order_id, customer_id, restaurant_name, price, name, age, gender)

       select fo.order_id , fo.customer_id , fo.restaurant_name , fo.price , c.name , c.age , c.gender 
       from food_orders fo 
       left join customers c 
       on fo.customer_id = c.customer_id ;

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

    1. [실습] 한국 음식의 주문별 결제 수단과 수수료율을 조회하기

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

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

    2. [실습] 고객의 주문 식당 조회하기

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

       select DISTINCT c.name , c.gender , c.age , fo.restaurant_name 
       from customers c 
       left join food_orders fo on c.customer_id = fo.customer_id
       order by c.name ;

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

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

         • 수수료율이 있는 경우만 조회

           select fo.order_id , fo.restaurant_name , fo.price , p.vat , fo.price * p.vat as ratio
           from food_orders fo 
           inner join payments p on fo.order_id = p.order_id;

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

         • 할인 : (나이-50)*0.005

         • 고객 정보가 없는 경우도 포함하여 조회, 할인 금액이 큰 순서대로 정렬

           SELECT cuisine_type,
           	   SUM(price) as "원래가격" ,
           	   sum(price) - sum(discount_price) as "할인적용가격",
           	   SUM(discount_price) as "할인가격"
           FROM 
           (select fo.cuisine_type ,
           	   fo.price ,
           	   fo.price * ((c.age - 50) * 0.005) discount_price
           FROM food_orders fo 
           left join customers c on fo.customer_id = c.customer_id 
           where c.age >= 50
           ) a
           group by 1
           order by 4 desc;

           ⇒ 오답

           select fo.cuisine_type,
           	   fo.price , 
           	   fo.price * (1 - ((c.age - 50) * 0.005)) as final_price,
           	   fo.price * ((c.age - 50) * 0.005) as dc_price
           FROM food_orders fo 
           left join customers c on fo.customer_id = c.customer_id 
           where c.age >= 50
           group by fo.cuisine_type 
           order by dc_price desc;

  • HW. 4주차 숙제 해설

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

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

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

               SELECT *,
                      case when avg_price < 5000 then '<5000'
                           when avg_price between 5000 and 9999 then '5000<x<10000'
                           when avg_price BETWEEN 10000 and 29999 then '10000<x<30000'
                           else '>30000' end as price_range,
                      case when avg_age < 20 then '10대'
                           when avg_age between 20 and 29 then '20대'
                           when avg_age BETWEEN 30 and 39 then '30대'
                           when avg_age BETWEEN 40 and 49 then '40대'	   		
                           else '50대 이상' end as age_range
               FROM 
               (select fo.restaurant_name ,
                      AVG(fo.price) as avg_price,
                      avg(c.age) as avg_age
               FROM food_orders fo 
               inner join customers c on fo.customer_id = c.customer_id
               group by 1
               ) a

오답노트

• 문제 잘 읽기, %는 소수로 변환해주어야 함.
  1. [실습] 음식점의 평균 단가별 segmentation 을 진행하고, 그룹에 따라 수수료 연산하기
     • 수수료 구간

       ~5000원	0.5%
       ~20000원	1%
       ~30000원	2%
       30000원 ~	3%

       • 정답

         select restaurant_name,
                price_per_plate*ratio_of_add "수수료"
         from 
         (
         select restaurant_name,
                case when price_per_plate<5000 then 0.005
                     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,
                price_per_plate
         from 
         (
         select restaurant_name, avg(price/quantity) price_per_plate
         from food_orders
         group by 1
         ) a
         ) b

       • 오답

         select restaurant_name,
         	   case when avg < 5000 then 0.05%
         	        when avg between 5000 and 20000 then 1%
         	        when avg between 20001 and 30000 then 2%
         	        else 3% end as "수수료"
         FROM 
         (select restaurant_name, AVG(price) as avg
         from food_orders fo 
         group by restaurant_name) a ;

         1. % → 0.0n 변경

            • 쿼리문 내에서 %는 인식 못하므로, 소수로 변경 필요.

         2. 음식 단가 = price/quantity

            • AVG(Price) 가 아닌 AVG(price/quantity) 로 정정 필요

         3. 구해야 할 것 : 수수료 (O) / 수수료율 (X)

            • 음식 단가에 수수료율을 곱한 쿼리를 작성해야 함.

            Select restaurant_name, **price_per_plate*ratio_of_add** as "수수료"
            from
            (Select restaurant_name,price_per_plate,
            			 case when price_per_plate < 5000 then **0.005**
            						when price_per_plate between 5000 and 20000 then **0.01**
            						when price_per_plate between 20000 and 30000 then **0.02**
            						else 0.03 end as ratio_of_add
            from
            (Select restaurant_name, **avg(price/quantity)** as price_per_plate
            from food_orders
            group by 1
            ) a
            ) b;

• Distinct : 중복 제거
  1. [실습] 음식 타입별 총 주문수량과 음식점 수를 연산하고, 주문수량과 음식점수 별 수수료율을 산정하기
     • 음식점수 5개 이상, 주문수 30개 이상 → 수수료 0.05%
     • 음식점수 5개 이상, 주문수 30개 미만 → 수수료 0.08%
     • 음식점수 5개 미만, 주문수 30개 이상 → 수수료 1%
     • 음식점수 5개 미만, 주문수 30개 미만 → 수수로 2%
     • 정답

       select *,
       	   case when tot_restaurant < 5 and tot_quantity < 30 then 0.02
       	   		when tot_restaurant < 5 and tot_quantity >= 30 then 0.01
       	   		when tot_restaurant >= 5 and tot_quantity < 30 then 0.0008
       	   		when tot_restaurant >= 5 and tot_quantity >= 30 then 0.0005
       	   		end as "수수료율"
       from
       (select cuisine_type,
       	   sum(quantity) as tot_quantity,
       	   COUNT(DISTINCT restaurant_name) as tot_restaurant
       FROM  food_orders fo
       group by 1
       ) a;

     • 오답

       select *,
       	   case when tot_restaurant < 5 and tot_quantity < 30 then 0.02
       	   		when tot_restaurant < 5 and tot_quantity >= 30 then 0.01
       	   		when tot_restaurant >= 5 and tot_quantity < 30 then 0.0008
       	   		when tot_restaurant >= 5 and tot_quantity >= 30 then 0.0005
       	   		end as "수수료율"
       from
       (select cuisine_type,
       	   sum(quantity) as tot_quantity,
       	   COUNT(restaurant_name) as tot_restaurant
       FROM  food_orders fo
       group by 1
       ) a;

       1. 음식 종류별 음식점 수를 알기 위해선 그 동안의 음식 주문건에서 조회되는 음식점들의 중복을 제거해주어야 함.
          • Distinct 사용

            select *,
            	   case when tot_restaurant < 5 and tot_quantity < 30 then 0.02
            	   		when tot_restaurant < 5 and tot_quantity >= 30 then 0.01
            	   		when tot_restaurant >= 5 and tot_quantity < 30 then 0.0008
            	   		when tot_restaurant >= 5 and tot_quantity >= 30 then 0.0005
            	   		end as "수수료율"
            from
            (select cuisine_type,
            	   sum(quantity) as tot_quantity,
            	   COUNT(**Distinct** restaurant_name) as tot_restaurant
            FROM  food_orders fo
            group by 1
            ) a;

• 논리 순서에 따라 구조화 필요
  1. [실습] 50세 이상 고객의 연령에 따라 경로 할인율을 적용하고, 음식 타입별로 원래 가격과 할인 적용 가격 합을 구하기
     • 조회 컬럼 : 음식 타입, 원래 가격, 할인 적용 가격, 할인 가격
       • 할인 : 나이-50*0.005
       • 고객 정보가 없는 경우도 포함하여 조회, 할인 금액이 큰 순서대로 정렬
       • 정답

         SELECT cuisine_type,
         	   SUM(price) as "원래가격" ,
         	   sum(price) - sum(discount_price) as "할인적용가격",
         	   SUM(discount_price) as "할인가격"
         FROM 
         (select fo.cuisine_type ,
         	   fo.price ,
         	   fo.price * ((c.age - 50) * 0.005) discount_price
         FROM food_orders fo 
         left join customers c on fo.customer_id = c.customer_id 
         where c.age >= 50
         ) a
         group by 1
         order by 4 desc;

       • 오답

         select fo.cuisine_type,
         	   fo.price , 
         	   fo.price * (1 - ((c.age - 50) * 0.005)) as final_price,
         	   fo.price * ((c.age - 50) * 0.005) as dc_price
         FROM food_orders fo 
         left join customers c on fo.customer_id = c.customer_id 
         where c.age >= 50
         group by fo.cuisine_type 
         order by dc_price desc;

         1. 위 쿼리 실행 시, cuisine_type 중 임의의 price 가 조회되므로, cuisine_type 별 각 price의 총합을 알 수 없음.

         2. 그러므로 우선 50대 이상 고객의 전체 주문 중 cuisine_type, price, discount_price 가 조회되는 쿼리를 작성 후 서브쿼리로 이용해야 함.

            SELECT cuisine_type,
            	   SUM(price) as "원래가격" ,
            	   sum(price) - sum(discount_price) as "할인적용가격",
            	   SUM(discount_price) as "할인가격"
            FROM 
            (select fo.cuisine_type ,
            	   fo.price ,
            	   fo.price * ((c.age - 50) * 0.005) discount_price
            FROM food_orders fo 
            left join customers c on fo.customer_id = c.customer_id 
            where c.age >= 50
            ) a
            group by 1
            order by 4 desc;