Intro

• 코로나 19사태 이후 폭발적으로 성장한 배달 산업의 데이터 관리 방법을 Mysql을 활용해 직접 구현
  Using Mysql to implement data management methods in the delivery industry that have grown explosively since the COVID-19 outbreak
• 고객들의 요구사항을 코드화하여 데이터베이스 자동화를 이끌어냄
  Program customer requirements to drive database automation

사용자 요구 분석(User Needs Analysis)

규칙(Rules)

음식점: 데이터베이스에 등록되어 있는 음식점

• 데이터베이스를 운용할 때 음식점의 수는 변동이 없다고 가정(폐업 혹은 개업은 없음)
• 성북구의 음식점들을 대상으로 데이터베이스를 설계
• 음식점은 주문 1건당 음식가격*0.2를 배달료로 배달원에게 지불

고객: 시스템에 회원가입이 되어 있는 사람 중 주문을 하는 주체

• 고객이 탈퇴 시에도 주문내역 및 정보는 사라지지 않음
• 성북구 음식점에서 배달이 가능한 성북구, 종로구, 동대문구 거주자를 대상으로 함
• 고객은 배달 시 음식점과의 거리가 일반거리일 경우 3000원, 장거리일 경우 5000원의 배달팁을 지불

배달원: 시스템에 회원가입이 되어 있는 사람 중 배달을 하는 주체

• 배달원이 탈퇴 시에도 배달내역 및 정보는 사라지지 않음
• 배달 1건당 30%의 수수료를 시스템에 지불하여 (배달료 + 배달팁)*0.7 만큼의 수익을 얻음

주문: 데이터베이스에 저장된 고객의 음식 배달 요청 사항

• 주문취소시 해당 주문은 데이터베이스에서 삭제됨
• 배달 중인 주문은 주문TABLE에 존재하지만 배달 TABLE에 도착완료시간의 값이 NULL 인 경우를 의미함
• 주문금액은 음식가격 + 배달팁으로 지출 금액을 나타냄
• Table에서 배달비용 추가 지역 항목이 0 이면 일반주문, 1이면 장거리 주문이라는 뜻임

배달 관련 시간 정의

• 도착완료시각은 실제 배달의 도착시간이며 도착예정시각은 주문시각 + 예상 배달 소요시간임
• 배달지연은 도착예정시각보다 도착완료시각이 더 늦었을 때를 의미함

수익 구조 정리

E-R Diagram

테이블 데이터 구성(Table Data Configuration)

임의로 구성된 데이터이므로 실제 개인정보와는 연관성이 없습니다.
Since the data is randomly configured, it is not related to actual personal information.

고객 테이블(customer table)

음식점 테이블(restaurant table)

배달원 테이블(delivery man table)

배달 테이블(delivery table)

주문 테이블에서 새로운 열 추가 생성(Create a new column from the order table)

주문내역에서의 주문금액(cost)는 고객의 배달팁이 포함된 금액이기에 실제 음식가격(food_cost)가 아니다.
그래서 임시 컬럼으로 food_cost 컬럼을 만들어서 사용한다.

food_cost = 주문금액 – 배달팁 기본료(3000) – 배달추가지역여부(0/1)*배달팁 추가료(2000)

# food_cost column generate
alter table `order`
	add food_cost int generated always as (cost - 3000 - 2000*region_additional_cost) virtual;

# food_cost column erase
alter table `order` drop food_cost;

주문 취소내역 테이블 생성(Create Order Cancellation History Table)

고객의 변심에 의해서 주문 취소가 발생할 수 있는데 이때 취소된 내역을 따로 저장할 수 있도록 withdraw_order 테이블을 생성했다.

트리거를 통해 주문 취소 발생시 이 테이블에 자동 저장이 된다. (자세한 내용은 Query 2-α 참조)

# 주문취소 내역 테이블 생성(create cancellation table)
create table withdraw_order(
	order_number int,
	order_time datetime ,
	order_history varchar(45) ,
	cost int ,
	region_additional_cost tinyint ,
	CUSTOMER_idCUSTOMER int,
	RESTAURANT_company_registration_number int,
	DELIVERY_MAN_idDELIVERY_MAN int
);

# 테이블 지우기용 (erase cancellation table)
-- drop table withdraw_order;

최종 주문테이블 구성(Final Order Table Configuration)

요구사항 구현(Implementing Requirements)

Query1. 음식점 A의 최근 주문 내역은 무엇인가?

• Code

# Query1
SELECT O.order_number, R.restaurant_name, O.order_time,O.cost, O.order_history
FROM restaurant AS R, `order` AS O
WHERE R.company_registration_number = 16 AND R.company_registration_number = O.RESTAURANT_company_registration_number
ORDER BY O.order_time DESC
					LIMIT 1; 

• 설명(Description)
  3-> order(주문) Table에서 음식점 사업자 등록번호가 16인 주문을 찾음
  4-> order_time(주문시각)을 최근순으로 나열하고 가장 위의 내역을 가져옴

• 실행결과(Execution results)
  

Query2. 현재 배달중인 주문 개수와 주문 정보

