Différence valeur entre ligne [Résolu]

[doudouallemand]

Bonjour.

J'ai une table avec 2 colonnes : date_time (datetime) et value (int)

Code :

1
2
3
4
5
 
2011-05-03 20:00:00     200
2011-05-03 20:15:00     220
2011-05-03 20:30:00     210
2011-05-03 20:45:00     190

et je cherche à faire la différence des value pour obtenir

Code :

1
2
3
4
 
2011-05-03 20:15:00     20  (220 - 200)
2011-05-03 20:30:00     -10 (210 - 220)
2011-05-03 20:45:00     -20 (190 - 210)

Je ne parviens pas à trouver la requête car je n'ai pas d'id (et je ne peux pas modifier la structure de la table.

Quelle requête peut me retourner ce résultat svp ?

Merci d'avance,
Cordialement

[skuatamad]

MySql n'est pas le SGBD le plus adapté pour ce genre de requête car il ne connaît pas les fonctions analytiques, notamment LAG. Il faut donc passer par quelque chose de moins perfomant.

J'ai quand même un doute sur la validité de ma requête car je pense que t1.date_time dans la ligne :
where t3.date_time < t1.date_time
est un peu bas (2 niveaux de requêtes corrélées, il me semble que c'est trop bas).

J'ai testé sur oracle en évitant les fonctionnalités spécifiques, et la requête a fonctionné, donc je propose quand même la solution. Mais je ne sais pas si elle fonctionnera sur MySql car même sur Oracle je trouve étrange qu'elle ait fonctionné...

Code :

1
2
3
4
5
6
7
8
9
10
11
12
SELECT date_time,
       val - prev_val AS diff
  FROM (SELECT date_time,val,
               (SELECT val 
                  FROM t t2 
                 WHERE date_time = (SELECT max(date_time) 
                                      FROM t t3 
                                     WHERE t3.date_time < t1.date_time)
               ) AS prev_val
          FROM t t1
        ) t4
  WHERE prev_val IS NOT NULL

[edit] En fait non, ça semble normal que ça fonctionne, pour que l'erreur se produise il faudrait en plus une sous-requetête dans la requête corrélée du select.
Ca devrait donc aussi fonctionner sur MySql.

[skuatamad]

Bon finalement je ne suis pas sûr que ça fonctionne sur MySql...
D'après un expert Oracle ma requête ne devrait pas forcément fonctionner sur Oracle même si elle fonctionne...

Peux tu nous faire un retour sur MySql stp ? ou quelqu'un peut il tester ?

Attention même si elle fonctionne sur MySql aujourd'hui, il est peut être possible que ça change dans les versions à venir, il faudrait pour en être sûr avoir l'avis d'un expert MySql ou d'un expert SQL ANSI.

[doudouallemand]

Bonjour.

Et bien même si elle n'est pas censé fonctionner, elle fait parfaitement ce que j'attends d'elle
Le résultat correspond tout à fait à mes attentes.

Merci de ton aide

[SQLpro]

Le risque de cette requête est
1) qu'elle plante s'il y a des doublons (une des sous requêtes introduite par "WHERE date_time = (" peut retourner de multiples valeurs)
2) qu'elle ne fournisse pas le bon résultat après correction de 1).

Il n'y a pas de véritables solutions de remplacement aux fonctions analytiques comme le ranking, la numérotation ou LEAD et LAG....
Sans compter que les temps de réponse sont catastrophique en comparaisons à une solution avec ces fonctions...

A +

[doudouallemand]

Envoyé par SQLpro

Le risque de cette requête est
1) qu'elle plante s'il y a des doublons (une des sous requêtes introduite par "WHERE date_time = (" peut retourner de multiples valeurs)
2) qu'elle ne fournisse pas le bon résultat après correction de 1).

Il n'y a pas de véritables solutions de remplacement aux fonctions analytiques comme le ranking, la numérotation ou LEAD et LAG....
Sans compter que les temps de réponse sont catastrophique en comparaisons à une solution avec ces fonctions...

A +

Du coup, que me conseillerais-tu pour pallier à ces problèmes stp ?

[skuatamad]

Effectivement, je n'avais pas pensé au doublon.
doudouallemand, pour être sûr que ça ne plantera pas dans ton cas précis tu peux rajouter une contrainte d'unicité sur date_time comme ça tu sera sûr que :

Code :

1
2
3
4
5
6
 
...
SELECT val 
  FROM t t2 
 WHERE date_time =
...

renverra toujours une ligne. Je pense que sur un date_time cette contrainte a un sens même fonctionel, mais c'est à toi de t'en assurer.

Sinon, donc à priori pas de contre-indication de la norme sur l'utilisation de t1.date_time dans WHERE t3.date_time < t1.date_time.

Merci de ton retour SQLPro.

[doudouallemand]

Petit complément : si je souhaite que sur les dates, ce ne soit plus entre lignes mais entre la première valeur d'une journée et la dernière valeur de cette journée, pour tous les jours présents, je dois modifiés comment la requête svp ?

Dans mon exemple ci-dessous, on fera aura donc une seule ligne de retour qui fera la différence entre la valeur de 20h (1ère de la journée) et la valeur de 20h45 (dernière de la journée).

Merci

[skuatamad]

Ben à priori dans ce cas c'est beaucoup plus simple, il suffit de faire la différence entre la date min et la date max par jour, non ?

Code :

1
2
3
SELECT DATE_FORMAT(date_time, '%Y-%m-%d'), max(date_time) - min(date_time) AS diff
  FROM t
 GROUP BY DATE_FORMAT(date_time, '%Y-%m-%d')

Il faudra peut être utiliser DATE_SUB à la place de - (moins), et peut être passer par une sous requête.

[doudouallemand]

Merci de ta réponse.
Cordialement