CODEWITHSUNDEEP
mysqltriggerssummary
windoo
windoo

Reputation: 73

MySQL Triggers, summary each minute

I have some data generated continously and i wonder how i could do something like that: Each minute it will take x records from one minute before, make some kind of summary and put it in the second table. Table looks like: http://puu.sh/bvNA3/9616f1037f.png Cols: ID, ID_pistol, ID_container, start_time, stop_time, volume, brutto, netto, temperature in result for one minute it would be sum of volume, bruto, netto cols and average of temperature.

If anyone have some suggestion i would be grateful.

Upvotes: 1

Views: 1236

Answers (1)

Rimas
Rimas

Reputation: 6024

Use Event Scheduler for this. Suppose ID in first table has AUTO_INCREMENT option. Then I suggest add to second table last_id column (to record last processed ID from first table). Then create event:

CREATE EVENT myevent
  ON SCHEDULE EVERY 1 MINUTE
  DO
    INSERT INTO second_table(volume_sum, bruto_sum, netto_sum, temperature_avg, last_id)
    SELECT SUM(f.volume), SUM(f.brutto), SUM(f.netto), AVG(f.temperature), MAX(f.ID)
      FROM first_table f
     WHERE f.ID > IFNULL((SELECT MAX(last_id) FROM second_table), 0);

This works only if Event Scheduler is started. User with admin privileges can check and start it. To check:

SHOW VARIABLES  like 'event_scheduler';

The answer must be ON. If it's OFF then run:

SET GLOBAL event_scheduler = ON;

Documentation:

Upvotes: 2

Related Questions