Compter les doublons, avec dates et avec plusieurs Tuple

Bonjour,
bon je débute, mon niveau ... je viens seulement de finir le tuto pour débutant lol et d'écrire ma première requête

j'ai écrit le bout de code suivant mais il ne répond pas comme je le souhaite

Code:

---

1 2 3 4 5 6 7 8 9

select *, count(numtel) as nombre from base where date (EndTime) = date ('2014-06-03') and numtel <> "" and Reason <> "planningclosed" and group in ("8", "10") and sens = "in" group by numtel having count(numtel) > 1

---

Mon tableau est comme ceci

EndTime

numtel

Reason

sens

group

J'aimerai extraire le nombre de numtel identique, en fonction de certain critère
il faut que la valeur dans le champs group soit égale à 8 ou 10.

Donc premièrement est-ce que ma requête est bonne ?
Deuxièmement c'est méga long, je crois que ça vient de la partie date, ai-je possibilité de faire autrement ?
Pour information, ma base est énorme avec pleins de colonne.


Merci d'avance à toute personne m'aidant et surtout je veux bien les explications de mes erreurs car j'apprends ainsi

Bonjour et bienvenue dans le beau monde du SQL :)


Plusieurs remarques :
1/ Il faut éviter les SELECT *, prend l'habitude de bien nommer tous les champs dont tu as besoin.

2/ Ecrite ainsi ta requete est fausse. MySQL te l'accepte mais un moteur plus rigoureux (ou si tu actives l'option de controle strict sur les GROUP BY dans MySQL) te la rejetera. En effet tu fais un GROUP BY numtel mais dans ton SELECT tu as d'autres champs non agrégés (tous les champs induit par l'* du SELECT).
Du coup que va faire MySQL ? Effectivement tu vas avoir une ligne par numtel, avec le nombre de lignes pour chaque numtel. Mais les valeurs pour les autres champs, MySQL va faire un peu ce qu'il veut car tu n'as pas spécifié si tu voulais un MIN, un MAX, une moyenne ou au contraire si tu voulais conserver toutes les valeurs et donc avoir plusieurs totaux de numtel.

3/ Pour la durée, sans informations complémentaires (index, nombre de lignes pour commencer) ça va être difficile. Mais ton égalité de date est effectivement surprenante. Si EndTime est de type DATE tu n'as aucune raison d'appliquer une fonction date dessus. Cela notamment pourrais empecher un index d'être utilisé.

4/ Tu indiques que ta base est énorme avec pleins de colonnes. Le nombre de colonnes aura peu d'influence dans ton cas (enfin une fois que tu auras réglé le problème de l'* et du GROUP BY) c'est plus le nombre de lignes qui va importer. Et à mon avis, si tu débutes tu dois penser que ta base est énorme mais je doute que ce soit réellement le cas. Pour moi une base énorme c'est au dela du milliard de lignes, avec une base à plusieurs To de données.

1) Changement fait ^^
2)
Alors oui, je viens de comprendre et c'est surement ça qui rend ma requête très longue, pourtant le résultat qu'il me renvoie est exactement celui que j'attends. Mais j'aimerai faire un truc plus propre.
Comment je lui dis
je veux une ligne par numtel avec le nombre à côté, et pour les autres champs tu m'inscris les données de la première fois que l’occurrence apparait donc concrètement lorsque le endtime est le plus bas

3) Ma base fait 375620 lignes, pour les index j'ai cherché ce que ça veut dire, mais j'ai trouvé index par clef primaire, id, par starttime, numtel et callref. je viens d'enlever la fonction date cela fonctionne, et j'ai gagné un peu de temps mais ma requête prend 6min c'est trop long

Voici mon nouveau code

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

Select CallRef, StartTime, EndTime, CustInfo, UserQualif, FirstServiceName, FirstDistribServiceName, FirstAgentName, FirstAgentGroupName, Reason, count(id) as nombre from base where EndTime >= '2014-06-04' And EndTime < '2014-06-05' and Direction = 'in' and Reason != 'planningclosed' and CustInfo <> "" and (FirstAgentGroupId = '8' or FirstAgentGroupId = '10') group by CustInfo having nombre > 1 order by nombre desc;

---

Bon numtel est devenu Custinfo, j'ai pris la donnée comme sur ma base sans changer le nom.

4) Ma base est une base de production mon It étant débordé, je me suis dit je me lance dans l'apprentissage des bases de Mysql donc ma base est grande

Pour le point 2), si ta requete te renvoie exactement ce que tu veux c'est un coup de chance et tu n'as aucune garantie que ce soit le cas pour l'ensemble de tes telnum.
Comme j'ai dit précédemment, mysql va faire ce qu'il peut/veut pour les champs non agrégés non présents dans le GROUP BY.
Dans tous les cas tu n'auras jamais les valeurs correspondant au EndTime le plus bas de façon systématique (ou alors sur un gros coup de chance parceque MySQL décide de prendre la première valeur trouvée pour chaque champ et que tes lignes en base sont insérées dans l'ordre du EndTime et que .... Bref c'est pas bon !)

Le problème étant que pour faire ce que tu souhaites, c'est plus compliqué. De façon naive, ce que tu voudrais c'est quelque chose du genre :

Code:

---

1 2 3 4 5

SELECT CustInfo, count(1), min(EndTime), callRef where EndTime = min(EndTime) .... FROM ...

