y1t66Du7

1DROP TABLE IF EXISTS statistik_report;  
2DROP TABLE IF EXISTS statistik_abstaende;  
3 
4CREATE TABLE  `statistik_report` (  
5 `user_id` int(10) unsigned NOT NULL,  
6 `artikel_id` int(10) unsigned NOT NULL, 
7 `timestamp` int(10) NOT NULL,  
8 `row` int(10) unsigned NOT NULL AUTO_INCREMENT,  
9 PRIMARY KEY (`user_id`,`artikel_id`, `row`)  
10) ENGINE=MyISAM DEFAULT CHARSET=latin1;  
11 
12INSERT INTO `statistik_report` (`user_id`,`artikel_id`, `timestamp`)   
13   SELECT `user_id`, `artikel_id`, `timestamp` FROM `flow_statistik`  
14   LEFT JOIN `flow_karton_artikel` ON `flow_karton_artikel`.`id` = `flow_statistik`.`karton_artikel_id`   
15   LEFT JOIN `flow_artikel` ON `flow_karton_artikel`.`artikel_id` = `flow_artikel`.`id`   
16	WHERE `flow_artikel`.`name` LIKE '%mauspad%'
17    AND `flow_artikel`.`hersteller` LIKE '%hersteller%'
18   
19        AND `flow_karton_artikel`.`zustand` IN ("A", "B", "C") 
20   ORDER BY `flow_statistik`.`user_id`, `flow_statistik`.`timestamp`;  
21 
22CREATE TABLE `statistik_abstaende` AS   
23   SELECT `vorgang2`.`timestamp` - `vorgang1`.`timestamp` as `differenz`  
24   FROM `statistik_report` `vorgang1`  
25   LEFT JOIN `statistik_report` `vorgang2`  
26   ON `vorgang1`.`user_id` = `vorgang2`.`user_id`  
27       AND `vorgang1`.`row` = `vorgang2`.`row` - 1  
28       AND `vorgang1`.`artikel_id` = `vorgang2`.`artikel_id` 
29   WHERE `vorgang2`.`timestamp` IS NOT NULL;  
30 
31DELETE FROM `statistik_abstaende` WHERE `differenz` > 3000;  
32 
33SELECT AVG(`differenz`) / 60 as "Minuten" FROM `statistik_abstaende`