Discussion :

[Chris1982]

Bonjour à tous,

J'aurais besoin d'aide à propos d'une requête SQL (MSSQL 2005) qui me pose problème depuis 2 jours. Voici quelques informations.

J'ai 3 tables (je simplifie les noms et nombre de champs pour une meilleure lisibilité):

PRODUITS: contient les champs prdID (numeric) et prdName (varchar)

VILLES: contient les champs postcodeID (numeric), postcode (numeric), city (varchar), latitude (numeric) et longitude (numeric)

OTM: contient les champs _prdID (numeric) et _postcodeID

et une fonction SQL (scalar): getDistance qui accepte 3 paramètres: postcodeID, latitude et longitude qui renvoie la distance calculée entre la ville dont le postcodeID est spécifié (et qui est connu dès le départ) et les autres villes inclues dans la requête.

Par exemple, prenons un client qui habite VilleX dont le postcodeID est 555.
La requête suivante

Code :

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

1
2
SELECT city, getDistance(555, latitude, longitude) AS [distance]
FROM VILLES ORDER BY distance

retourne toutes les villes présentes dans la table VILLES classées en fonction de leur distance pa rapport à VilleX. cette fonction marche à merveille mais clairement, je dois l'utiliser dans une requête qui utilise la table VILLES (pour disposer des deux champs latitude et longitude).

Maintenant, mon problème.

Chaque produit contenu dans la table PRODUITS peut être retiré par le client à différents endroits dans le pays. Pour chacun de ces endroits existe un enregistrement dans la table OTM avec chaque fois le prdID du produit et le postcodeID de la ville.

Ce que j'attends de la requête est qu'elle me renvoie chaque produit avec UNIQUEMENT l'endroit le plus proche de celui où se trouve le client. Les champs retournés doivent être prdID, prdName, postcode, city et distance.
Et c'est là que je sèche.

J'ai essayé avec une sous-requête telle que

Code :

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

1
2
3
4
SELECT _prdID, MIN(getDistance(555, POSTCODES.latitude, POSTCODES.longitude)) AS [Distance] 
FROM OTM LEFT JOIN POSTCODES 
ON OTM._postcodeID = POSTCODES.postcodeID 
GROUP BY _prdID

Fonctionne très bien mais à cause du GROUP BY, je suis limité à un seul champ: _prdID ou _postcodeID (autrement je me retrouve avec plus d'un enregistrement pour le même produit, ce qui n'est pas le but). Or, j'ai besoin de ces deux champs pour pouvoir retrouver les autres informations dont j'ai besoin.

J'espère que mon explication est un tant soit peu claire et que quelqu'un peut me donner un coup de main parce que vraiment je bloque.

Merci d'avance à tous,
Chris

[yal001]

Bonjour,

je ne suis pas sur d'avoir tout compris mais peut être pourrais tu inclure dans ce genre dans une clause WHERE:

Code :

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

1
2
3
WHERE getDistance(555, POSTCODES.latitude, POSTCODES.longitude) 
=(SELECT (MIN(getDistance(555, POSTCODES.latitude, POSTCODES.longitude)) 
FROM OTM LEFT JOIN POSTCODES ON OTM.postcodeID = POSTCODES.postcodeID ))

Bon je sais c'est moche, mais je pense que ça pourrait marcher (en faisant attention aux parenthèses )

[Chris1982]

Bonjour,
Merci pour la réponse mais je ne crois pas que j'ai été assez précis.
Je vais essayer de spécifier.

Disons que PRODUITS contient les enregistrements suivants:

prdID prdName
54 Gsm
56 GPS

et que la table OTM contient

_prdID _postcodeID
54 192
54 296
56 145

Avec une requête "simple", je pourrais retourner les résultats suivants:
prdID prdName postcode ville distance
54 Gsm 1800 Vilvoorde 21,5
54 Gsm 1160 Auderghem 15,2
56 GPS 1000 Bruxelles 18,5

Mais ce qu'il me faudrait, c'est un seul résultat par produit avec la distance la plus petite, donc ceci:
prdID prdName postcode ville distance
54 Gsm 1160 Auderghem 15,2
56 GPS 1000 Bruxelles 18,5

J'espère que ceci clarifie.
Merci à tous,
Chris

[yal001]

Tu pourrais stocker les résultats de ta requête "simple" dans un curseur et traiter par la suite. Il y a surement plus simple et performant, mais j'ai pas d'idée intelligente pour le moment

[Chris1982]

Je suis pratiquement certain qu'il y a un moyen plus "propre" de résoudre ça... mais merci pour tes réponses, yal001

quelqu'un a une idée pour y arriver avec une seule requête?

merci,
Chris

[Waldar]

Essayez ceci :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH Post_Dist AS
(
SELECT
    postcodeID,
    getDistance(postcodeID, latitude, longitude)) AS [Distance] 
