Discussion :

[sami_c]

j'ai la table suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

EMISSION(id, titre, duree, ordre)

duree est de type TIME
pour créer une grille je fais :
1, 'emission 1', '0:10:0', 1
1, 'emission 2', '0:5:0', 2
1, 'emission 3', '1:0:0', 3

donc comme vous voyez, je stocke la durrée, mais ce que je cherche c'est afficher pour chaque emission l'heure de début (et si possible l'heure de fin) sachant que la 1er emission commence à partir 04:00 (4h du mat)

[RaelRiaK]

Il faut faire une instruction SQL recursive, capable d'utiliser les resultat qu'elle calcul.

Du genre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
select début from 
(
select top 100 percent e.duree + (select sum(duree) from emission where ordre < e.ordre)  as début
from emission
)

Bon ma requete doit être archi fausse, c'est juste pour te donner une idée
Surtout t'oublie pas d'additionner le tout à tes 4:00 de début.

[sami_c]

j'avoue que j'ai pas bien compris la requete
ça veut dire quoi top 100 percent e.duree ?
Le e c'est l'alias du 1er EMISSION (FROM emission WHERE ordre<...) ou le 2eme ??
PS : j'utilise MySQL 5

[RaelRiaK]

- Cette requete n'est que du SQL simple, donc elle marche normalement partout.
- "TOP 100 Percent" signifie que tu prend tous les resultats renvoyé par la requete. Je crois que c'est obligatoire pour les sous requete en clause FROM. mais ca change rien à la requete.

- j'ai fait une petite erreur de frappe

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT début FROM 
(
SELECT top 100 percent e.duree + (SELECT sum(duree) FROM emission WHERE ordre < e.ordre)  AS début
FROM emission e
)

Le e représente la table emission de la table appelante. C'est comme si tu utilisais 2 fois la table pour les comparer. C'est ce qu'on appelle des requete SQL coréllées ou un truc du genre.

[jeremya]

j'ai la table suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

EMISSION(id, titre, duree, ordre)

duree est de type TIME
pour créer une grille je fais :
1, 'emission 1', '0:10:0', 1
1, 'emission 2', '0:5:0', 2
1, 'emission 3', '1:0:0', 3

donc comme vous voyez, je stocke la durrée, mais ce que je cherche c'est afficher pour chaque emission l'heure de début (et si possible l'heure de fin) sachant que la 1er emission commence à partir 04:00 (4h du mat)

Pour obtenir l'heure de début d'une émission, il faut ajouter les durée de toutes les émissions antérieures.
Pour obtenir l'heure de fin, il faut ajouter la durée de l'émission à son heure de début.
Il faut ensuite ajouter à ces heures 4 heures (la 1er emission commence à partir 04:00).

Par exemple, pour l'émission 3 :

• ajouter les durée de toutes les émissions antérieures
  ADDTIME('0:10:0','0:5:0')='0:15:0'
  ADDTIME('0:15:0','4:0:0')='4:15:0'
  L'heure de début est '4:15:0'
  
• ajouter la durée de l'émission à son heure de début
  ADDTIME('4:15:0','1:0:0')='5:15:0'
  L'heure de fin est '5:15:0'

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
SELECT e1.*,
  ADDTIME(SUM(e2.duree),'4:0:0') as heure_debut,
  ADDTIME(ADDTIME(SUM(e2.duree),e1.duree),'4:0:0') as heure_fin
FROM emission as e1
  LEFT JOIN emission as e2 ON e2.ordre<e1.ordre
GROUP BY e1.ordre

[sami_c]

voilà ce que ça donne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
emission A     00:10:00  	1  	NULL  	    NULL
emission B     00:05:00 	2 	04:10:00 	04:15:00
emission C     01:00:00 	3 	04:15:00 	05:15:00

Avec les colonnes respectives : Emission / durée / ordre / h_debut / h_fin

[jeremya]

Ajoute des IFNULL pour afficher '4:0:0' au lieu de NULL

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT e1.*,
  IFNULL(ADDTIME(SUM(e2.duree),'4:0:0'),'4:0:0') AS heure_debut,
  IFNULL(ADDTIME(ADDTIME(SUM(e2.duree),e1.duree),'4:0:0'),'4:0:0') AS heure_fin
FROM emission AS e1
  LEFT JOIN emission AS e2 ON e2.ordre<e1.ordre
GROUP BY e1.ordre

A part ce problème d'affichage pour la première ligne, est-ce bien les résultats que tu attends ?

[RaelRiaK]

J'aurais une petite question.
Je me rend bien compte que te méthode semble bien plus propre que la mienne. Mais pourrais tu m'expliquer pourquoi ?
Je suis toujours curieux parce que j'adore le SQL ...

[jeremya]

- Cette requete n'est que du SQL simple, donc elle marche normalement partout.
- "TOP 100 Percent" signifie que tu prend tous les resultats renvoyé par la requete. Je crois que c'est obligatoire pour les sous requete en clause FROM. mais ca change rien à la requete.

- j'ai fait une petite erreur de frappe

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT début FROM 
(
SELECT top 100 percent e.duree + (SELECT sum(duree) FROM emission WHERE ordre < e.ordre)  AS début
FROM emission e
)

Le e représente la table emission de la table appelante. C'est comme si tu utilisais 2 fois la table pour les comparer. C'est ce qu'on appelle des requete SQL coréllées ou un truc du genre.

Ce genre de requête est à éviter pour plusieurs raisons :

• utilisation d'un mot clé TOP x PERCENT
  Ce mot clé est propre à certains SGBDR et il est fort probable qu'un autre SGBDR ne la comprenne pas.
• utilisation de sous-requêtes
  Les sous-requêtes ne sont pas acceptées par tous les SGBDR.
  
• lisibilité
  Dans la mesure du possible, essaye de faire des requêtes que l'on peut comprendre dès la première lecture.
  Si on prends ta requête, il est peut probable que des personnes ne connaisant pas ou très peu le SQL puissent la comprendre.
  
  
• noms des fonctions
  Dans ma requête, les seuls éléments qui peuvent ne pas être compris par tous les SGBDR sont les fonctions.
  ADDTIME, IFNULL, etc... sont des fonctions.
  Les noms des fonctions peuvent différer selon le SGBDR utilisé.
  Toutefois, la requête peut être facilement tranposée pour un SGBDR particulier.
  

[RaelRiaK]

Je te remercie Jeremya, c'est tout à fait clair. Etant un habitué de SQL Server et Access (vive Microsoft ...) je dois avoué que je suis trés influencé par leur concept.

Encore merci

Renaud

[sami_c]

Ajoute des IFNULL pour afficher '4:0:0' au lieu de NULL

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT e1.*,
  IFNULL(ADDTIME(SUM(e2.duree),'4:0:0'),'4:0:0') AS heure_debut,
  IFNULL(ADDTIME(ADDTIME(SUM(e2.duree),e1.duree),'4:0:0'),'4:0:0') AS heure_fin
FROM emission AS e1
  LEFT JOIN emission AS e2 ON e2.ordre<e1.ordre
GROUP BY e1.ordre

A part ce problème d'affichage pour la première ligne, est-ce bien les résultats que tu attends ?

voilà ce que ça donne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
emission A     00:10:00  	1  	4:0:0  	    4:0:0
...

... le reste n'a pas changé

[jeremya]

En effet, j'ai oublié un élément dans le deuxième IFNULL.
L'heure de fin de la première émission (e1) n'est pas '4:0:0' mais
ADDTIME(e1.duree,'4:0:0')

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT e1.*,
  IFNULL(ADDTIME(SUM(e2.duree),'4:0:0'),'4:0:0') AS heure_debut,
  ADDTIME(IFNULL(ADDTIME(SUM(e2.duree),'4:0:0'),'4:0:0'),e1.duree) AS heure_fin
FROM emission AS e1
  LEFT JOIN emission AS e2 ON e2.ordre<e1.ordre
GROUP BY e1.ordre

[sami_c]

merci pour la requête mais je pense qu'il y a tjr un problème : parfois ça marche, parfois ça marche pas !!!!

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Table source
============
ordre 	 	duree 		nom
	4 		01:37:16 	OPEN REGGAE 
	3 	 	02:00:39 	DIRTY SOUTH SHIT 
	2 	 	01:29:13 	BOUCAN 
	1 	 	01:44:51 	BOMBATTACK 
	6 	 	02:09:04 	SANG D'ENCRE 
	7 	 	02:02:24 	WEST COAST LA 
 
Résultat de la requête
======================
ordre 	duree 		nom 			heure_debut 	heure_fin
1 	01:44:51 	BOMBATTACK  		04:00:00 	05:44:51
2 	01:29:13 	BOUCAN  		05:44:51 	07:14:04
3 	02:00:39 	DIRTY SOUTH SHIT  	04:00:00 	06:00:39
4 	01:37:16 	OPEN REGGAE  		04:00:00 	05:37:16
6 	02:09:04 	SANG D'ENCRE  		10:11:19 	12:20:23
7 	02:02:24 	WEST COAST LA  		12:20:23 	14:22:47

à partir de la 3eme, ça commence à déconner !!!

[jeremya]

Bonjour,
j'ai identifié le problème.
En fait, la fonction SUM ne fonctonne pas avec des durées, elle ne fonctionne qu'avec des nombres.
Il faut donc convertir les durées en secondes avant de faire la somme.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT e1 . *,
ADDTIME( sec_to_time( ifnull( SUM( time_to_sec( e2.duree ) ) , 0 ) ) , '4:0:0' ) AS heure_debut,
addtime( ADDTIME( sec_to_time( ifnull( SUM( time_to_sec( e2.duree ) ) , 0 ) ) , '4:0:0' ) , e1.duree ) AS heure_fin
FROM emission e1
LEFT JOIN emission e2 ON e2.ordre < e1.ordre
GROUP BY e1.ordre

Pour les conversions :

• time_to_sec pour convertir les durées en secondes
• sec_to_time pour convertir les secondes en durées

Voici les résultats que j'ai obtenu:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
ordre duree      nom              heure_debut   heure_fin 
1      01:44:51  BOMBATTACK       04:00:00      05:44:51 
2      01:29:13  BOUCAN           05:44:51      07:14:04 
3      02:00:39  DIRTY SOUTH SHIT 07:14:04      09:14:43 
4      01:37:16  OPEN REGGAE      09:14:43      10:51:59 
6      02:09:04  SANG D'ENCRE     10:51:59      13:01:03 
7      02:02:24  WEST COAST LA    13:01:03      15:03:27