Conception de requête via sous-requête et rownum

Version imprimable

02/11/2016, 15h11
lordjs

Conception de requête via sous-requête et rownum

Bonjour à tous,

Je n'ai pas trouvé de solution à mon problème dans les archives, alors je poste là.

J'ai une requête SQL qui me renvoie ce genre de résultat

IDENTIFIANT SOUS_IDENTIFIANT
001 1001
001 30125
002 30124
002 30123
002 30146
003 35874
003 01245
004 01254

Ma question est donc, comment n'afficher pour chaque identifiant qu'a partir du 2eme sous identifiant.

Mon but étant de rogner le premier sous identifiant pour chaque identifiant. J'ai essayer avec un rownum, avec des group by, tout et tout. Sans succès. Je vous passe par contre, la requête d'une complexité affligeante ...

select IDENTIFIANT, SOUS_IDENTIFIANT
from table
order by 1,2;

Merci d'avance a ceux qui voudront bien m'aider. :)
02/11/2016, 15h18
escartefigue

Si votre SGBD le permet (quel est il), utilisez la fonction de fenetrage RANK
Edit : vous pouvez aussi utiliser EXCEPT
02/11/2016, 15h18
skuatamad

Utilisez row_number ou rank

Exemple de solution avec RANK (inutilisable si vous utilisez MySQL ou Access)

Code:

1
2
3
4
5
6
7
8
9
10
With mycte as(
select distinct RANK()                                              
           over (partition by col1 order by Col2) as rang
         , col1                                                    
         , col2                                                    
         , ...                                                    
         , coln
from ma_table
where ...)
Select * from mycte where rang > 1

Si votre SGBD n'accepte pas les fonctions de fenêtrage, voici une solution avec except

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Select * from (
select col1
     , col2
     , ...
     , coln
from ma_table
EXCEPT
select col1
     , col2
     , ...
     , coln
from ma_table as main
where not exists
     (select 1
      from ma_table as subq
      where subq.col1 = main.col1
        and subq.col2 < main.col2)
) as temp
order by col1, col2

Edit : je viens de corriger la requete 2 dans laquelle il y avait une erreur, à tester quand même car rédigé sans test, des fautes de frappe sont possibles ;)

03/11/2016, 11h57
lordjs

La première fonctionne parfaitement au bout de 12800 secondes d’exécution...Mais malheureusement,c'est bien trop long, J’essaye de voir donc pour l'optimiser.

Mais sinon c'est fonctionnel. J’essaye de voir si c'est plus rapide avec la deuxième du coup.

Merci a vous deux !!!

Edit : Je suis sous oracle.
03/11/2016, 13h58
François DORIN
Bonjour,

Une proposition basée sur la deuxième solution d'escartefigue (usage d'EXCEPT):
Code:

1 2 3 4 5 6 7 select IDENTIFIANT, SOUS_IDENTIFIANT from ma_table EXCEPT select IDENTIFIANT, MIN(SOUS_IDENTIFIANT) from ma_table group by IDENTIFIANT
Cela fonctionne car, si j'ai bien compris, on est dans le cas où les identifiants et sous-identifiants sont triés. Du coup, pour avoir le premier, on peut se servir de MIN
03/11/2016, 14h16
escartefigue

Citation:

Envoyé par lordjs

La première fonctionne parfaitement au bout de 12800 secondes d’exécution...Mais malheureusement,c'est bien trop long, J’essaye de voir donc pour l'optimiser.
Mais sinon c'est fonctionnel. J’essaye de voir si c'est plus rapide avec la deuxième du coup.
Merci a vous deux !!!
Edit : Je suis sous oracle.

C'est beaucoup beaucoup trop long ! et étonnamment trop long, quel volume dans la table ?
Le distinct n'est peut être pas requis dans votre cas

Citation:

Envoyé par dorinf

Bonjour,
Une proposition basée sur la deuxième solution d'escartefigue (usage d'EXCEPT):

C'est ce qui est amusant avec SQL : d'avoir le plus souvent plusieurs solutions avec des variantes pour un même besoin :P
Il reste ensuite à choisir la plus efficace en fonction du contexte

Plus simple...

Code:

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE MaTable (IDENTIFIANT CHAR(3),  SOUS_IDENTIFIANT CHAR(5))
 
INSERT INTO MaTable VALUES
('001', '1001'),
('001', '30125'),
('002', '30124'),
('002', '30123'),
('002', '30146'),
('003', '35874'),
('003', '01245'),
('004', '01254');

Solution :

Code:

1
2
3
4
5
SELECT T2.IDENTIFIANT, MIN(T2.SOUS_IDENTIFIANT) AS SOUS_IDENTIFIANT2
FROM   MaTable AS T1
       JOIN MaTable  AS T2
	        ON T1.IDENTIFIANT = T2.IDENTIFIANT AND T1.SOUS_IDENTIFIANT > T2.SOUS_IDENTIFIANT
GROUP  BY  T2.IDENTIFIANT;

Résultat :

Code:

1
2
3
4
5
IDENTIFIANT SOUS_IDENTIFIANT2
----------- -----------------
001         1001 
002         30123
003         01245

Si cela vous pose des problèmes de performances, créez l'index suivant :
CREATE INDEX X ON MaTable (IDENTIFIANT, SOUS_IDENTIFIANT)

A +

03/11/2016, 16h11
lordjs

Alors concrètement, le rank marche très bien maintenant que j'ai optimiser ma requête et que les latences de bdd sont terminés.

Concernant l'except, ce n'est pas disponible sur Oracle, mais la fonction MINUS fait du coup parfaitement l'affaire.

Du coup, niveau temps d’exécution, je viens de comparer les deux et le MINUS offre des temps supérieurs a l'autre.

Je garde donc le rank.

C'est bon pour moi, je clôture ce topic.

Je vous remercie bien de votre aide.

Citation:

C'est ce qui est amusant avec SQL : d'avoir le plus souvent plusieurs solutions avec des variantes pour un même besoin
Il reste ensuite à choisir la plus efficace en fonction du contexte

=>>>>> Tout a fait !