Faire la moyenne de durée à partir de champs dateTimeDebut/dateTimeFin [Résolu]

[miam84]

Bonjour,
j'ai besoin d'aide pour la formation d'une requête dans MySQL 5.5 permettant d'avoir la moyenne de la durée des tournois pour une saison. Voici plus d'information:

J'ai une table "tournoi" dans ma base de donnée qui contient diverses informations pour des tournois de jeux de cartes. Voici les champs :
date type date qui est la date du tournoi et clé primaire
type type varchar(10) qui est le type du tournoi
saison type year(4) qui fait référence à la saison pour laquelle le tournoi est joué.
heureDebut type time qui est l'heure de début du tournoi.

J'ai également une table "elimination" qui contient:
tournoiDate type date qui est la date du tournoi et clé étrangère
membreSurnomAEteElimine varchar(20)
membreSurnomEliminePar varchar(20)
heure datetime qui est la date/heure d'élimination.

Je précise que je ne veux pas devoir modifier ces champs, car ils sont modélisé comme cela pour une raison particulière. Ex: saison ne peut pas être déduit de date, car on peut rattraper un tournoi annulé l'année suivante, etc...

A présent, je peux déduire la durée d'un tournoi de la saison avec un TIMEDIFF() entre CONCAT( date, " ", heureDebut ) de la table tournoi et l'heure de fin qui correspond a la dernière élimination de ce tournoi, donc "...max(heure) FROM elimination...".

Voici ma requête qui fonctionne

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT TIMEDIFF( 
(
SELECT max( heure )
FROM `tournoi`
INNER JOIN elimination ON date = tournoiDate
WHERE date = '2011-01-22'
GROUP BY tournoiDate
) ,
(
SELECT CONCAT( date, " ", heureDebut ) AS dateDebut
FROM `tournoi`
INNER JOIN elimination ON date = tournoiDate
WHERE date = '2011-01-22'
GROUP BY date
)
) AS duration

A présent, j'aurai aimé enlever le WHERE de chaque subquery afin de faire un AVG() sur plusieurs TimeDiff() (donc sur plusieurs durée de jeu) afin d'avoir la moyenne des temps de jeu par saison. Mais dans ce cas, le TimeDiff() ne fonctionne plus car il ne peut pas traiter plusieurs résultats.

Avez-vous une solution a me présenter afin que je n'ai pas besoin de rajouter un champ "durée" dans la table Tournoi, car ce ne serait pas très logique d'un point de vue redondance, vu que c'est un champ qui peut être déduit...

En espérant avoir réussi à me faire comprendre dans mon explication, je vous remercie d'avance.
Salutations
Miam84

[skuatamad]

Tu fais une jointure inutile pour récupérer la dateDebut :
Je ne pense pas qu'il soit nécessaire de passer par une sous-requête :

Code :

1
2
3
4
5
6
7
8
9
SELECT t.saison, avg(timediff(fin.h_fin, concat(t.date," ",t.heureDebut))) AS moy_duree
  FROM tournoi t
  JOIN (SELECT t2.date,  max( e.heure ) AS h_fin
          FROM tournoi t2
          JOIN elimination e ON t.date = e.tournoiDate
         GROUP BY t2.date
       ) fin
    ON t.date = fin.date
 GROUP BY t.saison

Mais sinon comme ça :

Code :

1
2
3
4
5
6
7
8
9
10
11
SELECT t3.saison, avg(t3.duree) AS moy_duree
  FROM (SELECT t.date, t.saison, timediff(fin.h_fin, concat(t.date," ",t.heureDebut)) AS duree
          FROM tournoi t
          JOIN (SELECT t2.date,  max( e.heure ) AS h_fin
                  FROM tournoi t2
                  JOIN elimination e ON t.date = e.tournoiDate
                 GROUP BY t2.date
                ) fin
            ON t.date = fin.date
       ) t3
 GROUP BY t3.saison