• Code

# Query2
select count(*) from delivery where arrival_time is null;
select o.order_number,o.CUSTOMER_idCUSTOMER,o.order_history,o.cost from `order` o, delivery d
where o.CUSTOMER_idCUSTOMER = d.CUSTOMER_idCUSTOMER and
o.DELIVERY_MAN_idDELIVERY_MAN = d.DELIVERY_MAN_idDELIVERY_MAN and
d.arrival_time is null ;

• 설명(Description)
  1 -> delivery(배달) 테이블에서 arrival_time(도착 시각)이 null 인 것의 개수를 select함
  2~5 -> order(주문) 테이블과 delivery(배달) 테이블을 join시키고 고객ID, 배달원 ID가 일치하고 arrival_time이 null인 것을 추출
  주문번호, 주문 고객 ID, 주문 내용 등을 select함

• 실행결과(Execution results)
  

Query2-α. 주문취소를 테이블에 반영하기

• Code

# Query 2-1 
drop trigger if exists out_ordertbl;
delimiter $$
create trigger out_ordertbl after delete on `order`
for each row
begin
	delete from delivery where 	CUSTOMER_idCUSTOMER = old.CUSTOMER_idCUSTOMER ;
    
    insert into withdraw_order
		values(old.order_number, old.order_time, old.order_history, old.cost, old.region_additional_cost,
        old.CUSTOMER_idCUSTOMER, old.RESTAURANT_company_registration_number, old.DELIVERY_MAN_idDELIVERY_MAN);
end $$
delimiter ;

delete from `order` where order_number = 11146;
select * from withdraw_order;

• 설명(Description)
  1 -> out_orderbl 이라는 트리거를 생성함
  3~ 11 -> order(주문) 테이블에서 삭제가 일어나면 delivery(배달) 테이블에서도 해당 정보가 삭제되도록 설정
  그리고 삭제된 정보를 withdraw_order(주문취소) 테이블에 자동 기입되도록함
  14 -> order테이블에서 주문번호가 11144인 주문을 삭제함
  15 -> withdraw_order 테이블을 보여줌(주문취소내역확인)

• 실행결과(Execution results)
  주문취소 내역을 보여줌
  

Query3. 음식점 A의 한달 매출

• Code

# Query3. 음식점 한달 매출
SELECT R.restaurant_name, sum(O.food_cost) as "한 달 매출"
FROM `order` AS O, restaurant AS R  
WHERE R.company_registration_number =12	 AND O.RESTAURANT_company_registration_number = R.company_registration_number 
AND YEAR(ORDER_TIME) = 2022 AND MONTH(ORDER_TIME) = 5;

• 설명(Description)
  3, 4-> 음식점 사업자 등록번호가 12 이고 join한 테이블에서 company_registration_number(사업자 등록번호)가 12로 같은 것을 찾음. 그리고 주문시각이 2022년 5월인 것을 가져옴
  1 -> 추출한 데이터 중 음식금액에 대하여 sum 함수를 이용해 합계를 구한 후 출력

• 실행결과(Execution results)
  

Query4. 음식점 A의 한달 배달비용 지출

• Code

# Query4
SELECT R.restaurant_name, sum(O.food_cost) * 0.2 as "한 달 배달비용"
FROM `order` AS O, restaurant AS R  
WHERE R.company_registration_number = 16 AND O.RESTAURANT_company_registration_number = R.company_registration_number 
AND YEAR(ORDER_TIME) = 2022 AND MONTH(ORDER_TIME) =4;

• 설명(Description)
  1 -> out_orderbl 이라는 트리거를 생성함
  3~ 11 -> order(주문) 테이블에서 삭제가 일어나면 delivery(배달) 테이블에서도 해당 정보가 삭제되도록 설정
  그리고 삭제된 정보를 withdraw_order(주문취소) 테이블에 자동 기입되도록함
  14 -> order테이블에서 주문번호가 11144인 주문을 삭제함
  15 -> withdraw_order 테이블을 보여줌(주문취소내역확인)

• 실행결과(Execution results)
  주문취소 내역을 보여줌
  

Query5. 고객X의 배달 도착 예정시간

• Code

# Query5
drop procedure if exists predictproc;
delimiter $$
create procedure predictproc(in my_id int)
begin
select o.order_number, c.customer_name, o.order_time,
d.expected_duration, date_add(o.order_time, interval d.expected_duration minute) as '도착예정시간'
from `order` o, delivery d, customer c
where d.CUSTOMER_idCUSTOMER = o.CUSTOMER_idCUSTOMER and
d.DELIVERY_MAN_idDELIVERY_MAN = o.DELIVERY_MAN_idDELIVERY_MAN and
d.arrival_time is null and
d.CUSTOMER_idCUSTOMER = my_id and
c.idCUSTOMER = o.CUSTOMER_idCUSTOMER;
end $$
delimiter ; 

 call predictproc('1125')

