차박 프로젝트 SQL 기능 LIST

UseCaseDiagram : https://gitmind.com/app/flowchart/41611830045
adminSequenceDiagram : https://gitmind.com/app/flowchart/b1f11842860

1. 용품&장소 리뷰를 관리자가 UPDATE시 TRIGGER

CREATE DEFINER=`songsama`@`%` TRIGGER `ReviewProduct_AFTER_UPDATE` AFTER UPDATE ON `ReviewProduct` FOR EACH ROW BEGIN
	if new.rp_adminConfirm <> old.rp_adminConfirm then
		begin
			#관리자가 리뷰 승인시
			if new.rp_adminConfirm = 0 then
				#해당 용품, 회원 리뷰수 +1
				update Product 
                set prod_reviewCount = prod_reviewCount + 1 
                where Product.prod_num = old.prod_num;
                
                update Member 
                set mem_reviewCount = mem_reviewCount + 1 
                where Member.mem_num = old.mem_num;
                
                #첫 리뷰시 포인트 +2000, 아니면 + 500 >>> 포인트내역 테이블에 INSERT
                if (select mem_reviewCount from Member where Member.mem_num = old.mem_num) = 1 then
					update Member 
                    set mem_point = mem_point + 2000 
                    where Member.mem_num = old.mem_num;
                    
                    insert into 
                    songsama.PointLog(mem_num,point_sysdate,point_reason,point_saving) 
                    values(old.mem_num,now(),1,2000);
				else 
					update Member 
                    set mem_point = mem_point + 500 
                    where Member.mem_num = old.mem_num;
                    
                    insert into 
                    songsama.PointLog(mem_num,point_sysdate,point_reason,point_saving) 
                    values(old.mem_num,now(),2,500);
				end if;
     
			#관리자가 리뷰 승인해제시 
			else
				#해당 용품, 회원 테이블 리뷰수 - 1
				update Product 
                set prod_reviewCount = prod_reviewCount - 1 
                where Product.prod_num = old.prod_num;
                
                update Member 
                set mem_reviewCount = mem_reviewCount - 1
                where Member.mem_num = old.mem_num;
			end if;
            
            #평점 갱신
            update Product 
            set prod_reviewScore = 
            (select round(avg(rp_score),1) from ReviewProduct where rp_adminConfirm = 0) 
            where Product.prod_num = old.prod_num;
		end;
	end if;
END

차박&용품리뷰 레코드에 UPDATE가 발생했을때
review_adminConfrim(관리자 승인여부를 알려주는 컬럼)이
UPDATE전후가 다르다면 일어나는 TRIGGER구현
일일이 SQL을 MyBatis에서 Mapping해야하는 부담감소

• 관리자가 리뷰 승인 했을때
  1. 해당 용품(장소), 회원의 리뷰수 + 1 UPDATE
  2. 만약 회원의 첫 리뷰라면 + 2000 point, 아니면 + 500 point
• 관리자가 리뷰승인 해제시
  1. 해당 용품(장소), 회원의 리뷰수 - 1 UPDATE
  타 사이트 처럼 POINT 회수 진행 X

  최종적으로 해당 용품(장소)의 리뷰수에 변화가 생겼으므로 총 평점을 갱신

2. 장바구니에서 결제시 대여내역 PROCEDURE

CREATE DEFINER=`songsama`@`%` PROCEDURE `cartToPay`(in num int,in rental_price int,in rental_usePoint int)
BEGIN
	#선언부
	declare mem_num int;
    declare prod_num int;
    declare agency_num int;
    declare rental_productCount int;
    declare rental_from varchar(60);
    declare rental_to varchar(60);
    
    #값 세팅
	set mem_num = (select a.mem_num from songsama.ProductCart a where a.cart_num = num);
    set prod_num = (select a.prod_num from songsama.ProductCart a where a.cart_num = num);
    set agency_num = (select a.agency_num from songsama.ProductCart a where a.cart_num = num);
    set rental_productCount = (select cart_prodCount from songsama.ProductCart where cart_num = num);
    set rental_from = (select cart_from from songsama.ProductCart where cart_num = num);
    set rental_to = (select cart_to from songsama.ProductCart where cart_num = num);
    
    #실행 부분 (RentalLog에 insert)
    insert into songsama.RentalLog
    (mem_num,prod_num,agency_num,rental_productCount,rental_from,rental_to,rental_return,rental_extend,rental_notReturn,rental_price,rental_usePoint,rental_extendPrice,rental_notReturnPrice,rental_pay) 
    values(mem_num,prod_num,agency_num,rental_productCount,rental_from,rental_to,0,0,0,rental_price,rental_usePoint,0,0,now());
    
    insert into songsama.PointLog(PointLog.mem_num,point_sysdate,point_reason,point_saving) 
    values(mem_num,now(),5,totalPrice*0.01);
    
    delete from songsama.ProductCart 
    where cart_num = num;
