Group By tranche de 10 minutes

[jcd]

Bonjour,

Pour des analyses sur un système d'alarmes, je souhaite extraire sur une période d'une semaine le Nb d'alarmes apparues dans chaque tranche de 10 minutes :

Code :

1
2
3
4
SELECT FctADefinir1(DateApparition) AS Tranche10Min, Count(DateApparition) AS NbAlmDansLaTranche
FROM tAlarmes
WHERE DateApparition BETWEEN '01/01/2012' AND '08/01/2012'
GROUP BY FctaDefinir2(DateApparition)

Où "Tranche10Min" pourrait être du genre '01/01/2012 00:00', '01/01/2012 00:10', '01/01/2012 00:20'... pour identifier chaque tranche

Merci d'avance pour tout conseil sur FctADefinir1 et FctADefinir2, qui peuvent être soit des fonctions standard SQL Server, soit des fonctions "utilisateur".

[Waldar]

Par exemple :

Code :

1
2
3
4
5
6
SELECT getdate() AS dt1
     , cast(floor(cast(getdate() AS float)*24*6)/24/6 AS datetime) AS dt2
 
dt1                     dt2
----------------------- -----------------------
2012-01-13 12:14:43.960 2012-01-13 12:10:00.000

[jcd]

Ok, merci super.

Cela me retourne effectivement la tranche de 10 minutes dans laquelle il y eu au moins une alarme et le Nb d'alarmes apparues dans cette tranche de 10 minutes.

Maintenant, pour continuer ma réflexion, si je veux tracer cela en graphique, il me serait plus simple d'obtenir autant d'enregistrement que de tranches de 10 min sur la période (soit l'affichage de toutes les tranches 10min) et le Nb d'alarmes correspondant par tranche (avec 0 - ou Null - pour toutes celles où il n'y a pas d'alarme) ?

Du genre :

Code :

1
2
3
4
5
6
7
Tranche             Nb
------------------- -------------
01/01/2012 00:00    4
01/01/2012 00:10    0
01/01/2012 00:20    0
01/01/2012 00:30    2
01/01/2012 00:40    0

Encore merci par avance.

[mikedavem]

Utilisez une table de temps avec vos intervalles de 10min.
ll suffira de regrouper en fonction de ces colonnes pour avoir votre représentation finale.

++

[elsuket]

Bonjour,

Allez un petit coup de pouce

Voici donc la table dont vous avez besoin :

Code :

1
2
3
4
5
6
7
CREATE TABLE tb_tranche_10_minutes
(
	debut_tranche datetime NOT NULL
	, fin_tranche datetime NOT NULL
	, CONSTRAINT PK_tb_tranche_10_minutes PRIMARY KEY (debut_tranche, fin_tranche)
)
GO

Et la procédure stockée pour la peupler sans se fatiguer :

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
30
31
32
CREATE PROCEDURE usp_tb_date_10_minutes_peuple
	@_annee_debut int
AS
BEGIN
	SET NOCOUNT ON
 
	IF @_annee_debut IS NULL
	BEGIN
		RAISERROR('Le paramètre en entrée @_annee_debut doit être valué', 16, 1)
		RETURN
	END
 
	IF LEN(@_annee_debut) <> 4
	BEGIN
		RAISERROR('La valeur %d pour le paramètre @_annee_debut est incorrecte', 16, 1, @_annee_debut)
		RETURN
	END
 
	DECLARE @date_debut datetime
		, @date_fin datetime
 
	SELECT	@date_debut = CAST(CAST(@_annee_debut AS char(4)) AS datetime)
	SELECT	@date_fin = DATEADD(year, 1, @date_debut)
 
	WHILE @date_debut < @date_fin
	BEGIN
		INSERT	INTO dbo.tb_tranche_10_minutes (debut_tranche, fin_tranche)
		SELECT	@date_debut, DATEADD(minute, 10, @date_debut)
 
		SELECT @date_debut = DATEADD(minute, 10, @date_debut)
	END
END

Pour peupler la table, il suffit d'exécuter :

Code :

EXEC dbo.usp_tb_date_10_minutes_peuple 2012

Sur mon petit PC portable, ça se fait en 30 secondes.
Je me suis en suite créé la table d'alertes suivante :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE alerte
(
	alerte_id int IDENTITY NOT NULL CONSTRAINT PK_alerte PRIMARY KEY
	, alerte_date_time datetime NOT NULL
)
GO
 
INSERT	INTO dbo.alerte (alerte_date_time)
SELECT	'20120112 00:05:00'
UNION ALL SELECT '20120112 00:15:00'
UNION ALL SELECT '20120112 00:16:00'
UNION ALL SELECT '20120112 00:17:00'
UNION ALL SELECT '20120113 01:20:00'
UNION ALL SELECT '20120113 01:21:00'

Et la requête est alors simple a écrire :

Code :

1
2
3
4
5
6
7
8
SELECT		T.debut_tranche
		, T.fin_tranche
		, COUNT(*) AS occurences
FROM		dbo.alerte AS A
INNER JOIN	dbo.tb_tranche_10_minutes AS T
			ON A.alerte_date_time >= T.debut_tranche
			AND A.alerte_date_time < T.fin_tranche
GROUP BY	T.debut_tranche, T.fin_tranche

Ce qui donne :

debut_tranche fin_tranche occurences
----------------------- ----------------------- -----------
2012-01-12 00:00:00.000 2012-01-12 00:10:00.000 1
2012-01-12 00:10:00.000 2012-01-12 00:20:00.000 3
2012-01-13 01:20:00.000 2012-01-13 01:30:00.000 2

@++

[jcd]

Merci beaucoup ELSUKET pour tous les détails de cette explication.

[Ptit_Dje]

Personellement, je vous recommenderais plutot une table interval dans le style de celle proposee par Elsuket mais ayant des type de donnees time plutot que datetime:

Code :

1
2
3
4
5
6
7
 
CREATE TABLE tb_tranche_10_minutes
(
	debut_tranche time NOT NULL
	, fin_tranche time NOT NULL
	, CONSTRAINT PK_tb_tranche_10_minutes PRIMARY KEY (debut_tranche, fin_tranche)
)

Ca vous donnera un nombre fini de valeurs qui pourront etre utilisees pour tous les jours.
Par contre vous aurez besoin de caster vos donnees de votre table alerte en time pour effectuer la jointure et remonter la tranche horaire.