Discussion :

[jlf]

bonjour
je rame sur le pbm suivant, qui est probablement très simple mais je suis nul

soient 2 tables :
T1 (ELEM, ID_T2)
T2 (ID, VALEUR)

T1.ELEM a des doublons (clef étrangère sur une autre table)
T1.ID_T2 est une clef étrangère sur T2
T2.ID est une clef primaire

je cherche pour chaque T1.ELEM distinct à récupérer le T2.ID correspondant au MAX(T2.VALEUR), n'importe lequel s'il y en a plusieurs

par ex le jeu de données suivant :

T1	T2
(1, 2)	(1, 11)
(1, 3)	(2, 7)
(3, 1)	(3, 15)
(3, 4)	(4, 11)

devrait sélectionner dans T1 :
(T1.ELEM, T1.ID_T2)
(1, 3)
(3, 1) ou (3, 4) peu importe mais un seul

c'est du Firebird, qui accepte les fonctions de fenêtre et les CTE, et pour d'assez grosses tables (T1 > 100.000 et T2 autour de 1.000)

je tourne en rond, ce serait vraiment sympa d'abréger mes souffrances si c'était possible

edit : oups c'est parti trop vite
j'ai bien pensé a faire un row_num sur une sous requête (T1.ELEM, T2.ID, T2.VALEUR) classée par (T1.ELEM, T2.VALEUR) et sélectionner les row_num = 1 mais ça me parait lourd ?

[al1_24]

Avec les fonctions de regroupement fenêtrées la solution est assez facile à atteindre :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
select  elem
    ,   id_t2
from    (   select  t1.elem
                ,   t1.id_t2
                ,   t2.valeur
                ,   max(t2.valeur) over (partition by t1.elem) as valmax
            from    t1
                inner join
                    t2
                    on  t2.id = t1.id_t2
        )   tmp
where   valeur  = valmax
;

La suppression des ex-aequo se résout par la recherche du max ou du min de id_t2 pour un même elem.

[fsmrel]

Bonjour,


Comme le propose al1_24, pour ne conserver qu’un élément en cas d’ex-aequo :

Code SQL :

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
select elem, min(id_t2) from
(
select  elem
    ,   id_t2
from    (   select  t1.elem
                ,   t1.id_t2
                ,   t2.valeur
                ,   max(t2.valeur) over (partition by t1.elem) as valmax
            from    t1
                inner join
                    t2
                    on  t2.id = t1.id_t2
        )   tmp
where   valeur  = valmax
) as x
group by elem
;

=> Je vote pour al1_24 !

[al1_24]

On peut même éviter une étape :4

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  elem
    ,   min(id_t2)  as id_t2
from    (   select  t1.elem
                ,   t1.id_t2
                ,   t2.valeur
                ,   max(t2.valeur) over (partition by t1.elem) as valmax
            from    t1
                inner join
                    t2
                    on  t2.id = t1.id_t2
        )   tmp
where   valeur  = valmax
group by elem
;

[jlf]

merci à tous les deux !

malheureusement pour moi ça ne résout pas mon pbm malgré ce que je pensais, parce que j'avais mal posé ma question
la requête que vous avez résolue est partie d'une autre qui lague beaucoup (30 mn !) et j'étais bêtement persuadé que le pbm venait du fait que j'utilisais row_number

en fait [max over partition] lague pareil
excusez moi, ça faisait un moment que je n'avais pas touché au sql

comme ma question initiale est résolue ça implique peut-être que j'ouvre un nouveau fil ?
si oui je le ferais, dans le doute j'essaie de reformuler ici car c'est peut-être assez proche :


soient en fait 3 tables :
T0 (ELEM)
T1 (ID, ELEM, ID_T2)
T2 (ID, VALEUR)

les ID sont clefs primaires, et les ID_Tx des clefs étrangères sur la table Tx
T0 est grande, et toutes ses lignes n'ont pas forcément une correspondance dans T1

ce que je voudrais c'est tous les (ELEM, T2.ID -> VALEUR MAX) ou (T0.ELEM, null) pour les ELEMs sans correspondance

