Calcul des statistiques

Version imprimable

20/02/2009, 13h02
cirvent

Calcul des statistiques

salut à tous,

j'aurai besoin d'info et conseille et aide pour faire un page de stat sur des info stocker dans une db mysql.

en gros j'ai stocker dans une db des interventions diverses j'ai un champ int dans le quel je stock la date exemple de date stocker : 20/02/2009 13:00:00

je ne sais pas si j'ai bien fait !

le problème c'est que j'aimerai faire de stat par date, 3 type de stat:
- par mois (ou l'on voir le nombre intervention par jour).
- par ans (ou l'on voir le nombre intervention par mois).
- depuis le début (ou l'on voir le nombre intervention par mois).

le tous si possible sous le forme d'histogramme.

quelqu'un aurai une idée ?
20/02/2009, 16h15
onet

Salut,

Euh, pas sur d'avoir compris (un extrait de ta base de donnée serait utile!).

Mais déja: un int pour stocker une date -> a banninr! Mysql dispose de fonctions prévue pour (datetime, ou, je conseille, timestamp).

Sinon, pour ton code directement, qu'as-tu déja fait? Car en fait, tu veux simplement effectuer un select, et générer un graphique, c'est ca? Il existe de nombreuses librairie de graphisme (artichaut par exemple).

Onet

ok voici la structure de la table en question:

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
DROP TABLE IF EXISTS `historique`;
CREATE TABLE `historique` (
  `id_historique` int(10) NOT NULL auto_increment,
  `type_historique` varchar(255) NOT NULL default 'Vide',
  `installateur` varchar(255) NOT NULL default 'Vide',
  `date_installation` varchar(255) NOT NULL default 'Vide',
  `modele_historique` varchar(255) NOT NULL default 'Vide',
  `sn_historique` varchar(255) NOT NULL default 'Vide',
  `utilisateur` varchar(255) NOT NULL default 'Vide',
  `numero_commande_historique` varchar(255) NOT NULL default 'Vide',
  `lieux_historique` varchar(255) NOT NULL default 'Vide',
  `telephone_type_ligne` varchar(255) NOT NULL default 'Vide',
  `telephone_numero` varchar(255) NOT NULL default 'Vide',
  `telephone_type_historique` varchar(255) NOT NULL default 'Vide',
  `ref_historique` varchar(255) NOT NULL default 'Vide',
  `designation_historique` varchar(255) NOT NULL default 'Vide',
  `marque_historique` varchar(255) NOT NULL default 'Vide',
  `ecran_taille_historique` varchar(255) NOT NULL default 'Vide',
  `ecran_type_historique` varchar(255) NOT NULL default 'Vide',
  `cable_longueur_historique` varchar(255) NOT NULL default 'Vide',
  `cable_type_historique` varchar(255) NOT NULL default 'Vide',
  `toner_imprimantes_historique` varchar(255) NOT NULL default 'Vide',
  `toner_derniere_commande_historique` varchar(255) NOT NULL default 'Vide',
  `uc_cpu_historique` varchar(255) NOT NULL default 'Vide',
  `uc_ram_historique` varchar(255) NOT NULL default 'Vide',
  `uc_hdd_historique` varchar(255) NOT NULL default 'Vide',
  `type_imprimante_historique` varchar(255) NOT NULL default 'Vide',
  PRIMARY KEY  (`id_historique`)
) ENGINE=MyISAM AUTO_INCREMENT=210 DEFAULT CHARSET=latin1;

et voici un enregistrement:

Code:

INSERT INTO `historique` VALUES ('67', 'UC', 'vincent.ch', '10/12/2008 17:00:00', 'Optiplex 740', 'XXXX24J', 'utilisateur', 'XXXXXXX', 'Intendance: Pôle Maison', 'Vide', 'Vide', 'Vide', 'Optiplex 740', 'Optiplex 740', 'DELL', 'Vide', 'Vide', 'Vide', 'Vide', 'Vide', 'Vide', '2', '2048', '160', 'Vide');

20/02/2009, 16h53
nico1214

Tu t'es pas embêté, t'as tout mis en varchar(255) ! :-D

Le plus simple pour faire des stats, c'est de séparer jour/mois/année (voire trimestre/semestre si t'es motivé) dans ta base de données.

Comme ça tu pourra faire des select différents.

exemple pour compter les enregistrements sur un jour précis (aujourd'hui) :

Code:

SELECT COUNT(*) FROM ma_table WHERE jour=20 AND mois=02 AND annee=2008

exemple pour compter les enregistrements sur un mois donné (ce mois-ci)

Code:

SELECT COUNT(*) FROM ma_table WHERE mois=02 AND annee=2008

etc.. etc... en utilisant des autres fonctions analytiques sur des champs en fonction de ce que tu veux faire
20/02/2009, 17h21
onet

Citation:

Envoyé par nico1214

Tu t'es pas embêté, t'as tout mis en varchar(255) ! :-D

Le plus simple pour faire des stats, c'est de séparer jour/mois/année (voire trimestre/semestre si t'es motivé) dans ta base de données.

Comme ça tu pourra faire des select différents.

exemple pour compter les enregistrements sur un jour précis (aujourd'hui) :

Code:

SELECT COUNT(*) FROM ma_table WHERE jour=20 AND mois=02 AND annee=2008

exemple pour compter les enregistrements sur un mois donné (ce mois-ci)

Code:

SELECT COUNT(*) FROM ma_table WHERE mois=02 AND annee=2008

etc.. etc... en utilisant des autres fonctions analytiques sur des champs en fonction de ce que tu veux faire

Surtout pas! C'est une horeur de faire ca! Et c'est plus que faux!

Mysql et PHP savent gérer les dates, tu sais? Tant avec les valeurs date, datetime ou timestamp, pour ne citer que les 3 plus courantes. Et avec cela, tu fais tout les tests que tu veux! (php dispose de time, mktime, date, etc...).

Onet
P.S: je n'ai pas le temps dans l'immédiat pour te donner une réponse a ta question d'avant. je reviendrais plus tard. Désolé).
20/02/2009, 17h31
nico1214

Citation:

Envoyé par onet

Surtout pas! C'est une horeur de faire ca! Et c'est plus que faux!

Alors là il va falloir me convaincre Onet :-D Surtout en terme de performance...
Tu as déjà fait des infocentres ? Parce que c'est bien d'un infocentre dont on parle, puisqu'il faut en sortir des métadonnées. Les périodes y sont indiquées dans une table séparée (table de référence) et les dates découpées pour ne pas, en plus, alourdir les requêtes avec des calculs de dates.
Mettre une date dans un champs datetime c'est bien quand il s'agit de sélectionner puis d'afficher, pas pour faire des calculs par période...
20/02/2009, 19h41
sabotage

Citation:

ettre une date dans un champs datetime c'est bien quand il s'agit de sélectionner puis d'afficher, pas pour faire des calculs par période...

Je suis perplexe par rapport a ce que tu proposes.
Comment ferrais-tu un resultat entre le 6 février et le 4 mars 2008 par exemple ?
20/02/2009, 23h17
nico1214
Effectivement, je n'ai pas été super clair, désolé.

Donc en admettant qu'on reprenne la table 'historique'. A la place de

Code:

`date_installation` varchar(255) NOT NULL default 'Vide',

on met

Code:

`ref_date_installation` int(5) NOT NULL default 0,

.

A côté, on a une table 'dim_date' contenant :
Code:

1 2 3 4 5 6 7 8 `id_date` INT(5) `date` DATETIME `date_jour` INT(2) `date_mois` INT(2) `date_annee` INT(4) `date_trimestre` INT(1) `date_semestre` INT(1)
A l'insertion d'une ligne dans 'historique' on vérifie si la date du jour est dans 'dim_date'.'date'.
. si elle y est, on récupère 'id_date' qu'on insère dans le champ 'ref_date' de 'historique'
. si elle n'y est pas, on crée la ligne en récupérant/calculant `date_jour`,`date_mois` ,`date_annee` ,`date_trimestre` et `date_semestre`
on récupère l'identifiant 'id_date' que l'on insère dans la ligne de 'historique'.

Pour les stats, en admettant qu'on compte :

. pour un jour :

Code:

SELECT COUNT(*) FROM historique WHERE ref_date = (SELECT id_date FROM dim_date WHERE date = 'date_voulue')

. pour plusieurs jours :

Code:

SELECT COUNT(*) FROM historique WHERE ref_date IN (SELECT id_date FROM dim_date WHERE date IN ('liste_de_dates'))

. pour un mois :

Code:

SELECT COUNT(*) FROM historique WHERE ref_date IN (SELECT id_date FROM dim_date WHERE date_mois = 'mois_voulu')

. pour plusieurs mois :

Code:

SELECT COUNT(*) FROM historique WHERE ref_date IN (SELECT id_date FROM dim_date WHERE date_mois IN ('liste_de_mois'))

etc...

Et je pense que c'est plus efficace que de faire tous les calculs pour toutes les dates de la table 'historique'.
21/02/2009, 10h03
onet

Salut,

Alors, en terme de performance, je pense que d'avoir un champ de time timestamp, et de faire une requete pour rechercher les éléments compris entre timestamp_start et timestamp_end, c'est largement plus rapide que de faire une requete composée de 2 select joint par un IN ! Surtout si la table est optimisée, et que le champs timestamp est placé en index!

Je n'ai effectivement pas l'expérience de ce cas bien précis, mais comme je gère un site de rencontre leader en suisse (plusiuers dizaine de millions de page views par moi, avec plus de 600'000 données membres, et tout ce que cela comporte autour), j'avoue avoir pu tester différents types d'optimisation. Et si il y a bien un type de where que je n'utilise JAMAIS dés le moment qu'on parle d'optimisation, c'est le IN. Il n'y apas grand chose de pire, a part peut etre un like, quand on a besoin de faire des recherches sur un grand nombres de données (la, je te parle de centaine de milliers, voir de millions d'informations, hein!).

Tu parle que c'estplus rapide de faire ce que tu propose que des calcul pour les dates voulues. On estbien d'accord, que dans le cadre d'optimisation, tu ne demande PAS à mysql de faire le calcul? PHP est la pour ca, et est largement plus optimiser pour le faire (de tout facon, tu en as besoin pour traiter tes dates, alors sortir d'une facon ou d'une autre, ca ne change rien!).

En plus, tu dis utiliser une table suplémentaire. Mais si je regarde la relation entre tes 2 tables, tu as une relation 1-1, non? (car j'ai de la peine à voir une information de log avec 2 entrées de date pour un même event!). Hors dans ce cas, tu augmente les risques d'erreurs, et le temps de traitement, alors que ce n'est pas nécessaire.

A moins bien sur que tu utilise de l'innoDB, afin de garder l'intégrité référentiel, mais dans ce cas tu perds a nouveau en performance, et donc pas d'intérêt!

D'autant que dans ton cas, comment tu fais pour trouver tout les événements qui ont eu lieu entre 10h et 14h un jour donné? Tu dois rajouter les champs heures, minutes, secondes. Et si tu fais ca, les chances que tu aie 2 entrées identiques sont apeu prêt égal à 0 (tu en aura bien 1-2, mais ca ne restera pas la majorité!).

Le seul cas ou ta facon pourrait être utile, c'est lorsque tu veux faire des statistiques sur toutes les entrées entre 10h et 12h pour l'année entière. Et meme la, si tu as un champ de type datetime, il te suffit de faire un "where hour(date) >10 and hour(date) < 12".

Sans parler du fait que le traitement, l'affichage, l'insertion etc... des dates est largement plus simple avec les champs correspondant!

Il faut être logique... Si ce n'était pas utile, pourquoi les développeurs se seraient casser la tête à créer des champs pour?

Je le redis, si ta table est bien modélisée, que les index sont mis aux bons endroit, alors l'utilisation des champs correspondant est bien plus efficace!

Onet
21/02/2009, 10h57
sabotage

Citation:

On estbien d'accord, que dans le cadre d'optimisation, tu ne demande PAS à mysql de faire le calcul? PHP est la pour ca, et est largement plus optimiser pour le faire

Je pense qu'on ne peut pas affirmer cela de maniére générale.
De quel calcul parle t-on ?
Par exemple, si je veux faire une somme, je dirais qu'il est plus rapide de demander a mysql de faire la somme, que de recuperer tous les résultats et de les additionner en PHP.
Je ne sais pas qui additionne le plus vite entre PHP et Mysql mais je pense la machinerie SUM de Mysql est plus performante que Recuperation-Parcours-Addition que dois faire PHP.
Ca serait interessant d'avoir de vrais bench la dessus.
21/02/2009, 14h12
onet

Citation:

Envoyé par sabotage

Je pense qu'on ne peut pas affirmer cela de maniére générale.
De quel calcul parle t-on ?
Par exemple, si je veux faire une somme, je dirais qu'il est plus rapide de demander a mysql de faire la somme, que de recuperer tous les résultats et de les additionner en PHP.
Je ne sais pas qui additionne le plus vite entre PHP et Mysql mais je pense la machinerie SUM de Mysql est plus performante que Recuperation-Parcours-Addition que dois faire PHP.
Ca serait interessant d'avoir de vrais bench la dessus.

Oui, je suis d'accord avec toi. En fait, je parlais la d'un cas particulier qui était la transformation de date. Après, il ne me viendrais jamais a l'esprit de faire un select * pour faire un count en php! J'avoue être aller trop vite dansma phrase. Mes dires se reportaient réellement a la situation présente.

Après, une fois de plus, tout dépends de la situation (type de table, quantité de donnée, nombres de demandes simultanées à Mysql, charge duserveur, etc...). L'optimisation Mysql est un métier en soit, ou presque, et on se retrouve souvent dans des situation ou chaque dixième de seconde compte!

Onet
21/02/2009, 23h55
nico1214

Citation:

Envoyé par onet

En plus, tu dis utiliser une table suplémentaire. Mais si je regarde la relation entre tes 2 tables, tu as une relation 1-1, non? (car j'ai de la peine à voir une information de log avec 2 entrées de date pour un même event!). Hors dans ce cas, tu augmente les risques d'erreurs, et le temps de traitement, alors que ce n'est pas nécessaire.

Ah non je pars du principe qu'on a une relation 1-n entre la table des dates et la table historique, sinon effectivement ça ne sert à rien de faire une table supplémentaire.
Il existe plein de cas où plusieurs évènements se passent dans une seule journée. Ex : nombre de commentaires.
Après c'est à cirvent de calculer la volumétrie prévisible de sa table.

Citation:

Envoyé par onet

D'autant que dans ton cas, comment tu fais pour trouver tout les événements qui ont eu lieu entre 10h et 14h un jour donné? Tu dois rajouter les champs heures, minutes, secondes. Et si tu fais ca, les chances que tu aie 2 entrées identiques sont apeu prêt égal à 0 (tu en aura bien 1-2, mais ca ne restera pas la majorité!).

Mon modèle est effectivement déclinable en heures/minutes/secondes mais après c'est l'intérêt de la chose qui en pâtit ! D'autant que, de même pour ce que tu défend, en décortiquant une date au format timestamp pour en obtenir les heures, minutes et secondes est aussi bien plus lourd et a peu d'intérêt.

Au point où on en est, j'en arrive a avoir envie de comparer ! :-D
cirvent, tu sais ce qu'il te reste à faire ! (D'ailleurs : où es-tu cirvent, qu'en penses-tu ?)
22/02/2009, 11h19
cirvent

salut a tous,

je viens de lire tous se que vous avez écrit et je je ne sai pas trop se que je doit choisir !!

la table historique sert a garde un historique du matériel installé, j'y stock les installation de uc, ecran, telephone ..., j'ai environ 20 entrées par jours.

Le serveur qui hébergé ce site et une Grosse machine qui à pour rôle primaire un service de déploiement WDS qui sert peu et ou on stock des fichier ISO.

d'après vous quelle serait la meilleur solution ?
22/02/2009, 15h26
cirvent

j'ai trouver une solution qui semble pas mal !

je vais passer le champs de date en timestamp

puis pour met requete d se type:

SELECT COUNT(*) AS total FROM historique WHERE DAY(date_installation) = 22 AND YEAR(date_installation) = 2009

vous en pensez quoi ?
22/02/2009, 21h08
nico1214

qu
22/02/2009, 23h21
cirvent

heu ........ tu a du oublier le reste ?