Discussion :

[ArcTan]

Bonjour,

J’ai une base avec deux tables :

La table utilisateur avec Nom, prénom et localisation,

La table Commune avec les noms des villes sous NCCENR.

Je voudrais compter le nombre de fois qu’une ville est associée à un utilisateur.

NCCENR COUNT

Paris 12

Nantes 33

...

PS : la localisation est rentrée par l’utilisateur et peut donc contenir des caractères en plus de la ville « from Paris »…

Voici la requête que j'ai réalisée:

Code :

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

1
2
3
4
SELECT commune.NCCENR, COUNT(*)
FROM `BASE`.`refcommune` as commune
left join `BASE`.`utilisateur` as user on user.Localisation like commune.NCCENR
GROUP BY NCCENR;

Mais elle me retourne 1 pour toutes les villes

Je travail sur MySQL Workbench 5.2.47

Merci.

[Exia_]

Bonjour,

Code :

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

1
2
3
4
SELECT commune.NCCENR, COUNT(user.nom)
FROM `BASE`.`refcommune` AS commune
LEFT JOIN `BASE`.`utilisateur` AS user ON user.Localisation LIKE commune.NCCENR
GROUP BY NCCENR;

Il faut préciser la table user pour le count.

[ArcTan]

Merci pour ta réponse Exia93,
Mais cela ne marche toujours pas, j'obtiens maintenant 0 pour chaques villes

[aieeeuuuuu]

Bonjour,

Le LIKE tel que vous le faites revient à un =...

essayez ceci :

Code :

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

1
2
3
4
5
 
SELECT commune.NCCENR, COUNT(user.nom)
FROM `BASE`.`refcommune` AS commune
LEFT JOIN `BASE`.`utilisateur` AS user ON user.Localisation LIKE '%' + commune.NCCENR + '%'
GROUP BY NCCENR;

[Exia_]

La colonne Localisation de la table user est-elle bien une clé étrangère vers la colonne NCCENR de la table Commune ?

[ArcTan]

Bonjour,

Le LIKE tel que vous le faites revient à un =...

essayez ceci :

Code :

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

1
2
3
4
5
 
SELECT commune.NCCENR, COUNT(user.nom)
FROM `BASE`.`refcommune` AS commune
LEFT JOIN `BASE`.`utilisateur` AS user ON user.Localisation LIKE '%' + commune.NCCENR + '%'
GROUP BY NCCENR;

J'ai essayé ta méthode mais cela ne marche pas ,j'obtiens 0 pour chaques villes

[ArcTan]

La colonne Localisation de la table user est-elle bien une clé étrangère vers la colonne NCCENR de la table Commune ?

Je ne peux pas déclarer Localisation comme clé étrangère de NCCENR car Localisation n'est pas une clé primaire

[aieeeuuuuu]

pardon, mauvaise concaténation de ma part pour MySQL:

c'est plutot ceci :

Code :

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

1
2
3
4
5
 
SELECT commune.NCCENR, COUNT(user.nom)
FROM `BASE`.`refcommune` AS commune
LEFT JOIN `BASE`.`utilisateur` AS user ON user.Localisation LIKE CONCAT('%', commune.NCCENR , '%')
GROUP BY NCCENR;

[ericd69]

salut

je ne crois pas que le + concatène, il transforme plutôt le résultat en nombre...
utilise plutôt:

Code sql :

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

concat('%',commune.NCCENR,'%')

dans le like

[ArcTan]

salut

je ne crois pas que le + concatène, il transforme plutôt le résultat en nombre...
utilise plutôt:

Code sql :

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

concat('%',commune.NCCENR,'%')

dans le like

oui c'est ce que j'avais utilisé pour tester la méthode de aieeeuuuuu et le résultat reste le même...

[aieeeuuuuu]

il faudrait fournir un jeu d'essai. La requête me semble bonne !

deux questions tout de même :

- est-ce que les noms sont bien renseignés dans voter base ? ou est-ce qu'ils sont à NULL ?
- quelle est la collation des colonnes user.Localisation et commune.NCCENR ?

[ArcTan]

Oui les noms sont tous renseignés.Je ne comprends pas ce que tu entends par collation entre les colonnes.

Je mets en PJ le code et les fichiers de jeu de données, peut etre que cela aidera.

Merci pour votre aide.

[Exia_]

