requete pour compter par date

**smariteau** · 05/01/2006, 02h10

Bonjour,

J utilise une base de donnees MySQL 4.1

J aimerais creer un graph ds le temps en classant les SMS envoyes par jour les 30 derniers jours.
J ai donc ma table (ao_message) ds laquelle j ai une colonne (timestamp) avec les dates comme par example: 2005-04-27 08:54:55
Chaque ligne represente un SMS envoye.

Ma requete est tres simple:

Code :

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

SELECT timestamp, count(*) FROM ao_message WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= timestamp group by timestamp

J aimerais ne pas prendre en compte le temps hh:mm:ss.

Merci pour toute aide

Xo · 05/01/2006, 08h38

Salut, et bienvenue sur ce forum,

Avec une fonction appropriée, ça ne devrait pas poser problème de tronquer ton champ en ne gardant que les infos utiles. La liste des fonctions dipso est ici : http://sql.developpez.com/sqlaz/fonctions/#L1.7

**smariteau** · 05/01/2006, 18h12

bonjour,

j ai ecris la requete en m aidant de cette doc:
http://dev.mysql.com/doc/refman/4.1/...functions.html

Bon je n avais qu a la copier. Ensuite je voudrais inserer la fonction DATE_FORMAT ds ma requete. C est le mieux que j ai trouve:

Code :

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

select InfoDeLaTable where DATE_FORMAT(LaTable.date,%c %e) = ('10 25')

Par contre, ds les examples que j ai vu la date doit etre toujours precise avec la fonction DATE_FORMAT ce qui ne me conviens pas. Ca devrait etre possible de combiner ces 2 parties mais je n'ai pas la solution. Ou alors je devrais faire une requete tout autre mais je seche.
J en ai lu des docs, mais la pratique c est tout autre!
Puis je obtenir un resultat en inserant ces 2 parties ensemble?

Toute indication est la bienvenue,
Merci

Xo · 05/01/2006, 18h44

Au vu de ta doc, si j'ai bien compris (je n'ai pas mySQL), la syntaxe suivante est correcte :

Code :

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

select InfoDeLaTable where DATE_FORMAT(LaDate, %d/%m/%Y) = '25/10/2005'

Si oui, est-ce que la requête suivante fonctionne ? Te convient-elle ?

Code :

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

1
2
3
4
SELECT DATE_FORMAT(timestamp, %d/%m/%Y), count(*) AS maDate
  FROM ao_message 
 WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= timestamp 
 GROUP BY DATE_FORMAT(timestamp, %d/%m/%Y)

**smariteau** · 06/01/2006, 01h25

Re,

La requete me donne bien le resultat que je souhaite:

Code :

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

SELECT Date_Format(timestamp,'%D %b') AS tempDate,count(*) FROM ao_messagelog WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= timestamp GROUP BY tempdate

J avais bien essaye des requetes du meme gout mais ce n etait pas encore ca.

Merci beaucoup

J avais oublie de preciser ds le post precedent qu il serait interessant pour ma requete de me donner egalement les jours ou aucun message n a ete envoye pour faire mon garaph. Je vais bucher dessus.

**pcaboche** · 06/01/2006, 02h41

Envoyé par smariteau

J avais oublie de preciser ds le post precedent qu il serait interessant pour ma requete de me donner egalement les jours ou aucun message n a ete envoye pour faire mon graph. Je vais bucher dessus.

Pour faire cela, il te faut une table "dates" (ou "jour", ou ce que tu veux), dans laquelle tu mets toutes les dates qui t'intéressent. Ensuite tu fait un LEFT OUTER JOIN entre "dates" et "ao_messagelog" (renvoie NULL si pas de message posté ce jour là), puis après tu fais un GROUP BY par date enfin tu fais un COUNT.

Le plus embêtant, c'est qu'il te faut remplir la table "dates".

Deuxième solution: tu gères les dates manquantes dans ton script qui te génère le graph.

**pcaboche** · 07/01/2006, 02h26

J'ai fait un petit script pour créer rapidement un calendrier, vous pouvez le copier-coller à votre guise.

Au final, la table "dates" contient toutes les dates de l'année 2006.

