Requête SELECT très complexe

Question

Bonjour,

Je dois faire une requête SELECT complexe et je rencontre un problème. J'espère qu'une bonne âme pourra me filer un coup de main pour délier tout ça car je suis loin d'être un expert en SQL.

Je dois donc sélectionner des lignes qui n'existent pas dans la table. Je vais tenter de faire simple sans vous embrouiller avec la complexité de la structure de la table et de la requête pour aller à l'essentiel du problème.

J'au une Table avec 

ID- Clé Primaire)
PAYS - Clé Étrangère
TYPE - (NVARCHAR(50))
MAX - INT
INDICE - INT

Voici un exemple de donnée (je ne vous fait pas les GUID, trop long et beaucoup moins lisible que des index INT...) :


  ID   PAYS    TYPE      MAX    INDICE 
  0  |   2  |  Type1  |   5   |   2
  1  |   2  |  Type2  |   2   |   4
  2  |   1  |  Type1  |   2   |   2
  3  |   1  |  Type2  |   1   |   1

Donc mon problème est que je doit me servir de la colonne max comme d'un multiplicateur. En gros je dois faire une requête SELECT qui pour chaque ligne de la table boucle sur la valeur de MAX et qui multiplie la valeur de la colonne INDICE. C'est dur à expliquer donc voilà un aperçu des données que je devrais obtenir : 
  
  ID   PAYS    TYPE     Multi    INDICE 
  0  |   2  |  Type1  |   1    |   2
  0  |   2  |  Type1  |   2    |   4
  0  |   2  |  Type1  |   3    |   6
  0  |   2  |  Type1  |   4    |   8
  0  |   2  |  Type1  |   5    |  10
  1  |   2  |  Type2  |   1    |   4
  1  |   2  |  Type2  |   2    |   8
  2  |   1  |  Type1  |   1    |   2
  2  |   1  |  Type1  |   2    |   4
  3  |   1  |  Type2  |   1    |   1
  3  |   1  |  Type2  |   2    |   2

En gros, les lignes du résultat sont multipliés, on retrouve 5 fois la ligne 1 avec son ID, et l'indice multiplié pour chaque ligne, 2 fois la ligne 2, 2 fois la ligne 3 et une fois la ligne 4. En gros il faut arriver à créer dynamiquement des lignes dans la table retournée par la requête.

j'ai déjà eu à faire ce genre de chose avec les dates pour les statistique d'un graphique, il fallait afficher tous les jours du mois même quand il n'y avait pas d'entrée en base, le graphique affichait alors un point zéro pour le jour. Avec les dates on peut aisément parcourir les jours d'un mois. Mais la je ne vois pas comment faire pour parcourir mon nombre MAX en partant de 1.

Encore une fois, je remercie d'avance ceux qui prendrons de leur temps pour me répondre et si je n'ai pas été clair n'hésitez pas à me demander des précisions.


Si le déboguage est l'art d'enlever les bogues, la programmation doit être l'art de les créer.

Guillemouze · Accepted Answer

Oui je parle du code cote serveur.
Alors je sais pas exactement ce qui se passe completement dans ton traitement, et je suppose que dans ta requete
SELECT * FROM T1 
WHERE T1.id NOT IN (SELECT...) 
AND T1.id NOT IN (SELECT...)
les "NOT IN SELECT" font des comparaisons avec T1.Multi .
N'y a-t'il pas moyen de faire ta requête plus simplement, quitte à la diviser ?
Ce que j'imagine (en gros, car il doit y avoir des subtilités dans ta requete dont je n'ai pas connaissance), c'est de faire ton select, puis, dans ton code qui traite ces resultats, ne traiter que les lignes qui correspondent à ton critère, en traitant chaque ligne N fois, avec la valeur de multi definie par ta boucle.

Mayzz · Answer

Pfiooooou !

Heureusement que je me suis relu avant de poster !!!

D'abord désolé pour les fautes d'orthographe inadmissibles (je doit...) et puis la structure de la tables n'est pas très claire, je la refais :

ID      -   Clé Primaire 
PAYS    -   Clé Étrangère 
TYPE    -   NVARCHAR(50)
MAX     -   INT 
INDICE  -   INT

En suite, pour ce qui est du résultat la dernière ligne ne devrait pas apparaître, le MAX de la quatrième ligne étant de 1, celle-ci n'est pas sensée être multipliée.


Si le déboguage est l'art d'enlever les bogues, la programmation doit être l'art de les créer.

yann_lo_san · Answer

Salut,

avec quel sgbd tu voudrais faire cela ?

Avec Sql-Server, ce serait assez simple.

. crée une table temp matchant le résultat voulu
. curseur en avant sur les lignes primaires 
. remplie autant de ligne que necessaire dans table temp selon champ MAX
. Puis insert dans la table finale a la sortie du curseur.

Par contre si tu veux faire cela avec un seul select, ça me parait assez compliqué.

bye...

Mayzz · Answer

Bonjour,

Déjà je te remercie pour l'intérêt que tu portes à mon problème. Les réponses ne sont pas foule ici, je suis un habitué du forum de vbfrance ou la fréquentation est bien plus dense (même si elle a nettement réduit ces dernières années malheureusement).

Pour revenir à mon problème donc j'ai bien lu ta solution mais comme je l'ai précisé je ne suis pas un expert en SQL. Si tu pouvais élargir un peu plus cela m'aiderais grandement ^^

Pour le SGBD il s'agit bien de SQL Server (2008R2).

En ce qui concerne mon problème il n'est qu'une partie de la requête SELECT finale. J'utilise une requête qui exclu des résultats par plusieurs NOT IN (SELECT ...). La partie sur laquelle je bute n'est qu'une sous requête de plus. Donc je peux faire plusieurs SELECT imbriqués en sous requêtes mais je ne peux pas faire plusieurs requêtes distinctes et séparés en effet.

En ce qui concerne les performances de la requête elles n'ont pas d'importance car cette requête est utilisée pour la configuration des comptes d'un site. Ses utilisateur ne l'utiliseront pas tous les jours et toutes les minutes donc ce n'est pas comme une requête de messagerie ou de forum.

Si le déboguage est l'art d'enlever les bogues, la programmation doit être l'art de les créer.

Guillemouze · Answer

Salut,
je pense que tu cherche a faire en une opération d'une manière compliquée (si elle est possible !) ce que tu peux faire en quelques opérations supplémentaires par le code. Si, pour chaque ligne, tu boucle de 0 à MAX pour faire tes traitements, tu arrivera au resultat, et aura moins de chances d'avoir des erreurs, qui de plus seraient plus colmplexes à corriger !

Mayzz · Answer

Bonjour,

Je ne comprends pas vraiment de quoi tu parles lorsque tu dis "par code". Tu parles du code coté serveur ? Parce que si tel est le cas je ne vois pas comment créer une table dynamique dans une requête SQL via un code serveur, même en bouclant.

Ce que j'expose plus haut n'est qu'une partie de ma requête finale. En gros ma requête fait :

WITH T1 (requête manquante qui générera des lignes dynamiques, lignes n'existant pas dans la table)

SELECT * FROM T1
WHERE T1.id NOT IN (SELECT...)
AND T1.id NOT IN (SELECT...)

Je pars donc de cette table contenant des lignes créés dynamiquement et en exclu petit à petit via des NOT IN.

À moins que tu veuilles dire par code SQL c'est ça ?


Si le déboguage est l'art d'enlever les bogues, la programmation doit être l'art de les créer.

Mayzz · Answer

[quote=Guillemouze]Oui je parle du code cote serveur./quote

Dans ce cas ce n'est pas possible. Car un code serveur ne peux pas ajouter des lignes à une requête. Mais je vois ce que tu veux dire.

[quote=Guillemouze]Ce que j'imagine (en gros, car il doit y avoir des subtilités dans ta requete dont je n'ai pas connaissance)/quote

