IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Langage SQL Discussion :

Comparaison de listes


Sujet :

Langage SQL

  1. #1
    Membre du Club
    Homme Profil pro
    Administrateur systèmes et réseaux
    Inscrit en
    octobre 2011
    Messages
    50
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Administrateur systèmes et réseaux
    Secteur : Administration - Collectivité locale

    Informations forums :
    Inscription : octobre 2011
    Messages : 50
    Points : 42
    Points
    42
    Par défaut Comparaison de listes
    Bonjour,
    Je suis pas expert en SQL et je rencontre une grosse difficulté sur un problème qui me paraissait simple.
    Pour faire le plus simple possible (en réalité il y a des tables intermédiaires mais cela n'est pas pertinent pour l'exemple),
    dans une base de donnée (postgres) j'ai :
    - d'un coté des utilisateurs qui possèdent une liste d'aptitudes
    - d'un autre coté j'ai des taches qui nécessitent certaines aptitudes et inaptitudes (une liste d'aptitude et une liste d'inaptitudes).

    Pour un utilisateur donné je souhaite récupérer toutes les taches compatibles avec ses aptitudes/inaptitudes (certaines taches requièrent que la personne soit inapte).

    je suis arrivé à un truc comme ca:
    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
    select prerequis.id as prerequis_id,
    			(select count(*) from prerequis_aptitudes where prerequis_aptitudes.id_prerequis=prerequis.id) as nb_apt, 
    			(select count(*) from prerequis_inaptitudes where prerequis_inaptitudes.id_prerequis=prerequis.id) as nb_inapt, 
    			(select count (*) from (
    					select id_aptitude from prerequis_aptitudes where prerequis_aptitudes.id_prerequis=prerequis.id
    					intersect 
    					select aptitude.id from usdata
    					join usdata_aptitudes on usdata.id = usdata_aptitudes.id_usdata
    					join aptitude on usdata_aptitudes.id_aptitude = aptitude.id
    					where usdata.user_id=759) as popo) as user_apt,
    			(select count (*) from (
    				select id_aptitude from prerequis_inaptitudes where prerequis_inaptitudes.id_prerequis=prerequis.id
    				intersect 
    				select aptitude.id from aptitude
    				Where aptitude.id not in(select id_aptitude from usdata
    				join usdata_aptitudes on usdata.id = usdata_aptitudes.id_usdata
    				where usdata.user_id=759)) as poo
    			) as user_inapt
    from prerequis
    ce qui me donne :
    Nom : Capture d’écran 2021-01-12 à 19.05.04-sm.jpg
Affichages : 64
Taille : 29,1 Ko

    Avec ça je cree une vue(depuis une procedure stockée pour passer le userid en paramètre) puis un select avec un where pour comparer les count de valeurs nb_* / user_*.

    Bref c'est une vrai usine à gaz et je pense que quelqu'un de plus péchu que moi aura surement une solution plus simple et plus élégante.
    Si en plus je peux le transcrire en JPA cela serait merveilleux.

    Merci beaucoup.

  2. #2
    Expert éminent sénior
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    6 504
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 6 504
    Points : 20 165
    Points
    20 165
    Billets dans le blog
    2
    Par défaut
    Bonjour,

    Quelle différence y a-t-il entre une inaptitude et le fait de ne pas avoir une certaine aptitude ?
    Il me semble que être inapte à la cuisine, c'est ne pas avoir la compétence cuisine.
    Si l'on modélise deux notions distinctes, on risque les incohérences : rien n'interdira d'associer la compétence cuisine et l'incompétence cuisine à une même personne...

    Du coup, de ce que je comprends, le modèle de données devrait être le suivant (MCD à gauche, MLD à droite) :

    Nom : Sans titre.png
