ただのメモ。
itemcounts テーブルにあるカウント値を、30分毎に以下のような procedure を cron で実行して icount_report に入れていくだけ。
DELIMITER //
DROP PROCEDURE IF EXISTS sp_summary
//
CREATE PROCEDURE sp_summary()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE count_value INT;
DECLARE itemid INT;
DECLARE s_time TIMESTAMP;
DECLARE e_time TIMESTAMP;
DECLARE c1 CURSOR FOR
SELECT id, i_count FROM itemcounts;
DECLARE EXIT HANDLER FOR NOT FOUND SET done = 0;
-- ハンドラ宣言
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
SET s_time = ADDTIME(NOW(), '-0:30:0');
SET e_time = NOW();
OPEN c1;
REPEAT
FETCH c1 INTO itemid, count_value;
IF NOT done THEN
INSERT INTO icount_report (item_id, i_count, start_time, end_time )
SELECT itemid, count_value, s_time ,e_time ;
END IF;
UNTIL done END REPEAT;
CLOSE c1;
TRUNCATE itemcounts;
END
//
DELIMITER ;