---

En Oracle tu pourrais t'en sortir avec des fonctions de fenêtrage. Malheureusement elles n'existent pas en MySQL. Il va donc te falloir utiliser une sous-requete.

Code:

---

1 2 3 4 5 6 7 8 9 10

SELECT b.callRef, b.StartTime, b.CustInfo, min_time.nombre, b.EndTime ... FROM base b JOIN ( SELECT CustInfo, count(1) nombre, min(EndTime) EndTime FROM base WHERE ... GROUP BY CustInfo HAVING nombre > 1 ) min_time ON b.CustInfo = min_time.CustInfo AND b.EndTime = min_time.EndTime ORDER BY nombre DESC;

---

Il y a peut être (probablement) des erreurs dans ma requête mais l'idée est là.

Pour le date que tu as retiré, il ne fallait pas le retirer des deux cotés.
La en mettant [INNERCODE]EndTime >= '2014-06-04' [/INNERCODE] tu laisses MySQL faire la transformation de ta chaine en date. Le résultat pourra dépendre de la configuration de MySQL. Tu dois donc bien expliciter la conversion coté chaine en spécifiant le format de cette dernière.

Enfin pour les perfs il va te falloir un index sur CustInfo,EndTime pour ta jointure avec ta sous-requete.
Et probablement un autre soit sur EndTime, soit sur FirstAgentGroupId ou les deux en fonction de si c'est suffisement discriminant.

Alors grand merci pour le truc des sous-requête, je viens de lire pour voir comment cela fonctionnait ^^

mon code est presque parfait, juste un message d'erreur code 1054 unknown colum 'id' in 'from clause'

J'ai désactivé les endroits où il y avait l'id la requête fonctionne mais j'aimerai trouvé l'erreur et la requête prend 480secondes c'est trop, je ne sais pas comment l'alléger.
Et pour la date je n'ai pas compris ce qu'il fallait faire pour que ce soit mieux.

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

SELECT base.CallRef, base.StartTime, base.EndTime, base.CustInfo, base.UserQualif, base.FirstServiceName, base.FirstDistribServiceName, base.FirstAgentName, base.FirstAgentGroupName, cool.nombre FROM base inner join (SELECT CustInfo, id/*min(id)*/, count(id) AS nombre FROM base WHERE startTime >= '2014-05-01' AND starttime < '2014-06-01' AND Direction = 'in' AND Reason != 'planningclosed' AND CustInfo <> '' AND FirstAgentGroupId in ('11' , '20', '9', '10', '8', '7') -- and id = min(id) GROUP BY CustInfo HAVING nombre > 1) as cool USING (Id) -- order by id desc

---

Ta condition id = min(id) que tu as mise en commentaire est incorrecte.
Tu ne peux pas mettre de fonction d'aggrégat dans une clause WHERE. Si tu enlèves juste cette condition et que tu remets min(id) au lieu de id dans le select ça devrait fonctionner.

Ensuite tu fais ta jointure sur le champ id. Es-tu sur que ton min(id) correspond à ton EndDate minimal ? Vu que c'est ce que tu avais dis que tu souhaitais comme valeur. Si ce n'est pas le cas il vaut mieux faire la jointure sur CustInfo, EndDate comme je l'avais indiqué.


Pour la date tu dois avoir quelque chose du genre :

Code:

---

1 2	startTime >= STR_TO_DATE('2014-05-01', '%Y-%m-%d') AND starttime < STR_TO_DATE('2014-06-01', '%Y-%m-%d')

---

Comme cela tu es sur que ta date sera bien interpretée par MySQL. Sinon comment MySQL fait la différence entre 2014-05-01 et 2014-01-05 par exemple ? Qui est le jour et le mois dans ce cas ?

Pour tes perfs, comme je l'ai déjà indiqué, il faut que tu postionnes les bon indexs.
Si tu restes sur une jointure sur id, il te faut donc un index sur id (qui dois déjà probablement exister en tant que clef primaire) et après un autre bien pensé sur les champs de ta clause WHERE en fonction de leur discriminalité.

J'ai bien regardé un peu partout et avec tes conseils,je suis enfin arriver à avoir un truc "propre".
la requête si dessous fonctionne... Juste sur les dates j'ai tenté comme toi et d'autre chose rien ne semblent vraiment accélérer cela.
J'ai utilisé des sous-requête (merci) ça accélère vraiment la requête et j'ai ciblé uniquement des champs indexés.
L'id le plus petit correspond bien à mon starttime ou endtime le plus bas, donc parfait
Mon résultat:

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

SELECT base.CallRef, base.StartTime, base.EndTime, base.CustInfo, base.UserQualif, base.FirstServiceName, base.FirstDistribServiceName, base.FirstAgentName, base.FirstAgentGroupName, cool2.nombre, id FROM base inner join (select min(id) as ID, count(cool1.id) as nombre from (SELECT CustInfo, Id FROM base WHERE StartTime between '2014-05-01 00:00:00' AND '2014-06-01 00:00:00' AND Direction = 'in' AND Reason != 'planningclosed' AND FirstAgentGroupId in ('11' , '20', '9', '10', '8', '7')) as cool1 group by CustInfo having nombre > 1) as cool2 USING (id) order by nombre desc

---

Ma prochaine étape extraire du XML d'une cellule ...