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

Développement SQL Server Discussion :

Trouver les éléments manquants par groupe dans une jointure [2012]


Sujet :

Développement SQL Server

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre expérimenté
    Profil pro
    Mangeur de gauffre
    Inscrit en
    Octobre 2007
    Messages
    4 413
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations professionnelles :
    Activité : Mangeur de gauffre

    Informations forums :
    Inscription : Octobre 2007
    Messages : 4 413
    Par défaut Trouver les éléments manquants par groupe dans une jointure
    Bonjour

    Je ne sais pas si ce que je cherche a faire est possible en SQL mais je n'ai pas encore trouvé de solution

    J'ai deux tables

    - Sales Channel (3 elements : 1,2,3)
    - SalesPrice

    Pour chaque article présent dans SalesPrice je dois avoir un prix correspondant our Canaux définis sans Sales Channel 1,2 et 3
    J'essaye donc de créer une jointure entre ces deux tables qui ferait apparaitre les articles n'ayant pas les prix correspondant a chaque canaux

    Mais je sèche !

    La requête ci dessous me sort une ligne (pour SalesChannel 1)

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    select 
    * 
    from SalesPrice 
    FULL OUTER JOIN SalesChannel SC on SC.pkid=SalesPrice.SalesChannelId
    where SalesPrice.ArtId=22939
    Mais j'aimerais voir apparaître 3 lignes dont deux auraient au moins L'article et le salesChannel, le reste null afin d'identifier les valeurs manquantes

    Est-ce raisonnablement possible en SQL ?
    Merci de votre aide

    Voici le DDL de mes deux table

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    CREATE TABLE dbo.SalesChannel(
    pkId int IDENTITY(1,1) NOT NULL,
    Code varchar(32) NOT NULL,
    Name varchar(128) NOT NULL,
    Rank int NOT NULL,
    CONSTRAINT PK_SalesChannel PRIMARY KEY CLUSTERED 
    (
    pkId ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
    )
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    CREATE TABLE [dbo].[SalesPrice](
    [pkId] [int] IDENTITY(1,1) NOT NULL,
    [ArtId] [int] NOT NULL,
    [SalesChannelId] [int] NULL,
    [Price] [decimal](8, 2) NULL,
    [LastUpdateBy] [varchar](64) NULL,
    [DateLastUpdate] [datetime] NOT NULL,
    CONSTRAINT [PK_SalesPrice] PRIMARY KEY CLUSTERED 
    (
    [pkId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
    )

  2. #2
    Modérateur

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Janvier 2005
    Messages
    5 826
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 44
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2005
    Messages : 5 826
    Par défaut
    Bonjour,

    J'essaye donc de créer une jointure entre ces deux tables qui ferait apparaitre les articles n'ayant pas les prix correspondant a chaque canaux
    En fait vous vouliez dire tous les canaux, c'est à dire les trois

    C'est effectivement possible, et je comprends que cela vous soit paru un peu tordu.
    Les demi-jointures préservent une ligne par tuple correspondant, donc on ne peut pas obtenir l'ensemble des tuples possibles, même avec FULL JOIN.

    Il est donc nécessaire de "générer" l'ensemble des tuples que l'on cherche, c'est à dire un produit cartésien : CROSS JOIN.
    Une demi-jointure suffit alors à repérer les canaux pour lesquels il manque un prix.

    J'ai fait un petit test avec le jeu de données suivant :

    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
    INSERT INTO dbo.SalesChannel
    (
    	Code
    	,Name
    	,Rank
    )
    VALUES ('Code Channel 1', ' Name Channel 1', 1)
    	, ('Code Channel 2', ' Name Channel 2', 2)
    	, ('Code Channel 3', ' Name Channel 3', 3)
    GO
     
    INSERT INTO dbo.SalesPrice
    (
    	ArtId
    	,SalesChannelId
    	,Price
    	,LastUpdateBy
    	,DateLastUpdate
    )
    VALUES(1, NULL, 12, 'toto', GETDATE())
    	, (2, NULL, 15, 'titi', GETDATE())
    	, (3, 1, 5, 'tata', GETDATE())
    	, (4, 2, 52, 'tutu', GETDATE())
    	, (5, 3, 65, 'tyty', GETDATE())
    GO
    Voici la requête :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    SELECT		SP.ArtId
    		, SP.DateLastUpdate
    		, SP.Price
    		, SC.Code
    		, SC.Name
    		, SPC.*
    FROM		dbo.SalesPrice AS SP
    CROSS JOIN	dbo.SalesChannel SC
    LEFT JOIN	dbo.SalesChannel AS SPC
    			ON SPC.pkid = SP.SalesChannelId
    --WHERE		SP.ArtId=1
    ORDER BY	SP.ArtId
    Quelques remarques cependant, car il me semble qu'il y a quelques écarts dans le modèle physique :

    • Il n'y a pas de contrainte de clé étrangère sur la colonne SalesChannelId de la table dbo.SalesPrice référençant la table dbo.SalesChannel
    • Pourquoi permettre que la colonne SalesChannelId de la table dbo.SalesPrice n'ait pas de valeur ? (i.e. NULLable)
    • Il devrait probablement y avoir une contrainte d'unicité sur chacune 3 colonnes métier de la table dbo.SalesChannel
    • Est-il possible que la colonne Rank de la table dbo.SalesChannel contienne de valeurs supérieures à 255 ou inférieures à zéro ? Si non, vous pouvez utiliser le type tinyint
    • Il devrait probablement y avoir une contrainte d'unicité sur les deux colonnes ArtId et SalesChannelId de la table dbo.SalesPrice
    • Il est probable qu'un prix ne soit pas jamais négatif : si tel est le cas, il manque une contrainte de domaine sur la colonne Price de la table dbo.SalesPrice
    • La colonne LastUpdateBy de la table dbo.SalesPrice est de type varchar(64), ce qui laisse supposer que l'on stockera le nom de l'utilisatuer qui a réalisé le dernier changement. Il serait bien moins coûteux, à tout point de vue, de stocker l'id de cet utilisateur


    Il en reste une, mais elle tellement énorme que je suppose que vous avez donné un coup de ciseau dans les relations pour donner un DDL smiplifié de vos tables et demander de l'aide

    @++

  3. #3
    Membre expérimenté
    Profil pro
    Mangeur de gauffre
    Inscrit en
    Octobre 2007
    Messages
    4 413
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations professionnelles :
    Activité : Mangeur de gauffre

    Informations forums :
    Inscription : Octobre 2007
    Messages : 4 413
    Par défaut
    Merci Elsuket

    Effectivement en poursuivant mes recherche j'etais arrivé a la conclusion que le CROSS JOIN etait la solution
    Je m’apprêtais a ajouter un commentaire quand j'ai trouvé ta réponse pour laquelle je n'avais curieusement pas reçu de notification

    Toutes tes autres remarques sont très pertinentes mais ont toutes leurs explication

    - Simplification volontaire du modèle en phase de prototype
    - Rabotage du DDL pour l'exemple

    Et pour le UserID, les sources d'identity pouvant etre maintenue exterieurement a la DB opérationelle, le VarChar est plus confortable

    NB : Je n'utilise presque plus jamais autre choses que le INT ou parfois BigINT si vraiment nécessaire !

    Autrefois j'étais maniaque de l'optimisation des ressources mais j'ai fini par me dire que l'effort n'en valait plus la peine !

  4. #4
    Modérateur

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Janvier 2005
    Messages
    5 826
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 44
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2005
    Messages : 5 826
    Par défaut
    Autrefois j'étais maniaque de l'optimisation des ressources mais j'ai fini par me dire que l'effort n'en valait plus la peine !
    Qu'est-ce qui t'a conduit à cela ?

    @++

  5. #5
    Membre expérimenté
    Profil pro
    Mangeur de gauffre
    Inscrit en
    Octobre 2007
    Messages
    4 413
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations professionnelles :
    Activité : Mangeur de gauffre

    Informations forums :
    Inscription : Octobre 2007
    Messages : 4 413
    Par défaut
    Qu'est-ce qui t'a conduit à cela ?
    La réflexion que les processeur sont de toute façon optimisé pour traiter des int32 (au moins)
    Que l'espace disque ne vaut plus grand chose
    Et que ce contexte l'effort de couper des int en bytes et bytes en bit n'apporte pas grand chose de plus que la satisfaction intellectuelle de l'avoir fait !

    Sauf dans des contexte particulier avec des contraintes matérielles importantes !

  6. #6
    Modérateur

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Janvier 2005
    Messages
    5 826
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 44
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2005
    Messages : 5 826
    Par défaut
    Que l'espace disque ne vaut plus grand chose
    Certes, mais je ne crois pas que ce soit une raison pour le gaspiller. Plus on en met en RAM, meilleures seront les IOs et donc les temps de réponse
    D'ailleurs, cela se retrouve chez les éditeurs de logiciels dans les copies d'environnements de développement et de test.

    l'effort de couper des int en bytes et bytes en bit n'apporte pas grand chose de plus que la satisfaction intellectuelle de l'avoir fait
    Là encore il s'agit de tailler au plus juste pour minimiser les IOs, pas d'un délire de rêveur ou d'une personne physcho-rigide

    Le stockage reste la partie la plus lente du hardware, même si elle évolue constamment, et que les disques SSD ont apporté un bond en avant énorme.
    D'ailleurs, la mécanique interne de l'implémentation du stockage orienté colonne dans SQL Server montre que c'est bien d'actualité, puisque les valeurs des colonnes sont implicitement compressées.
    L'unique but que cela sert est la minimisation des IOs.

    Je constate toujours que construire une base de données sans prendre le soin de la modéliser et de choisir avec attention les types de données les mieux adaptés pose toujours des problèmes de performance non-négligeables lors des montées en volumes et en charge. J'observe cela aussi bien sur du stockage HDD que sur du SSD. Maintenant, si ça marche pour vous, je n'y vois pas d'inconvénient.

    @++

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Réponses: 2
    Dernier message: 28/02/2014, 14h12
  2. Afficher les éléments d'un tableau dans une ArrayList
    Par AmeniESC dans le forum Collection et Stream
    Réponses: 7
    Dernier message: 23/03/2012, 12h52
  3. Réponses: 3
    Dernier message: 10/08/2009, 17h39
  4. Réponses: 12
    Dernier message: 14/05/2008, 17h15
  5. Réponses: 6
    Dernier message: 13/12/2007, 14h27

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