Discussion :

[Erakis]

Bonjours à tous,

Je désire faire un graphique de 200 points sur un interval de 1 mois pour une date données. Comme par exemple :

200 points sur un interval de un mois entre les date 2008-12-01 et 2008-12-31.

Donc, cela consistera à faire 200 moyennes de sous intervales de l'intervale principale.

Voici la table en question "SensorsValues"

Code :

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

1
2
3
4
5
6
7
 
DROP TABLE IF EXISTS `hydrolink`.`sensorsvalues`;
CREATE TABLE  `hydrolink`.`sensorsvalues` (
  `sv_Date` datetime NOT NULL ,
  `sv_Value` float(7,2) DEFAULT NULL,
  PRIMARY KEY (`sv_Date`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Représentation de la table "SensorsValues" avec des données fictives

Code :

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

1
2
3
4
5
6
7
sv_Date                      sv_Value
2008-01-01 00:00:00           10.20
2008-01-01 00:05:00           15.25
2008-01-01 00:10:00            8.30
...                            ...
2009-02-04 04:30:00           12.67

Le résultat de la requête devra contenir 200 points en tout.

Cette requête doit aussi permettre de varier le nombre de point ainsi que la date de l'intervale.

Donc on pourrait aussi demander un interval de 800 points entre la date suivante : 2008-01-01 et 2009-05-31.

Je ne sais pas trop par où commencer... quelqu'un aurait une idée ?

Merci

[Maljuna Kris]

Saluton,
Tu as l'air de savoir exactement ce que tu veux obtenir, par contre tu sembles avoir quelques difficultés à nous l'illustrer.
Les notions de points et d'intervalle, dont je pressens qu'elles devraient impacter les clauses WHERE et GROUP BY d'une requête, me demeurent assez improbables à formaliser concrètement.

[CinePhil]

Je suis un peu dans le même état que Maljuna Kris face à cette demande.

Ta table semble contenir plusieurs valeurs par jour mais tous les jours ont-ils au moins une valeur ?

Si ce n'est pas le cas, et si tu veux représenter des moyennes pour chaque jour de ton intervalle, tu dois joindre ta table à une table de calendrier, comme expliqué dans le tutoriel de SQLPro sur le temps, sa mesure et son calcul.

un interval de un mois entre les date 2008-12-01 et 2008-12-31.

Pour avoir toutes les valeurs entre ces deux dates (ou tout autre intervalle), il fuat faire la requête suivante :

Code :

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

1
2
3
4
SELECT DATE(sv_Date), sv_Value
FROM hydrolink.sensorsvalues
WHERE DATE(sv_Date) BETWEEN '2008-12-01' AND '2008-12-31'
ORDER BY DATE(sv_Date)

Pour avoir la moyenne par jour (mais ça ne fera évidemment au maximum que 31 moyennes) :

Code :

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

1
2
3
4
5
SELECT DATE(sv_Date), AVG(sv_Value) AS Moyenne
FROM hydrolink.sensorsvalues
WHERE DATE(sv_Date) BETWEEN '2009-12-01' AND '2009-12-31'
GROUP BY DATE(sv_Date)
ORDER BY DATE(sv_Date)

Notre difficulté de compréhension vient de cette partie de ton besoin :

Donc, cela consistera à faire 200 moyennes de sous intervales de l'intervale principale.

Que sont ces 200 sous intervalles ?

Faut-il prendre toutes les valeurs de l'intervalle principal et les regrouper en 200 paquets égaux en nombre de valeurs ?

Si c'est le cas, j'essaierai un truc dans le genre :

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
SET @current_row = 0;
 
CREATE TABLE hydrolink.tmp
SELECT @current_row := @current_row + 1 AS Num_ligne, DATE(sv_Date) AS sv_Date, sv_Value
FROM hydrolink.sensorsvalues
WHERE DATE(sv_Date) BETWEEN '2008-12-01' AND '2008-12-31'
ORDER BY DATE(sv_Date);
 
SET @intervalle = (
  SELECT FLOOR(COUNT(*) / 200)
  FROM hydrolink.tmp
);
 
SELECT CEILING(Num_ligne / @intervalle) AS Intervalle, AVG(sv_Value) AS Moyenne
FROM hydrolink.tmp
GROUP BY CEILING(Num_ligne / @intervalle)
ORDER BY CEILING(Num_ligne / @intervalle);

La première requête crée une table ordonnée ne contenant que les valeurs de l'intervalle de dates choisi. A vérifier quand même qu'aux numéros de lignes dans l'ordre correspondent bien des dates dans l'ordre.

La seconde permet de calculer la population de chaque sous-intervalle.

La troisième devrait calculer les 200 moyennes pour les 200 sous-intervalles.

[Erakis]

Ta table semble contenir plusieurs valeurs par jour mais tous les jours ont-ils au moins une valeur ?

Il est possible qu'il n'y est pas de données pour certainnes journées.

Que sont ces 200 sous intervalles ?

J’ai une table remplie de valeurs associées à un temps d’acquisition.

Je veux générer un graphique d’amplitude des valeurs en fonction du temps.

Donc, une fonction permettant de demander un nombre X de points sur une période de temps déterminée par deux dates (min/max).

Pour ce faire, je divise la période de temps principal en X sous périodes consécutives qui, mis tous les unes après les autres, couvriront ma période principale.

Dans chacune de ces sous périodes, je demande à mySQL de générer la moyenne de toutes les valeurs qui sont comprises dans cette sous période.

Les résultats des moyennes de chacune de ces sous périodes sont les points du graphique.

Pour l’instant, je suis obligé de faire autant de requête mySQL que le nombre de points que le client me demande sur le graphique, par conséquent ce n’est pas l'idéal en terme de performances.

Ce que je cherche à faire, c’est de faire ce paquet de moyennes consécutives dans une seule requête qui retournerait un result-set de la dimension du nombre de points requis.

Merci pour votre aide et désolé de m'être mal exprimé au départ

[CinePhil]

OK. La problématique change en effet ...

Donc il faut diviser la période choisie (dans ton exemple, décembre 2008) en x intervalles de temps.

J'arrive à la procédure ci-dessous :

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
-- Variables à entrer
SET @debut_periode = '2008-12-01';
SET @fin_periode = '2008-12-31';
SET @nb_intervalles = 200;
 
-- Durée du point en secondes
SET @duree_point = (
  SELECT ((DATEDIFF(@fin_periode, @debut_periode) + 1) * 86400) / @nb_intervalles
);
 
-- Sélection des valeurs et ordonnancement
CREATE TABLE hydrolink.tmp
SELECT sv_Date, sv_Value
FROM hydrolink.sensorsvalues
WHERE sv_Date BETWEEN @debut_periode AND @fin_periode
ORDER BY sv_Date;
 
-- Calcul et regroupement par point
SELECT CEILING(TIME_TO_SEC(TIMEDIFF(sv_Date, @debut_periode)) / @duree_point) AS Point, 
  AVG(sv_Value) AS Moyenne
FROM hydrolink.tmp
GROUP BY CEILING(TIME_TO_SEC(TIMEDIFF(sv_Date, @debut_periode)) / @duree_point);

Explication
1)

Code :

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

TIMEDIFF(sv_Date, @debut_periode

Donne la différence, exprimée en hhh:mm:ss, entre la sv_date et la date choisie comme début de période.

Exemple :

Code :

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

SELECT TIMEDIFF('2008-12-03 00:00:00', '2008-12-01 00:00:00')

==> 48:00:00

2)

Code :

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

TIME_TO_SEC(TIMEDIFF(sv_Date, @debut_periode))

Convertit le calcul précédent en nombre de secondes.

Exemple :

Code :

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

SELECT TIME_TO_SEC(TIMEDIFF('2008-12-03 00:00:00', '2008-12-01 00:00:00'))

==> 172800
48 heures * 3600 secondes = 172 800 secondes.

3)

