[내일배움캠프-사전캠프 07일차] SQL 강의

1. 4주차 3 강의 실습

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

- 단계별로 찾는다. 라고 생각하면 쉽다.

- 1단계 - 식당이름, 시도, avg() 평균시간만 추출

- 2단계 - 평균 배달시간 별로 그분화.

일단은 GROUP BY 절을 사용할때,

SELECT restaurant_name, SUBSTR(addr,1,2) as sido, avg(delivery_time)
From food_orders fo 
group by 1,2

sido이라는 열을 넣을지 안 넣을지 고민했다.

• restaurant_name만 기준으로 그룹화됨.
• SUBSTR(addr,1,2) as "지역"은 SELECT 문에서 사용되지만, GROUP BY에는 포함되지 않았기 때문에 그룹의 대표값(임의의 값)이 반환됨.
• 즉, 같은 restaurant_name을 가진 모든 행이 하나의 그룹이 되며, 지역 컬럼 값은 여러 개가 있을 경우 랜덤한 값 하나가 표시될 가능성이 있음.

지역이라는 열을 넣을 경우는

• restaurant_name AND 지역(즉, SUBSTR(addr,1,2))을 기준으로 그룹화됨.
• 즉, 같은 restaurant_name이라도 지역이 다르면 별도의 그룹으로 취급됨.
• 따라서 첫 번째 쿼리보다 더 많은 그룹이 생길 가능성이 큼.

이렇게 된다. 그래서 지역이라는 열을 넣어서 포함해야 된다.

SELECT restaurant_name, sido, 
    CASE WHEN avg_time <=20 then '<=20'
         when avg_time>20 and avg_time <=30 then '20<x<=30'
         when avg_time>30 then '>30' end time_segment
FROM(
SELECT restaurant_name, SUBSTR(addr,1,2) as sido, avg(delivery_time) as avg_time
From food_orders fo 
group by 1,2
) as a

2. 4주차 4강의

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

select cuisine_type, 
                total_quantity,
                count_of_restautant,
       case when count_of_restautant>=5 and total_quantity>=30 then 0.005
            when count_of_restautant>=5 and total_quantity<30 then 0.008
            when count_of_restautant<5 and total_quantity>=30 then 0.01
            when count_of_restautant<5 and total_quantity<30 then 0.02 end rate
from
(
select cuisine_type,
       sum(quantity) total_quantity,
       count(distinct restaurant_name) count_of_restautant
from food_orders
group by 1
) a

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

- 1단계: 총주문수량, 주문금액 연산.

- 2단계: 조건 별로 수수료 할인율 구하기

SELECT restaurant_name,total_quantity,total_price,
CASE WHEN total_quantity <= 5 then 0.1
     WHEN total_quantity > 15 AND total_price >= 300000 then 0.005
     ELSE 0.01 end discount
FROM(
SELECT restaurant_name, sum(quantity) total_quantity, sum(price) total_price 
FROM food_orders fo 
group by restaurant_name ) a

3. 4주차 4강의 Join

- 공통 컬럼을 기준으로 묶는 것.

- LEFT JOIN: A는 모두 가져오는데, B 같은 경우는 A와 공통된 부분만 가져오겠다.

- INNER JOIN: A와 B의 공통된 부분만 가져오겠다.

- payments랑 food_orders 는 order_id가 겹침

- food_orders랑 customer 는 customer_id가 겹침

4. 4주차 7강의

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

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

1단계 50세 이상 고객 연령별로 경로 할인율 적용.

2단계 음식 타입별로, 원래가격과 할인율 적용된 가격 합산 구하기

SELECT cuisine_type, sum(price) total_price, sum(discount) total_discount, sum(discounted) total_discounted
FROM
(
SELECT f.cuisine_type, f.price,(c.age-50)*0.005*price as discount, f.price-(c.age-50)*0.005*price as discounted
FROM food_orders f LEFT JOIN customers c ON f.customer_id = c.customer_id 
WHERE c.age>= 50) a
GROUP BY 1
ORDER BY 3 DESC

5. 4주차 숙제

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

1단계 - 식당별 평균 주문 금액, 평균 연령 조회, customers 테이블과 연결

2단계 - Segmentation

SELECT restaurant_name,
CASE WHEN agv_price <= 5000 then 'price_group1'
     WHEN agv_price <= 10000 then 'price_group2'
     WHEN agv_price <= 30000 then 'price_group3'
     WHEN agv_price > 30000 then 'price_group4' end 'price_group',
CASE WHEN agv_age < 30 then 'age_group1'
     WHEN agv_age < 40 then 'age_group2'
     WHEN agv_age < 50 then 'age_group3'
     WHEN agv_age >= 50 then 'age_group4' end 'age_group'
