-- 한 문장씩 차례대로 실행하는 것을 권장합니다.
-- 컴퓨터와 MySQL 설정에 따라 많은 시간이 걸릴 수 있습니다.
USE startdbmy;
-- -----------------------------------------------------------------------
-- 튜닝용 테이블 제거
-- -----------------------------------------------------------------------
DROP TABLE IF EXISTS startdbmy.ms_mbr_big;
DROP TABLE IF EXISTS startdbmy.ms_shop_big;
DROP TABLE IF EXISTS startdbmy.tr_ord_big_tmp;
DROP TABLE IF EXISTS startdbmy.tr_ord_big;
DROP TABLE IF EXISTS startdbmy.tr_ord_det_big;
DROP TABLE IF EXISTS startdbmy.tr_event_entry_big
-- -----------------------------------------------------------------------
-- 튜닝용 테이블 생성(startuppg DB에 접속해서 실행해야함)
-- -----------------------------------------------------------------------
CREATE TABLE startdbmy.ms_shop_big
(
shop_id VARCHAR(40) NOT NULL
,shop_nm VARCHAR(100) NULL
,shop_size INTEGER NULL
,shop_oper_tp VARCHAR(40) NULL
,table_qty INTEGER NULL
,chair_qty INTEGER NULL
,open_time VARCHAR(100) NULL
,close_time VARCHAR(100) NULL
,shop_st VARCHAR(40) NULL
,shop_start_ymd VARCHAR(8) NULL
,shop_end_ymd VARCHAR(8) NULL
);
CREATE TABLE startdbmy.ms_mbr_big
(
mbr_id VARCHAR(40) NOT NULL
,nick_nm VARCHAR(100) NULL
,mobl_no VARCHAR(100) NULL
,emal_adr VARCHAR(100) NULL
,join_dtm DATETIME NULL
,join_tp VARCHAR(40) NULL
,mbr_gd VARCHAR(40) NULL
,mbr_st VARCHAR(40) NULL
,leave_dtm DATETIME NULL
);
CREATE TABLE startdbmy.tr_ord_big_tmp
( ord_no BIGINT NOT NULL
,ord_dtm DATETIME NULL
,prep_cmp_dtm DATETIME NULL
,pkup_dtm DATETIME NULL
,mbr_id VARCHAR (40) NULL
,shop_id VARCHAR (40) NULL
,ord_st VARCHAR (40) NULL
,ord_amt DECIMAL (18,3) NULL
,pay_tp VARCHAR (40) NULL
,PRIMARY KEY (ord_no)
);
CREATE TABLE startdbmy.tr_ord_big
( ord_no BIGINT UNSIGNED NOT NULL
,ord_dtm DATETIME NULL
,ord_ymd VARCHAR(8) NULL
,prep_cmp_dtm DATETIME NULL
,pkup_dtm DATETIME NULL
,mbr_id VARCHAR (40) NULL
,shop_id VARCHAR (40) NULL
,ord_st VARCHAR (40) NULL
,ord_amt DECIMAL (18,3) NULL
,pay_tp VARCHAR (40) NULL
,PRIMARY KEY (ord_no)
);
CREATE TABLE startdbmy.tr_ord_det_big
( ord_no BIGINT UNSIGNED NOT NULL
,ord_det_no INTEGER NOT NULL
,item_id VARCHAR (40) NULL
,ord_qty DECIMAL (10,0) NULL
,sale_prc DECIMAL (18,3) NULL
,PRIMARY KEY(ord_no, ord_det_no)
);
CREATE TABLE startdbmy.tr_event_entry_big
( event_id VARCHAR(40) NOT NULL
,entry_no INTEGER NOT NULL
,shop_id VARCHAR(40) NOT NULL
,mbr_id VARCHAR(40) NOT NULL
,entry_dtm DATETIME NOT NULL
,entry_rslt_cd VARCHAR(40) NOT NULL
,entry_rslt_dtm DATETIME NOT NULL
);
-- -----------------------------------------------------------------------
-- ms_shop -> startdbmy.ms_shop_big 생성
-- -----------------------------------------------------------------------
INSERT INTO startdbmy.ms_shop_big
(
shop_id
,shop_nm
,shop_size
,shop_oper_tp
,table_qty
,chair_qty
,open_time
,close_time
,shop_st
,shop_start_ymd
,shop_end_ymd
)
SELECT shop_id
,shop_nm
,shop_size
,shop_oper_tp
,table_qty
,chair_qty
,open_time
,close_time
,shop_st
,shop_start_ymd
,shop_end_ymd
FROM startdbmy.ms_shop;
-- -----------------------------------------------------------------------
-- ms_mbr -> startdbmy.ms_mbr_big 생성
-- -----------------------------------------------------------------------
INSERT INTO startdbmy.ms_mbr_big
(mbr_id ,nick_nm ,mobl_no ,emal_adr ,join_dtm, join_tp, mbr_gd ,mbr_st, leave_dtm)
SELECT CONCAT('M' , LPAD(t1.rno,5,'0'))
,CONCAT(t1.nick_nm ,
CASE WHEN t1.rno_by_mbr = 0 THEN ''
ELSE CONCAT('-' , t1.rno_by_mbr) END) nick_nm
,CONCAT('100' , LPAD(t1.rno,5,'0')) mobl_no
,CONCAT(LPAD(t1.rno,5,'0') , '@gm.com') emal_adr
,DATE_SUB(t1.join_dtm, INTERVAL (t1.rno_by_mbr + MOD(t1.rno, 60)) DAY) join_dtm
,CASE WHEN MOD(rno,10) <= 6 THEN 'DRCT'
WHEN MOD(rno,10) <= 8 THEN 'SNS'
ELSE 'INV' END join_tp
,t1.mbr_gd
,t1.mbr_st
,t1.leave_dtm
FROM (
SELECT t1.mbr_id
,t1.nick_nm
,t1.mobl_no
-- 위에서 신규로 부여 ,t1.emal_adr
,t1.join_dtm
,t1.mbr_gd
,t1.mbr_st
,t1.leave_dtm
,ROW_NUMBER() OVER(ORDER BY t1.mbr_id ,t2.rno) rno
,ROW_NUMBER() OVER(PARTITION BY t1.mbr_id ORDER BY t2.rno asc)-1 rno_by_mbr
FROM startdbmy.ms_mbr t1
,(SELECT ROW_NUMBER() OVER() - 1 rno
FROM startdbmy.ms_mbr LIMIT 10) t2
) t1;
-- -----------------------------------------------------------------------
-- startdbmy.tr_ord_big_tmp 데이터 생성 프로시저 생성(월별로 반복 실행되는 프로시저)
-- 다른 SQL과 같이 실행하면 정상적으로 생성안될수 있음. 별도 창에서 Alt+x로 단독 실행을 권장함.
-- -----------------------------------------------------------------------
DELIMITER $$;
CREATE PROCEDURE startdbmy.usp_insert_tr_ord_big_tmp()
BEGIN
-- 커서 관련 변수
DECLARE v_yyyymm CHAR(6);
DECLARE done INT DEFAULT FALSE;
-- 커서 선언: 월별(YYYYMM) 목록
DECLARE curs CURSOR FOR
SELECT DATE_FORMAT(ord_dtm, '%Y%m') AS yyyymm
FROM startdbmy.tr_ord
GROUP BY DATE_FORMAT(ord_dtm, '%Y%m')
ORDER BY DATE_FORMAT(ord_dtm, '%Y%m');
-- NOT FOUND 시 done 플래그 세팅
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 커서 오픈
OPEN curs;
-- LOOP 시작
read_loop: LOOP
FETCH curs INTO v_yyyymm;
IF done THEN
LEAVE read_loop;
END IF;
-- 실제 Insert 로직
INSERT INTO startdbmy.tr_ord_big_tmp
(ord_no
,ord_dtm
,prep_cmp_dtm
,pkup_dtm
,mbr_id
,shop_id
,ord_st
,ord_amt
,pay_tp
)
SELECT /*+ SET_VAR(sort_buffer_size=512M) */
ROW_NUMBER() OVER (ORDER BY T3.new_ord_dtm, T3.shop_id, T3.new_mbr_id)
+ COALESCE(
(SELECT MAX(x.ord_no) FROM startdbmy.tr_ord_big_tmp x),
0
) AS new_ord_no
,T3.new_ord_dtm
,T3.new_prep_cmp_dtm
,T3.new_pkup_dtm
,T3.new_mbr_id
,T3.shop_id
,T3.ord_st
,NULL AS ord_amt
,T3.pay_tp
FROM
(
SELECT
-- second 단위로 더하기
DATE_ADD(t1.ord_dtm, INTERVAL t2.n SECOND) AS new_ord_dtm,
DATE_ADD(t1.prep_cmp_dtm, INTERVAL t2.n SECOND) AS new_prep_cmp_dtm,
DATE_ADD(t1.pkup_dtm, INTERVAL t2.n SECOND) AS new_pkup_dtm,
-- mbr_id 생성 로직 ('M' + right padding)
CONCAT(
'M',
LPAD(
-- ( substring(t1.mbr_id, 2, 4)::integer + (t1.ord_no % 999 + 1) ) * (t2.n + 1) % 99990 + 1
MOD(
(
(CAST(SUBSTR(t1.mbr_id, 2, 4) AS UNSIGNED)
+ (MOD(t1.ord_no, 999) + 1)
) * (t2.n + 1)
),
99990
) + 1,
5,
'0'
)
) AS new_mbr_id,
t1.shop_id,
t1.ord_st,
NULL AS ord_amt,
t1.pay_tp
FROM startdbmy.tr_ord t1
-- generate_series(0,35) → 재귀 CTE 사용
JOIN (
WITH RECURSIVE nums AS (
SELECT 0 AS n
UNION ALL
SELECT n + 1 FROM nums WHERE n < 35
)
SELECT n FROM nums
) t2 ON 1=1
WHERE
t1.ord_dtm >= STR_TO_DATE(CONCAT(v_yyyymm,'01'), '%Y%m%d')
AND t1.ord_dtm < DATE_ADD(STR_TO_DATE(CONCAT(v_yyyymm,'01'), '%Y%m%d'), INTERVAL 1 MONTH)
) T3;
-- 월별 처리 후 COMMIT
COMMIT;
END LOOP;
-- 커서 종료
CLOSE curs;
END$$
DELIMITER ;
-- -----------------------------------------------------------------------
-- startdbmy.tr_ord_big_tmp 데이터 생성 프로시저 실행
-- -----------------------------------------------------------------------
CALL startdbmy.usp_insert_tr_ord_big_tmp();
-- -----------------------------------------------------------------------
-- startdbmy.tr_ord_big_tmp -> startdbmy.tr_ord_det_big 데이터 생성
-- -----------------------------------------------------------------------
-- 14분.
INSERT INTO startdbmy.tr_ord_det_big(ord_no, ord_det_no, item_id, ord_qty, sale_prc)
SELECT /*+ SET_VAR(sort_buffer_size=512M) */
t1.ord_no,
ROW_NUMBER() OVER(PARTITION BY t1.ord_no ORDER BY t1.item_id) AS ord_det_no,
t1.item_id,
SUM(t1.ord_qty) AS ord_qty,
MAX(t1.sale_prc) AS sale_prc
FROM
(
SELECT
T1.ord_no,
1 AS ord_det_no,
T2.item_id,
1 AS ord_qty,
T2.sale_prc
FROM startdbmy.tr_ord_big_tmp T1
INNER JOIN
(
SELECT
t1.item_id,
t1.prc_start_dt,
t1.prc_end_dt,
ROW_NUMBER() OVER(ORDER BY t1.prc_start_dt, t1.prc_end_dt, t1.item_id) AS item_no,
t1.sale_prc
FROM startdbmy.ms_item_prc_hist t1
WHERE t1.item_id IN ('AMB','AMR','IAMB','IAMR','CLB','CLR','ICLB','ICLR')
AND t1.prc_start_dt < '2023-01-01'
) T2 ON (T2.item_no = MOD(T1.ord_no, 8) + 1)
WHERE T1.ord_dtm >= T2.prc_start_dt
AND T1.ord_dtm < DATE_ADD(T2.prc_end_dt, INTERVAL 1 DAY)
AND T1.ord_dtm < '2023-01-01'
UNION ALL
SELECT
T1.ord_no,
2 AS ord_det_no,
T2.item_id,
1 AS ord_qty,
T2.sale_prc
FROM startdbmy.tr_ord_big_tmp T1
INNER JOIN
(
SELECT
t1.item_id,
t1.prc_start_dt,
t1.prc_end_dt,
ROW_NUMBER() OVER(ORDER BY t1.item_id DESC, t1.prc_start_dt, t1.prc_end_dt) AS item_no,
t1.sale_prc
FROM startdbmy.ms_item_prc_hist t1
) T2 ON (T2.item_no = MOD(T1.ord_no, 44) + 1)
WHERE T1.ord_dtm >= T2.prc_start_dt
AND T1.ord_dtm < DATE_ADD(T2.prc_end_dt, INTERVAL 1 DAY)
AND T1.ord_dtm < '2023-01-01'
) t1
GROUP BY t1.ord_no, t1.item_id;
-- 21분
INSERT INTO startdbmy.tr_ord_det_big(ord_no, ord_det_no, item_id, ord_qty, sale_prc)
SELECT /*+ SET_VAR(sort_buffer_size=512M) */
t1.ord_no,
ROW_NUMBER() OVER(PARTITION BY t1.ord_no ORDER BY t1.item_id) AS ord_det_no,
t1.item_id,
SUM(t1.ord_qty) AS ord_qty,
MAX(t1.sale_prc) AS sale_prc
FROM
(
SELECT
T1.ord_no,
1 AS ord_det_no,
T2.item_id,
1 AS ord_qty,
T2.sale_prc
FROM startdbmy.tr_ord_big_tmp T1
INNER JOIN
(
SELECT
t1.item_id,
t1.prc_start_dt,
t1.prc_end_dt,
ROW_NUMBER() OVER (ORDER BY t1.prc_start_dt, t1.prc_end_dt, t1.item_id) AS item_no,
t1.sale_prc
FROM startdbmy.ms_item_prc_hist t1
WHERE t1.item_id IN ('AMB','AMR','IAMB','IAMR','CLB','CLR','ICLB','ICLR')
AND t1.prc_start_dt >= '2023-01-01'
) T2 ON (T2.item_no = MOD(T1.ord_no, 8) + 1)
WHERE T1.ord_dtm >= T2.prc_start_dt
AND T1.ord_dtm < DATE_ADD(T2.prc_end_dt, INTERVAL 1 DAY)
AND T1.ord_dtm >= '2023-01-01'
UNION ALL
SELECT
T1.ord_no,
2 AS ord_det_no,
T2.item_id,
1 AS ord_qty,
T2.sale_prc
FROM startdbmy.tr_ord_big_tmp T1
INNER JOIN
(
SELECT
t1.item_id,
t1.prc_start_dt,
t1.prc_end_dt,
ROW_NUMBER() OVER (ORDER BY t1.item_id DESC, t1.prc_start_dt, t1.prc_end_dt) AS item_no,
t1.sale_prc
FROM startdbmy.ms_item_prc_hist t1
) T2 ON (T2.item_no = MOD(T1.ord_no, 44) + 1)
WHERE T1.ord_dtm >= T2.prc_start_dt
AND T1.ord_dtm < DATE_ADD(T2.prc_end_dt, INTERVAL 1 DAY)
AND T1.ord_dtm >= '2023-01-01'
) t1
GROUP BY t1.ord_no, t1.item_id;
-- -----------------------------------------------------------------------
-- startdbmy.tr_ord_big_tmp + startdbmy.tr_ord_det_big -> startdbmy.tr_ord_big 데이터 생성
-- -----------------------------------------------------------------------
-- 33분
INSERT INTO startdbmy.tr_ord_big(ord_no,ord_dtm,ord_ymd,prep_cmp_dtm,pkup_dtm,mbr_id,shop_id,ord_st,ord_amt,pay_tp)
SELECT /*+ SET_VAR(sort_buffer_size=512M) */
t1.ord_no ,t1.ord_dtm, DATE_FORMAT(t1.ord_dtm,'%Y%m%d') ord_ymd ,t1.prep_cmp_dtm ,t1.pkup_dtm, t1.mbr_id ,t1.shop_id ,t1.ord_st
,t2.ord_amt ,t1.pay_tp
FROM startdbmy.tr_ord_big_tmp t1
INNER JOIN (
select x.ord_no , sum(x.ord_qty * x.sale_prc) ord_amt
from startdbmy.tr_ord_det_big x
group by x.ord_no
) t2
on (t1.ord_no = t2.ord_no)
;
-- -----------------------------------------------------------------------
-- startdbmy.tr_ord_big_tmp 제거
-- -----------------------------------------------------------------------
DROP TABLE startdbmy.tr_ord_big_tmp;
-- -----------------------------------------------------------------------
-- startdbmy.tr_ord_big -> startdbmy.tr_event_entry_big 생성
-- -----------------------------------------------------------------------
INSERT INTO startdbmy.tr_event_entry_big
(
event_id
,entry_no
,shop_id
,mbr_id
,entry_dtm
,entry_rslt_cd
,entry_rslt_dtm
)
SELECT
t2.event_id,
ROW_NUMBER() OVER (
PARTITION BY t2.event_id
ORDER BY t2.entry_dtm, t2.mbr_id, t2.shop_id
) AS entry_no,
t2.shop_id,
t2.mbr_id,
t2.entry_dtm,
CASE
/* “mmss” 값을 정수로 바꾼 뒤 5로 나눈 나머지로 성공/실패 결정 */
WHEN MOD(CAST(DATE_FORMAT(t2.entry_dtm, '%i%S') AS UNSIGNED), 5) = 0
THEN 'SUCC'
ELSE 'FAIL'
END AS entry_rslt_cd,
/* 해당 시각을 시(00분 00초)에 맞춘 뒤 +30 분 */
TIMESTAMP(DATE_FORMAT(t2.entry_dtm, '%Y-%m-%d %H:00:00'))
+ INTERVAL 30 MINUTE AS entry_rslt_dtm
FROM (
SELECT
CONCAT('EV-', DATE_FORMAT(t1.ord_dtm, '%y%m%d%H')) AS event_id,
MIN(t1.shop_id) AS shop_id,
t1.mbr_id,
MIN(t1.ord_dtm) AS entry_dtm
FROM startdbmy.tr_ord_big AS t1
WHERE DATE_FORMAT(t1.ord_dtm, '%H') BETWEEN '08' AND '20'
GROUP BY
CONCAT('EV-', DATE_FORMAT(t1.ord_dtm, '%y%m%d%H')),
t1.mbr_id
) AS t2;
-- -----------------------------------------------------------------------
-- PK 생성
-- -----------------------------------------------------------------------
ALTER TABLE startdbmy.ms_shop_big ADD CONSTRAINT ms_shop_big_pk PRIMARY KEY(shop_id);
ALTER TABLE startdbmy.ms_mbr_big ADD CONSTRAINT ms_mbr_big_pk PRIMARY KEY(mbr_id);
ALTER TABLE startdbmy.tr_event_entry_big ADD CONSTRAINT tr_event_entry_big_pk PRIMARY KEY(event_id,entry_no);
-- -----------------------------------------------------------------------
-- 통계 생성
-- -----------------------------------------------------------------------
ANALYZE TABLE startdbmy.ms_shop_big;
ANALYZE TABLE startdbmy.ms_mbr_big;
ANALYZE TABLE startdbmy.tr_ord_big;
ANALYZE TABLE startdbmy.tr_ord_det_big;
ANALYZE TABLE startdbmy.tr_event_entry_big;