Requete de moyenne pour chaque heure

[wonderboutin]

Bonjour à tous!

Je ne suis pas un expert en requete sql mais je me débrouille. J'ai une bd sql qui a des enregistrements à chaques minutes.(environ 10000 lignes par semaine) J'ai besoin de faire un rapport le min moy max des valeurs à chaque semaine.

Select Min(Champ1) as Min, Max(Champ1) as Max, Avg(Champ1) as Moy From BD WHERE DateAndTime > 'Date1' and DateAndTime < 'Date2'

Cette requete me donne le min,moy et max de toute la période que j'ai selectionné (entre Date1 et Date2) ce qui me donne une ligne avec le champ Min, Max et Moy.

Le problème est que je ne veux pas extraire les Min,Max et Moy pour la période entre Date1 et Date2 mais plutot les Min,Max et Moy de chaque jour.

Donc, si je fais une requete entre le 1er et le 31 janvier, je devrais avoir 31 ligne (une pour chaque jour) et non une seul moyenne.

Cette requete est-elle possible ou je dois créer une requete pour chaque jour ? Si je dois créer une requete par jours par programmation le rapport est très long à exécuter si je veux extraire une année complete par exemple.

Merci!

[elsuket]

Bonjour,

Cette requete est-elle possible ou je dois créer une requete pour chaque jour ?

Bien sûr ! A partir du moment ou vous traitez des ensembles, cela s'exprime très facilement en SQL.
La seule chose que vous avez oublié pour ce faire dans votre requête, c'est la clause GROUP BY.
Si vous êtes sous SQL Server 2008, vous pouvez écrire :

Code :

1
2
3
4
5
6
7
8
9
SELECT	CAST(DateAndTime AS date) AS date_stamp
	, MIN(colonne1) AS Min_colonne1
	, MAX(colonne1) AS Max_colonne1
	, AVG(colonne1) AS Moy_colonne1
FROM	dbo.BD
WHERE	DateAndTime > 'Date1'
AND	DateAndTime < 'Date2'
GROUP	BY CAST(DateAndTime AS date)
ORDER	BY CAST(DateAndTime AS date)

Si vous êtes sous une version antérieure :

Code :

1
2
3
4
5
6
7
8
9
SELECT	CAST(FLOOR(CAST(DateAndTime AS float)) AS datetime) date_stamp
	, MIN(colonne1) AS Min_colonne1
	, MAX(colonne1) AS Max_colonne1
	, AVG(colonne1) AS Moy_colonne1
FROM	dbo.BD
WHERE	DateAndTime > 'Date1'
AND	DateAndTime < 'Date2'
GROUP	BY CAST(FLOOR(CAST(DateAndTime AS float)) AS datetime)
ORDER	BY CAST(FLOOR(CAST(DateAndTime AS float)) AS datetime)

@++

[wonderboutin]

J'ai souvant utilisé le GROUP BY mais quelle est la fonction de SELECT CAST(DateAndTime AS date) AS date_stamp ?

[elsuket]

Cela extrait la partie date des valeurs datetime de la colonne DateAndTime.

Faites les test suivant :

Code :

SELECT CAST(GETDATE() AS date)

@++

[wonderboutin]

Merci beaucoup !



Je vais pousser plus loin. est-il possible de faire la même chose mais pour chaque heures (24 lignes par jour) ??

[elsuket]

La réponse est toujours oui : il vous suffit d'ajouter dans la clause GROUP BY et dans le SELECT :

Code :

, DATEPART(hour, DateAndTime)

