[SQL] Procedure 를 통한 임시 데이터 보관 테이블 생성

0. 목표

 - Procedure 를 통한 임시 데이터 보관 테이블 생성

1. Flow Chart

2. SQL

 1) 테이블 생성

  (1) sp_test1(raw table 저장소)

CREATE TABLE pbj_db.sp_test1
(
	datetime datetime,
    result int
) ENGINE = INNODB;

  (2) sp_test2(임시 데이터 저장소)

CREATE TABLE pbj_db.sp_test2
(
	datetime datetime,
    result int
) ENGINE = INNODB;

 2) 프로시저 생성

  - 실행시 1분 전의 데이터들을 모아서 연산

DELIMITER $$
CREATE PROCEDURE pbj_db.what_time_is_it_now()
BEGIN
	# 지우기
    DELETE FROM pbj_db.sp_test2;
    # 넣기
    INSERT INTO pbj_db.sp_test2(datetime, result) (
		SELECT 
			DATE_FORMAT(datetime, "%Y-%m-%d %H:%i") AS datetime,
            SUM(result) AS result
		FROM pbj_db.sp_test1
        WHERE datetime >= DATE_ADD(NOW(), INTERVAL -1 MINUTE)
		GROUP BY DATE_FORMAT(datetime, "%Y-%m-%d %H:%i")
    );
END $$
DELIMITER ;

 3) 이벤트 생성

  - 1분마다 실행

CREATE EVENT pbj_db.test_sp
ON SCHEDULE EVERY 1 MINUTE
DO CALL pbj_db.what_time_is_it_now();

3. python

 - 5초다마다 현재시각과 데이터 삽입

import pymysql
import time

while 1:
    db = pymysql.connect(host='127.0.0.1', port=3306, user='root', db='pbj_db', charset='utf8')
    cursor = db.cursor()
    sql = '''INSERT INTO pbj_db.sp_test1(datetime, result) VALUES(now(), 1);'''
    cursor.execute(sql)
    result = cursor.fetchall
    db.commit()
    db.close()
    time.sleep(5)

4. 확인

 1) sp_test1(raw) 확인

SELECT * FROM pbj_db.sp_test1;

 2)  sp_test2 확인

SELECT * FROM pbj_db.sp_test2;

  - 1분 뒤

SELECT * FROM pbj_db.sp_test2;

5. 지우기

DROP event pbj_db.test_sp;
DROP PROCEDURE pbj_db.what_time_is_it_now;

DROP TABLE pbj_db.sp_test1;
DROP TABLE pbj_db.sp_test2;