END



#Mybatis에서 Mapping
<insert id="insertCartLog" parameterType="java.util.Map" statementType="CALLABLE">
	{call cartToPay(#{cart_num},#{rental_price},#{rental_usePoint})}
<insert>

장바구니에서 결제하여 대여내역으로 넘어갈때 실행할 SQL구문이 많으므로
위의 트리거와 마찬가지의 이유로 PROCEDURE로 기능 구현
JAVA의 METHOD와 유사한 개념
Mybatis에서 CALL문 하나로 많은 양의 SQL을 한번에 실행가능

• 값 선언
  JAVA처럼 변수명과 변수타입 세팅
• 값 세팅
  기존 ProductCart의 cart_num(PK)를 이용하여 RentalLog에 필요한 값 조회&세팅
• 실행 부분
  1. 세팅한 값들로 RentalLog 테이블에 INSERT
  2. 포인트 내역을 관리하기 위한 PointLog 테이블에 INSERT
  3. 장바구니를 비우기 위해 ProductCart 테이블에서 DELETE
• 데이터의 무결성과 SELECT 기능 향상을 위해 정규화를 하여 각 테이블에 INSERT

3. 대여내역 체크를 위한 EVENT SCHEDULER

use songsama;
DROP EVENT IF EXISTS event_returnCheck;
DELIMITER $
CREATE EVENT IF NOT EXISTS event_returnCheck
ON SCHEDULE
  EVERY 1 DAY
  STARTS '2022-06-15 00:10:00'
DO
BEGIN
  CALL event_returnCheck();
END $
DELIMITER;

매일마다 많은양의 대여내역을 체크할 수 없으므로 매일 일정한 시간에 사용자가 정의한 SQL문을 실행시켜주는 EVENT SCHEDULER를 DB에 CREATE
매일 00시 10분에 DO절에 있는 구문을 수행한다 (event_returnCheck PROCEDURE수행)

EVENT SCHEDULER의 event_returnCheck PROCEDURE

CREATE DEFINER=`songsama`@`%` PROCEDURE `event_returnCheck`()
BEGIN
	#대기 > 대여중
	update songsama.RentalLog
    set rental_return = 1
    where rental_return = 0 and rental_from = curdate();
    
	#대여중 > 미납
	update songsama.RentalLog
    set rental_return =  4,rental_notReturn = rental_notReturn + 1,
    rental_notReturnPrice = rental_notReturnPrice + rental_price/(datediff(rental_to,rental_from)+1)
    where rental_return = 1 and date(rental_to) < CURDATE();
    
    #연장중 > 미납
    update songsama.RentalLog
    set rental_return = 4,rental_notReturn = rental_notReturn + 1,
    rental_notReturnPrice = rental_notReturnPrice + rental_price/(datediff(rental_to,rental_from)+1)
    where rental_return = 3 and date(rental_to) + rental_extend < CURDATE();
    
    #미납 > 미납 (미납+1씩 갱신)
	update songsama.RentalLog
    set rental_notReturn = rental_notReturn + 1,
    rental_notReturnPrice = rental_notReturnPrice + rental_price/(datediff(rental_to,rental_from)+1)
    where rental_return = 4;
END

rental_returnCheck() 컬럼 (TINYINT(1))
도메인 >> 0: 대기 | 1: 대여중 | 2: 반납완료 | 3: 연장중 | 4: 미납

• 아직 대여전인 건의 대여시작일이 현재날짜와 같을때 대여중으로 UPDATE
• 대여중인 건의 대여종료일이 현재날짜를 초과하였을경우 미납으로 UPDATE
• 연장중인 건의 대여종료일이 현재날짜를 초과하였을경우 미납으로 UDPATE
• 미납 건이 아직 미납일경우 미납일과 미납금액 누적 갱신 UPDATE

4. 매달 인기리뷰 선정 EVENT SCHEDULER

CREATE EVENT event_popularCheck
ON SCHEDULE EVERY 1 MONTH
STARTS '2022-07-01 00:10:00'
DO
CALL songsama.check_popular();

매 달 1일 00시 10분에 인기 장소, 용품, 리뷰를 각각 10개 선정하는 EVENT SCHEDULER

EVENT SCHEDULER의 event_returnCheck PROCEDURE

CREATE DEFINER=`songsama`@`%` PROCEDURE `check_popular`()
BEGIN
	#장소
    UPDATE songsama.CarCampingRegion 
    SET ccr_popular = 1 
    WHERE ccr_popular = 0;
    
	UPDATE songsama.CarCampingRegion
    SET ccr_popular = 0 
    WHERE ccr_num 
    IN 
    (SELECT * FROM (
		(SELECT ccr_num 
		FROM songsama.CarCampingRegion 
		ORDER BY ratingRegion(ccr_num,ccr_likeCount,ccr_reviewCount) DESC LIMIT 10)
		) tmp
	);
    .
    .
    .
    #장소리뷰
    UPDATE songsama.ReviewRegion 
    SET review_popular = 1 
    WHERE review_popular = 0;
    
	UPDATE songsama.ReviewRegion
    SET review_popular = 0 
    WHERE review_num 
    IN 
    (SELECT * FROM (
		(SELECT review_num 
		FROM songsama.ReviewRegion 
		ORDER BY ratingReviewRegion(review_num,review_readCount,review_likeCount) DESC LIMIT 10)
        ) tmp
	);
    .
    .
    .
END

현재 인기인 객체들을 모두 일반으로 초기화하고 좋아요 수, 조회수, 평점등을 이용하는
점수 계산 알고리즘을 내포한 FUNCTION을 이용하여 LIMIT로 상위 10개를 출력하여
인기객체로 설정

• IN절에서 SubQuery를 두 번 감싼 이유는 현재 MySQL Version(8.0.23)에선
  SubQuery안에 포함된 LIMIT는 허용되지 않음
• 위와 같이 LIMIT 쿼리가 포함된 쿼리로 임시 테이블을 생성하도록하면 LIMIT가 포함된 쿼리도 서브 쿼리로 사용 가능 (ALIAS 필수)
• 용품&용품리뷰도 이와 같은 방법 사용

PROCEDURE안의 점수 환산 알고리즘 FUNCTION

CREATE DEFINER=`songsama`@`%` FUNCTION `ratingRegion`(
num INT,
ccr_likeCount INT,
ccr_reviewCount INT
) RETURNS FLOAT
BEGIN
	#선언부
	DECLARE positiveReviews INT;
	DECLARE avg_rating FLOAT;
    DECLARE weight FLOAT;
    DECLARE score FLOAT;
    
    #값 세팅
    SELECT COUNT(*) 
    FROM songsama.CarCampingRegion a JOIN songsama.ReviewRegion b
    ON a.ccr_num = b.ccr_num 
    WHERE b.ccr_num = num AND b.review_regionScore >= 4.0
    INTO positiveReviews;
    
    #가중치 계산
    SET avg_rating = positiveReviews/ccr_reviewCount;
    SET weight = avg_rating - (avg_rating - 0.5)*POW(2,-LOG10(ccr_reviewCount));
    
    #계산된 가중치를 좋아요수에 적용
    SET score = ccr_likeCount*weight;
    
RETURN score;
END

STEAM게임사의 인기시스템 알고리즘 인용

• positive reviews : 평점이 4.0 이상인 긍정적인 리뷰수
• reviews : 전체 리뷰수
• 조회수 1, 평점 5.0인 객체와 조회수 100, 평점 4.5인 객체의 가중치를 다르게 하기위한 목적