Affichages : 42
Taille : 26,6 Ko

    à partir de là, pour répondre au besoin, il suffit de faire une jointure entre la table AQ des compétences acquises et la tables RQ des compétences requises qui ont en commun l'identifiant de l'aptitude AP_ident

  3. #3
    Membre du Club
    Homme Profil pro
    Administrateur systèmes et réseaux
    Inscrit en
    octobre 2011
    Messages
    50
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Administrateur systèmes et réseaux
    Secteur : Administration - Collectivité locale

    Informations forums :
    Inscription : octobre 2011
    Messages : 50
    Points : 42
    Points
    42
    Par défaut
    Bonjour,

    C'est le modèle que j'avais initialement avant d'ajouter cette contrainte d'inaptitude.
    La difference entre une inaptitude et le fait de ne pas avoir une certaine aptitude, au niveau de l'utilisateur aucune.
    Au niveau de la tache par contre toutes les aptitudes ne sont pas évaluées.
    Pour reprendre l'exemple, mettons que nous ayons 3 aptitudes: balai, cuisine, ps4.
    Pour la tache "préparer le repas" j'ai besoin de trouver une personne qui ait la compétence cuisine, pas la compétence balai, la compétence ps4 n'est pas prise en compte pour cette tache.
    Au final, c'est le caractère facultatif de certaines competences qui ne seront donc pas évaluées qui me pose problème.
    c'est la raison qui m'a amené a créer les 2 listes pour évaluer les aptitudes/inaptitudes nécessaires à cette tache.

    Merci

  4. #4
    Expert éminent sénior
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    6 504
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 6 504
    Points : 20 165
    Points
    20 165
    Billets dans le blog
    2
    Par défaut
    Avec le modèle proposé plus haut il n'y a aucun problème : faites la jointure entre AQ et RQ sur l'identifiant de d'aptitude et le tour est joué

  5. #5
    Expert éminent sénior
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    6 504
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 6 504
    Points : 20 165
    Points
    20 165
    Billets dans le blog
    2
    Par défaut
    Voici le script de création des tables selon le modèle proposé plus haut :

    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
    31
    32
    33
    34
    35
    36
    CREATE TABLE UT_utilisateur(
       UT_ident SERIAL,
       UT_nom VARCHAR(50) NOT NULL,
       PRIMARY KEY(UT_ident)
    );
     
    CREATE TABLE AP_aptitude(
       AP_ident SERIAL,
       AP_code CHAR(4) NOT NULL,
       AP_libelle VARCHAR(128) NOT NULL,
       PRIMARY KEY(AP_ident),
       UNIQUE(AP_code)
    );
     
    CREATE TABLE TA_tache(
       TA_ident SERIAL,
       TA_description VARCHAR(50) NOT NULL,
       PRIMARY KEY(TA_ident)
    );
     
    CREATE TABLE AQ_acquerir(
       UT_ident INTEGER,
       AP_ident INTEGER,
       AQ_date DATE NOT NULL,
       PRIMARY KEY(UT_ident, AP_ident),
       FOREIGN KEY(UT_ident) REFERENCES UT_utilisateur(UT_ident),
       FOREIGN KEY(AP_ident) REFERENCES AP_aptitude(AP_ident)
    );
     
    CREATE TABLE RQ_requerir(
       AP_ident INTEGER,
       TA_ident INTEGER,
       PRIMARY KEY(AP_ident, TA_ident),
       FOREIGN KEY(AP_ident) REFERENCES AP_aptitude(AP_ident),
       FOREIGN KEY(TA_ident) REFERENCES TA_tache(TA_ident)
    );
    Le script de création d'un petit jeu d'essais :

    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
    insert into UT_utilisateur(UT_nom)
           values ('Baucuse'), ('Fee du logis'), ('Bricolo')
    ;
    insert into AP_aptitude (AP_code, AP_libelle)
           values ('CUIS', 'Cuisine')
                , ('MENA', 'Ménage')
                , ('BRIC', 'Bricolage')
    ;
    insert into TA_tache(TA_description)
           values ('préparer un clafouti aux bananes')
                , ('réparer la boite aux lettres')
                , ('faire une pâte feuilletée')
                , ('repasser les chemises')
                , ('réparer la gazinière')
    ;            
    insert into AQ_acquerir(UT_ident, AP_ident, AQ_date)
           values (1, 1, '1945-10-12')
                , (1, 2, '1945-10-12')
                , (1, 3, '1970-09-23')
                , (2, 2, '1983-05-22')
                , (3, 3, '1990-06-11')
    ;
    insert into RQ_requerir(AP_ident, TA_ident)
           values (1, 1)
                , (3, 2)
                , (1, 3)
                , (2, 4)
    ;
    liste des personnes capables pour chaque tache :

    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
    select TA.TA_description
         , AP.AP_libelle
         , UT.UT_nom
    from TA_tache TA
    inner join RQ_requerir RQ
       on RQ.TA_ident=TA.TA_ident
    inner join AP_aptitude AP
       on AP.AP_ident=RQ.AP_ident
    left join AQ_acquerir AQ
         inner join UT_utilisateur UT
            on UT.UT_ident = AQ.UT_ident
       on AQ.AP_ident = AP.AP_ident
    where not exists
         (select 1
          from RQ_requerir R2
          where R2.TA_ident = RQ.TA_ident
            and R2.AP_ident<> RQ.AP_ident
            and not exists
               (select 1
                from AQ_acquerir Q2
                where Q2.AP_ident = R2.AP_ident
                  and Q2.UT_ident = UT.UT_ident
               )
         )
    ;
    Résultat :

    Nom : Sans titre.png