De cette façon vous aurez un groupement par jour et par heure.
Si vous voulez voir globalement les valeurs par heure (c'set-à-dire sans la date), il suffit d'enlever CAST(DateAndTime AS date) partout dans la requête, et de conserver le groupement par heure

@++

[wonderboutin]

Voici mon code qui fonctionne pour me donner une ligne par jour.

Code :

1
2
3
4
5
6
7
8
 
SELECT CAST(FLOOR(CAST(Time_Stamp AS float)) AS datetime) date_stamp
      ,MIN(PuitsVicto_Chlore) AS ValMin
      ,MAX(PuitsVicto_Chlore) AS ValMax
      ,AVG(PuitsVicto_Chlore) AS ValMoy
FROM [Indusoft].[dbo].[PuitsVicto]
  GROUP	BY CAST(FLOOR(CAST(Time_Stamp AS float)) AS datetime)
ORDER	BY CAST(FLOOR(CAST(Time_Stamp AS float)) AS datetime)

Si je veux une ligne pour chaque heure, tu me parle d'ajouter le code : , DATEPART(hour, DateAndTime) mais ne suis pas sur de l'endroi. Peux-tu m'aider ?

Merci!

[elsuket]

Donc si tu recherches par heure et par jour :

Code :

1
2
3
4
5
6
7
8
SELECT	CAST(FLOOR(CAST(Time_Stamp AS float)) AS datetime) date_stamp
	, DATEPART(hour, Time_Stamp)
	, MIN(PuitsVicto_Chlore) AS ValMin
	, MAX(PuitsVicto_Chlore) AS ValMax
	, AVG(PuitsVicto_Chlore) AS ValMoy
FROM	[Indusoft].[dbo].[PuitsVicto]
GROUP	BY CAST(FLOOR(CAST(Time_Stamp AS float)) AS datetime), DATEPART(hour, Time_Stamp)
ORDER	BY CAST(FLOOR(CAST(Time_Stamp AS float)) AS datetime), DATEPART(hour, Time_Stamp)

En revanche si tu veux voir par heure (c'est à dire tous les jours confondus) :

Code :

1
2
3
4
5
6
7
SELECT	DATEPART(hour, Time_Stamp)
	, MIN(PuitsVicto_Chlore) AS ValMin
	, MAX(PuitsVicto_Chlore) AS ValMax
	, AVG(PuitsVicto_Chlore) AS ValMoy
FROM	[Indusoft].[dbo].[PuitsVicto]
GROUP	BY DATEPART(hour, Time_Stamp)
ORDER	BY DATEPART(hour, Time_Stamp)

@++

[same66]

Bonjour,

je me permets de réagir à la suite de se sujet car je me trouve dans la même situation et j'ai un petit soucis de compréhension par rapport à ce que vous faites.

J'ai une table dans laquelle est stockée une liste de bugs avec une heure bien précise (type datetime) et je souhaite pour un jour donné, récupérer le nombre de bugs par heure.

Ma requête est donc la suivante :

Code :

1
2
3
4
5
6
7
 
SELECT	DATEPART(hour, 20) AS 'heure',
		COUNT (ErrorDate) AS 'nbBugs'
FROM	[Logs].[dbo].[tLogsBugs]
WHERE convert(char(14),ErrorDate,126) LIKE '2011-11-02%'
GROUP	BY DATEPART(hour, 20)
ORDER	BY DATEPART(hour, 20)

Et en exécutant cette requête, j'ai l'erreur suivante :

Msg*164, Niveau*15, État*1, Ligne*1
Chaque expression GROUP BY doit contenir au moins une colonne qui n'est pas une référence externe.

Auriez vous une idée ?
Merci par avance

PS : Pour l'exemple là, j'ai donné une heure fixe DATEPART(hour, 20) car je ne sais pas comment faire pour que automatiquement SQL fasse la requête pour TOUTES les heures de la journée pour une date donnée.

Merci par avance

[serge0934]

tu peux l'écrire comme cela

Code :

1
2
3
4
5
6
SELECT COUNT (*) AS 'nbBugs',heure 
	FROM (	SELECT	DATEPART(hour, ErrorDate) AS 'heure',ErrorDate
	FROM	[Logs].[dbo].[tLogsBugs]
	WHERE convert(char(14),ErrorDate,126) LIKE '2011-11-02%') A
	GROUP	BY heure
	ORDER	BY heure

[elsuket]

Bonjour,

L'erreur que vous obtenez est due à la mauvaise utilisation de la fonction DATEPART().
En outre la clause WHERE est horrible parce qu'elle n'est pas cherchable.
En effet SQL Server maintient des statistiques sur les valeurs qui sont dans les colonnes et les index.
Mais il ne peut pas maintenir de statistiques sur toutes les fonctions que l'on peut appliquer à une colonne.
En conséquence, il doit à chaque fois lire toute la table ...

Si vous écrivez ceci :

Code :

1
2
3
4
5
6
SELECT	DATEPART(HOUR, ErrorDate) AS heure
	, COUNT(*) AS nbBugs
FROM	Logs.dbo.tLogsBugs
WHERE	ErrorDate BETWEEN '20111102' AND '20111103'
GROUP	BY DATEPART(HOUR, ErrorDate)
ORDER	BY DATEPART(HOUR, ErrorDate)

Et que vous avez un index sur la colonne ErrorDate, votre requête sera très rapide.
Si vous êtes flemmard comme moi, vous pouvez écrire :

Code :

1
2
3
4
5
6
7
8
9
DECLARE @jour datetime = '20111102'
-----------------------------------------
DECLARE @jour_fin datetime = DATEADD(day, 1, @jour)
SELECT	DATEPART(HOUR, ErrorDate) AS heure
	, COUNT(*) AS nbBugs
FROM	Logs.dbo.tLogsBugs
WHERE	ErrorDate BETWEEN @jour AND @jour_fin
GROUP	BY DATEPART(HOUR, ErrorDate)
ORDER	BY DATEPART(HOUR, ErrorDate)

Si vous êtes encore plus flemmard, vous pouvez faire :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE PROCEDURE usp_BugsParJourEtParHeure
	@_jour datetime
AS
BEGIN
	SET NOCOUNT ON
 
	DECLARE @jour_fin datetime = DATEADD(day, 1, @jour)
 
	SELECT	DATEPART(HOUR, ErrorDate) AS heure
		, COUNT(*) AS nbBugs
	FROM	Logs.dbo.tLogsBugs
	WHERE	ErrorDate BETWEEN @_jour AND @jour_fin
	GROUP	BY DATEPART(HOUR, ErrorDate)
	ORDER	BY DATEPART(HOUR, ErrorDate)
END

Ce qui vous permet d'exécuter :

Code :

EXEC dbo.usp_BugsParJourEtParHeure '20111102'

@++