T0	T1	T2
1	(1, 2)	(1, 11)
2	(1, 3)	(2, 7)
3	(3, 1)	(3, 15
3	(3, 4)	(4, 11)

je cherche donc dans cet exemple à récupérer 3 lignes :
(1, 3)
(2, null)
(3, 1) ou (3, 4)

si je mets votre soluce pour la partie que je suspectais à tort dans une CTE, et que je fais un left outer join dessus :

Code :

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

1
2
select T0.ELEM, cte.ID_T2
left outer join cte on cte.ELEM = T0.ELEM

... alors j'ai le temps de faire tous les bistrots du quartier, sauf qu'ils sont tous fermés

si je remplace outer par inner ça devient presque instantané, mais bien sûr il me manque les ELEM sans correspondance

vous pensez qu'il y a une soluce ?
en tous cas merci pour les réponses déjà fournies

edit : pour ne pas recommencer à rater des éléments importants voici le modèle de données complet, ELEM est une clef étrangère sur une 4ème table et est donc indexé, et par ailleurs j'ai aussi une fk sur T0 dans T1
je ne sais pas mais ça change peut-être les données du pbm

soient en fait 4 tables :

T_ELEMS	( ID )
T0	(ID, ID_ELEMS)
T1	(ID, ID_ELEMS, ID_T0, ID_T2)
T2	(ID, VALEUR)

[fsmrel]

j'ai le temps de faire tous les bistrots du quartier, sauf qu'ils sont tous fermés

Ach...

Quel est votre SGBD ? Avez-vous fait des explains ?

En passant, votre table T1 viole la 3NF (troisième forme normale). En effet il existe dans T1 la dépendance fonctionnelle {ID_T0} → {ID_ELEMS} : la colonne ID_ELEMS devrait être supprimée dans T1.

La paire {ID_T0, ID_T2} est vraisemblablement une clé candidate de la table T1. Quoi qu’il en soit, cette paire est-elle dotée d’un index ?

Si le inner join ne pose pas de problème de performance, c’est donc que c’est son complément (partie outer) qui fiche la patouille. Que donne le inner join avec UNION ALL d’un NOT EXISTS affecté au complément ? La partie outer toute seule ?

[jlf]

Quel est votre SGBD ? Avez-vous fait des explains ?

Firebird 3.0 et je viens d'essayer, pas d'explains

il existe dans T1 la dépendance fonctionnelle {ID_T0} → {ID_ELEMS} : la colonne ID_ELEMS devrait être supprimée dans T1

je peux le virer si ça améliore les performances ?

T1.ID_ELEMS existe pour éviter de passer par T0 dans certaines requêtes, group by pour l'essentiel
T0 contient beaucoup d'ID_ELEMS différents et T1 beaucoup moins, même si le nbre de lignes total est souvent du même ordre pour les deux
donc je me disais qu'on gagnerait des ms précieuses en attaquant T1 directement quand c'est possible (pas le cas de la requete qui m'embête en ce moment)

La paire {ID_T0, ID_T2} est vraisemblablement une clé candidate de la table T1. Quoi qu’il en soit, cette paire est-elle dotée d’un index ?

je ne connais pas cette notion de clef candidate, j'ai regardé wiki mais ça a l'air compliqué, j'approfondirais s'il le faut mais pas glop

les index actuels sont uniquement ceux des clefs etrangères dispo :
(prim, fk)
(T_ELEMS.ID, T0.ID_ELEM)
(T_ELEMS.ID, T1.ID_ELEM)
(T0.ID, T1.ID_T0)
(T2.ID, T1.ID_T2 )

c’est son complément (partie outer) qui fiche la patouille. Que donne le inner join avec UNION ALL d’un NOT EXISTS affecté au complément ? La partie outer toute seule ?

je viens d'essayer ces requêtes :

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
select T0.ID, T1.ID, T2.ID
from T0
  left outer join T1 on T1.ID_T0 = T0.ID
  left outer join T2 on T2.ID = T1.ID_T2
 
--**************************
 
select T0.ID
from T0
where exists 
  (
    select t1.id_T0
    from T1
    where T1.ID_T0 = T0.ID
  )

tu as mis dans le mille, les deux fonctionnent sans lag excessif
mais je ne comprends pas pourquoi la 1ère précédente est rapide toute seule, et que si je lui ajoute la raquete cherchant l'ID_T2 de valeur max elle devient encore plus lente que moi à sortir du lit le dimanche !

en tous cas je vais donc pouvoir faire une union entre les T0_ID présents dans T1 et ceux qui ne le sont pas, c'est sans doute pas super élégant mais ça me va

je suppose que tous ces difficultés viennent du modèle de données que j'ai choisi
je laisse un moment avant de mettre résolu, pour le cas où tu trouverais mieux

en tous cas merci, hier j'étais vraiment coincé

[jlf]

je crois que je viens de trouver une soluce encore plus simple, au moins en lecture :

Code :

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

1
2
3
4
5
select distinct
        t1.ID_ELEM,
        first_value(T2.ID) over (partition by T1.ID_ELEM order by T2.VALEUR desc) as ID_T2
from T1
inner join T2 on T2.ID = T1.ID_T2

ça a l'air de marcher, j'ai le bon nombre de lignes et les quelques vérif par ligne individuelles que j'ai faites sont OK pour l'instant
bizarrement la vitesse semble un chouia en dessous de votre soluce, mais ça reste supportable

je suis tombé dessus en cherchant un moyen d'avoir à la fois l'id_t2 de la valeur max et et celui de la valeur min
avec first_value il me suffit de rajouter une ligne identique en inversant juste l'order by en asc

bon, à voir, il y a peut-être un os qui se cache le dimanche ...

en tous cas merci encore de vos conseils

[fsmrel]

Bonsoir jlf,

il y a peut-être un os qui se cache le dimanche

Je viens seulement de prendre connaissance de votre message. Tout ce qui suit concerne le précédent, mais sur le fond, pas de changement.

Firebird 3.0 et je viens d'essayer, pas d'explains

Je ne connais pas Firebird, mais manifestement le terme EXPLAIN a un sens pour lui, voir ici

Manifestement on peut voir l’utilisation bonne ou défaillante des index et tout ça : Advanced Plan Output.

Ça a l’air minimal, mais toujours bon à prendre.

Voir aussi chez developpez ce message d’il y a quinze ans. ^^

je peux le virer si ça améliore les performances ?

Faire la comparaison. A moins que ça ne dégrade la performance, effectivement virer, afin de ne pas avoir d’incohérences (redondances à maintenir) entre les tables T0 et T1.

je ne connais pas cette notion de clef candidate, j'ai regardé wiki mais ça a l'air compliqué

Disons qu’il s’agit d’une contrainte d’unicité. Si dans la table T1, pour une valeur de la paire {ID_T0, ID_T2} il ne peut y avoir qu’une seule valeur pour l’attribut ID, alors {ID_T0, ID_T2} est clé candidate. Si pour une valeur de {ID_T0, ID_T2} il peut y avoir plusieurs valeurs pour l’attribut ID, alors {ID_T0, ID_T2} n’est pas clé candidate.
S’il s’avère que {ID_T0, ID_T2} est clé candidate pour la table T1, alors l’existence de l’attribut ID peut être remise en cause, autrement dit quelles seraient les conséquences de sa disparition d’un point de vue strictement fonctionnel ? Si fonctionnellement parlant (c’est-à-dire du point de vue de l’utilisateur) l’attribut ID peut disparaître, alors c’est la paire {ID_T0, ID_T2} qui devient clé primaire, tandis que l’attribut ID disparaît de la structure de la table T1. Do ou follow me ?


Si en plus la table T1 est normalisée, c’est-à-dire si l’attribut ID_ELEMS est supprimé, elle révèle formellement sa nature authentique : association de plusieurs à plusieurs des tables T0 et T2.

je suppose que tous ces difficultés viennent du modèle de données que j'ai choisi

Outre ce que je viens de dire, pour qu’on y voie plus clair (notamment en ce qui concerne les clés primaires et étrangères), il faudrait que vous fournissiez votre DDL (Les CREATE TABLE, CREATE INDEX) ainsi que les résultats d’EXPLAIN (cf. Advanced Plan Output ci-dessus).


N.B. Comme le suggère al1_24, n’hésitez pas à liker les réponses qui ont pu vous apporter quelque chose...
 

[jlf]

Je ne connais pas Firebird, mais manifestement le terme EXPLAIN a un sens pour lui

OK merci pour ces liens

Do you follow me ?

I try to ;o)

si j'ai bien suivi malgré l'amputation invalidante d'une heure de sommeil ce we, {ID_T0, ID_T2} n’est pas clef candidate :

en effet dans T1 il y a des doublons sur le triplet {ID, ID_T0, ID_T2}, et plusieurs ID différents peuvent avoir la même paire {ID_T0, ID_T2}
tout ça pour des motifs qui tiennent à l'origine des données sur laquelle je n'ai pas la main

merci de cette explication très claire

pour qu’on y voie plus clair (notamment en ce qui concerne les clés primaires et étrangères), il faudrait que vous fournissiez votre DDL (Les CREATE TABLE, CREATE INDEX) ainsi que les résultats d’EXPLAIN

je prends note de la proposition et j'en profiterai sans doute, il y a peu de chances qu'un contournement comme celui que vous avez trouvé soit toujours possible
mais plus tard, là il faut que j'avance
encore merci

N.B. Comme le suggère al1_24, n’hésitez pas à liker les réponses qui ont pu vous apporter quelque chose...
 

effectivement je n'avais pas remarqué que ce forum avait adopté cette pratique, je m'exécute de suite

[escartefigue]

Bonjour Jlf,

Si je comprends bien votre besoin, vous pourrez y trouver une solution dans mon blog ici