SELECT NULL , problème de GROUP BY

Version imprimable

30/12/2018, 00h34
nullos

SELECT NULL , problème de GROUP BY

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:

$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.
30/12/2018, 10h15
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

Citation:

Envoyé par nullos

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 :aie:

Citation:

Envoyé par nullos

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 :roll:, 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...

Citation:

Envoyé par nullos

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

Code:

$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:

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

Citation:

Envoyé par nullos

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 ?

Citation:

Envoyé par nullos

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.

Code:

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

30/12/2018, 18h35
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.
01/01/2019, 22h44
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 :?
02/01/2019, 13h30
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.

Bonjour, et merci pour votre intérêt.

Pardon, je me suis mal exprimé

La requête fonctionnait mais ne renvoyait que les résultats où il existait un identifiant pour le client.

Pour l'explication, voici la requête qui omettait les clients sans identifiant, simplifiée pour la compréhension
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 INSERT INTO test (nom, prenom, membre) (SELECT a.nom, a.prenom, Ifnull(a.membre, '99999') FROM rtable a INNER JOIN (SELECT prenom, nom, Ifnull(membre, '99999') 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 Ifnull(membre, '99999') = a.membre ) WHERE date_achat > Date_add(Now(), INTERVAL -365 day) GROUP BY a.nom, a.prenom, a.membre)
Je crois comprendre qu'avec INNER JOIN la comparaison des deux valeurs "membre" de "a" et de "b" est impossible si les deux sont nulles et donc par conséquent les résultats où les valeurs des identifiants des deux membres sont nulles (ici les clients sans identifiant) sont exclus.

En voulant faire des Ifnull(membre, '99999') as membreb dans mon select pour que ma clause on soit plus simple à écrire sans connaître l'ordre FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY ... j'ai perdu mon temps

Enfin, voici la requête exemple modifiée qui permet de contourner mon problème.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 INSERT INTO test (nom, prenom, membre) (SELECT a.nom, a.prenom, Ifnull(a.membre, '99999') FROM rtable a INNER JOIN (SELECT prenom, nom, Ifnull(membre, '99999') 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 ( ( NOT Ifnull(membre, '99999') <=> a.membre ) OR ( Ifnull(membre, '99999') = a.membre ) ) ) WHERE date_achat > Date_add(Now(), INTERVAL -365 day) GROUP BY a.nom, a.prenom, a.membre)
Ici l'opérateur "<=>" ->"IS NOT DISTiNCT" pour MYSQL utilisé négativement permet de ressortir les résultats où les clients n'ont pas d'identifiant.

Y'a certainement moyen de simplifier ou d'améliorer mais c'est une solution à mon problème qui me permet d'avancer.

~~Je modifie le titre du topic parce que le problème était lié à INNER JOIN et non à GROUP BY~~ ([EDIT]ah ben non, je ne peux pas Mouarf ! [/EDIT]
Merci encore

n.

04/01/2019, 11h01
escartefigue

Citation:

Envoyé par nullos

Je crois comprendre qu'avec INNER JOIN la comparaison des deux valeurs "membre" de "a" et de "b" est impossible si les deux sont nulles et donc par conséquent les résultats où les valeurs des identifiants des deux membres sont nulles (ici les clients sans identifiant) sont exclus.

Ce qu'il faut comprendre c'est que NULL n'est pas une valeur, mais un marqueur d'absence de valeur. Vous n'aurez jamais égalité entre deux colonnes qui sont marquées "NULL" c'est à dire qui n'ont pas de valeur.
Et bien sur c'est vrai avec INNER JOIN, mais aussi OUTER JOIN, WHEREou tout autre clause voulant comparer des colonnes nulles !

Salut à tous.

Citation:

Envoyé par nullos

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) !

Citation:

Envoyé par nullos

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.

Citation:

Envoyé par nullos

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:

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:

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:

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.

@+

05/01/2019, 01h11
nullos

Bonjour,

Merci pour votre intérêt.

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

Citation:

Envoyé par Artemus24

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.