Maximum pour des données proches

[flymer]

Bonjour à tous,

Débutant en SQL, j'ai un petit problème pour effectuer une requête.

J'ai 2 tables : une contenant des observations (heure => V1 et hauteur => V2), l'autre des prédictions (heure => V1 et hauteur => V2).

Il faut que : pour chaque tuple dans la table prédiction je récupère l'observation dont V2 est maximale, parmi toutes les observations dans une plage de plus ou moins 1h par rapport à l'heure de prédiction (V1).

Voici un exemple :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
>  obs
  V1 V2
1  1  5
2  2  9
3  3  3
4  4 20
5  5  2
 
> pred
   V1 V2
1 1.2  6
2 3.9 17

Il faudrait donc obtenir ceci :

Code :

1
2
3
4
> res
  V1 V2
1  2  9
2  4 20

Je pensais à une requête de ce genre :

Code :

1
2
3
4
5
6
SELECT DISTINCT obs.V1, obs.V2
FROM obs,pred
WHERE obs.V2>=(SELECT max(obs.V2)
               FROM obs,pred
               WHERE abs(obs.V1 - pred.V1) <  1
               GROUP BY pred.V1)

Mais elle n'est pas bonne.

Si vous avez des suggestions, je suis preneur!

Je vous remercie.

[aieeeuuuuu]

Bonjour,

Je n'ai pas bien compris ton exemple (il semble y avoir 3 colonnes dans ton jeu de données, alors que tu ne parle que de deux colonnes : V1 et V2 ?)
Mais d'après l'explication que tu as données, je ferai quelque chose comme ça :

Code SQL :

1
2
3
4
5
6
7
8
9
10
11
 
SELECT 
	p.V1 AS HeurePrediction,
	p.V2 AS HauteurPrediction,
	MAX(o.V2) AS MaxHauteurObservation
FROM pred p
LEFT OUTER JOIN obs o
	ON o.V1 BETWEEN DATEADD(HOUR, -1, p.V1) AND DATEADD(HOUR, 1, p.V1)
GROUP BY 
	p.V1 AS HeurePrediction,
	p.V2 AS HauteurPrediction

