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

**v1nce** · 04/08/2009, 18h42

Bonjour,

Soit la table suivante

Code :

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

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 :

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

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 :

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

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

**Bruno2r** · 04/08/2009, 22h14

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

Code :

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

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

**pachot** · 04/08/2009, 22h27

Bonjour,

Avec les fonctions analytiques:

Code :

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

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.

**Waldar** · 05/08/2009, 04h18

Sans sous-requête :

Code :

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

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

**v1nce** · 07/08/2009, 15h46

Hello,

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

Envoyé par Bruno2r

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

Code :

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

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.

**Bruno2r** · 08/08/2009, 13h53

Bonjour,

Hello,

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

Envoyé par Bruno2r

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

Code :

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

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

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

**v1nce** · 10/08/2009, 10h29

Bonjour,

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

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.

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

et félicitations (et merci) aux autres.

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

Ca a l'air en effet.

Merci à tous.

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

SQL Oracle

Discussions similaires

Partager

Partager