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 :

Eviter les join multiples


Sujet :

Langage SQL

  1. #1
    Modérateur
    Avatar de grunk
    Homme Profil pro
    Architecte Web / Android
    Inscrit en
    août 2003
    Messages
    5 571
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 35
    Localisation : France, Côte d'Or (Bourgogne)

    Informations professionnelles :
    Activité : Architecte Web / Android
    Secteur : Industrie

    Informations forums :
    Inscription : août 2003
    Messages : 5 571
    Points : 15 103
    Points
    15 103
    Par défaut Eviter les join multiples
    Bonjour à tous,

    j'ai actuellement une base sqlite avec 2 tables :
    Les tables
    detection :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    CREATE TABLE `detection` (
    	`created_at`	TEXT NOT NULL,
    	`count_value`	INTEGER NOT NULL,
    	`type_obj`	INTEGER NOT NULL DEFAULT 0
    );
    et caracteristique :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    CREATE TABLE `caracteristique ` (
    	`key`	TEXT NOT NULL,
    	`value`	TEXT NOT NULL,
    	`id_datas`INTEGER NOT NULL
    );
    Il n'ya pas de clé étrangère entre ces deux tables car la table caracteristique peux contenir des données pour d'autres tables (j'ai volontairement exclu les champs non utile à ma question).

    Donc une ligne de la table "detection" peux avoir n lignes dans la table "caracteristique". L'idée derrière les champs key et value c'est que les caracterisitques et valeurs sont des champs "libre" et donc les utilisateur peuvent créer des caracteristique sans avoir à intervenir sur la structure de la bdd.

    Les données

    Exemples de données pour bien comprendre :
    detection :
    "2019-06-26 09:40:00" "1" "1"

    caracteristique :
    "age" "35" "4"
    "gender" "M" "4"

    Avec cette architecture je peux donc facilement trouver toutes les détections qui ont par exemple un genre égal à "M" :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    SELECT * FROM detection d LEFT JOIN caracteristique ca ON d.rowid = ca.id_datas AND ca.type_table = 1 WHERE ca.key = 'gender' AND ca.value = 'M';
    Le select * est juste là pour la démo , pas tapé

    Mon problème arrive quand je veux combiner plusieurs "caracteristiques" , par exemple faire la somme de toutes les détections ayant un genre à 'M' et un age égale à "35".
    J'ai réussi en faisant une jointure supplémentaire mais je trouve ça fastidieux (surtout si j'ai bcp de caracteristiques à combiner) :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    SELECT * FROM detection d LEFT JOIN caracteristique ca ON d.rowid = ca.id_datas AND ca.type_table = 1 LEFT JOIN carac ca2 ON d.rowid = ca2.id_datas AND ca2.type_table = 1 WHERE ca.key = 'gender' AND ca.value = 'M' AND ca2.key = 'age' AND ca2.value='35';
    Y'a t'il moyen de faire mieux ?

    Merci
    Pry Framework php5 | N'oubliez pas de consulter les FAQ Java et les cours et tutoriels Java

  2. #2
    Modérateur

    Profil pro
    dba
    Inscrit en
    janvier 2010
    Messages
    5 351
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : janvier 2010
    Messages : 5 351
    Points : 12 251
    Points
    12 251
    Par défaut
    bonjour,

    Vous avez une alternative avec un regroupement, mais pas sûr du coup que ça soit plus efficace, surtout si la table caracteristique est correctement indexée

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
     
    SELECT 
    		rowid
    	,	SUM(count_value) AS somme
    FROM detection d 
    LEFT JOIN caracteristique ca 
    	ON d.rowid = ca.id_datas 
    	AND ca.type_table = 1 
    WHERE ca.key IN ('gender', 'age')
    GROUP BY rowid
    HAVING MAX(CASE WHEN ca.key = 'gender' AND ca.value = 'M' THEN 1 END) = 1
    AND MAX(CASE WHEN ca.key = 'age' AND ca.value = '35' THEN 1 END) = 1
    ;

  3. #3
    Modérateur
    Avatar de grunk
    Homme Profil pro
    Architecte Web / Android
    Inscrit en
    août 2003
    Messages
    5 571
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 35
    Localisation : France, Côte d'Or (Bourgogne)

    Informations professionnelles :
    Activité : Architecte Web / Android
    Secteur : Industrie

    Informations forums :
    Inscription : août 2003
    Messages : 5 571
    Points : 15 103
    Points
    15 103
    Par défaut
    Merci de la réponse.

    La table caracteristique est indexée avec un index unique (key,id_datas).

    En passant par un regroupement la requête semble être plus facile à générer dans le code , c'est une alternative intéressante.

    En revanche dans ton exemple le WHERE n'a pas lieux d'être je pense.
    Pry Framework php5 | N'oubliez pas de consulter les FAQ Java et les cours et tutoriels Java

  4. #4
    Modérateur

    Profil pro
    dba
    Inscrit en
    janvier 2010
    Messages
    5 351
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : janvier 2010
    Messages : 5 351
    Points : 12 251
    Points
    12 251
    Par défaut
    Concernant le WHERE, le but et de permettre enventuellement un filtre en amont, pour manipuler moins de données. il est donc là juste pour aider l'optimiseur.

    Cela dit, je ne sais pas si le résultat de ma requete est vraiment exact : suite à la jointure, il y aura donc deux lignes pour chaque ligne dans la table detection, chaque valeur sera donc comptée deux fois.

    quelle somme voulez-vous faire exactement ?

    un petit jeu d'essai + résultat attendu aiderait certainement.

    Enfin, concernant l'index, mettre id_datas en premier serait peut-être préférable (certainement plus discriminant), mais surtout il faudrait ajouter la colonne type_table afin que l'index soit couvrant

  5. #5
    Modérateur
    Avatar de grunk
    Homme Profil pro
    Architecte Web / Android
    Inscrit en
    août 2003
    Messages
    5 571
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 35
    Localisation : France, Côte d'Or (Bourgogne)

    Informations professionnelles :
    Activité : Architecte Web / Android
    Secteur : Industrie

    Informations forums :
    Inscription : août 2003
    Messages : 5 571
    Points : 15 103
    Points
    15 103
    Par défaut
    Je suis pas non plus certains que les résultats soient juste.
    Je n'arrive pas aux même résultat qu'avec les mutiples join sur certaines recherche.

    mais surtout il faudrait ajouter la colonne type_table afin que l'index soit couvrant
    il l'est , je l'ai pas précisé car je l'avais pas mis dans mon create table , mais sur le jeux de données réel il est bien présent.

    Le regroupement avec having n'est pas forcément plus claire à la lecture, donc je vais peut être choisir la simplicité et resté avec les join.
    Pry Framework php5 | N'oubliez pas de consulter les FAQ Java et les cours et tutoriels Java

  6. #6
    Modérateur

    Profil pro
    dba
    Inscrit en
    janvier 2010
    Messages
    5 351
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : janvier 2010
    Messages : 5 351
    Points : 12 251
    Points
    12 251
    Par défaut
    Citation Envoyé par grunk Voir le message
    il l'est , je l'ai pas précisé car je l'avais pas mis dans mon create table , mais sur le jeux de données réel il est bien présent.
    je voulais dire : ajouter la colonne type_table à l'index
    ainsi, il ne devrait plus y avoir d’accès à la table caractéristique, l'index suffira

Discussions similaires

  1. Eviter les doublons dans une join table
    Par nouanda dans le forum SQLite
    Réponses: 2
    Dernier message: 19/03/2018, 03h43
  2. Eviter les requêtes multiples
    Par Catalyst dans le forum Requêtes
    Réponses: 2
    Dernier message: 23/07/2013, 16h17
  3. Script + Cron - Comment eviter les lancements multiples
    Par jean-A dans le forum Administration système
    Réponses: 11
    Dernier message: 21/06/2011, 14h55
  4. Joined-subclass ==> Eviter les outer join
    Par bidi dans le forum Hibernate
    Réponses: 1
    Dernier message: 11/09/2009, 15h49
  5. Eviter les include multiples
    Par ninours23 dans le forum Bibliothèques
    Réponses: 2
    Dernier message: 02/02/2008, 15h41

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