PS : Un id autoincrémenté en PK de tournoi est nettement préférable à la date du tournoi, en cas de replanification de tournois...
Il ne faut pas nommer une colonne date car c'est un mot clé réservé.

[miam84]

Merci skuatamad pour ta réponse ,

en effet en ce qui concerne le champ date ce n'est pas au top, mais j'ai repris le code tel quel et la modification de la DB entraînerai un remaniement de tout les codes appellant, donc je me dis tant que ça n'induit pas de fatal error j'essaie de faire avec...

Pour ce qui est du résultat, si j'implémente ta requête, ça a l'air de marcher nickel, il y a juste un truc que je ne comprend pas c'est ce qui m'arrive en retour:

Code :

1
2
3
4
 
saison 	moy_duree
2010 	54500
2011 	66125

En fait je m'attendais à avoir un time stamp ou une difference de type time mais là je ne comprend pas bien le type de ce qui me vient en retour...

[skuatamad]

Effectivement le résultat est étrange :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
mysql> SELECT * FROM t;
+---------------------+---------------------+
| c1                  | c2                  |
+---------------------+---------------------+
| 2011-06-10 15:00:00 | 2011-06-10 10:00:00 |
| 2011-05-10 15:00:00 | 2011-05-09 10:00:00 |
| 2011-05-02 15:00:00 | 2011-04-25 10:00:00 |
| 2011-05-02 15:22:10 | 2011-04-25 10:01:30 |
| 2011-05-02 15:22:10 | 2011-04-25 10:51:50 |
+---------------------+---------------------+
5 rows IN SET (0.00 sec)
 
mysql> SELECT timediff(c1,c2)
    ->   FROM t;
+-----------------+
| timediff(c1,c2) |
+-----------------+
| 05:00:00        |
| 29:00:00        |
| 173:00:00       |
| 173:20:40       |
| 172:30:20       |
+-----------------+
5 rows IN SET (0.00 sec)
 
mysql> SELECT avg(timediff(c1,c2))
    ->   FROM t;
+----------------------+
| avg(timediff(c1,c2)) |
+----------------------+
|   1105012.0000000000 |
+----------------------+
1 row IN SET (0.00 sec)

Est ce qu'un résultat en heure minutes secondes conviendrait mieux :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mysql> SELECT hour(timediff(c1,c2)) AS heure,
    ->        minute(timediff(c1,c2)) AS min,
    ->        second(timediff(c1,c2)) AS sec
    ->   FROM t;
+-------+------+------+
| heure | min  | sec  |
+-------+------+------+
|     5 |    0 |    0 |
|    29 |    0 |    0 |
|   173 |    0 |    0 |
|   173 |   20 |   40 |
|   172 |   30 |   20 |
+-------+------+------+
5 rows IN SET (0.00 sec)
 
mysql> SELECT avg(hour(timediff(c1,c2))) AS heure,
    ->        avg(minute(timediff(c1,c2))) AS min,
    ->        avg(second(timediff(c1,c2))) AS sec
    ->   FROM t;
+----------+---------+---------+
| heure    | min     | sec     |
+----------+---------+---------+
| 110.4000 | 10.0000 | 12.0000 |
+----------+---------+---------+
1 row IN SET (0.00 sec)

Donc avec ton code ça donnerait :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
SELECT t.saison, 
       avg(hour(timediff(fin.h_fin, concat(t.date," ",t.heureDebut)))) AS moy_heure,
       avg(minute(timediff(fin.h_fin, concat(t.date," ",t.heureDebut)))) AS moy_min,
       avg(second(timediff(fin.h_fin, concat(t.date," ",t.heureDebut)))) AS moy_sec
  FROM tournoi t
  JOIN (SELECT t2.date,  max( e.heure ) AS h_fin
          FROM tournoi t2
          JOIN elimination e ON t.date = e.tournoiDate
         GROUP BY t2.date
       ) fin
    ON t.date = fin.date
 GROUP BY t.saison

[miam84]

Et ben...
trop beau, ça à l'air d'aller nickel.
Un grand merci à toi et à bientôt
++
Miam84