Requête style reporting

[popachubby]

Bonjour,

je me prends la tête depuis quelques heures sans arriver à mes fins :

Etant donnée une table, disons MaTable, avec comme champs :

-------------------------------------------------------------------------
Horodatage(datetime) | Variable1(float) | Variable2(float) | Variable3(float)
-------------------------------------------------------------------------
19/06/2008 14:47:43 | 0,4564 | 256,456 | ...
19/06/2008 14:47:44 | 0,3344752 | ...
19/06/2008 14:47:45 | 1,45752 | ..
19/06/2008 14:47:46 | 0,46 |
19/06/2008 14:47:47 | 0,37 |

Mettons qu'il y ait quelques milliers de ligne, un "horodatage" par seconde, j'essaie de trouver une requête me permettant de retourner par ex. les moyennes de Variable1, variable2 et Variable3 à la minute. J'essaie donc de faire une requête qui me renverrai ça :

-------------------------------------------------------------------------
Horodatage(datetime) | Variable1(float) | Variable2(float) | Variable3(float)
-------------------------------------------------------------------------
19/06/2008 14:47:00 | 0,4564 | 256,456 | ...
19/06/2008 14:48:00 | 0,3344752 | ...
19/06/2008 14:49:00 | 1,45752 | ..
19/06/2008 14:50:00 | 0,46 |
19/06/2008 14:51:00 | 0,37 |

Où, pour chaque ligne, on aurait la moyenne des valeurs pour la minute précédent l'horodatage (ex. ici la ligne 1 = moyenne des variables de 14H46 à 14H47)...
Avec une contrainte de plus : chaque datetime du résultat doit tomber "pile" (minute pile, ou heure pile pour des moyennes horaires, etc)...
Et, encore un truc, si la table peut contenir plusieurs mois de données, on peut ne vouloir restituer que tel ou tel jour, telle tranche horaire etc...

J'ai essayé plusieurs choses, j'arrive par ex. à retourner tous les horodatages à la minute dont j'ai besoin, mais pas avec les moyennes...

il me faudrait en fait ça :
select MAX(horodatage), avg(variable1), avg(variable2) from MaTable where horodatage between @dateMinimumAND @dateMaximum
mais pour tous les points à restituer, et en une seule requête...

J'ai essayé aussi ça :

Code :

1
2
3
4
    SELECT DISTINCT(DATEADD(minute, DATEDIFF(minute, 0, t1.horodatage), 1)), avg(t2.variable1) AS MoyVar1, avg(t2.variable2) AS MoyVar2, avg(t2.variable3) AS MoyVar3
    FROM MaTable t1 
    INNER JOIN MaTable t2 ON t2.horodatage BETWEEN (SELECT DATEADD(minute, -1, DATEADD(minute, DATEDIFF(minute, 0, t1.horodatage), 1))) AND DATEADD(minute, DATEDIFF(minute, 0, t1.horodatage), 1)
    GROUP BY t1.horodatage

ce qui est, vous en conviendrez, assez lamentable, et en plus ne retourne... rien !

Bref, à l'aide ...

[bdmax]

essaye quelque chose comme ça

Code :

1
2
3
4
5
 
SELECT convert(varchar, horodatage, 103) + ' ' + LEFT(convert(varchar, horodatage, 114),5),
avg(variable1) AS MoyVar1, avg(variable2) AS MoyVar2, avg(variable3) AS MoyVar3
FROM MaTable t1
GROUP BY convert(varchar, horodatage, 103) + ' ' + LEFT(convert(varchar, horodatage, 114),5)

Encore mieux !

Code :

1
2
3
4
5
 
SELECT cast(horodatage AS smalldatetime),
avg(variable1) AS MoyVar1, avg(variable2) AS MoyVar2, avg(variable3) AS MoyVar3
FROM MaTable t1
GROUP BY cast(horodatage AS smalldatetime)

[popachubby]

Merci de ta réponse,

effectivement la deuxième requête est mieux (la première pose de gros soucis, par ex. sur une fenêtre de dates entre le 31 decembre et le 2 janvier...) ; par contre y'a un gros problème : ça ne donne pas le bon résultat...

J'y suis preque arrivé, en faisant ça :

Code :

1
2
3
4
SELECT (DATEADD(minute, DATEDIFF(minute, 0, MAX(horodatage)), 1)) AS dateheure,
avg(variable1) AS MoyVar1, avg(variable2) AS MoyVar2, avg(variable3) AS MoyVar3
FROM Matable
GROUP BY (DATEADD(minute, DATEDIFF(minute, 0, horodatage), 1))

seulement c'est "décalé" : comme j"'arrondis" les dates, j'ai pour chaque ligne la moyenne des valeurs pour l'horodatage suivant (ex. : une ligne avec les moyennes de 12:00 à 12:01, mais la colonne "dateheure" marque 12:00 au lieu de 12:01).

J'ai beau trifouiller les paramètres de DATEDIFF et DATEADD, ça change rien...

[popachubby]

Je poste une solution pour les googliens qui auraient le même problème que moi ; si quelqu'un a une idée moins tarabiscotée, et surtout plus performante, je suis preneur.

ex. pour un "rapport" des valeurs moyennées à la minute, entre dateMin et dateMax :

Code :

1
2
3
4
5
6
SELECT DATEADD(minute, 1, DATEADD(minute, DATEDIFF(minute, 0, horodatage), 1)) AS horo,
        avg(variable1) AS MoyVariable1,
avg(Variable2) AS MoyVariable2
    FROM MaTable
WHERE horodatage BETWEEN @dateMin AND @dateMax
    GROUP BY (DATEADD(minute, DATEDIFF(minute, 0, horodatage), 1))