• 설명(Description)
  3 -> predictproc라는 프로시저를 만들고 my_int라는 정수를 입력받음
  8~12 -> delivery 와 order테이블에서 배달원 id와 고객 id를 일치시켜 join하고 도착시간이 null이며(배달이 미도착 해야 예정도착 시간 궁금해하기 때문) 입력받은 값인 고객 id를 가지는 값을 추출함.
  6 -> 주문시각에 예상소요시간을 더해 도착예정시간을 구함

• 실행결과(Execution results)
  

Query6. 고객X의 최근 3개월 주문내역

• Code

# Query6
drop procedure if exists orderproc;
delimiter $$
create procedure orderproc(in customer_id int)
begin
	select order_number, order_history, cost, order_time as '주문시각' from `order`
    where order_time between date_sub(now(), interval 3 month) and now() and
    CUSTOMER_idCUSTOMER = customer_id ; 
end $$
delimiter ;

call orderproc(1112);

• 설명(Description)
  3 -> 고객 ID 를 프로시저에서 입력받음
  4~8 -> order(주문) 테이블에서 입력받은 고객 ID와 일치하며 현재로부터 3개월 간격의 내에 있는 order_time(주문시각)의 정보만을 추출함
  11 -> 고객 ID가 1111인 사람의 최근 3개월 주문내역을 출력함

• 실행결과(Execution results)
  

Query7. 고객X의 한달간 주문금액 합계

• Code

# Query 7
select year(`order`.order_time) '연도', Month(`order`.order_time) '달', sum(`order`.cost) '이번 달 총 주문금액'
from `order`
where `order`.CUSTOMER_idCUSTOMER = 1115 and year(`order`.order_time)=2022 and
Month(`order`.order_time) = 4

• 설명(Description)
  3, 4-> order(주문) 테이블에서 고객 ID가 1111이고 년도가 2022이고 달이 5인 데이터만 가져옴
  1 -> 연도와 달 그리고 테이블에서 cost(주문금액)을 다 더한 것을 표시

• 실행결과(Execution results)
  

Query8. 배달원P의 하루수익

• Code

# Query8
drop procedure if exists dailyproc;
delimiter $$
create procedure dailyproc(in d_id int) 
begin 
	select 
    d.deliveryman_name,
    0.7*sum(if ( o.region_additional_cost = '1',5000+o.food_cost*0.2,3000+o.food_cost*0.2))
    as '배달원 하루 수익'
    from `order` o, delivery_man d 
    where d.idDELIVERY_MAN = o.DELIVERY_MAN_idDELIVERY_MAN and d.idDELIVERY_MAN = d_id
    and year(o.order_time) = 2022 and month(o.order_time) = 5
    and day(o.order_time) = 21;
end $$
delimiter ;
call dailyproc(112);

• 설명(Description)
  3 -> daily proc 프로시저 제작하고 d_int라는 정수값을 할당받도록 함
  5~8 -> 추가비용지역이면 배달팁이 5000원이고 일반지역은 3000원인 것을 if문으로 분리하고 배달원 수익인 (배달비+배달팁)*0.7을 수식화 시켰음
  10-> 우리가 찾고자 하는 배달원 id에 해당하는 table 항목을 불러옴
  11~12-> 알고자 하는 하루를 선택함

• 실행결과(Execution results)
  

Query9. 배달원P의 배달 지연률

• Code

# Query9
drop procedure if exists delayproc;
delimiter $$ 
create procedure delayproc(in deli_id int, in o_year int, in o_month int)
begin
	declare all_count int ;
    declare delay_count int ;
    
    select count(*) into all_count
    from delivery d
    where d.DELIVERY_MAN_idDELIVERY_MAN = deli_id 
    and year(d.arrival_time) = o_year and month(d.arrival_time) = o_month;
    
    select count(*) into delay_count
    from delivery d, `order` o
    where d.DELIVERY_MAN_idDELIVERY_MAN = deli_id and
    d.CUSTOMER_idCUSTOMER = o.CUSTOMER_idCUSTOMER and
    d.DELIVERY_MAN_idDELIVERY_MAN = o.DELIVERY_MAN_idDELIVERY_MAN and
    year(o.order_time) = o_year and
    month(o.order_time) = o_month and
    date_add(o.order_time, interval d.expected_duration minute) < d.arrival_time ;
    
    select (delay_count/all_count)*100 as '배달지연률(%)';
    
end $$
delimiter ;

call delayproc(114,2022,3);

• 설명(Description)
  3-> 배달원 id와 년도, 달을 입력받고 해당하는 배달원이 해당하는 달에 늦은 배달을 할 비율 구하는 프로시저 제작
  8~11-> 배달원이 해당 달에 한 배달 횟수를 구해서 all_count라는 변수에 저장
  13~20-> 배달원이 해당 달에 한 배달 중 arrival_time(실제 도착시간)이 order_time(주문시각) +
  expected_duration(예상 배달 소요시간) 보다 큰 횟수를 구해서 delay_count라는 변수에 저장
  22-> delay_count/all_count를 해서 배달 지연률을 구함
  27-> delayproc 프로시저에 (114, 2022, 3)를 넣어서 배달원 id 114의 3월 배달 지연률을 구함

• 실행결과(Execution results)