Comment combiner deux lignes et calculer la différence de temps entre deux valeurs d'horodatage dans MySQL?

Si vous n'avez pas l'esprit de la création d'une table temporaire*, alors je pense que la suite devrait bien fonctionner. Je l'ai testé avec de 120 000 dossiers, et l'ensemble du processus se termine en moins de 6 secondes. Avec de 1 048 576 dossiers terminé en un peu moins de 66 secondes - et c'est sur un vieux Pentium III avec 128 mo de RAM:

*En MySQL 5.0 (et peut-être d'autres versions) la table temporaire ne peut pas être un vrai MySQL table temporaire, comme vous ne peut pas se référer à une table TEMPORAIRE plus d'une fois dans la même requête. Voir ici:

http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html

Au lieu de cela, il suffit de déposer/créer un tableau normal, comme suit:

DROP TABLE IF EXISTS `tmp_log`;
CREATE TABLE `tmp_log` (
    `id` INT NOT NULL,
    `row` INT NOT NULL,
    `name` VARCHAR(16),
    `ts` DATETIME NOT NULL,
    `eventtype` VARCHAR(25),
    INDEX `row` (`row` ASC),
    INDEX `eventtype` (`eventtype` ASC)
);

Cette table est utilisée pour stocker un classement et liste numérotée des lignes de la requête de sélection suivante:

INSERT INTO `tmp_log` (
    `id`,
    `row`,
    `name`,
    `ts`,
    `eventtype`
)
SELECT
    `id`,
    @row:=@row+1,
    `name`,
    `ts`,
    `eventtype`
FROM log,
(SELECT @row:=0) row_count
ORDER BY `name`, `id`;

Ci-dessus requête SELECT trie les lignes par le nom et l'id (vous pouvez utiliser le timestamp au lieu de l'id, si tant est que le début des événements apparaissent avant l'arrêt des événements). Chaque ligne est numérotée. En faisant cela, les paires d'événements sont toujours à côté de l'autre, et le numéro de ligne du début de l'événement est toujours un de moins que la ligne id de l'événement d'arrêt.

Maintenant, sélectionnez les paires correspondantes de la liste:

SELECT
    start_log.row AS start_row,
    stop_log.row AS stop_row,
    start_log.name AS name,
    start_log.eventtype AS start_event,
    start_log.ts AS start_time,
    stop_log.eventtype AS stop_event,
    stop_log.ts AS end_time,
    TIMEDIFF(stop_log.ts, start_log.ts) AS duration
FROM
    tmp_log AS start_log
INNER JOIN tmp_log AS stop_log
    ON start_log.row+1 = stop_log.row
    AND start_log.name = stop_log.name
    AND start_log.eventtype = 'start'
    AND stop_log.eventtype = 'stop'
ORDER BY start_log.id;

Une fois que vous avez terminé, il est probablement une bonne idée de supprimer la table temporaire:

DROP TABLE IF EXISTS `tmp_log`;row

Mise à JOUR

Vous pouvez essayer de l'idée suivante, ce qui élimine le temp des tables et des jointures complètement en utilisant des variables pour stocker des valeurs de la rangée précédente. Il trie les lignes par le nom puis le temps de timbre, qui regroupe toutes les valeurs avec le même nom, et met chaque groupe dans l'ordre du temps. Je pense que cela devrait s'assurer que tous les correspondants start/stop événements sont à côté les uns des autres.

SELECT id, name, start, stop, TIMEDIFF(stop, start) AS duration FROM (
    SELECT
        id, ts, eventtype,
        (@name <> name) AS new_name,
        @start AS start,
        @start := IF(eventtype = 'start', ts, NULL) AS prev_start,
        @stop  := IF(eventtype = 'stop',  ts, NULL) AS stop,
        @name  := name AS name
    FROM table1 ORDER BY name, ts
) AS tmp, (SELECT @start:=NULL, @stop:=NULL, @name:=NULL) AS vars
WHERE new_name = 0 AND start IS NOT NULL AND stop IS NOT NULL;

Je ne sais pas comment il va comparer à Ivar Bonsaksen de la méthode, mais il fonctionne assez rapide sur ma boîte.

Voici comment j'ai créé les données de test:

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=MyISAM;

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();

De l'essayer.

select start.name, start.ts start, end.ts end, timediff(end.ts, start.ts) duration from (
    select *, (
        select id from log L2 where L2.ts>L1.ts and L2.name=L1.name order by ts limit 1
    ) stop_id from log L1
) start join log end on end.id=start.stop_id
where start.eventtype='start' and end.eventtype='stop';

J'ai eu de travail en combinant vos deux solutions, mais la requête n'est pas très efficace, et je n'aurais pensé qu'il y aurait une façon plus intelligente d'omettre ces indésirables lignes.

Ce que j'ai maintenant est:

SELECT y.name, 
       y.start, 
       y.stop, 
       TIMEDIFF(y.stop, y.start) 
  FROM (SELECT l.name, 
               MAX(x.ts) AS start, 
               l.ts AS stop 
          FROM log l 
          JOIN (SELECT t.name, 
                       t.ts 
                  FROM log t 
                 WHERE t.eventtype = 'start') x ON x.name = l.name 
                       AND x.ts < l.ts 
         WHERE l.eventtype = 'stop' 
      GROUP BY l.name, l.ts) y 
WHERE NOT EXISTS (SELECT 1 
                    FROM log AS d 
                   WHERE d.ts > y.start AND d.ts < y.stop AND d.name = y.name 
                         AND d.eventtype = 'stop')

Limitée à un "nom", la requête va d'environ 0,5 secondes environ 14 secondes quand j'inclus le WHERE NOT EXISTS clause... La table va devenir très grand et je suis inquiet au sujet de combien d'heures cela va prendre pour tous les noms à la fin. Je suis actuellement seulement avoir des données pour le mois de juin 2010 dans le tableau (10 jours) et c'est maintenant à 109888 lignes.