[SQL 기초] 4주차 강의

 

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문을 이용하여 평균 배달 시간을 범위를 지정하여 세분화했다.

학습질문에서 group by에 대한 질문이 있어 도움이 될 것 같아 캡쳐해옴

☑️ 복잡한 연산을 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개월을 달린다!! 아자아자ㅠㅠ! 글을 쓰다보니 날짜가 넘어가면서 오늘이 시작 날이 되었는데 졍말.. 설렘뽕짝하면서도 어려움이 있을거라 너무 손가락이 떨린다..^-^