GROUP BY: comment "conserver" les champs hors du group by

Version imprimable

Bonjour,

Soit la table suivante
Code:

1 2 3 4 5 6 7 8 9 10 JOINTURE IP ;REGLE ;NOM 192.168.000.001;192.168* ;"réseau local";... 192.168.000.001;192.168.000* ;"réseau local / sous réseau 0";... 192.168.000.001;192.168.000.001;"machine à toto";... 192.168.000.002;192.168* ;"réseau local";... 192.168.000.002;192.168.000* ;"réseau local / sous réseau 0";... 192.168.000.002;192.168.000.001;"machine à dede";... 127.000.000.001;127.000.000.001;"moi-meme"
NB: le joker * est toujours à la fin de la regle

Comment obtenir
Code:

1 2 3 4 192.168.000.001;192.168.000.001;"machine à toto";... 192.168.000.002;192.168.000.001;"machine à dede";... 127.000.000.001;127.000.000.001;"moi-meme"
Comme si on faisait un group by sur la colonne IP et que l'on conservait tous les champs associés à la règle la plus discriminante (length(regle) la plus longue)

Si ça peut aider au lieu de considérer la table jointure, a l'origine j'ai pluôt les 2 tables suivantes
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Adresses_IP [IP,...,..] 192.168.000.001;... 192.168.000.002;... 127.000.000.001;... Regles [REGLE,NOM,...] 192.168* ;"réseau local";... 192.168.000* ;"réseau local / sous réseau 0";... 192.168.000.002;"machine à toto";... 192.168.000.001;"machine à dede";... 127.000.000.001;"moi-meme"
D'avance merci,

Vincent

04/08/2009, 22h14
Bruno2r
Bonsoir,
Je me trompe peut être mais il me semble que la requête suivante devrait suffire ... non ?
Code:

1 2 3 SELECT A.IP, B.REGLE, B.NOM FROM Adresses_IP A, Regles B WHERE A.IP = B.REGLE

Bonjour,

Avec les fonctions analytiques:

Code:

1
2
3
select IP,REGLE,NOM from (
 select IP,REGLE,NOM, max(length(REGLE)) over (partition by IP) max_length_regle from JOINTURE
) where max_length_regle = length(REGLE)

Cordialement,
Franck.

Sans sous-requête :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
WITH ADRESSES_IP AS
(
SELECT '192.168.000.001' AS IP FROM DUAL UNION ALL
SELECT '192.168.000.002'       FROM DUAL UNION ALL
SELECT '127.000.000.001'       FROM DUAL
),
     REGLES AS
(
SELECT '192.168*' AS REGLE, 'réseau local'          AS NOM FROM DUAL UNION ALL
SELECT '192.168.000*'     , 'réseau local / sous réseau 0' FROM DUAL UNION ALL
SELECT '192.168.000.002'  , 'machine à toto'               FROM DUAL UNION ALL
SELECT '192.168.000.001'  , 'machine à dede'               FROM DUAL UNION ALL
SELECT '127.000.000.001'  , 'moi-meme'                     FROM DUAL
)
SELECT
    AI.IP,
    MAX(RG.REGLE) KEEP (DENSE_RANK FIRST ORDER BY LENGTH(RG.REGLE) DESC) AS REGLE,
    MAX(RG.NOM)   KEEP (DENSE_RANK FIRST ORDER BY LENGTH(RG.REGLE) DESC) AS NOM
FROM
    ADRESSES_IP AI
    INNER JOIN REGLES RG
      ON AI.IP LIKE REPLACE(RG.REGLE, '*', '%')
GROUP BY
    AI.IP;
 
 IP              REGLE           NOM           
--------------- --------------- ---------------
127.000.000.001	127.000.000.001	moi-meme
192.168.000.001	192.168.000.001	machine à dede
192.168.000.002	192.168.000.002	machine à toto

07/08/2009, 15h46
v1nce
Hello,

Merci pour toutes ces réponses, je vais les étudier.
Citation:
Envoyé par Bruno2r

Bonsoir,
Je me trompe peut être mais il me semble que la requête suivante devrait suffire ... non ?

Code:

