2024. 1. 17. 13:02ㆍProgramming/데이터베이스
4주차
03. [실습] User Segmentation와 조건별 수수료를 Subquery로 결합해보기
[실습1] 음식점의 평균 단가별 segmentation을 진행하고, 그룹에 따라 수수료 연산하기
(수수료 구간 - ~5000원 미만 0.05%, ~20000원 미만 1%, ~30000원 미만 2%, 30000원 초과 3%)
select restaurant_name, avg_price * avg_fee_rate as fee from
(
select restaurant_name, avg_price,
case
when avg_price < 5000 then 0.0005
when avg_price < 20000 then 0.01
when avg_price < 30000 then 0.02
when avg_price >= 30000 then 0.03
end as avg_fee_rate
from
(
select restaurant_name, avg(price/quantity) as avg_price
from food_orders group by restaurant_name
) avg_price_result
) avg_fee_rate_result
식당별 평균단가를 구하기 위해 'avg_price_result' subquery를 작성하였다. 식당을 그룹으로 묶은 후 price / quantity 개수별 가격을 avg 함수를 통해 평균 단가를 만들었고 이를 avg_price 이름으로 명칭하였다.
avg_price의 결과를 사용하여 범위별 수수료율을 구하기 위해 'avg_fee_rate_result' subquery를 작성하였다. 수수료율을 구하기 위해 case문을 사용하였으며, 해당 column을 avg_fee_rate 이름으로 명칭하였다. avg_fee_rate_result를 사용하여 avg_price * avg_fee_rate를 곱하여 수수료 fee를 계산한다.
[실습2] 음식점의 지역과 평균 배달시간으로 segmentation 하기
select restaurant_name, region_name,
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 as time_segment
from
(
select restaurant_name, substr(addr, 1, 2) as region_name, avg(delivery_time) avg_time from food_orders
group by restaurant_name, region_name
) avg_delivery_time음식점, 지역을 그룹으로 평균 배달시간 avg_time을 구하는 subquery를 생성한다. 지역은 서울, 경기, 광주처럼 주소 앞에 2글자를 추출하여 시, 도 명칭을 구한다. subquery의 avg_time을 사용하여 case문을 통해 segmentation 한다.
04. [실습] 복잡한 연산을 Subquery로 수행하기
1. [실습] 음식 타입별 지역별 총 주문수량과 음식점 수를 연산하고, 주문 수량과 음식점수 별 수수료율을 산정하기
음식 타입별, 지역별을 그룹으로 select를 진행한다. 지역은 위에 마찬가지로 substr(addr, 1, 2) as region을 통해 주소 앞에 2글자를 가져온다. 총 주문수량은 quantity 칼럼을 sum 함수를 호출하여 구한다. 음식점 수는 count(distinct restaurant_name)을 사용하여 구한다.
2. [실습] 음식점의 총 주문수량과 주문 금액을 연산하고, 주문 수량을 기반으로 수수료 할인율 구하기
(할인 조건. 수량이 5개 이하 -> 10%, 수량이 15개 초과, 총 주문금액이 300000 이상 -> 0.5%, 이 외에는 일괄 1%)
select restaurant_name, sum_quantity, sum_price,
case
when sum_quantity < 5 then 10 * 0.01
when sum_quantity > 15 and sum_price > 300000 then 5 * 0.01
else 0.01
end as fee_discount_ratio
from
(
select restaurant_name, sum(quantity) sum_quantity, sum(price) sum_price from food_orders
group by restaurant_name order by restaurant_name asc
) sub
restaurant_name을 group으로 하여 quantity, price를 더하여 sum 값을 생성하는 subquery를 생성하였고 이를 기반으로 조건 별 segmentation을 출력할 수 있도록 하였다.
05. 필요한 데이터가 서로 다른 테이블에 있을 때 조회하기 (JOIN)
[실습1] 주문 테이블과 고객 테이블을 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
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
주문 번호, 식당 이름, 주문 가격은 food_orders 테이블에, 주문 번호, 결제 수단, 수수료율는 payments 테이블에 저장되어 있다. 주문 번호 order_id를 사용하여 join한 후 해당 테이블을 출력한다.
[실습2] 고객의 주문 식당 조회하기
(조회 컬럼 : 고객 이름, 연령, 성별, 주문 식당),
* 고객명으로 정렬, 중복 없도록 조회
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
group by c.customer_id, fo.restaurant_name
order by c.name asc
food_orders와 customer 테이블의 customer_id를 key로 join 후 c.customer_id, fo.restraurant_name을 group하여 고객별 주문한 가게를 중복되지 않도록 하였다.
07. [실습] JOIN으로 두 테이블의 값을 연산하기
[실습] 주문 가격과 수수료율을 곱하여 주문별 수수료 구하기
(조회 컬럼 : 주문번호, 식당 이름, 주문 가격, 수수료율, 수수료)
select fo.order_id, fo.restaurant_name , fo.price , p.vat,
fo.price * p.vat as fee
from payments p left join food_orders fo on fo.order_id = p.order_id
[실습] 50세 이상 고객의 연령에 따라 경로 할인율을 적용하고, 음식 타입별로 원래 가격과 할인 적용 가격 합을 구하기
(조회 컬럼 : 음식 타입, 원래 가격, 할인 적용 가격, 할인 가격)
* 할인 : 나이 - 50 * 0.005
* 고객 정보가 없는 경우도 포함하여 조회, 할인 금액이 큰 순서대로 정렬
select cuisine_type, price, discount_ratio,
price * discount_ratio as discount_price,
price - (price * discount_ratio) as discount_appied_price from
(
select fo.cuisine_type, fo.price, c.name,
if(c.age> 50, (c.age - 50) * 0.005, 0) as discount_ratio
from food_orders fo left join customers c on fo.customer_id = c.customer_id
) sub
order by discount_price desc
할인율을 subquery를 사용하여 계산 후 반환하여 main query에서 해당 컬럼 값을 사용할 수 있도록 하였다.
08. Homework
식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation 하기
- 평균 음식 주문 금액 기준 : 5000 / 10000 / 30000 / 30000 초과
- 평균 연령 : ~20대 / 30대 / 40대 / 50대
select restaurant_name, cuisine_type, price, name, avg_price, avg_age,
case
when avg_price between 0 and 5000 then 'price_0_to_5000'
when avg_price between 5000 and 10000 then 'price_5000_to_10000'
when avg_price between 10000 and 30000 then 'price_10000_to_30000'
when avg_price > 30000 then 'price_30000_to'
end price_seg,
case
when avg_age between 0 and 29 then '~20s'
when avg_age between 30 and 39 then '30s'
when avg_age between 40 and 49 then '40s'
when avg_age >= 50 then '50s~'
end
from
(
select fo.restaurant_name, fo.cuisine_type, fo.price, c.name, avg(fo.price) as avg_price, avg(c.age) as avg_age
from food_orders fo left join customers c on fo.customer_id = c.customer_id
group by fo.restaurant_name
) sub
order by restaurant_name asc
5주차
04. [실습] SQL로 Pivot Table 만들어보기
Pivot Table은 다음과 같이 "구분컬럼", "집계기준"으로 Column, Row를 만들어서 출력하는 형태
[실습1] 음식점별 시간별 주문건수 Pivot Table 뷰 만들기 (15~20시 사이, 20시 주문건수 기준 내림차순)
음식별과 시간별로 group을 한 후 subquery로 반환한 후에 main table에 if 문으로 시간대별로 컬럼을 나누는 SQL을 작성하고 출력을 하면 다음과 같이 출력된다.
select name, hh, restaurant_hh_count,
if(hh < 15, restaurant_hh_count, 0) as from_15,
if(hh='15', restaurant_hh_count, 0) as '15',
if(hh='16', restaurant_hh_count, 0) as '16',
if(hh='17', restaurant_hh_count, 0) as '17',
if(hh='18', restaurant_hh_count, 0) as '18',
if(hh='19', restaurant_hh_count, 0) as '19',
if(hh='20', restaurant_hh_count, 0) as '20',
if(hh > 20, restaurant_hh_count, 0) as '20_to'
from
(
select fo.restaurant_name name, p.date, p.time, substring(p.time, 1, 2) hh, count(1) restaurant_hh_count
from food_orders fo inner join payments p on fo.order_id = p.order_id
group by fo.restaurant_name, hh
) sub
order by name
위의 경우 '18'을 예를 들었을 때 Bareburger에서 hh = 18인 경우를 제외하면 0으로 출력된다. 이름으로 group했을 때 '18' 컬럼에 값이 있는 '2'가 출력하고 싶으므로 if 문을 max함수로 묶어 최대값만 출력될 수 있도록 한다.
select name, hh, restaurant_hh_count,
max(if(hh < 15, restaurant_hh_count, 0)) _from_15,
max(if(hh='15', restaurant_hh_count, 0)) _15,
max(if(hh='16', restaurant_hh_count, 0)) _16,
max(if(hh='17', restaurant_hh_count, 0)) _17,
max(if(hh='18', restaurant_hh_count, 0)) _18,
max(if(hh='19', restaurant_hh_count, 0)) _19,
max(if(hh='20', restaurant_hh_count, 0)) _20,
max(if(hh > 20, restaurant_hh_count, 0)) _20_to
from
(
select fo.restaurant_name name, p.date, p.time, substring(p.time, 1, 2) hh, count(1) restaurant_hh_count
from food_orders fo inner join payments p on fo.order_id = p.order_id
group by fo.restaurant_name, hh
) sub
group by name
order by _20 desc
name을 group으로 묶은 후 max함수를 각 컬럼에 적용하여 값이 출력될 수 있도록한다.
[실습2] 성별, 연령별 주문건수 Pivot Table 뷰 만들기 (나이는 10~59세 사이, 연령 순으로 내림차순)
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 age고객의 성별 'gender' 컬럼과 10~59세 까지 10살 단위로 나눈 segmentation 'age' 컬럼을 group하고 해당 주문건수를 count(1) 함수를 통해 계산하는 subquery를 생성하였다. 해당 subquery는 다음과 같이 결과를 출력한다.
여기서 male, female을 column으로 이동 시키기 위해 main query에서는
if(gender='male', order_count, 0) male, if(gender=‘female', order_count, 0) female을 통해 column을 생성한다. age를 group화 하지 않고, max함수를 호출하지 않으면 다음과 같이 출력된다.
age를 그룹화하고 male, female 값에 max값을 적용하면 다음과 같이 Pivot Table이 정상적으로 생성된다.
05. 업무 시작을 단축시켜 주는 마법의 문법 (Window Function - RANK, SUM)
Window Function이란?
각 행의 관계를 정의하기 위한 함수로 그룹 내의 연산을 쉽게 만들어준다.
사례
* 한식 식당 중에서 주문건수가 많은 순으로 순위를 매기고 싶다.
* 한식 식당 전체 주문건수 중에서 A 식당이 차지하는 비율을 알고 싶다.
* 2건 이상 주문을 한 소비자 중에, 처음 주문한 식당과 2번째로 주문한 식당을 같이 조회하고 싶다.
Window Function 기본 구조
window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)* window_function : 기능 명을 사용해줍니다.(sum, avg와 같이 기능명이 있음)
* argument : 함수에 따라 작성 혹은 생략
* partition by : 그룹을 나누기 위한 기준. group by 절과 유사
* order by : 정렬 기준
[실습1] N 번째까지의 대상을 조회하고 싶을 때, RANK
음식 타입별로 주문 건수가 가장 많은 상점 3개씩 조회
select cuisine_type, restaurant_name, order_count, rn from
(
select cuisine_type, restaurant_name,
rank() over (partition by cuisine_type order by order_count desc) rn,
order_count
from (
select count(1) as order_count, fo.cuisine_type, fo.restaurant_name
from food_orders fo group by fo.cuisine_type, fo.restaurant_name
) sub1
) sub2
where rn <= 3
order by cuisine_type, rn
먼저 가장 안쪽의 subquery인 sub1을 살펴보면 음식 타입, 식당별 주문 건수를 반환한다. 해당 쿼리는 다음과 같이 값을 반환한다.
rn 컬럼의 rank window function을 살펴본다. partition은 cuisine_type으로 설정하였고 order_count의 내림 차순으로 정렬하였다. 쿼리를 하면 다음과 같은 결과가 출력된다.
rn은 cusine_type별 order_count가 가장 많은 순서에 따라 1,2,3... 순위를 매긴다. 위 결과를 예로 들면 Alidoro 음식점은 American 음식 타입 중 33번째로 음식 주문을 많이 받은 식당이라는 의미이며, Han Dynasty는 Chinese 음식 타입을 3번째로 많이 음식 주문을 많이한 받은 식당이라는 뜻이다.
sub2의 결과 중 rank 1,2,3만을 출력하기 위해서는 main query에서 where절을 사용해야한다. where rn <= 3을 통해 결과를 추려내고 order by cuisine_type, rn 을 통해 음식 타입, 랭크 순으로 정렬한다.
[실습2] 전체에서 차지하는 비율, 누적합을 구할 때, Sum
* Sum은 앞서 배운 합계를 구하는 기능과 동일
* 다만, 누적합이 필요하고나 카테고리별 합계컬럼과 원본 컬럼을 함께 이용할 때 유용하게 사용됨
각 음식점의 주문건이 해당 음식 타입에 차지하는 비율을 구하고, 주문건이 낮은 순으로 정렬했을 때 누적 합 구하기
select cuisine_type, restaurant_name, order_count,
(order_count / sum_cuisine_type) * 100 order_percent,
sum_cuisine_type, cumulative_sum_1, cumulative_sum_2
from
(
select cuisine_type,
restaurant_name,
order_count,
sum(order_count) over (partition by cuisine_type) sum_cuisine_type,
sum(order_count) over (partition by cuisine_type order by order_count) cumulative_sum_1,
sum(order_count) over (partition by cuisine_type order by order_count, restaurant_name) cumulative_sum_2
from
(
select cuisine_type, restaurant_name, count(1) order_count
from food_orders
group by 1, 2
) sub1
) sub2
해당 쿼리는 다음과 같은 값을 출력한다.
sum_cuisine_type은 cusine_type별 order_count를 sum한다. cumulative_sum_1는 cusine_type, order count별로 sum을 진행하고 순서대로 누적합을 구한다. cumulative_sum_2는 cusine_type, order_count, restaurant_name별로 sum을 진행하고 누적합을 구한다.
06. 날짜 포맷과 조건까지 SQL로 한번에 끝내기 (포맷 함수)
[실습1] 날짜 데이터의 여러 포맷
date type 을 date_format 을 이용하여 년, 월, 일, 주 로 조회해보기
select date(date) date_type,
date_format(date(date), '%Y') "년",
date_format(date(date), '%m') "월",
date_format(date(date), '%d') "일",
date_format(date(date), '%w') "요일"
from payments
[실습2] 년도별 3월의 주문건수 구하기
select year, month, order_count from
(
select
date_format(date(date), '%Y') year,
date_format(date(date), '%m') month,
count(1) order_count
from food_orders fo left join payments p on fo.order_id = p.order_id
group by year, month
) sub
where month = '03'
order by year
07. Homework
음식 타입별, 연령별 주문건수 pivot view 만들기 (연령은 10~59세 사이)
select cuisine_type,
max(if(age between 10 and 19, order_count, 0)) _10s,
max(if(age between 20 and 29, order_count, 0)) _20s,
max(if(age between 30 and 39, order_count, 0)) _30s,
max(if(age between 40 and 49, order_count, 0)) _40s,
max(if(age between 50 and 59, order_count, 0)) _50s
from (
select c.age, fo.cuisine_type, count(1) order_count
from food_orders fo left join customers c on fo.customer_id = c.customer_id
group by c.age, fo.cuisine_type
) sub
where age between 10 and 59
group by cuisine_type
order by cuisine_type
'Programming > 데이터베이스' 카테고리의 다른 글
| 엑셀보다 쉽고 빠른 SQL - 1~3주차 내용 정리 (0) | 2024.01.12 |
|---|