recherche par approximation
Paskal
Dans une base de recherche d'objet MySQL, je propose, parmi plusieurs
champs, de rentrer le dimaètre d'un objet à rechercher.
Je le fais sous la forme : taille mini et taille maxi, puis je recherche
avec un simple :
WHERE diametre > tailleMini AND diametre < tailleMax
J'aimerai plutot proposer à mon utilisateur de rentrer la dimention de ce
qu'il cherche, puis faire une requete où les résultats qui s'approchent le
plus de ce qu'il cherche s'affichent.
Pourriez vous m'orienter dans mes recherches ? Et est-ce faisable avec une
requete ?
merci
Pascal
paul POULAIN
en mySQL ?
un truc du genre :
select diametre-tailleChoisie as diff order by diff asc LIMIT 10
on fait une recherche sur la différence entra la taille saisie et la taille
voulue, et on limite aux 10 premières réponses.
PS : le LIMIT 10 est spécifique à mySQL
--
Paul
Fred Brouard - SQLpro
select *
FROM ???
ORDER BY ABS(diametre-tailleChoisie)
LIMIT 10
A +
paul POULAIN a écrit :
--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*********************** http://www.sqlspot.com *************************
Paskal
Quand je vois la solution...C'était d'une simplicité enfantine.
Grand merci : ça fonctionne à merveille
@+
pascal
> Manque un détail : ABS...
>
> select *
> FROM ???
> ORDER BY ABS(diametre-tailleChoisie)
> LIMIT 10
Fred Brouard - SQLpro
La recherche de la tranche la plus proche
=========================================
La recherche de la tranche la plus proche, est un autre problème qui se
pose parfois dans la gestion de tranches.
Prenons comme exemple un électronicien confronté au douloureux problème
du choix des composants qu'il doit implanter. Il lui faut une résistance
de 2,75 ohms alors qu'il ne dispose que de résistances dans la gamme
suivante : 1 ohm, 1,44 ohms, 1,73 ohms, 2 ohms, 3,46 ohms, 5 ohms, 7,5
ohms et 10 ohms. Quelle est la résistance la plus appropriée pour son
montage électronique ?
Voici notre table de test :
CREATE TABLE RESISTANCE
(RST_ID INTEGER,
RST_VALEUR DECIMAL(5,2))
Et les valeurs associées :
INSERT INTO RESISTANCE (RST_ID, RST_VALEUR) VALUES (1, 1.00)
INSERT INTO RESISTANCE (RST_ID, RST_VALEUR) VALUES (2, 1.44)
INSERT INTO RESISTANCE (RST_ID, RST_VALEUR) VALUES (3, 1.73)
INSERT INTO RESISTANCE (RST_ID, RST_VALEUR) VALUES (4, 2.00)
INSERT INTO RESISTANCE (RST_ID, RST_VALEUR) VALUES (5, 3.46)
INSERT INTO RESISTANCE (RST_ID, RST_VALEUR) VALUES (6, 5.00)
INSERT INTO RESISTANCE (RST_ID, RST_VALEUR) VALUES (7, 7.50)
INSERT INTO RESISTANCE (RST_ID, RST_VALEUR) VALUES (8, 10.00)
L'idée sous jacente est de calculerla différence entre la valeur
recherchée et toutes les valeurs proposées et de prendre la différence
la moins importante. La requête suivante calcule toutes les différences
possibles :
Exemple 8.85 :
SELECT *, RST_VALEUR - 2.75 AS DIFFERENCE
FROM RESISTANCE
Et donne :
RST_ID RST_VALEUR DIFFERENCE
----------- ---------- ----------
1 1.00 -1.75
2 1.44 -1.31
3 1.73 -1.02
4 2.00 -.75
5 3.46 .71
6 5.00 2.25
7 7.50 4.75
8 10.00 7.25
Le seul handicap est de se retrouver avec des valeurs positives et
négatives pour lequelles il est difficile de faire des comparaisons. On
peut éliminer ce problème si votre dialecte SQL utilise une fonction
calculant la valeur absolue (ou s'il possède la fonction SIGN retournant
1, 0 ou -1 en fonction du signe de la valeur passée en argument) :
Exemple 8.86 :
SELECT *, ABS(RST_VALEUR - 2.75) AS DIFFERENCE
FROM RESISTANCE
Ou encore
SELECT *, (RST_VALEUR - 2.75) * SIGN(RST_VALEUR - 2.75) AS DIFFERENCE
FROM RESISTANCE
Qui donne :
RST_ID RST_VALEUR DIFFERENCE
----------- ---------- ----------
1 1.00 1.75
2 1.44 1.31
3 1.73 1.02
4 2.00 .75
5 3.46 .71
6 5.00 2.25
7 7.50 4.75
8 10.00 7.25
Dès lors, par une imbrication de requête un peu à la manière de celle
que nous venons de voir précédemment, on peut arriver à une solution
simple :
Exemple 8.87 :
SELECT *
FROM RESISTANCE
GROUP BY RST_ID, RST_VALEUR
HAVING MIN(ABS(RST_VALEUR - 2.75)) = (SELECT MIN(ABS(RST_VALEUR - 2.75))
FROM RESISTANCE)
Qui retourne le bon choix :
RST_ID RST_VALEUR
----------- ----------
5 3.46
Bien entendu cette requête peut être adaptée pour la recherche d'une
valeur temporelle la plus proche, comme ce serait le cas si nous
cherchions à savoir quel vol ou quel train part à la date la plus
adaptée à la demande du client. Il suffit de convertir en valeur
temporelle l'intervalle entre la date et l'heure de départ souhaité et
celles proposées et d'en extraire l'intervalle le plus fin.
Recherche de la tranche la plus fine
====================================
Un dernier problème de la gestion des tranches consiste à rechercher la
tranche la plus fine dans laquelle se trouve notre critère. Ce problème
suppose que certaines tranches recouvrent la plage des valeurs de
certaines autres.
Nous voici avec un chimiste qui possède une batterie de filtres à
particule dont les limites d'utilisation, exprimées en microns, sont les
suivantes :
FLT_ID FLT_MIN FLT_MAX
------ ------- -------
1 250 400
2 300 420
3 125 300
4 100 300
5 80 180
6 320 640
7 260 450
8 100 500
9 50 260
10 110 220
La question est : quel est le filtre le plus adapté à l'élimination de
particules de 155 microns ?
Créons tout d'abord notre jeu de test :
CREATE TABLE FILTRE
(FLT_ID INTEGER,
FLT_MIN INTEGER,
FLT_MAX INTEGER)
Et les valeurs :
INSERT INTO FILTRE (FLT_ID, FLT_MIN, FLT_MAX) VALUES (1, 250, 400)
INSERT INTO FILTRE (FLT_ID, FLT_MIN, FLT_MAX) VALUES (2, 300, 420)
INSERT INTO FILTRE (FLT_ID, FLT_MIN, FLT_MAX) VALUES (3, 125, 300)
INSERT INTO FILTRE (FLT_ID, FLT_MIN, FLT_MAX) VALUES (4, 100, 300)
INSERT INTO FILTRE (FLT_ID, FLT_MIN, FLT_MAX) VALUES (5, 80, 180)
INSERT INTO FILTRE (FLT_ID, FLT_MIN, FLT_MAX) VALUES (6, 320, 640)
INSERT INTO FILTRE (FLT_ID, FLT_MIN, FLT_MAX) VALUES (7, 260, 450)
INSERT INTO FILTRE (FLT_ID, FLT_MIN, FLT_MAX) VALUES (8, 100, 500)
INSERT INTO FILTRE (FLT_ID, FLT_MIN, FLT_MAX) VALUES (9, 50, 260)
INSERT INTO FILTRE (FLT_ID, FLT_MIN, FLT_MAX) VALUES (10, 110, 220)
La requête suivante :
Exemple 8.88 :
SELECT *, FLT_MAX - FLT_MIN as PLAGE
FROM FILTRE
WHERE 155 BETWEEN FLT_MIN AND FLT_MAX
Permet de déterminer tous les filtres possibles et donne une première
idée sur celui qui doit être le plus adapté :
FLT_ID FLT_MIN FLT_MAX PLAGE
----------- ----------- ----------- -----------
3 125 300 175
4 100 300 200
5 80 180 100
8 100 500 400
9 50 260 210
10 110 220 110
En l'occurrence il suffit d'exiger de ne retenir que celui pour lequel
la valeur de la colonne PLAGE est la plus petite.
Encore une fois l'imbrication des requêtes à l'aide de la clause HAVING
permet de résoudre le problème :
Exemple 8.89 :
SELECT FLT_ID
FROM FILTRE
GROUP BY FLT_ID, FLT_MIN, FLT_MAX
HAVING FLT_MAX - FLT_MIN = (SELECT MIN (FLT_MAX - FLT_MIN)
FROM FILTRE
WHERE 155 BETWEEN FLT_MIN AND FLT_MAX)
Et de trouver le filtre le plus "serré" :
FLT_ID
-----------
5
Le problème peut d'ailleurs être pris dans un autre sens : notre
biologiste pratique des tests sur un poison violent et désire connaître
la taille approximative de la molécule. Tous les filtres ont été testés
sur des cobayes et voici les résultats du panel de test :
FLT_ID FLT_MIN FLT_MAX PASSE ?
----------- ----------- ----------- -------
1 250 400 NON
2 300 420 NON
3 125 300 OUI
4 100 300 OUI
5 80 180 NON
6 320 640 NON
7 260 450 NON
8 100 500 OUI
9 50 260 OUI
10 110 220 NON
Sauriez vous donner une plage de valeur pour la taille de notre molécule ?
Voici une requête possible
Exemple 8.90 :
SELECT MIN(FLT_MAX) AS TAILLE, 'MAXI'
FROM FILTRE
WHERE FLT_ID IN (3, 4, 8, 9)
UNION
SELECT MAX(FLT_MIN) AS TAILLE, 'MINI'
FROM FILTRE
WHERE FLT_ID IN (3, 4, 8, 9)
ORDER BY TAILLE
Qui donne :
TAILLE
----------- ----
125 MINI
260 MAXI
On recherche en fait le maximum du minimum et le minimum du maximum...
*** copyright F. BROUARD / Campus Press - 2001 ***
A +
Paskal a écrit :
Paskal
Je vois ta réponse un peu tard (tant pis pour moi).
J'ai eu effectivement cette même problématique avec les valeurs positives et
négatives, et les valeurs 'hors norme' lorsque les données d'un objet sont à
0 lorsqu'elles ne sont pas saisies.
J'ai mis en place une solution, en travaillant avec PHP sur les résultats.
Pour éviter les résultats 'hors norme', j'ai fais
SELECT * FROM mabase WHERE ABS(diametre-tailleChoisie)<tailleChoisie ORDER
BY ABS(diametre-tailleChoisie);
Puis, j'affiche en "pourcentage" la pertinence du résultat.
En tout cas, merci de l'aide que tu m'as apporté.
Pascal
"Fred Brouard - SQLpro" <brou...@club-internet.fr> a écrit dans le message
de news: 47f4b09e$0$21146$7a62...@news.club-internet.fr...
JGP_NoMail
> Extrait de mon ancien bouquin SQL (développement)
Très intéressant pour le débutant que je suis, mais je crois qu'il y a
un bug dans :
> Recherche de la tranche la plus fine
> La question est : quel est le filtre le plus adapté à l'élimination de
> particules de 155 microns ?
> Créons tout d'abord notre jeu de test :
> CREATE TABLE FILTRE
> (FLT_ID INTEGER,
> FLT_MIN INTEGER,
> FLT_MAX INTEGER)
> Et les valeurs :
> INSERT INTO FILTRE (FLT_ID, FLT_MIN, FLT_MAX) VALUES (1, 250, 400)
> INSERT INTO FILTRE (FLT_ID, FLT_MIN, FLT_MAX) VALUES (2, 300, 420)
Ici, si on remplace la ligne 2 par :
INSERT INTO FILTRE (FLT_ID, FLT_MIN, FLT_MAX) VALUES (2, 300, 400)
> INSERT INTO FILTRE (FLT_ID, FLT_MIN, FLT_MAX) VALUES (3, 125, 300)
> INSERT INTO FILTRE (FLT_ID, FLT_MIN, FLT_MAX) VALUES (4, 100, 300)
> INSERT INTO FILTRE (FLT_ID, FLT_MIN, FLT_MAX) VALUES (5, 80, 180)
> INSERT INTO FILTRE (FLT_ID, FLT_MIN, FLT_MAX) VALUES (6, 320, 640)
> INSERT INTO FILTRE (FLT_ID, FLT_MIN, FLT_MAX) VALUES (7, 260, 450)
> INSERT INTO FILTRE (FLT_ID, FLT_MIN, FLT_MAX) VALUES (8, 100, 500)
> INSERT INTO FILTRE (FLT_ID, FLT_MIN, FLT_MAX) VALUES (9, 50, 260)
> INSERT INTO FILTRE (FLT_ID, FLT_MIN, FLT_MAX) VALUES (10, 110, 220)
> La requête suivante :
> Exemple 8.88 :
> SELECT *, FLT_MAX - FLT_MIN as PLAGE
> FROM FILTRE
> WHERE 155 BETWEEN FLT_MIN AND FLT_MAX
[snip...], ici c'est OK
> Exemple 8.89 :
> SELECT FLT_ID
> FROM FILTRE
> GROUP BY FLT_ID, FLT_MIN, FLT_MAX
> HAVING FLT_MAX - FLT_MIN = (SELECT MIN (FLT_MAX - FLT_MIN)
> FROM FILTRE
> WHERE 155 BETWEEN FLT_MIN AND FLT_MAX)
> Et de trouver le filtre le plus "serré" :
Et ici, cela n'est pas bon car, on obtient :
FLT_ID
2
5
Le 2 ne devrait pas être présent, car 150 n'est pas dans la tranche
300-400.
la commande ne devrait t'elle pas être :
SELECT FLT_ID
FROM FILTRE
WHERE 155 BETWEEN FLT_MIN AND FLT_MAX
GROUP BY FLT_ID, FLT_MIN, FLT_MAX
HAVING FLT_MAX - FLT_MIN = (SELECT MIN (FLT_MAX - FLT_MIN)
FROM FILTRE
WHERE 155 BETWEEN FLT_MIN AND FLT_MAX)
Cordialement,
JGP
Fred Brouard - SQLpro
Il y a des chances !!!
A +
>
>
>
> Cordialement,
> JGP