Code :

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

CEILING(TIME_TO_SEC(TIMEDIFF(sv_Date, @debut_periode)) / @duree_point)

On divise le résultat précédent par la durée du point et on arrondit à l'unité supérieure pour démarrer au point n° 1.

Pour changer les paramètres de date de début et de fin et de nombre de points, il suffit de changer les valeurs des variables en début de script.

A essayer.

[Erakis]

J'ai essayé votre requête, mais malhereusement ce n'est pas ce que j'ai besion.

Ici, vous retournez une seule valeur qui consiste en une moyenne. Ce que j'ai besion, c'est une liste des moyennes de chacune de ces sous périodes.

Allons-y avec un exemple concret :

Dans cette exemple, nous récupérons les valeurs des capteurs avec un intervale de 2 jours. Donc, du (1 janvier 2009 à 00:00:00 au 2 janvier 2009 23:59:00). Voici la requête qui effectue cette récupération :

Code :

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

1
2
3
4
SELECT sv_Date, sv_Value
FROM hydrolink.sensorsvalues
WHERE sv_Date BETWEEN '2009-01-01 00:00:00' AND '2009-01-02 23:59:59'
ORDER BY sv_Date;

Supposons que la table SensorsValues contienne des valeurs à toutes les 5 minutes et qu'il n'y est pas de troue. La requête précédante retounerait 576 valeurs, car (48h * 12 valeurs par heures) .

Pour l'exemple, on demande une précision sur 12 points.

Comme résultat la requête devrait me retourner 12 points calculé de la façon suivante :

Code :

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

1
2
3
4
5
6
 
2009-01-01 04:00:00   Moyenne des valeurs entre (00:00:00 et 04:00:00)
2009-01-01 08:00:00   Moyenne des valeurs entre (04:00:00 et 08:00:00)
2009-01-01 12:00:00   Moyenne des valeurs entre (08:00:00 et 12:00:00)
...
2009-01-02 20:00:00   Moyenne des valeurs entre (20:00:00 et 23:59:59)

