Discussion :

[stbeau]

Bonjour à tous,

Je vais essayer d'exposer mon problème du moment.
Je précise que je suis sous SQL server 2014.

J'ai une application qui enregistre dans une table les valeurs de plusieurs compteurs d’énergies (2 compteurs eau dans l'exemple décrit). Ces valeurs sont des compteurs perpétuelles (pas de remise a zéro).



On me demande d'extraire les consommations par mois sur les 12 derniers mois et c'est sur ce point que je cale.




Exemple de formule pour le calcul de la consommation du mois de février :
Somme des compteurs d'eau des derniers enregistrement de février - Somme des compteurs d'eau des derniers enregistrement de janvier


Rq: Ceci est un bilan de consommation par mois, mais par la suite, je devrais également le faire par jour sur le 30 derniers jours et par heure sur les 3 derniers jours.


J'espère avoir été assez clair et vous remercie d'avance si vous arrivez à m'aider sur ce sujet.

Cordialement.

[julien94320]

Bonjour,

Au hasard je testerai une requete dans ce genre :

Code :

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

1
2
3
4
5
6
 
 
SELECT MONTH(cpt_Horodate) as _Mois
sum(cpt_valeur) as _Conso
FROM ma table 
GROUP BY MONTH (cpt_Horodate)

Si je veux aussi le faire par mois, année

je fais la requete suivante :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
 
SELECT 
 MONTH(cpt_Horodate) as _Mois
 YEAR(cpt_Horodate) as _Annee
 ,SUM(cpt_valeur) as _Conso
 FROM ma table 
 GROUP BY 
 MONTH (cpt_Horodate)
,YEAR(cpt_Horodate)

Si je veux le faire sur une année je rajoute le critètere suivant :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
 
SELECT 
 MONTH(cpt_Horodate) as _Mois
 YEAR(cpt_Horodate) as _Annee
 ,SUM(cpt_valeur) as _Conso
 FROM ma table 
 WHERE 
 CAST(cpt_Horodate AS DATE) between CAST(DATEADD(year,-1,getdate()) AS DATE) and CAST(getdate() AS DATE)
 GROUP BY 
 MONTH (cpt_Horodate)
,YEAR(cpt_Horodate)

[elsuket]

Bonjour,

On peut pas faire un SUM() direct, puisque les compteurs sont constamment croissants.

Voici les tables avec lesquelles j'ai travaillé :

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
CREATE TABLE dbo.compteur_cpt
(
	cpt_id int NOT NULL IDENTITY
		CONSTRAINT PK_compteur_cpt PRIMARY KEY
	, cpt_Nom varchar(16) NOT NULL
		CONSTRAINT UQ_compteur_cpt__cpt_Nom UNIQUE
)
GO
 
CREATE TABLE dbo.consommation_csm
(
	csm_id int NOT NULL IDENTITY
		CONSTRAINT PK_consommation_csm PRIMARY KEY
	, cpt_id int NOT NULL
		CONSTRAINT FK_consommation_csm__cpt_id FOREIGN KEY (cpt_id) REFERENCES dbo.compteur_cpt
	, cpt_Horodate datetime NOT NULL
	, cpt_valeur decimal (19, 6) NOT NULL
		CONSTRAINT CHK_consommation_csm__cpt_valeur CHECK(cpt_valeur >= 0)
)
GO

Et voici les requêtes pour générer un jeu de données :

Code :

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

1
2
3
INSERT INTO dbo.compteur_cpt (cpt_Nom)
VALUES ('Compteur eau 1'), ('Compteur eau 2')
GO

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
WITH
	N AS(SELECT NULL AS v UNION ALL SELECT NULL)
	, N1 AS (SELECT A.v FROM N AS A CROSS JOIN N AS B)
	, N2 AS (SELECT A.v FROM N1 AS A CROSS JOIN N1 AS B)
	, N3 AS (SELECT A.v FROM N2 AS A CROSS JOIN N2 AS B)
	, N4 AS (SELECT A.v FROM N3 AS A CROSS JOIN N3 AS B)
	, N5 AS (SELECT A.v FROM N4 AS A CROSS JOIN N4 AS B)
	, IC AS
	(
		-- Génère une liste de nombres par incréments de 1
		SELECT	ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS increment
		FROM	N5
	)
	, F AS
	(
		-- Limite le nombre de nombres à la plage dont on a besoin
		SELECT	increment
		FROM	IC
		WHERE	increment <= DATEDIFF(minute, '20140101', '20160722') / 5
	)
INSERT INTO dbo.consommation_csm
(
	cpt_id
	, cpt_Horodate
	, cpt_valeur
)
SELECT		C.cpt_id
		, DATEADD(minute, increment * 5, '20140101')
		, increment + ABS(CHECKSUM(NEWID())) % 0.9 -- Génère une valeur de relevé de compteur aléatoire
FROM		F
CROSS JOIN	(SELECT 1 UNION ALL SELECT 2) AS C(cpt_id)
GO

On ajoute l'index suivant :

Code :

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

1
2
3
CREATE INDEX IX_consommation_csm__cpt_Horodate__cpt_id
ON dbo.consommation_csm(cpt_Horodate, cpt_id)
GO