Ce code a été écrit de manière à marcher même sur des versions anciennes de MySQL (testé sur une 4.0):

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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
DROP TABLE IF EXISTS dd ;
DROP TABLE IF EXISTS mm ;
DROP TABLE IF EXISTS yyyy ;
DROP TABLE IF EXISTS dates ;
 
 
CREATE TABLE dd (
 dd CHAR(2) NOT NULL,
 PRIMARY KEY (dd)
) ;
 
 
CREATE TABLE mm (
 mm CHAR(2) NOT NULL,
 PRIMARY KEY (mm)
) ;
 
 
 
CREATE TABLE yyyy (
 yyyy CHAR(4) NOT NULL,
 PRIMARY KEY (yyyy)
) ;
 
 
 
 
CREATE TABLE dates (
 day DATE NOT NULL,
 PRIMARY KEY (day)
) ;
 
 
 
 
# Tous les jours possibles
 
INSERT INTO dd VALUES
('01'),
('02'),
('03'),
('04'),
('05'),
('06'),
('07'),
('08'),
('09'),
('10'),
('11'),
('12'),
('13'),
('14'),
('15'),
('16'),
('17'),
('18'),
('19'),
('20'),
('21'),
('22'),
('23'),
('24'),
('25'),
('26'),
('27'),
('28'),
('29'),
('30'),
('31') ;
 
 
 
# Tous les mois possibles
 
INSERT INTO mm VALUES
('01'),
('02'),
('03'),
('04'),
('05'),
('06'),
('07'),
('08'),
('09'),
('10'),
('11'),
('12');
 
 
 
# Les années considérées (ici, seulement 2006)
 
INSERT INTO yyyy VALUES
('2006');
 
 
 
 
# On créé notre calendrier
 
INSERT IGNORE INTO dates
SELECT (CONCAT(yyyy.yyyy, "-", mm.mm, "-", dd.dd) + INTERVAL 0 DAY) AS jour
FROM dd, mm, yyyy ;

Explications de la dernière requête:
- on génère des chaines de caractères (CONCAT) correspondant aux dates du calendrier (produit cartésien)
- le "+ INTERVAL 0 DAY" permet de convertir la chaîne en date. Si une date n'est pas valide, MySQL l'adapte au mois suivant (ex: "2006-02-31" est traduit en "2006-03-03"). Donc on se retrouve avec plusieurs fois les mêmes dates
- le "INSERT IGNORE INTO" insère sans tenir compte des doublons

***

Maintenant qu'on a notre joli calendrier (6.945 octets en base: 1 825 pour les données, 5 120 pour les index), pour avoir le nombre de messages par jour (y compris les jours où il n'y a pas eu de messages), on peut faire (code non testé):

Code :

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

1
2
3
4
5
6
7
8
SELECT dates.day, count(*)
 
FROM dates
LEFT OUTER JOIN ao_messagelog ON DATE(ao_messagelog.timestamp)=dates.day
 
WHERE dates.day BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND CURDATE()
 
GROUP BY dates.day

**pcaboche** · 07/01/2006, 11h44

J'ai trouvé une méthode beaucoup plus simple (la nuit porte conseil): tu crées une table 'nbr' qui contient un champ 'nbr' contenant les nombres de 0 à 30. Ensuite, tu fais:

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT 
  DATE_SUB(CURDATE(), INTERVAL nbr.nbr DAY) AS jour,
  COUNT(*) AS nombreMessages
 
FROM nbr
LEFT OUTER JOIN ao_messagelog
ON DATE(ao_messagelog.timestamp)=jour
 
GROUP BY jour
ORDER BY jour

**smariteau** · 10/01/2006, 00h10

bonjour,

Merci pour ces indications. Je commence tout juste a programmer MySQL (actuellement je commence a programmer tout simplement!).

Juste apres avoir poster mon dernier message j avais pense que je devrais pouvoir ajouter mes dates manquantes en dehors des requetes.
De plus, l'inconvenient est que je ne peux pas modifier la base de donnees moi meme et je ne suis pas encore apte a le faire.
Neanmoins vos idees m aideront tot ou tard,
Merci une fois encore.
A+

requete pour compter par date

Requêtes MySQL

Discussions similaires

Partager

Partager