1 2 3 SELECT A.IP, B.REGLE, B.NOM FROM Adresses_IP A, Regles B WHERE A.IP = B.REGLE
A priori ta requête me retourne le contenu de la table jointure (soit : pour chaque IP, la liste de toutes les règles correspondantes) moi ce que je souhaitais obtenir c'est la règle la plus restrictive (=la plus longue) pour chaque adresse.

Vincent.
08/08/2009, 13h53
Bruno2r
Bonjour,
Citation:
Hello,

Merci pour toutes ces réponses, je vais les étudier.

Citation:

Envoyé par Bruno2r

Bonsoir,
Je me trompe peut être mais il me semble que la requête suivante devrait suffire ... non ?

Code:

1 2 3 SELECT A.IP, B.REGLE, B.NOM FROM Adresses_IP A, Regles B WHERE A.IP = B.REGLE

A priori ta requête me retourne le contenu de la table jointure (soit : pour chaque IP, la liste de toutes les règles correspondantes) moi ce que je souhaitais obtenir c'est la règle la plus restrictive (=la plus longue) pour chaque adresse.

Vincent.
Je ne crois pas qu'avec une Equijointure A.IP=B.Règle tu récupères la table jointure. En revanche, si on se base sur tes données en exemple on perd Machine à dede :(

Puisque (et c'est une chose que je n'avais percuté en lisant trop rapidement ton exemple) les lignes à récupérer n'ont pas toujours IP = Règle (vrai pour toto et moi même, faux pour dede)
Suis pas sûr que Waldar l'ait vu non plus car il ne l'a pas reproduit dans son exemple :mouarf:

Autre chose que je n'avais pas devinée c'est que s'il n'y a pas de ligne "machine de..." tu veux sûrement récupérer le sous réseau ... ?

Dans ce cas et de toute façon la solution de Waldar est vraiment parfaite :ccool:
10/08/2009, 10h29
v1nce

Bonjour,

Citation:

Envoyé par Bruno2r

Bonjour,

Je ne crois pas qu'avec une Equijointure A.IP=B.Règle tu récupères la table jointure. En revanche, si on se base sur tes données en exemple on perd Machine à dede :(

Puisque (et c'est une chose que je n'avais percuté en lisant trop rapidement ton exemple) les lignes à récupérer n'ont pas toujours IP = Règle (vrai pour toto et moi même, faux pour dede)
Suis pas sûr que Waldar l'ait vu non plus car il ne l'a pas reproduit dans son exemple :mouarf:

Comme l'avait deviné Waldar la jointure se fait sur AI.IP LIKE REPLACE(RG.REGLE, '*', '%').
J'aurai du l'écrire dans ma question initiale, cela aurait été plus clair pour tout le monde.

Citation:

Autre chose que je n'avais pas devinée c'est que s'il n'y a pas de ligne "machine de..." tu veux sûrement récupérer le sous réseau ... ?

Exactement. Je veux récupérer tous les champs de la ligne qui contient la règle la plus "précise" (* étant un caractère joker)
Pour 192.168.000.001 j'ai plusieurs correspondances possibles :
192.168* ;"réseau local";...
192.168.000* ;"réseau local / sous réseau 0";...
192.168.000.001;"machine à toto";...
La ligne qui m'intéresse est bien "machine à toto".

Pour une hyptothétique machine 192.168.000.003 j'aurai 2 correspondances possibles
192.168* ;"réseau local";...
192.168.000* ;"réseau local / sous réseau 0";...
Cette fois-ci je souhaite obtenir "sous réseau 0"

En fait, mon problème ne porte pas sur des réseaux/sous-réseaux/machines mais sur une hiérarchie de catégories comptables.
J'avais pris l'exemple des réseaux pensant que cela serait plus parlant, sauf que je me suis emmêlé les pinceaux dans les données
de mes exemples. Bref, désolé pour ceux qui ont peiné à trouver le bon résultat avec de mauvaises données :roll: et félicitations (et merci) aux autres. :)

Citation:

Dans ce cas et de toute façon la solution de Waldar est vraiment parfaite :ccool:

Ca a l'air en effet.

Merci à tous.