La requête pour la consommation annuelle qui peut vous de modèle servir les autres demandes :

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
;WITH
	CTE AS
	(
		-- Recherche le dernier relevé de compteur par année et mois
		SELECT	cpt_id
			, YEAR(cpt_Horodate) AS cpt_Horodate_year
			, MONTH(cpt_Horodate) AS cpt_Horodate_month
			, MAX(cpt_Horodate) AS last_cpt_Horodate
		FROM	dbo.consommation_csm
		WHERE	cpt_Horodate >= DATEADD(month, -12, CAST(GETDATE() AS date))
		GROUP	BY cpt_id, YEAR(cpt_Horodate), MONTH(cpt_Horodate)
	)
	, ANNEE AS
	(
		-- Récupère la valeur du dernier relevé
		SELECT		CSM.cpt_id
				, C.cpt_Horodate_year
				, C.cpt_Horodate_month
				, CSM.cpt_valeur
		FROM		CTE AS C
		INNER JOIN	dbo.consommation_csm AS CSM
					ON C.cpt_id = CSM.cpt_id
					AND C.cpt_Horodate_month = MONTH(CSM.cpt_Horodate)
					AND C.last_cpt_Horodate = CSM.cpt_Horodate
	)
SELECT		A.cpt_Horodate_year
		, A.cpt_Horodate_month
		, SUM(B.cpt_valeur - A.cpt_valeur) AS month_conso
FROM		ANNEE AS A
INNER JOIN	ANNEE AS B
			ON
			(
				A.cpt_Horodate_year = B.cpt_Horodate_year
				AND A.cpt_Horodate_month + 1 = B.cpt_Horodate_month
			)
			OR
			(
				-- Gère la consommation à cheval sur deux années
				A.cpt_Horodate_year + 1 = B.cpt_Horodate_year
				AND A.cpt_Horodate_month = 12
				AND B.cpt_Horodate_month = 1
			)
GROUP BY	A.cpt_Horodate_year, A.cpt_Horodate_month
ORDER BY	A.cpt_Horodate_year, A.cpt_Horodate_month

@++

[François DORIN]

Bonjour,

Je viens proposer une alternative, en me basant sur les scripts fournis par elsuket (merci !).

Le principe : tu ajoutes une colonne à la table des consommations, afin de n'avoir que la consommation réelle depuis le dernier relevé. Il te suffit ensuite de réaliser le calcul régulièrement, et tu pourras alors récupérer la consommation sur n'importe quelle période très facilement, via l'opérateur SUM().

Voici mon script :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
ALTER TABLE consommation_csm ADD delta DECIMAL(19,6);
 
GO
 
UPDATE consommation_csm 
	SET delta = C.cpt_valeur - (SELECT TOP(1) D.cpt_valeur FROM consommation_csm AS D WHERE D.cpt_id = C.cpt_id AND D.cpt_Horodate < C.cpt_Horodate ORDER BY D.cpt_Horodate DESC)
	FROM consommation_csm AS C
	WHERE C.delta IS NULL;
 
GO
 
CREATE INDEX IX_consommation_csm__delta
ON dbo.consommation_csm(delta)

Il faut exécuter ceux de elsuket avant. Le script se décompose en 4 étapes :

1. Ajouter une colonne "delta" qui va contenir la consommation réelle depuis le dernier relevé (en gros, valeur de cpt_valeur du présent relevé - valeur de cpt_valeur du relevé précédent) ;
2. Calculer les "delta" pour tous les enregistrements existants;
3. Un petit index pour que la réexécution de la requête à l'étape précédente soit plus rapide lors de la mise à jour suite à l'apparition de nouveaux relevés ;
4. Régulièrement, réexécuter la requête en 2 afin de calculer les delta pour les nouveaux enregistrements.

Une précaution à prendre toutefois : cela suppose que les relevés ne changent jamais ! Je pense que cette hypothèse est justifiée mais dans le cas où elle serait erronnée, il faudrait alors recalculer l'ensemble des delta.

La requête de mise à jour des delta est longue (plus d'6h dans mon cas, pour la mise à jour de 537408 enregistrements). C'est normal dans la mesure où pour chaque enregistrement, je refais une requête afin de trouver la valeur du précédent relevé. Mais le calcul des delta ne se fait qu'une fois par enregistrement et cela te permettra d'optimiser le calcul des consommations sur n'importe quelle période, puisqu'il suffira d'utiliser l'aggrégateur SUM.

[aieeeuuuuu]

Bonjour,

Puisque vous êtes sous 2014, vous pouvez aussi utiliser la fonction LAG pour trouver la consommation, et faire vos calculs dessus

Quelque chose comme :

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
 
CTE AS (
	SELECT 
			cpt_id 
		,	cpt_horodate
		,	COALESCE(cpt_valeur- LAG(cpt_valeur) OVER(PARTITION BY cpt_id ORDER BY cpt_horodate), 0) AS conso
	FROM LaTable
)
SELECT 
		cpt_id
	,	MONTH(cpt_horodate)
	,	YEAR(cpt_horodate)
	,	SUM(conso) AS ConsoTotale
FROM CTE
GROUP BY 
		cpt_id
	,	MONTH(cpt_horodate)
	,	YEAR(cpt_horodate)