Discussion :

[ARAGORN93]

Bonjour,

Mon problème paraitra surement simple aux spécialistes, mais étant débutant dans sql server, je n'y connais rien.

J'ai dans ma BDD une table avec des données qui y sont rappatriées toutes les minutes.
Je voudrais créer une autre table avec ces valeurs moyénnées au 1/4h.

Comment faire ?

[elsuket]

Bonsoir,

Voici une piste, elle est simple mais ce n'est peut-être pas la meilleure, parce que je n'ai pas trouvé le moyen de faire des aggrégats sur 15 minutes

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
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
SET NOCOUNT ON
 
DECLARE @Min_today DATETIME,
		@Max_today DATETIME
 
-- Récupération de la date : ce matin à minuit
-- et de la date : ce soir à minuit
SELECT @Min_today = CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME),
		@Max_today = CAST(CEILING(CAST(GETDATE() AS FLOAT)) AS DATETIME)
 
-- Création de la table volatile des quarts d'heure
DECLARE @quart_heure TABLE
(
	quart_heure_debut DATETIME,
	quart_heure_fin DATETIME,
	Moyenne NUMERIC(10,2),
	Somme INT
)
 
-- Population de la table des quarts d'heure
INSERT INTO @quart_heure
(
	quart_heure_debut,
	quart_heure_fin
)
VALUES
(
	@Min_today,
	DATEADD(minute, 15, @Min_today)
)
 
WHILE @Min_today < DATEADD(minute, -15, @Max_today)
BEGIN
	SELECT @Min_today = DATEADD(minute, 15, @Min_today)
 
	INSERT INTO @quart_heure
	(
		quart_heure_debut,
		quart_heure_fin
	)
	VALUES
	(
		@Min_today,
		DATEADD(minute, 15, @Min_today)
	)
END
 
--SELECT quart_heure_debut, quart_heure_fin, Moyenne, Somme
--FROM @quart_heure
 
-- Réinitialisation des variables
SELECT @Min_today = CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME),
		@Max_today = CAST(CEILING(CAST(GETDATE() AS FLOAT)) AS DATETIME)
 
-- Création d'une table volatile fictive qui contient les données logguées toutes les minutes
DECLARE @log TABLE
(
	date DATETIME,
	amount TINYINT
)
 
-- Population de cette table
INSERT INTO @log VALUES (@Min_today, 1)
 
WHILE @Min_today < @Max_today
BEGIN
	SELECT @Min_today = DATEADD(minute, 1, @Min_today)
 
	INSERT INTO @log VALUES (@Min_today, 1)
END
 
-- SELECT date, amount FROM @log
 
-- Calculs
UPDATE @quart_heure
SET Moyenne = (
				SELECT AVG(amount)
				FROM @log
				WHERE date BETWEEN Q.quart_heure_debut AND Q.quart_heure_fin
			),
	Somme = (
				SELECT SUM(amount)
				FROM @log
				WHERE date BETWEEN Q.quart_heure_debut AND Q.quart_heure_fin
			)
FROM @quart_heure Q, @log L
 
SELECT * FROM @quart_heure

Vous pouvez remplacer la table @quart_heure par une table de votre base de données, et peupler celle-ci tous les jours après l'avoir tronquée juste avant, par exemple

[SQLpro]

Le plus simple et le plus performant n'est pas de pisser du code itératif, mais de créer une table de temps avec des datetime au 1/4 d'heure pour tout l'intervalle que vous voulez scruter. A défaut du 1er janvier 2000 au 31 décembre 9999 !

Il ne vous restera plus qu'à faire une jointure externe droite avec cette table.

A +

[elsuket]

Pouvez-vous donc nous expliquer comment vous pissez du code non itératif pour remplir votre table de temps avec des datetime au 1/4 d'heure s'il-vous-plaît ?

[SQLpro]

Elsuket, vous vous méprenez sur mes propos. Je n'ai pas dit qu'il fallait générer la table des dates au quart d'heure de manière ensembliste, mais qu'il fallait faire des requêtes et non des traitements ensemblistes.

Maintenant pour alimenter de manière ensembliste c'est aussi possible mais ne revêt pas grand intérêt :

Code :

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

1
2
3
4
5
6
7
8
9
CREATE TABLE T_QUART (T DATETIME NOT NULL PRIMARY KEY)
INSERT INTO T_QUART VALUES ('20000101')
 
WHILE NOT EXISTS(SELECT *
                 FROM   T_QUART 
                 WHERE  T > '20101231 23:59:59.997')
   INSERT INTO T_QUART
   SELECT DATEADD(MINUTE, 15, MAX(T_QUART))
   FROM   T_QUART

A +

[elsuket]

Bonsoir SQL Pro,

C'est exact donc excusez-moi, mais soyez un peu plus tolérant : j'ai précisé que c'est une piste, et nous n'avons pas tous votre expérience