Je te laisse éventuellement adapter la fonction de date DATEADD (qui permet dans cette requête d'ajouter/retirer une heure) en fonction de ton SGBDR

[flymer]

Excuse moi, la première colonne n'existe pas : c'est juste le numéro du tuple (je travaille sous R).

J'utilise le package "sqldf" sous R pour effectuer mes requêtes SQL et il ne comprend pas la fonction dateadd en effet ; j'ai donc modifié ton code comme ceci (et je veux récupérer les infos de obs, non de pred) :

Code :

1
2
3
4
5
SELECT o.V1 , MAX(o.V2) 
FROM obs o
LEFT OUTER JOIN pred p
	ON  abs(o.V1 - p.V1) <  0.083
GROUP BY o.V1

Mais on n'obtient toujours pas ce qu'il faut!
En tout cas, merci d'avoir pris le temps de me répondre.

[aieeeuuuuu]

Mais on n'obtient toujours pas ce qu'il faut!

C'est a dire ???

tu as inversé l'ordre des tables dans la jointure externe par rapport à la requête que je t'avais donnée, il est donc normal que les résultats ne soient pas ceux attendus !


d'autre part :

Code SQL :

1
2
 
ON abs(o.V1 - p.V1) < 0.083

hmmm... tu peux nous en dire plus la dessus ? je ne comprend pas !

Dans ton jeu d'essai, il ne s'agit pas de date mais d'entier, pour tester, tu peux déjà faire simplement

Code SQL :

1
2
3
4
5
6
7
8
9
10
11
 
SELECT 
	p.V1 AS HeurePrediction,
	p.V2 AS HauteurPrediction,
	MAX(o.V2) AS MaxHauteurObservation
FROM pred p
LEFT OUTER JOIN obs o
	ON o.V1 BETWEEN p.V1 - 1 AND p.V1 + 1
GROUP BY 
	p.V1 AS HeurePrediction,
	p.V2 AS HauteurPrediction

[flymer]

Oui, j'ai oublié de le préciser, mes dates sont des entiers (nombre de jours depuis une date référence).

Désolé pour le 0.083, car dans mes vraies données la condition c'est plus ou moins 0.083 jour (i.e. plus ou moins 2h). Je fais passer dans l'exemple suivant cette valeur à 0.1

Je reprend pour que ce soit plus clair :

Mes tables sont comme ceci :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
> obs
   V1      V2
37733.7   5
37733.9   9
37734.1   3
37734.5   10  
37734.8   7
 
> pred
   V1      V2
37733.0   2
37733.6   4
37734.0   9
37734.6   7 
37735.1   5

Pour chaque tuple de pred, je veux l'observation (V1 et V2) dont V2 maximale parmi toutes les observations qui se trouvent dans abs(obs.V1 - pred.V1) < 0.1.

On devrait donc obtenir ceci :

Code :

1
2
3
4
   V1      V2
37733.7   5
37733.9   9
37734.5   10

Avec ton code, j'obtiens :

Code :

1
2
3
4
5
6
       V1    V2    MAX(o.V2)
 37733.0    2         9
 37733.6    4        10
 37734.0    9        10
 37734.6    7        10
 37735.1    5        10

J'espère que c'est plus clair comme ça !

[aieeeuuuuu]

Envoyé par flymer

> pred

V1 V2
37733.0 2
37733.6 4
37734.0 9
37734.6 7
37735.1 5

Pour chaque tuple de pred, [...]
On devrait donc obtenir ceci :

V1 V2
37733.7 5
37733.9 9
37734.5 10

Pour chaque ligne de pred !!! dans ton exemple, le résultat attendu ne contient pas le même nombre de lignes que la table pred ????
ce que te donne la requête que je t'ai donnée :

Avec ton code, j'obtiens :

V1 V2 MAX(o.V2)
37733.0 2 9 --> Max de obs.V2 pour V1 = 37733.0 +/- 1
37733.6 4 10
37734.0 9 10
37734.6 7 10
37735.1 5 10

J'espère que c'est plus clair comme ca!

absolument pas !
Ton exemple ne correspond pas à ta demande

[flymer]

Oui, je suis vraiment désolé, j'ai répondu en speed hier soir

Quand j'ai dis "pour chaque ligne de pred", je sous-entendais aussi d'enlever directement les lignes de pred n'ayant pas de correspondance dans obs, d'où peut-être la confusion.

J'ai en effet oublié de mettre 0.1 au lieu de 1 dans ton dernier code, ce qui me rend maintenant :

Code :

1
2
3
4
5
6
       V1    V2    MAX(o.V2)
 37733.0    2       <NA>
 37733.6    4        5.0
 37734.0    9         9.0
 37734.6    7       10.0
 37735.1    5      < NA>

L'idée est là, mais au lieu de récupérer en 1ere colonne le V1 de pred, il me faudrait le V1 d'obs. Je change donc ton code comme ceci (j'enlève les "AS") :

Code :

1
2
3
4
5
SELECT o.V1, MAX(o.V2) 
FROM pred p
LEFT OUTER JOIN obs o
	ON o.V1 BETWEEN p.V1 - 0.1 AND p.V1 + 0.1
GROUP BY o.V1

Ca me renvoie maintenant :

Code :

1
2
3
4
5
6
       V1   MAX(o.V2)
      NA        <NA>
 37733.7       5.0
 37733.9       9.0
 37734.1       3.0
 37734.5      10.0

Y'a un souci, car ça retourne la ligne 37734.1 3.0.
En effet, j'ai l'impression que quand le code arrive à la 3eme ligne de pred (37734.0 9), il renvoie toutes les valeurs de obs dont V1 est dans [37733.9 ; 37734.1], au lieu d'une seule valeur (celle dont V2 est maximale pour les obs où V1 est dans cet intervalle).

Enfin bon, t'embête pas plus longtemps avec ça (je vais coder sous R), je te remercie pour ce que t'as fait et m'a appris la commande LEFT OUTER JOIN