J'ai une situation que je suis sûr est assez courante et je me dérange vraiment que je ne peux pas comprendre comment le faire ou quoi de rechercher pour trouver un exemple / solution pertinent. Je suis relativement nouveau à MySQL (utilisez MSSQL et PostgreSQL plus tôt) et que chaque approche que je puisse penser est bloquée par certaines caractéristiques manquant de mysql.
J'ai une table "log" qui répertorie simplement de nombreux événements avec leur horodatage (stocké en tant que type DateTime). Il y a beaucoup de données et de colonnes dans le tableau non pertinentes pour ce problème, dites donc que nous avons une table simple comme celle-ci: disons que certaines lignes ont un eventType = 'Démarrer' et d'autres ont un eventType = 'Stop'. Ce que je veux faire, c'est d'en quelque sorte couple chaque "Startrow" avec chaque "stoprow" et trouvez la différence de temps entre les deux (puis résumer les durées à chaque nom, mais ce n'est pas là que le problème réside). Chaque événement "Démarrer" devrait avoir un événement "Stop" correspondant se produire à une autre étape plus tard, puis sur l'événement "Démarrer", mais en raison de problèmes / bugs / écrasé avec le collecteur de données, il est possible que certains manquent. Dans ce cas, je voudrais ignorer l'événement sans un "partenaire". Cela signifie que compte tenu des données: .. Je voudrais juste ignorer l'événement de début de 19:45 et non seulement obtenir deux lignes de résultat à la fois en utilisant l'arrêt 20:13 événement comme heure d'arrêt. J'ai essayé de rejoindre la table de manière à différentes manières, mais les problèmes clés pour moi semblent être de trouver un moyen d'identifier correctement l'événement "Stop" correspondant à L'événement "Démarrer" pour le "nom" donné. Le problème est exactement le même que vous auriez si vous aviez une table avec des employés estampant et hors du travail et que vous vouliez savoir combien ils étaient réellement au travail. Je suis sûr qu'il doit y avoir des solutions bien connues, mais je ne peux pas sembler les trouver ...
6 Réponses :
Que diriez-vous:
SELECT start_log.ts AS start_time, end_log.ts AS end_time FROM log AS start_log INNER JOIN log AS end_log ON (start_log.name = end_log.name AND end_log.ts > start_log.ts) WHERE NOT EXISTS (SELECT 1 FROM log WHERE log.ts > start_log.ts AND log.ts < end_log.ts) AND start_log.eventtype = 'start' AND end_log.eventtype = 'stop'
Pour clarifier, il peut y avoir (et sera) de nombreux événements entre les deux, mais pas pour le "nom" en question qui est de type "Démarrer" ou "Stop". Il devrait toujours être possible de faire quelque chose comme celui-ci en modifiant la sélection de «interdits», mais le problème est que cela est extrêmement lent. J'ai réécrit votre requête pour s'adapter aux tables actuelles et testées, et même lorsque je limite la requête à un seul "nom" et la période à un jour, il faut plusieurs minutes.
Le retour est vide parce que je n'ai pas réécris les conditions et il n'y a jamais eu de cas où le «début» et «arrêter» se suivent, mais je le faisais juste pour le tester. Je ne suis pas que dans la manière dont une requête utilise des index, tous les champs impliqués sont indexés mais pas dans le même indice. Cela pourrait-il être la raison de la réponse extrêmement lente?
Yipes. Cela prendra certainement Quelques performances , mais cela ne devrait pas être si génial. Expliquer un Expliquer Montrer ce que MySQL fait, exactement, cela le rend si lent.
La production d'explication ne me dit vraiment pas beaucoup, mais comme mentionné ci-dessus, la table compte environ 100 000 rangées dedans, il est tout simplement simplement à cause de cela.
Peut-être que c'est une table terriblement petite pour rencontrer des problèmes. Je m'attendrais à ce qu'un indice couvrant (nom, TS, EventType) de travailler des merveilles.
J'ai ajouté un tel index et l'a rendu avec ma "solution combinée" mais je ne peux voir aucun gain de performance. Cela signifie probablement que les indices existants faisaient déjà leur travail.
Je l'ai eu à travailler en combinant vos deux solutions, mais la requête n'est pas très efficace et je penserais qu'il y aurait un moyen plus intelligent d'omettre ces lignes non désirées.
Ce que j'ai maintenant est: < BR>
limité à un "nom" donné La requête passe d'environ 0,5 seconde à environ 14 secondes lorsque j'inclus le où il n'existe pas de clause .. . La table deviendra assez grande et je suis inquiet de combien d'heures cela prendra pour tous les noms à la fin. Je n'ai actuellement que des données pour juin 2010 dans le tableau (10 jours) et il est maintenant à 109888 lignes.
Quelqu'un n'a-t-il aucune approche meilleure que ce qui précède?
Je crois que cela pourrait être un moyen plus simple d'atteindre votre objectif:
SELECT
start_log.name,
MAX(start_log.ts) AS start_time,
end_log.ts AS end_time,
TIMEDIFF(MAX(start_log.ts), end_log.ts)
FROM
log AS start_log
INNER JOIN
log AS end_log ON (
start_log.name = end_log.name
AND
end_log.ts > start_log.ts)
WHERE start_log.eventtype = 'start'
AND end_log.eventtype = 'stop'
GROUP BY start_log.name
Il court en effet, mais il a le problème opposé de celui des poneys OMG. Il combine un seul événement d'arrêt avec deux événements de démarrage dans le cas où un événement d'arrêt est manquant, donnant ainsi un chronogène total trop élevé.
Désolé mon mauvais! Vous devrez également utiliser max (start_log.ts) dans le TimeDiff, afin de calculer le chronodiff correct. J'ai édité le SQL, et cela devrait maintenant produire le résultat souhaité.
Cette méthode est élégante et intelligente, mais reste lente. Il a pris plus de 52 minutes avec ma table d'essai de 120 000 lignes, contre moins de 6 secondes à l'aide de ma méthode de la table temporaire. En utilisant Expliquez sur mes données de test pour obtenir une estimation approximative du nombre de lignes que chaque méthode doit traiter, montre cette requête comme suit: 49 095 x 24 000 = 1 178 280 000. La méthode Table Table: 120 000 + 50,168 = 170.168. Ces chiffres se corrélent bien avec mes résultats réels. En supposant que la méthode que j'ai utilisée produisent réellement les résultats corrects (les deux produisent le même nombre d'enregistrements), cela peut valoir la peine d'essayer.
Je ne vois pas comment vous avez pu faire de cette requête prendre 52 minutes ... je viens d'ajouter 1.000.000 lignes de test avec début, arrêt et d'autres événements à une table comme celle-ci, ajouté des index (importants!) et la requête a pris 15 secondes pour produire 150 000 rangs de résultat ...
C'est génial que vous puissiez le faire courir si vite et me mène de me demander ce que je fais différemment. J'ai créé la table comme indiqué dans la question, avec des index sur les champs Nom, TS et EventType. Je dois admettre que je ne m'attendais pas à ce qu'il fallait aussi longtemps que cela, mais j'ai dirigé votre requête et la mienne plusieurs fois, et obtenez les mêmes résultats à chaque fois. En fait, la seule raison pour laquelle je suis venu avec ma solution de table Temp était parce que ma première tentative, semblable à la vôtre, était très lente. Je pourrais l'essayer sur un ordinateur différent, avec plus de ressources.
Il fonctionne à une vitesse raisonnable, mais: vous devez échanger les arguments de TimeDiff (donne des résultats négatifs) et il combine toujours un événement unique avec plusieurs événements de démarrage lorsqu'un ou plusieurs événements de début sont manquants. Le max dans le temporisateur n'a pas semblé se débarrasser de cela, et c'est le noyau de mon problème. Le début et l'arrêt correspondants ont généralement des minutes entre eux alors qu'il s'agit d'heures d'arrêt et de l'événement suivant. Cela signifie qu'une telle "mauvaise combinaison" ajoute des heures au résultat et ruine complètement le rapport.
Je ne vois pas comment il pourrait éventuellement combiner un événement d'arrêt sans événement de départ correspondant avec quoi que ce soit aussi longtemps que cela ne correspond que des événements avec le même nom ... J'avais l'impression que chaque nom n'était censé être utilisé que par un Groupe de start-stop (plus d'autres événements entre les deux).
Si cela ne vous dérange pas de créer une table temporaire *, je pense que ce qui suit devrait bien fonctionner. Je l'ai testé avec 120 000 enregistrements et tout le processus se termine en moins de 6 secondes. Avec 1 048 576 enregistrements, il est terminé en un peu moins de 66 secondes - et c'est sur un ancien Pentium III avec 128 Mo de RAM:
* dans mysql 5.0 (et peut-être d'autres versions) La table temporaire ne peut pas être une vraie table temporaire MySQL, comme vous ne pouvez pas vous référer. à une table temporaire plus d'une fois dans la même requête. Voyez ici: P>
http: //dev.mysql.com/doc/refman/5.0/fr/Temporary-table-problems.html p>
Au lieu de cela, il suffit de supprimer / créer une table normale, comme suit: P >
CREATE TABLE `table1` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(5),
`ts` DATETIME,
`eventtype` VARCHAR(5),
PRIMARY KEY (`id`),
INDEX `name` (`name`),
INDEX `ts` (`ts`)
) ENGINE=InnoDB;
DELIMITER //
DROP PROCEDURE IF EXISTS autofill//
CREATE PROCEDURE autofill()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 1000000 DO
INSERT INTO table1 (name, ts, eventtype) VALUES (
CHAR(FLOOR(65 + RAND() * 26)),
DATE_ADD(NOW(),
INTERVAL FLOOR(RAND() * 365) DAY),
IF(RAND() >= 0.5, 'start', 'stop')
);
SET i = i + 1;
END WHILE;
END;
//
DELIMITER ;
CALL autofill();
Je n'ai pas testé cette solution que je dois admettre, car cela doit être utilisé pour un rapport, et je ne peux supposer que l'utilisateur a le droit de créer des tables. Les véritables tables temporaires et la déclaration avec la déclaration seraient d'une grande aide dans cette question, mais c'est certaines des approches que je suppose être indisponibles en raison du manque de fonctionnalités de MySQL.
Pouvez-vous modifier le collecteur de données? Si oui, ajoutez un champ de groupe_id (avec un index) dans la table du journal et écrivez l'identifiant de l'événement de démarrage (même ID pour le démarrage et la fin dans le groupe_id). Ensuite, vous pouvez faire
SELECT S.id, S.name, TIMEDIFF(E.ts, S.ts) `diff`
FROM `log` S
JOIN `log` E ON S.id = E.group_id AND E.eventtype = 'end'
WHERE S.eventtype = 'start'
Essayez ceci.
J'aime votre méthode stop_id, son intelligent. Cela m'a aidé à résoudre un problème similaire que j'ai. Merci