Biensûr dans cette exemple tout arrive pile. Dans le cas réel, il se peut qu'il est y des troues dans la table SensorsValues, où que je demande 800 points pour un bloc de 4 heures. Ce qui donnerait 0.005 valeurs par heures.

J'espère que c'est un peu plus claire ?

[CinePhil]

J'ai essayé votre requête, mais malhereusement ce n'est pas ce que j'ai besion.

Ici, vous retournez une seule valeur qui consiste en une moyenne. Ce que j'ai besion, c'est une liste des moyennes de chacune de ces sous périodes.

Comment ça une seule valeur ?
Il devrait y avoir une moyenne par point !
Par contre mes points sont numérotés de 1 à X et nom pas selon leur heure de fin.

Allons-y avec un exemple concret :

Dans cette exemple, nous récupérons les valeurs des capteurs avec un intervale de 2 jours. Donc, du (1 janvier 2009 à 00:00:00 au 2 janvier 2009 23:59:00). Voici la requête qui effectue cette récupération :

Code :

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

1
2
3
4
SELECT sv_Date, sv_Value
FROM hydrolink.sensorsvalues
WHERE sv_Date BETWEEN '2009-01-01 00:00:00' AND '2009-01-02 23:59:59'
ORDER BY sv_Date;

Supposons que la table SensorsValues contienne des valeurs à toutes les 5 minutes et qu'il n'y est pas de troue. La requête précédante retounerait 576 valeurs, car (48h * 12 valeurs par heures) .

Pour l'exemple, on demande une précision sur 12 points.

Comme résultat la requête devrait me retourner 12 points calculé de la façon suivante :

Code :

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

1
2
3
4
5
6
 
2009-01-01 04:00:00   Moyenne des valeurs entre (00:00:00 et 04:00:00)
2009-01-01 08:00:00   Moyenne des valeurs entre (04:00:00 et 08:00:00)
2009-01-01 12:00:00   Moyenne des valeurs entre (08:00:00 et 12:00:00)
...
2009-01-02 20:00:00   Moyenne des valeurs entre (20:00:00 et 23:59:59)

Il me semblait bien avoir compris quelque chose dans le genre et avoir donné une solution possible.
Je serais curieux de voir un exemple de résultat donné par ma procédure.

Biensûr dans cette exemple tout arrive pile. Dans le cas réel, il se peut qu'il est y des troues dans la table SensorsValues, où que je demande 800 points pour un bloc de 4 heures. Ce qui donnerait 0.005 valeurs par heures.

Euh... comment faire une moyenne d'une population inférieure à 1 ?

[Erakis]

Voici en pièce jointe HydroLink_Developpez.zip une partie de ma base de données avec la table "SensorsValues" ainsi que des valeurs simulées.

Après avoir "importé" la base de données test dans MySQL, vous n'aurez qu'à appeler la procédure stockée.

Code :

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

CALL Test()

Cette procédure stockée est en fait la solution que vous m'avez fait par sur ce forum.

Vous constaterez que pour 2 jours avec une précision de 200 points sur un capteur précis, j'obtient UNE SEULE valeur comme résultat final.

Merci beaucoup pour votre aide, c'est apprécié.

[CinePhil]

J'ai trouvé une erreur.
Il faut entrer les variables @debut_periode et @fin_periode en format TIMESTAMP sinon la fonction TMEDIFF renvoie NULL.

Donc en début de procédure, faire ceci :

Code :

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

1
2
  SET @debut_periode = '2009-11-25 00:00:00';
  SET @fin_periode = '2009-12-27 00:00:00';

Par contre apparemment, le résultat commence au point 4. Il doit y avoir encore des ajustements à faire, mais l'idée générale fonctionne.

Pour débugguer ce genre de truc un peu complexe, on réduit au minimum, on contrôle le résultat et on complexifie petit à petit la requête.
Ainsi, face à la dernière requête :

Code :

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

1
2
3
4
SELECT CEILING(TIME_TO_SEC(TIMEDIFF(sv_Date, @debut_periode)) / @duree_point) AS Point,
         AVG(sv_Value) AS Moyenne
  FROM hydrolink_developpez.tmp
  GROUP BY CEILING(TIME_TO_SEC(TIMEDIFF(sv_Date, @debut_periode)) / @duree_point);

J'ai d'abord exécuté ceci :

Code :

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

1
2
3
SELECT TIMEDIFF(sv_Date, '2009-11-25')
FROM tmp
LIMIT 100

Et ça m'a renoyé 100 NULL.

Par contre avec ceci :

Code :

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

1
2
3
SELECT TIMEDIFF(sv_Date, '2009-11-25 00:00:00')
FROM tmp
LIMIT 100

J'ai bien obtenu des résultats cohérents.

Ensuite on ajoute TIME_TO_SEC, puis la division, puis CEILING...
Et idem bien sûr sur la partie GROUP BY.

Je vous laisse terminer la procédure.