FROM POSTCODES 
)
SELECT
    OTM._prdID,
    min(Post_Dist.Distance) AS Distance_Min
FROM
    OTM LEFT JOIN Post_Dist ON OTM._postcodeID = Post_Dist.postcodeID 
GROUP BY OTM._prdID

[Chris1982]

Bonjour Waldar,

Merci beaucoup, je pense que cela peut fonctionner!
Je vais devoir le confirmer un peu plus tard parce que je suis dans l'impossibilité de faire fonctionner un statement WITH AS
Je suspecte que le mode de compatibilité de ma base données est réglé sur SQL2000 et pas SQL2005. Je vois avec mon hébergeur s'il est possible de modifier cela, j'essaie et je vous dis ce qu'il en est.

Merci,
Chris

[Waldar]

Vous pouvez toujours essayer avec une sous-requête alors :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT
    OTM._prdID,
    min(Post_Dist.Distance) AS Distance_Min
FROM
    OTM LEFT JOIN
      (SELECT postcodeID, getDistance(postcodeID, latitude, longitude)) AS [Distance]
       FROM POSTCODES) AS Post_Dist
      ON OTM._postcodeID = Post_Dist.postcodeID 
GROUP BY OTM._prdID

[pacmann]

Salut !

Ton SQL Server prend les fonctions analytiques ?
Pour simplifier, on va considérer la table T qui représente ton résultat :

prdID prdName postcode ville distance
54 Gsm 1800 Vilvoorde 21,5
54 Gsm 1160 Auderghem 15,2
56 GPS 1000 Bruxelles 18,5

Code :

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

1
2
3
4
5
 
SELECT * FROM (
SELECT t.*, row_number() OVER(PARTITION BY prdId ORDER BY distance) AS rk
FROM T) t1
WHERE rk = 1

[Chris1982]

re-bonsoir Waldar, salut Pacmann,

Merci pour vos deux réponses!

Waldar: je n'ai pas encore de nouvelles de mon hébergeur pour la solution WITH ... AS mais je réalise en relisant que ça ne résoudrait pas mon problème. Idem avec la deuxième solution que vous proposez et qui équivaut à ce que j'avais déjà essayé. Le problème est le même que celui que je rencontrais: la requête retourne prdID et Distance mais pas postcodeID

Pacmann: Ingénieux! Mais comme je le disais à Waldar, je campe pour le moment avec un MSSQL 2000 et je *pense* que la partition OVER() n'est dispo que depuis la version 2005 non?

A+
Chris

[Chris1982]

Je me permets de faire remonter cette discussion; je viens en effet d'apprendre que le passage vers MSSQL 2005 n'est pas envisageable dans l'immédiat, je recherche donc toujours une solution pour obtenir ce résultat avec MSSQL 2000.

Merci à tous,
Chris

[yal001]

Au lieu d'utiliser un with ça revient au meme si tu stockes le résultat dans une table temporaire, non?

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH Post_Dist AS
(
SELECT
    postcodeID,
    getDistance(postcodeID, latitude, longitude)) AS [Distance] 
FROM POSTCODES 
)
SELECT
    OTM._prdID,
    min(Post_Dist.Distance) AS Distance_Min
FROM
    OTM LEFT JOIN Post_Dist ON OTM._postcodeID = Post_Dist.postcodeID 
GROUP BY OTM._prdID

deviendrait

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
    postcodeID,
    getDistance(postcodeID, latitude, longitude)) AS [Distance] 
INTO #Post_Dist
FROM POSTCODES 
 
SELECT
    OTM._prdID,
    min(#Post_Dist.Distance) AS Distance_Min
FROM
    OTM LEFT JOIN Post_Dist ON OTM._postcodeID = #Post_Dist.postcodeID 
GROUP BY OTM._prdID
 
DROP TABLE #Post_Dist

[Chris1982]

Salut yal001,

En effet mais ça pose deux problèmes:

- à chaque requête, ça "compile" l'entièreté de la table POSTCODES, pas idéal niveau performances :-(
- le résultat est le même qu'avant: ça retourne prdID et Distance mais toujours pas postcodeID

Mais j'apprécie ta tenacité

Chris

[Waldar]

Le problème est le même que celui que je rencontrais: la requête retourne prdID et Distance mais pas postcodeID

Il suffit alors de le rajouter :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
    OTM._prdID,
    OTM._postcodeID,
    min(Post_Dist.Distance) AS Distance_Min
FROM
    OTM LEFT JOIN
      (SELECT postcodeID, getDistance(postcodeID, latitude, longitude)) AS [Distance]
       FROM POSTCODES) AS Post_Dist
      ON OTM._postcodeID = Post_Dist.postcodeID 
GROUP BY
    OTM._prdID,
    OTM._postcodeID