Affichages : 39
Taille : 9,9 Ko

    EDIT : j'ai légèrement modifié le jeu d'essai et la requête car je n'avais pas tenu compte du fait que certaines taches requièrent plusieurs compétences

  6. #6
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    20 473
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 20 473
    Points : 48 328
    Points
    48 328
    Par défaut
    La solution de la requête est une division relationnelle. À me lire :
    https://sqlpro.developpez.com/cours/divrelationnelle/

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  7. #7
    Expert éminent sénior
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    6 504
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 6 504
    Points : 20 165
    Points
    20 165
    Billets dans le blog
    2
    Par défaut
    Citation Envoyé par SQLpro Voir le message
    La solution de la requête est une division relationnelle. À me lire :
    https://sqlpro.developpez.com/cours/divrelationnelle/

    A +
    C'est en effet la division relationnelle qui est utilisée dans ma requête corrélée tout en bas

  8. #8
    Expert éminent sénior
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    6 504
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 6 504
    Points : 20 165
    Points
    20 165
    Billets dans le blog
    2
    Par défaut
    Citation Envoyé par escartefigue Voir le message
    Bonjour,

    Quelle différence y a-t-il entre une inaptitude et le fait de ne pas avoir une certaine aptitude ?
    Il me semble que être inapte à la cuisine, c'est ne pas avoir la compétence cuisine.
    Si l'on modélise deux notions distinctes, on risque les incohérences : rien n'interdira d'associer la compétence cuisine et l'incompétence cuisine à une même personne...
    Je m'auto corrige : une contrainte d'exclusion permettrait de le faire, mais c'est compliquer inutilement la solution

    @mrboliboli : avez vous testé, sujet résolu ?

  9. #9
    Membre du Club
    Homme Profil pro
    Administrateur systèmes et réseaux
    Inscrit en
    octobre 2011
    Messages
    50
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Administrateur systèmes et réseaux
    Secteur : Administration - Collectivité locale

    Informations forums :
    Inscription : octobre 2011
    Messages : 50
    Points : 42
    Points
    42
    Par défaut
    Bonjour,
    Je n'ai pas eu de temps à consacrer à cela dernièrement, désolé.

    Je suis en train de regarder, mais j'avoue que ca pique un peu...
    Il ne me reste plus qu'a étudier cela.

    Merci beaucoup.

Discussions similaires

  1. comparaison 2 listes
    Par moi5252 dans le forum Access
    Réponses: 2
    Dernier message: 27/05/2008, 18h47
  2. Comparaison sur liste chainée
    Par calagan dans le forum C
    Réponses: 9
    Dernier message: 24/07/2007, 22h58
  3. Comparaison de liste
    Par Bourdet dans le forum Langage
    Réponses: 2
    Dernier message: 13/10/2006, 10h50
  4. comparaison de listes chainee
    Par smalto dans le forum C
    Réponses: 5
    Dernier message: 22/09/2006, 19h10
  5. [List][Map?] Comparaison de Lists
    Par yolepro dans le forum Collection et Stream
    Réponses: 8
    Dernier message: 25/08/2006, 18h39

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo