Discussion :

[nullos]

Bonjour à toutes et tous,

Je suis amené à retravailler une requête sql d'un site que j'essaie de faire évoluer et je rencontre un souci dans la modification de cette requète.

Je m'explique le site répertoriait ses clients par leur seuls nom et prénom. Il a été décidé d'attribuer aux nouveaux clients un identifiant propre.

Nous avions une requête qui permettait de calculer un classement des meilleurs clients de l'année, classement foireux quand il y avait des homonymes (d'où l'idée de créer un identifiant)

Le souci a donc été d'intégrer dans la requête, cet identifiant.

En effet, (évoluant dans le milieu du droit) cette requête dont l'auteur (un génie selon moi) n'est pas connu est pour moi infiniment complexe puisque effectuant un inner join entre deux tables dont l'une est un alias de l'autre (désolé si les termes sont inadaptés).

Je vous soumets la requête pdo (simplifiée pour la question) et modifiée pour tenir compte de la nouvelle colonne "membre"

Code :

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

$reqI = $bdd->prepare("INSERT INTO test (nom, prenom, membre, total) (SELECT a.nom, a.prenom, a.membre,  round( avg(a.Indice), 2 ) AS total, nb_achat FROM rtable a inner join (select count(achat) as nb_achat,prenom, nom, membre from rtable where date_achat > DATE_ADD(NOW(), INTERVAL -365 DAY) group by nom, prenom, membre) as b on (b.nom=a.nom and b.prenom=a.prenom and b.membre=a.membre) where date_achat > DATE_ADD(NOW(), INTERVAL -365 DAY) and nb_achat >10 and find_in_set( a.Indice, ( SELECT substring_index( group_concat( b.Indice ORDER BY Indice ASC ) , ',', 11) FROM rtable b where date_achat > DATE_ADD(NOW(), INTERVAL -365 DAY) and a.nom = b.nom and a.prenom=b.prenom and a.membre=b.membre GROUP BY b.nom, b.prenom, b.membre ) ) >0 GROUP BY a.nom, a.prenom, a.membre ORDER BY `total` ASC, nb_achat desc)");

Le classement s'effectue hélas sur les clients qui ont un identifiant "membre". Il semble que les clients qui n'ont pas d'identifiant (valeur de membre = null) ne soit pas pris en compte mais je ne sais à quel niveau ces entrées sont ignorées, je suppose que ce n'est pas au niveau du select car un ifnull sur membre ne solutionne pas le problème.

Je suppose que le group by ignore les null mais le fait de faire ifnull(membre,0) dans les select aurait du y remédier non ?

Merci par avance à celles et à ceux qui voudront bien se pencher sur mon problème pour m'apporter une piste, parce que ça fait plusieurs jours que j'écume le net sans trouver de solution alors je me décide d'exposer mon problème.


Précision, j'ai songé attribueer à tous les clients sans identifiant le même identifiant bidon via une requête mais la table obèse présentant plus d'1 .500.000 entrées, j'aimerai éviter.

n.

[Michel]

bonjour,
pour moi, cela est un vrai plat de nouilles.
Peut-on avoir la structure de la table pour avoir une petite chance de s'y retrouver.
A priori, j'aurais envie de dire que cette base devrait être revue et cette table scindée en plusieurs mais je m'avance peut-être dans le brouillard

[escartefigue]

Nous avions une requête qui permettait de calculer un classement des meilleurs clients de l'année, classement foireux quand il y avait des homonymes (d'où l'idée de créer un identifiant)

Allez botter les fesses de l'abruti qui a conçu une table des clients sans identifiant unique

En effet, (évoluant dans le milieu du droit) cette requête dont l'auteur (un génie selon moi) n'est pas connu est pour moi infiniment complexe puisque effectuant un inner join entre deux tables dont l'une est un alias de l'autre (désolé si les termes sont inadaptés).

C'est le bon terme, un alias est, comme dans le français courant, un surnom utilisé pour désigner soit une table ou une vue comme dans votre requête, soit une colonne comme c'est également le cas avec l'alias "total".
Dans le milieu du droit si deux clients "Jacques Martin" ne peuvent être distingués faute d'identifiant, on envoie les courriers au hasard à l'un d'entre eux , imaginez une copie de jugement de tribunal envoyée au "mauvais" client !
Les effets peuvent être catastrophiques à la fois pour le client mais aussi pour le cabinet d'avocats ! L'auteur est en effet un véritable génie, il doit certainement se cacher dans sa lampe merveilleuse...

Je vous soumets la requête pdo (simplifiée pour la question) et modifiée pour tenir compte de la nouvelle colonne "membre"

Code :

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

$reqI = $bdd->prepare("INSERT INTO test (nom, prenom, membre, total) (SELECT a.nom, a.prenom, a.membre,  round( avg(a.Indice), 2 ) AS total, nb_achat FROM rtable a inner join (select count(achat) as nb_achat,prenom, nom, membre from rtable where date_achat > DATE_ADD(NOW(), INTERVAL -365 DAY) group by nom, prenom, membre) as b on (b.nom=a.nom and b.prenom=a.prenom and b.membre=a.membre) where date_achat > DATE_ADD(NOW(), INTERVAL -365 DAY) and nb_achat >10 and find_in_set( a.Indice, ( SELECT substring_index( group_concat( b.Indice ORDER BY Indice ASC ) , ',', 11) FROM rtable b where date_achat > DATE_ADD(NOW(), INTERVAL -365 DAY) and a.nom = b.nom and a.prenom=b.prenom and a.membre=b.membre GROUP BY b.nom, b.prenom, b.membre ) ) >0 GROUP BY a.nom, a.prenom, a.membre ORDER BY `total` ASC, nb_achat desc)");

Cette requête est illisible !
La première des choses à faire est de la mettre en forme, or, après l'avoir fait, voilà ce qu'on trouve

Code :

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

1
2
3
4
5
6
7
8
9
INSERT INTO test 
      (nom, prenom, membre, total) 
(SELECT a.nom
      , a.prenom
      , a.membre
      , round(avg(a.Indice), 2) AS total
      , nb_achat 
 FROM rtable a 
 inner join ...

Cette requête ne peut pas fonctionner, puisque vous insérez 5 valeurs (clause select) alors que vous ne déclarez que 4 colonnes !
Pour vous aider il existe des sites gratuits qui aident à remettre des requêtes sous une forme présentable, par exemple http://www.dpriver.com/pp/sqlformat.htm

Le classement s'effectue hélas sur les clients qui ont un identifiant "membre". Il semble que les clients qui n'ont pas d'identifiant (valeur de membre = null) ne soit pas pris en compte mais je ne sais à quel niveau ces entrées sont ignorées, je suppose que ce n'est pas au niveau du select car un ifnull sur membre ne solutionne pas le problème.
Je suppose que le group by ignore les null mais le fait de faire ifnull(membre,0) dans les select aurait du y remédier non ?

Il n'est pas nécessaire d'insérer dans une table de travail pour faire un classement, sauf si vous avez besoin de cette table "test" pour un usage ulterieur.
Je profite de l'occasion pour relever le barbarisme "solutionner", et vous invite à lire ce lien http://www.academie-francaise.fr/solutionner
Pour les marqueurs nulls, préférez l'utilisation de COALESCE qui est la norme SQL plutôt que IFNULL qui ne l'est pas.
Quoi qu'il en soit, si vous n'avez pas moyen d'attribuer un identifiant fiable pour les clients existants, il vaut peut être mieux renoncer à effectuer un classement les concernant non ?

Précision, j'ai songé attribuer à tous les clients sans identifiant le même identifiant bidon via une requête mais la table obèse présentant plus d'1 .500.000 entrées, j'aimerai éviter.

C'est exactement ce que fera COALESCE qui remplacera les id marqués "null" par la valeur précisée dans COALESCE au moment du select.

[nullos]

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
37
38
39
40
41
42
43
44
INSERT INTO test
            (nom,
             prenom,
             membre,
             total, nb_achat)
(SELECT a.nom,
        a.prenom,
        a.membre,
        Round(Avg(a.indice), 2) AS total,
        nb_achat
 FROM   rtable a
        INNER JOIN (SELECT Count(achat) AS nb_achat,
                           prenom,
                           nom,
                           membre
                    FROM   rtable
                    WHERE  date_achat > Date_add(Now(), INTERVAL -365 day)
                    GROUP  BY nom,
                              prenom,
                              membre) AS b
                ON ( b.nom = a.nom
                     AND b.prenom = a.prenom
                     AND b.membre = a.membre )
 WHERE  date_achat > Date_add(Now(), INTERVAL -365 day)
        AND nb_achat > 10
        AND Find_in_set(a.indice, (SELECT Substring_index(Group_concat(b.indice
                                                          ORDER BY
                                                              indice ASC), ',',
                                          11)
                                   FROM   rtable b
                                   WHERE  date_achat > Date_add(Now(), INTERVAL
                                                       -365 day
                                                       )
                                          AND a.nom = b.nom
                                          AND a.prenom = b.prenom
                                          AND a.membre = b.membre
                                   GROUP  BY b.nom,
                                             b.prenom,
                                             b.membre)) > 0
 GROUP  BY a.nom,
           a.prenom,
           a.membre
 ORDER  BY `total` ASC,
           nb_achat DESC)

Bonjour et merci

Effectivement, la table comprend 27 colonnes avec de nombreuses données qui auraient du faire l'objet de tables particulières. J'ai modifié les noms des colonnes pour qu'elles soient plus parlantes alors la structure réelle de la table rajouterait des difficultés de compréhension.

La création d'identifiants pour des clients poursuit également cet objectif de scinder la table (jusqu'à peu table unique) .

Effectivement la requête telle que je l'ai simplifiée ne peut fonctionner pour la raison donnée.

L'auteur de la requête n'est pas l'auteur du site, juste un gars qui a filé un coup de main un jour.

J'avais testé COALESCE sans plus de succès.

Merci encore

[escartefigue]

Procédez par étape, validez d'abord le contenu de chaque requête corrélée, celle de la jointure et celle de la restriction, avant d'exécuter la requête complète.

[nullos]

Je suis parti dans une mauvaise direction avec mes group by car, a priori, un inner join exclue les null.

J'ai perdu beaucoup de temps avec mes coalesce et mes ifnull

[escartefigue]

Un inner join exclut les null ?

Si le prédicat de jointure n'est pas vérifié
- avec une jointure "inner" aucune colonne de la table droite et de la table gauche n'est restituée dans la table résultante pour les lignes concernées par le prédicat de jointure
- avec une jointure "left ou right outer" seules les colonnes de la table gauche ("left outer") ou de la table droite ("right outer") sont restituées, les colonnes de l'autre table sont marquées "null" dans la table résultante
- avec une jointure "full outer" ce sont les colonnes de droite ou de gauche qui sont restituées selon le cas

Et bien sur, une jointure inner avec un ensemble vide, produit un ensemble vide. Dans votre cas, si la table d'alias b est un ensemble vide (par exemple il n'existe pas d'achat dans l'année en cours) le résultat final sera vide.

[Artemus24]

Salut à tous.

le site répertoriait ses clients par leur seuls nom et prénom. Il a été décidé d'attribuer aux nouveaux clients un identifiant propre.

J'aimerai savoir comment vous allez faire la distinction entre les homonymes ?
Il serait bien de nous communiquer votre table qui est en cause (rtable) !

Le souci a donc été d'intégrer dans la requête, cet identifiant.

En premier lieu, il faudrait une table contenant tous vos clients.
Un client par ligne, et donc un identifiant qui rend unique les lignes.
Genre : "id integer unsigned not null auto_increment primary key,".
Par le suite, vous vous servez de cet identifiant pour distinguer tous vos clients.

Je me pose des questions sur la nature de l'identifiant que vous avez utilisé ?
Surtout que vous avez, si j'ai bien compris, des NULL.
C'est franchment bizarre d'avoir des NULL dans un identifiant qui va certainement servir comme clef primaire.

puisque effectuant un inner join entre deux tables dont l'une est un alias de l'autre (désolé si les termes sont inadaptés).

Ce n'est pas a priori un problème. La bonne question est surtout pourquoi avez-vous besoin de procéder ainsi.

Mise en forme de votre requête :

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
INSERT INTO test (nom, prenom, membre, total)
(SELECT     a.nom,
            a.prenom,
            a.membre,
            round( avg(a.Indice), 2 ) AS total,
            nb_achat
 
      FROM  rtable a
 
inner join  (  select  count(achat) as nb_achat,
                       prenom,
                       nom,
                       membre
 
                 from  rtable
                where  date_achat > DATE_ADD(NOW(), INTERVAL -365 DAY)
             group by  nom, prenom, membre
            ) as b
        on   b.nom    = a.nom
       and   b.prenom = a.prenom
       and   b.membre = a.membre
 
     where  date_achat > DATE_ADD(NOW(), INTERVAL -365 DAY)
       and  nb_achat > 10 
       and  find_in_set( a.Indice, (  SELECT  substring_index( group_concat( b.Indice ORDER BY Indice ASC ) , ',', 11) 
                                        FROM  rtable b
                                       where  date_achat > DATE_ADD(NOW(), INTERVAL -365 DAY)
                                         and  a.nom      = b.nom
                                         and  a.prenom   = b.prenom
                                         and  a.membre   = b.membre
                                    GROUP BY  b.nom, b.prenom, b.membre
                                   )
                       ) > 0
  GROUP BY  a.nom, a.prenom, a.membre
  ORDER BY  `total` ASC, nb_achat desc
);

Je suppose que "membre" est votre identifiant unique avec, pour l'instant, des NULL.

Je ne comprends pas trop l'utilité du "find_in_set". Voir la documentation à ce sujet :
--> https://dev.mysql.com/doc/refman/8.0...on_find-in-set
Je préfère utiliser le "where exists (...)" qui est plus performant.
Encore que, je me pose la question de l'utilité du test sur "indice".

Vous vérifiez l'existence du contenu de la colonne "indice", sur les mêmes critères que ceux de la sélection.
Autrement dit, cela se sert à rien !

Je propose ceci :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
insert into `test` (membre,nom,prenom,indice,nb_achat)
select    membre
          nom,
          prenom,
          indice,
          count(achat) as nb_achat
 
    from  rtable as a
   where  date_achat > DATE_ADD(NOW(), INTERVAL -365 DAY)
      or  membre is null
 
group by  membre, nom, prenom, indice
  having  nb_achat > 10

Vu que j n'ai pas testé, il se peut que le résultat ne soit pas attendu.

Code :

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

or  membre is null

Ceci permet de faire apparaître les lignes dont la colonne "membre" contient le marqueur NULL.

L'important est de bien modéliser votre base de données.

Il nous faudrait un jeu d'essai complet, la structure de la table et le résultat que vous attendez.

@+

[nullos]

Bonjour,

Merci pour votre intérêt.

Je réponds même si la question est résolue.

Salut à tous.


J'aimerai savoir comment vous allez faire la distinction entre les homonymes ?

Dans la question, il s'agissait d'établir de modifier une requête établissant un classement des meilleurs clients des 365 derniers jours, classement basé sur un indice moyen donné à chaque client.
La modification de la requête portait sur l'ajout dans celle ci de l'id des clients nouvellement inscrits.

Car, auparavant, les clients n'avait pas besoin de s'inscrire pour commander, il n'existait pas de table client et donc pas d'id. Pire dans la table principale des achats, on trouvait sur chaque ligne le nom, le prénom du client, les éléments de son adresse la date, le prix, les références de la commande, le numéro de l'article, et quelques caractéristiques de type poids, L xlxh..., bref du grand n'importe quoi.

La table des clients inscrits a bien été créée avec un id primary key et les clients doivent désormais s'inscrire pour commander.

Seulement pendant 365 jours à compter de la création de la table clients, le classement des meilleurs clients tel qu'établit par la requête avant modification ne permettait pas de ressortir l'id du client quand celui-ci était inscrit.

C'est cette coexistence de clients avec et sans id qui me posait problème car j'avais besoin de comparer des résultats de colonnes membres qui parfois étaient nuls car pas d'id client d’où l'utilisation en dernier ressort de <=>

(Cependant, la solution exposée dans mon précédent post n'était pas pleinement satisfaisante en ce qu'elle créait des doublons sur les homonymes et surtout faussait le classement des homonymes en ne renvoyant pas le bon indice moyen de chaque homonyme -> j'ai donc fait deux requêtes distinctes - id-pas d'id- alimentant une même table temporaire puis copié le contenu de ma table temporaire classé par indice moyen desc dans ma table définitive / faute de savoir comment faire pour réinitialiser ma clé primaire servant également de place de classement de ma table définitive si je l'avais rempli directement avec mes deux requêtes).

Pour le reste, votre incompréhension est normale puisque j'ai simplifié de manière grossière la requête et que ce je vous ai laissé voir n'a pas vraiment d'intérêt ou de sens, ce qui peut être frustrant, pardon.

Merci encore

n.