FROM(
SELECT f.restaurant_name, AVG(f.price) agv_price, AVG(c.age) agv_age
FROM food_orders f INNER JOIN customers c ON f.customer_id = c.customer_id 
GROUP BY 1
) a
ORDER BY 1

앞 조건이 충족되면 그 뒤의 조건을 확인하지 않음. 즉, CASE 문은 첫 번째로 만족하는 조건을 만나면 바로 실행을 종료해요

6. 5주차 1강의

1) coalesce(age,대체값)

- 다른 값으로 대체한다는 것.

2) if(조건, 원래값, 대체값)

- if 문으로도 가능

7. 5주차3 강의

- 값이 비이상적일 때, 대체 값을 설정한다.

SELECT name, age
CASE WHEN age < 15 then 15
     WHEN age >= 80 then 80
     else age end re_age
FROM customers

8. 5주차 4강의 - Pivot Tabel

- Pivot Table이란 ? 2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여주는 것을 의미

- 베이스 데이터 베이스를 만들고 > 그걸로 Pivot table을 만든다.

- 행에 올 것을 먼저 열로 써주어야 한다.

- max로 항상 덮어줘야한다.

- Pivot Tabel 실습 1

select restaurant_name,
       max(if(hh='15', cnt_order, 0)) "15",
       max(if(hh='16', cnt_order, 0)) "16",
       max(if(hh='17', cnt_order, 0)) "17",
       max(if(hh='18', cnt_order, 0)) "18",
       max(if(hh='19', cnt_order, 0)) "19",
       max(if(hh='20', cnt_order, 0)) "20"
from 
(
select a.restaurant_name,
       substring(b.time, 1, 2) hh,
       count(1) cnt_order
from food_orders a inner join payments b on a.order_id=b.order_id
where substring(b.time, 1, 2) between 15 and 20
group by 1, 2
) a
group by 1
order by 7 desc

- Pivot Table 실습 2

select age,
       max(if(gender='male', order_count, 0)) male,
       max(if(gender='female', order_count, 0)) female
from 
(
select b.gender,
       case when age between 10 and 19 then 10
            when age between 20 and 29 then 20
            when age between 30 and 39 then 30
            when age between 40 and 49 then 40
            when age between 50 and 59 then 50 end age,
       count(1) order_count
from food_orders a inner join customers b on a.customer_id=b.customer_id
where b.age between 10 and 59
group by 1, 2
) t
group by 1
order by 1 desc

9. 5주차 5강의 - Window 함수

1) Window Function - 윈도우 함수

- window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)

1) rank() over

- rank() over (partition by 그룹 기준 컬럼 order by 정렬 기준)

- 특정 기준으로 순위를 매겨주는 기능임

2) Sum() over

- Sum() over (partition by 그룹 기준 컬럼 order by 정렬 기준)

- 누적 합을 구하는 기능임.

10. 5주차 6강의 - 날짜 포매팅

1) date(date)

- yyyy-mm-dd 형식의 컬럼을 date type으로 변경해줌.

2) date_format

date_format(date(date), '%Y') "년",
date_format(date(date), '%m') "월",
date_format(date(date), '%d') "일",
date_format(date(date), '%w') "요일"

 

11. 5주차 숙제

- 음식 타입별, 연령별 주문건수 pivot view 만들기 (연령은 10~59세 사이)

- 1단계 베이스가 될 데이터 만들기

SELECT f.cuisine_type, 
CASE WHEN c.age >= 10 AND c.age < 20 then '10대'
	 WHEN c.age >= 20 AND c.age < 30 then '20대'
	 WHEN c.age >= 30 AND c.age < 40 then '30대'
	 WHEN c.age >= 40 AND c.age < 50 then '40대'
	 else '50대' end age,
	 count(1) count
FROM food_orders f INNER JOIN customers c on f.customer_id = c.customer_id 
GROUP BY 1,2

- 2단계 피벗테이블 만들기

select cuisine_type,
       max(if(age=10, order_count, 0)) "10대",
       max(if(age=20, order_count, 0)) "20대",
       max(if(age=30, order_count, 0)) "30대",
       max(if(age=40, order_count, 0)) "40대",
       max(if(age=50, order_count, 0)) "50대"
from (...)
group by 1

---

오늘의회고

- SQL 강의를 다 끝냈다. 원래 이번주 안에 달리기 반을 끝낼려고 했으나, 사정상 그러지 못해 아쉽다.

- 이번 주말에 추가적으로 달리기반 문제를 풀 예정이다. 

- 얼른 끝내고 fetch 강의 듣고 싶다...