DROP TABLE IF EXISTS NODE_CLOSURE; CREATE TABLE NODE_CLOSURE ( `ID` char(36) NOT NULL DEFAULT '', `PARENT_ID` char(36) NOT NULL DEFAULT '', `NAME` varchar(255) DEFAULT NULL, `DISTANCE` int(11) NOT NULL, PRIMARY KEY (`ID`,`PARENT_ID`), KEY `ANC_DAY_AGG` (`ID`,`PARENT_ID`), KEY `IX_AGG_NODE_CLOSURE_ID` (`ID`), KEY `IX_AGG_NODE_CLOSURE_PARENT_ID` (`PARENT_ID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; DROP TABLE IF EXISTS FACT_AGG; CREATE TABLE FACT_AGG ( `LOCATIONID` char(36) DEFAULT NULL, `DAY` int(8) NOT NULL, `TIME` int(6) NOT NULL, `FACT_COUNT` int(11) NOT NULL, PRIMARY KEY (LOCATIONID, DAY, `TIME`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; DROP TABLE IF EXISTS CAMERA; CREATE TABLE CAMERA ( `ID` varchar(36) NOT NULL DEFAULT '', `NODE_ID` varchar(72) DEFAULT NULL, PRIMARY KEY (`ID`), KEY `FKF1963CA6756C05D7` (`NODE_ID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; DROP TABLE IF EXISTS FACT; CREATE TABLE FACT ( `ID` varchar(36) NOT NULL DEFAULT '', `RESOURCEID` varchar(36) DEFAULT NULL, `INSERTIONTIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `STARTTIME` datetime DEFAULT NULL, PRIMARY KEY (`ID`), KEY `FACT_RESOURCEID_STARTTIME` (`RESOURCEID`,`STARTTIME`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; DELIMITER $$ DROP PROCEDURE IF EXISTS aggregate$$ CREATE PROCEDURE aggregate(IN FACT_INTERVAL_START TIMESTAMP, IN FACT_INTERVAL_END TIMESTAMP) BEGIN SELECT MAX(INSERTIONTIME), MIN(STARTTIME), MAX(STARTTIME) INTO FACT_INTERVAL_END, @FACT_MIN_CREATION_TIME, @FACT_MAX_CREATION_TIME FROM FACT WHERE INSERTIONTIME >= FACT_INTERVAL_START AND INSERTIONTIME < FACT_INTERVAL_END; -- round min time by quater hour SET @FACT_MIN_CREATION_TIME = FROM_UNIXTIME((UNIX_TIMESTAMP(@FACT_MIN_CREATION_TIME) DIV 900) * 900); SET @FACT_MAX_CREATION_TIME = FROM_UNIXTIME((UNIX_TIMESTAMP(@FACT_MAX_CREATION_TIME) DIV 900 + 1) * 900); PREPARE stmt FROM 'INSERT INTO FACT_AGG (LOCATIONID, DAY, TIME, FACT_COUNT) SELECT LOCATIONID, DAY, TIME, FACT_COUNT FROM (SELECT PARENT_ID AS LOCATIONID, DATE_FORMAT(STARTTIME, "%Y%m%d") AS DAY, HOUR(STARTTIME) * 10000 + 1500 * (MINUTE(STARTTIME) DIV 15) AS TIME, COUNT(*) AS FACT_COUNT, UNIX_TIMESTAMP(STARTTIME) DIV 900 AS GROUP_COLUMN FROM FACT JOIN CAMERA ON FACT.RESOURCEID = CAMERA.ID JOIN NODE_CLOSURE ON CAMERA.NODE_ID = NODE_CLOSURE.ID WHERE STARTTIME >= ? AND STARTTIME < ? AND DISTANCE > 0 GROUP BY LOCATIONID, GROUP_COLUMN ) _AGG ON DUPLICATE KEY UPDATE FACT_COUNT = VALUES(FACT_COUNT)'; EXECUTE stmt USING @FACT_MIN_CREATION_TIME, @FACT_MAX_CREATION_TIME; DEALLOCATE PREPARE stmt; END $$ DELIMITER ; TRUNCATE NODE_CLOSURE; TRUNCATE CAMERA; TRUNCATE FACT_AGG; TRUNCATE FACT; INSERT INTO CAMERA VALUES ('7a7db2cb-21f9-49a3-8f04-9b16f0dec96e', '16a67626-0309-40d5-bdcb-b142cf64bc1d'); INSERT INTO NODE_CLOSURE(ID, PARENT_ID, NAME, DISTANCE) VALUES ('00000000-0000-0000-0000-000000000001','00000000-0000-0000-0000-000000000001','ROOT',0), ('16a67626-0309-40d5-bdcb-b142cf64bc1d','00000000-0000-0000-0000-000000000001','Entrance',2), ('16a67626-0309-40d5-bdcb-b142cf64bc1d','16a67626-0309-40d5-bdcb-b142cf64bc1d','Entrance',0), ('16a67626-0309-40d5-bdcb-b142cf64bc1d','ad617309-c673-48aa-a908-b66225576e4c','Entrance',1), ('ad617309-c673-48aa-a908-b66225576e4c','00000000-0000-0000-0000-000000000001','Entrance',1), ('ad617309-c673-48aa-a908-b66225576e4c','ad617309-c673-48aa-a908-b66225576e4c','Entrance',0); INSERT INTO FACT(ID, RESOURCEID, INSERTIONTIME, STARTTIME) VALUES ('8a44e591-9b38-11e0-abd9-739384ddc647','7a7db2cb-21f9-49a3-8f04-9b16f0dec96e','2011-06-16 18:06:39','2011-06-16 18:06:39'); call aggregate('2009-01-01 00:00:00', '2012-01-01 00:00:00'); INSERT INTO FACT(ID, RESOURCEID, INSERTIONTIME, STARTTIME) VALUES ('8a5a8831-9b38-11e0-abd9-739384ddc647','7a7db2cb-21f9-49a3-8f04-9b16f0dec96e','2011-06-16 18:08:31','2011-06-16 18:08:30'), ('8a5ab133-9b38-11e0-abd9-739384ddc647','7a7db2cb-21f9-49a3-8f04-9b16f0dec96e','2011-06-16 18:08:47','2011-06-16 18:08:47'), ('8a5ad99e-9b38-11e0-abd9-739384ddc647','7a7db2cb-21f9-49a3-8f04-9b16f0dec96e','2011-06-16 18:15:30','2011-06-16 18:15:30'), ('8a5b016b-9b38-11e0-abd9-739384ddc647','7a7db2cb-21f9-49a3-8f04-9b16f0dec96e','2011-06-16 18:15:49','2011-06-16 18:15:49'); call aggregate('2011-06-16 18:06:39', '2012-01-01 00:00:00');