En effet, j'ai expliqué ce que je cherchais à faire main non pourquoi. Tu dois penser que le résultat de cette requête est destiné à être affiché dans une liste HTML par exemple, et que coté serveur ou JS, il me suffirait d'ajouter manuellement les lignes.

Malheureusement ce n'est pas le cas, cette requête doit récupérer les lignes et les insérer en base dans une autre table. Le meilleur moyen pour ça c'est une requête SQL, car même si j'ajoute dynamiquement des résultats en multipliant via une boucle comme tu le suggère, le code coté serveur ne sait pas exécuter une requête SELECT sur ces résultats lui. Ce que je veux dire c'est qu'il n'est pas possible de créer une table par code (C#/PHP), de l'envoyer vers un SGBD (MSQLS/MySQL) et de faire une requête SELECT pour en obtenir le résultat (à ma connaissance).

Je vais essayer de détailler un peu plus. En fait, cette requête est un filtrage. Elle doit permettre à l'utilisateur de saisir un chiffre. Ce chiffre devra permettre de déterminer pour chaque Pays et chaque Type l'indice le plus proche. Jusque-là rien de complexe, on gère ça avec ORDER BY et GROUP BY. Le problème c'est le multiplicateur. Par exemple si ma table contient :

  ID   PAYS    TYPE      MAX    INDICE 
  0  |   2  |  Type1  |   5   |   8 
  0  |   2  |  Type1  |   2   |   5


Et que l'utilisateur saisi le chiffre "10" les enregistrements ayant l'indice le plus proche de 10 ressortiront :

  ID   PAYS    TYPE      MAX    INDICE 
  0  |   2  |  Type1  |   5   |   8 

 
Ça c'est simple à faire via GROUP BY et ORDER BY. Après le souci que je rencontre c'est que le comparateur doit prendre en compte le multiplicateur. La ligne retournée dans l'exemple à un indice de 8 et 8 est beaucoup plus proche de 10 que 5 nous sommes bien d'accord. Seulement la ligne N°2 de la table a un MAX de 5, elle peut donc être multipliée jusqu&#8217;à 5 fois. et en la multipliant 2 fois nous obtenons le chiffre 10. Qui devient donc le résultat le plus probant.

C'est pourquoi je dois créer la table par code, mais code SQL. L'instruction WITH aide à créer une table virtuelle. Mais je ne parviens pas à boucler ou à trouver une solution pour obtenir ce résultat :

  ID   PAYS    TYPE     Multi    INDICE 


Si le déboguage est l'art d'enlever les bogues, la programmation doit être l'art de les créer.

Guillemouze · Answer

Ok, j'y vois deja un peu plus clair.
Alors une piste qui peut etre interessante si tes MAX ont une borne supérieure pas trop elevée, c'est de faire une combinaison pour tout les multiplicateurs. Par exemple :

SELECT ..., INDICE, 1 AS  Multi, (INDICE * 1) AS Total From MaTable WHERE MAX > 1
UNION
SELECT ..., INDICE, 2 AS  Multi, (INDICE * 2) AS Total From MaTable WHERE MAX > 2
...
UNION
SELECT ..., INDICE, N AS  Multi, (INDICE * N) AS Total From MaTable WHERE MAX > N

et tu obtiendrai pour ton exemple du message precedent:

  ID   PAYS    TYPE      MAX    INDICE   MULTI  TOTAL
  0  |   2  |  Type1  |   5   |   8    |   1   |   8  
  0  |   2  |  Type1  |   2   |   5    |   1   |   5  
  0  |   2  |  Type1  |   5   |   8    |   2   |  16  
  0  |   2  |  Type1  |   2   |   5    |   2   |  10  
  0  |   2  |  Type1  |   5   |   8    |   3   |  24  
  0  |   2  |  Type1  |   5   |   8    |   4   |  32  
  0  |   2  |  Type1  |   5   |   8    |   5   |  40

Mayzz · Answer

Le problème encore une fois c'est que je pars de la table de base que je vais appeler TB

  ID   PAYS    TYPE      MAX    INDICE 
  0  |   2  |  Type1  |   5   |   2
  1  |   2  |  Type2  |   2   |   4


Et je souhaite trouver une solution pour obtenir une table virtuelle que je vais appeler TV ou la colonne MAX laisse place à un multiplicateur.

  ID   PAYS    TYPE     Multi    INDICE
  0  |   2  |  Type1  |   1    |   2
  0  |   2  |  Type1  |   2    |   4
  0  |   2  |  Type1  |   3    |   6
  0  |   2  |  Type1  |   4    |   8
  0  |   2  |  Type1  |   5    |  10
  1  |   2  |  Type2  |   1    |   4
  1  |   2  |  Type2  |   2    |   8


Pour ensuite faire un sélect sur cette table.

C'est assez difficile à expliquer alors je vais imager avec des dates ça sera plus simple. Imagine que tu doives récupérer le chiffre d'affaire de tes ventes pour les afficher sur un graphique de façon journalière. Ta table contiendrais par exemple les données suivantes :

01/03/2013 20€
05/03/2013 40€
22/03/2013 50€

Ton graphique n'aurait alors que 3 points et ne serait pas très lisible. Donc pour ça tu vas afficher tous les jours du mois dans ton graphique, même ceux ou tu n'as pas fait de CA, tu vas donc créer des enregistrement pour tous les jours du mois ou de ta période de sélection. C'est en gros ce que je cherche à faire. Créer une table virtuelle, contenant des enregistrements inventés de toutes pièces mais basés sur une table réelle. Voici le code que j'ai utilisé :

WITH T0 AS (SELECT        CAST(@DateDebut AS DATE) AS JOUR), T1 AS
    (SELECT        JOUR
      FROM            T0 AS T0_1
      UNION ALL
      SELECT        DATEADD(DAY, 1, JOUR) AS Expr1
      FROM            T1 AS T1_2
      WHERE        (DATEADD(DAY, 1, JOUR) <= CAST(@DateFin AS DATE)))

C'est un code que j'ai trouvé sur un forum et que j'ai modifié même si je ne suis pas sur de tout comprendre. Il génère des entrées dans la table pour chaque jour compris dans une période données (@DateDebut et @DateFin). Il ne reste plus en suite qu'à faire des UNION et SELECT avec T0. Bien entendu je ne cherche pas à travailler avec des dates mais avec des indices.

Dans ton exemple ci-dessus tu saisi des valeurs "en dur", or je ne connais pas à l'avance le MAX de chaque entrée, et c'est bien à ça que servent les DB, à avoir des données dynamiques sinon aucun intérêt. Le champ MAX sera toujours compris entre 1 et 10 je pense, même si il peut changer. Le fait est qu'on ne peut pas coder en dur dans la requête le résultat d'une donnée de la table qu'on ne connait pas à l'avance.

Il faudrait en effet faire une sorte de boucle FOR NEXT avec MAX pour chaque enregistrement de TB de et les ajouter à TV en mettant le résultat de la multiplication en indice. Mais j'ignore la syntaxe pour faire une telle chose, une boucle for dans une requêtes SELECT je ne sais même pas si c'est possible.

En tout cas je te remercie de prendre le temps de m'aider à résoudre ce problème.

Si le déboguage est l'art d'enlever les bogues, la programmation doit être l'art de les créer.

Guillemouze · Answer

ok je vois un peu mieux.
Par contre ta requete (des dates) est assez complexe : ca fait un SELECT dans le resultat d'un SELECT; dans la construction de T0, ca fait un SELECT sur T0 !

Question subsidiaire : la table dont tu parles :
C'est en gros ce que je cherche à faire. Créer une table virtuelle, contenant des enregistrements inventés de toutes pièces mais basés sur une table réelle.
Que fais-tu ensuite de cette table ? Elle te sert à faire d'autres opérations SQL dessus, ou ce sont les données que tu va ensuite utiliser dans ton script ? Si c'est le cas, alors ma 1ere remarque disant que tu peux generer les lignes virtuelles toi même est valable.

Sinon, c'est peut être jouable avec du PL/SQL, mais je ne sais pas si ca existe avec SQL Server.
Mais si la requete des dates fonctionne, tu devrait pouvoir l'adapter a ton cas, non ?

cs_Malkuth · Answer

Tu as deux posibilités : 

AVEC UN MAX Borné :
Si le MAX ne dépasse jamais 10.

Tu commences par créer une table secondaire :
CREATE TABLE [Nombres]
(
    [Nombre] INT NOT NULL PRIMARY KEY
);

Ensuite tu la prépares en y insérant tous les nombres possibles jusqu'à ta borne MAX maximal (ici : 10) :

INSERT INTO [Nombres]([Nombre])
SELECT 1 UNION SELECT 2
UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6
UNION SELECT 7 UNION SELECT 8
UNION SELECT 9 UNION SELECT 10;

Ensuite : la requette, on la créer sous la forme d'une Vue pour la retrouvé rapidement. L'astuce réside dans une jointure avec un prédicat de type <=

CREATE VIEW AS [TB_Multi]
WITH SCHEMABINDING
AS
SELECT
    [TB].[ID],
    [TB].[PAYS],
    [TB].[TYPE],
    [Nombres].[Nombre] AS [Multi],
    [Nombres].[Nombre] * [TB].[INDICE] AS [INDICE]
FROM [TB]
INNER JOIN [Nombres]
    ON [Nombres].[Nombre] <= [TB].[MAX]

Avec une CTE récursive
C'est la méthode employé dans le post précédent (Ici pour mieux la comprendre : http://sqlpro.developpez.com/cours/sqlserver/cte-recursives/).

Avec cette méthode pas besoin de connaitre le MAX maximum à l'avance et pas de table supplémentaire, par contre je suis pas sur que les performances soit aussi bonne.

CREATE VIEW AS [TB_Multi]
WITH SCHEMABINDING
AS 
WITH [RECURSION] (
    SELECT
        [TB].[ID],
        [TB].[PAYS],
        [TB].[TYPE],
        [TB].[INDICE],
        [TB].[MAX] AS [Multi]
    FROM [TB]
    UNION ALL
    SELECT
        [RECURSION].[ID],
        [RECURSION].[PAYS],
        [RECURSION].[TYPE],
        [RECURSION].[INDICE],
        [RECURSION].[Multi] - 1 AS [Multi]
    FROM [RECURSION]
    WHERE [RECURSION].[Multi] > 1
)
SELECT
    [RECURSION].[ID],
    [RECURSION].[PAYS],
    [RECURSION].[TYPE],
    [RECURSION].[Multi],
    [RECURSION].[INDICE] * [RECURSION].[Multi] AS [INDICE]
FROM [RECURSION]

Quel que soit la méthode une fois la vue créer tu t'en sert comme d'une table, par example les 10 ligne les plus proche de l'indice 10 :

SELECT TOP(10) * 
FROM [TB_Multi]
ORDER BY ABS([INDICE]-10)

Je te conseil d'oublier les idées de curseur qui sont rarement très indiqués en BDD relationnel.

Mayzz · Answer

Malkuth> Merci pour ta réponse. Je vais étudier cela de très près. Cela me semble très complexe et je ne peux comprendre au premier coup d'oeil. En tous cas merci. À défaut de pouvoir de dire immédiatement si ça résout mon problème je peux déjà te dire que ce sera au moins très instructif.


Si le déboguage est l'art d'enlever les bogues, la programmation doit être l'art de les créer.

Requête SELECT très complexe

12 réponses

Votre réponse

sur les mêmes thèmes