Chez moi j'obtient bien le résulat voulu seulement vue qu'il y'a 36000 communes et que 13 utilisateurs tu as beaucoup de commune avec 0 utilisateur.

Pour voir si la jointure ce fait bien il suffit de faire la vérification en affichant seulement les commune avec plus d'1 utilisateur:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT commune.NCCENR, COUNT(user.nom)
FROM `refcommune` AS commune
LEFT JOIN `utilisateur` AS user ON user.Localisation = commune.NCCENR
GROUP BY NCCENR
HAVING COUNT(user.nom) > 0
 
-- Ou plus logiquement en utilisant une jointure interne
SELECT commune.NCCENR, COUNT(user.nom)
FROM `refcommune` AS commune
INNER JOIN `utilisateur` AS user ON user.Localisation = commune.NCCENR
GROUP BY NCCENR

De plus j'utilise l'opérateur = au lieu de like vue que je ne fait pas de traitement sur la chaîne de caractère.

[manzeki]

J'ai appliqué ces commandes pour nettoyer les sauts de ligne sur les colonnes Localisation et NCCENR

Code :

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

1
2
3
4
5
6
7
8
 
update `refcommune` set `NCCENR` = replace(`NCCENR` ,'\r','');
update `refcommune` set `NCCENR` = replace(`NCCENR` ,'\n','');
update `refcommune` set `NCCENR` = replace(`NCCENR` ,'\r\n','');
 
update `utilisateur` set `Localisation` = replace(`Localisation` ,'\r','');
update `utilisateur` set `Localisation` = replace(`Localisation` ,'\n','');
update `utilisateur` set `Localisation` = replace(`Localisation` ,'\r\n','');

Changer le COUNT(*) en COUNT(user.nom) supprime les 1 retournés pour chaque ville.
La requête suivante fonctionne bien (environ 3 secondes) :

Code :

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

1
2
3
4
5
6
 
SELECT c.NCCENR, COUNT(r.nom) total
FROM refcommune c
LEFT JOIN utilisateur r ON r.Localisation = c.NCCENR
GROUP BY c.NCCENR
ORDER BY total DESC

J'ai aussi ajouté des index sur les champs NCCENR et Localisation et cela m'a permis de diviser le temps d'exécution en 10. Il sera difficile d'optimiser d'une telle requête. Les meilleures performances sont obtenues avec la requête ci-dessous (environ 0.008 secondes) :

Code :

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

1
2
3
4
5
6
 
SELECT c.NCCENR, COUNT(r.nom) total
FROM refcommune c, utilisateur r
WHERE r.Localisation = c.NCCENR
GROUP BY c.NCCENR
ORDER BY total DESC

[Exia_]

Les deux dernières requêtes que tu donne sont complètement différente la première est une jointure externe tandis que la dernière une interne seulement elle est écrite comme il y'a 20 ans :

Code :

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

1
2
3
4
5
6
-- Ecriture actuelle et plus lisible je trouve
SELECT c.NCCENR, COUNT(r.nom) total
FROM refcommune c
INNER JOIN utilisateur r ON r.Localisation = c.NCCENR
GROUP BY c.NCCENR
ORDER BY total DESC

[ArcTan]

Merci pour votre aide.
D’après ce que vous me dite le problème doit être résolu, cependant en prenant vos solutions j'obtiens des colonnes vides, le problème doit donc venir d'ailleurs.

[Exia_]

Si c'est lorsque vous utilisez la jointure externe c'est qu'il ne doit pas y avoir d'enregistrement dans la table utilisateur pour une commune et donc 0 est retourné.

[ArcTan]

Si c'est lorsque vous utilisez la jointure externe c'est qu'il ne doit pas y avoir d'enregistrement dans la table utilisateur pour une commune et donc 0 est retourné.

non il retourne rien c'est a dire je n'ai même pas la liste des villes affichée.

[Exia_]

Si tu n'a aucun résultat c'est que la clause de jointure ON r.Localisation = c.NCCENR n'est pas valide, que contiennent exactement c'est deux colonnes ?

[ArcTan]

Si tu n'a aucun résultat c'est que la clause de jointure ON r.Localisation = c.NCCENR n'est pas valide, que contiennent exactement c'est deux colonnes ?

Localisation contient le nom de ville renseigné par l'utilisateur et NCCENR contient la liste de toutes les villes de france. J'ai joints les fichiers dans un des messages au dessus.