insertion d'enregistrements manquants

**verdifre** · 21/08/2012, 15h38

Bonjour,
Cela fait 20 ans que je n'ai pas touché à SQL et pour "m'amuser" je developpe un suivi de consommation EDF
Mon problème est le suivant:
j'ai une table avec une date et des relevés d'index de compteurs. Dans cette table j'ai en theorie un enregistrement par jour. Cependant il y a de nombreux trous (vacances, oubli de relevés etc...) . J'aimerai pouvoir recréer ces enregistrements manquants (par interpollation entre 2 dates).
Pour l'instant j'ai été capable d'importer mes données depuis open office calc, de calculer des consommations journaliéres, mensuelles et annuelles. Je bloque sur la conception même de la requete d'insertion des dates manquantes dans ma table.
Si quelqu'un pouvait m'indiquer par quel bout prendre le problème, cela me depannerai
fred

**guillaumerivière7** · 21/08/2012, 16h59

Salut,

Tu pourrais faire une procédure stockée qui effectue ton calcul.

A+

**verdifre** · 21/08/2012, 19h42

Bonjour, et merci pour ta réponse,
cependant même si une procedure stockée offre plus de possibilitées cela ne change pas fondamentalement mon problème.
Si j'essaie de decomposer l'ensemble, le premier probleme que je rencontre est de trouver les dates manquantes dans ma serie de données.
J'ai a priori deux options pour faire cela
1)
a) générer une "liste" (je ne sais pas si c'est le terme aproprié) de dates entre la date de début et aujourd'hui (je ne sais pas faire)
b) retirer de cette liste toutes les dates existantes dans ma table, une clause "not in" devrait faire l'affaire
c) inserer mes nouvelles dates

2)
a)rechercher par une "auto" jointure sur ma table les dates encadrant mes "trous" (je devrais pouvoir m'en sortir)
b) un "date diff" me donne alors le nombre de jours a inserer dans le trou et il me permet aussi de calculer l'interpolation lineaire sur mes index compteurs
le probleme est que si je vois a peu prés comment faire pour un trou de 1 jour, je ne reussi pas à imaginer comment faire pour un trou de plusieurs jours

pour mieux situer le probleme, un petit exemple d'une zone de la table pleine de trous

2010-01-14 17470 49055
2010-01-16 17546 49244
2010-01-18 17605 49402
2010-01-19 17631 49463
2010-01-20 17654 49550
2010-01-23 17757 49777
2010-01-26 17834 49999
2010-01-29 17947 50286
2010-02-03 18090 50657

fred

**CinePhil** · 21/08/2012, 20h26

Le mieux est effectivement de faire une table des jours.
Voir l'article de SQLPro sur la modélisation d'un calendrier pour y puiser ce qui vous sera nécessaire.

**verdifre** · 21/08/2012, 22h23

Bonjour,
Merci pour ce lien , je suis en train d'étudier cela.
fred

**StringBuilder** · 22/08/2012, 12h02

Hmmm, je viens de me rendre compte que je viens de mettre au point une requête, je pense, pour rien...

Est-ce que MySQL supporte les CTE ?

Et les CTE récursives ?

Si oui, alors cette requête (à la syntaxe SQL Server, donc à adapter) répond au problème sans toucher à la structure de la base :

Code sql :

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
 
drop table releve;
 
create table releve
(
	rel_id int identity(1,1) not null,
	rel_date date not null,
	rel_valeur int not null
);
 
create unique clustered index uix_rel_date on releve(rel_date);
 
alter table releve
add primary key (rel_id);
 
insert into releve (rel_date, rel_valeur) values ('2012-08-01', 1000);
insert into releve (rel_date, rel_valeur) values ('2012-08-02', 1010);
insert into releve (rel_date, rel_valeur) values ('2012-08-05', 1040);
insert into releve (rel_date, rel_valeur) values ('2012-08-10', 1120);
insert into releve (rel_date, rel_valeur) values ('2012-08-15', 1180);
insert into releve (rel_date, rel_valeur) values ('2012-08-21', 1500);
 
select * from releve;
 
WITH cte_cpt (cpt)
AS
(
	select 0
	UNION ALL 
	select cpt + 1 from cte_cpt where cpt < 100
)
insert into releve (rel_date, rel_valeur) 
select dateadd(day, cte_cpt.cpt, r1.rel_date), r1.rel_valeur + (cte_cpt.cpt * ((r2.rel_valeur - r1.rel_valeur) / DATEDIFF(day, r1.rel_date, r2.rel_date)))
from releve r1
inner join releve r2 on r2.rel_date > dateadd(day, 1, r1.rel_date)
inner join cte_cpt on cte_cpt.cpt < DATEDIFF(day, r1.rel_date, r2.rel_date) and cte_cpt.cpt > 0
where r2.rel_date = (select MIN(rel_date) from releve where rel_date > r1.rel_date);
 
select * from releve;

J'ai limité la récursion ici à 100 pour la table compteur, puisque c'est la limite par défaut de SQL Server.
Il faut qu'elle corresponde au nombre de jours maximum sans relevé (ou au pire, il faudra lancer plusieurs fois de suite la requête).

Si MySQL ne supporte pas les CTE récursive, on peut aussi simplement créer une table "cpt" avec une unique colonne avec les valeurs 0 à 100, cela revient au même.

Ceci évite d'avoir à maintenir un calendrier.
Cela permet aussi de gérer de genre de demandes de façon générique, pas seulement pour des dates, mais n'importe quel interval avec des trous.

A noter aussi qu'il peut être judicieux de non pas stocker les valeurs, mais utiliser une vue. Ceci permet de faire la différence entre les données réelles et les données calculées.

Code sql :

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 view releve_sans_trou
as
WITH cte_cpt (cpt)
AS
(
	select 0
	UNION ALL 
	select cpt + 1 from cte_cpt where cpt < 100
)
select dateadd(day, cte_cpt.cpt, r1.rel_date) rel_date, r1.rel_valeur + (cte_cpt.cpt * ((r2.rel_valeur - r1.rel_valeur) / DATEDIFF(day, r1.rel_date, r2.rel_date))) rel_valeur
from releve r1
inner join releve r2 on r2.rel_date > dateadd(day, 1, r1.rel_date)
inner join cte_cpt on cte_cpt.cpt < DATEDIFF(day, r1.rel_date, r2.rel_date) and cte_cpt.cpt > 0
where r2.rel_date = (select MIN(rel_date) from releve where rel_date > r1.rel_date)
union all
select rel_date, rel_valeur
from releve;
 
select * from releve_sans_trou order by rel_date;

**CinePhil** · 22/08/2012, 12h06

Envoyé par StringBuilder

Hmmm, je viens de me rendre compte que je viens de mettre au point une requête, je pense, pour rien...

Est-ce que MySQL supporte les CTE ?

Et les CTE récursives ?

Non ! C'est une de ses nombreuses lacunes !

**StringBuilder** · 22/08/2012, 12h10

Du coup, table compteur, ou table calendrier.

Même si perso, je préfère la table compteur, plus générique

**verdifre** · 24/08/2012, 13h26

Bonjour,
j'ai reussi à faire ce que je voulais, vos conseils m'ont été trés utiles, merci beaucoup
ce que j'ai fait

Code SQL :

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
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
DELIMITER $$
 
DROP PROCEDURE IF EXISTS `ENERGIE`.`calc_diff_date`$$
CREATE DEFINER=`fred1`@`%` PROCEDURE `calc_diff_date`( in mode int)
BEGIN
declare date_deb date;
declare date_fin date;
declare date_inf  date;
declare date_sup date;
declare dure int;
declare index_HC_inf int;
declare index_HP_inf int;
declare index_HC_sup int;
declare index_HP_sup int;
declare index_HC_inter int;
declare index_HP_inter int;
declare conso_HC int;
declare conso_HP int;
declare conso_HC_inter int;
declare conso_HP_inter int;
 
declare i date default date_deb;
#### remplissage du calendrier t_date########
 
 
# mode = 0 , on bouche tous les trous de la table
# et on crée entierement un jeu d'enregistrements tout neuf
# utilisé en maintenance ou une fois au début
# si mode <> 0 on est en mode ajout typiquement declenché par un trigger lors d'un nouveau relevé
if mode = 0 
	then
		delete from t_date;
		select min(date_cour) into date_inf from EDF_BIS where date_cour > '0000-00-00';
		select max(date_cour) into date_sup from EDF_BIS;
		insert into t_date (date_cal) values(date_inf);
		set i:= date_inf;
		set date_deb :=date_inf;
		set date_fin :=date_sup;
		select date_inf,date_sup;
		BOUCLE: repeat
			set i := adddate(i,1);
			insert INTO t_date (date_cal)	values (i);
		until i = date_fin END repeat BOUCLE;
	else
		select max(date_cour) into date_sup from EDF_BIS;
		select max(date_cour) into date_inf from EDF_BIS where date_cour < date_sup;
		set i:= date_inf;
		set date_deb :=date_inf;
		set date_fin :=date_sup;
		BOUCLE: repeat
			set i := adddate(i,1);
			insert INTO t_date (date_cal)	values (i);
		until i = date_fin END repeat BOUCLE;
end if;
 
#### boucle generale ###########
boucle_gen: while date_deb < date_fin do
	select min(date_cour) into date_inf from EDF_BIS where date_cour >= date_deb;
	select min(date_cour) into date_sup from EDF_BIS where date_cour > date_inf;
	set date_deb := date_sup;
 
	select index_HC, index_HP  into index_HC_inf , index_HP_inf from EDF_BIS where date_cour = date_inf;
	select index_HC , index_HP into index_HC_sup , index_HP_sup from EDF_BIS where date_cour = date_sup;
 
 
	#   valeurs necessires pour démarer la boucle d'update
	#   1) la nombre de jours à "combler" , variable dure
	#   2) la date de la borne inferieure de la periode , variable date_inf
	#   3) la date de la borne superieure de la periode , variable date_sup
	#
	#   estimation de la consommation journalière
	#   on calcule la conso sur la periode et on divise par le nombre de jours de la période
	#   la consommation de la période index_sup - index_inf
	#   la consommation journalière estimée = consommation periode / durée periode 
	boucle_update: while date_inf < date_sup do
		set conso_HP := index_HP_sup - index_HP_inf;
		set conso_HC := index_HC_sup - index_HC_inf;
		set dure := datediff(date_sup,date_inf);
		set conso_HP_inter := round(conso_HP / dure);
		set conso_HC_inter := round(conso_HC / dure);
		#
		# on peut maintenant calculer les index estimés
		#
		set index_HP_inf := index_HP_inf + conso_HP_inter;
		set index_HC_inf := index_HC_inf + conso_HC_inter;
		#
		# on incrémente la date_inf
		set date_inf := adddate(date_inf,1);
		#
		# on a maintenant tout pour faire l'update
		#
		if date_inf < date_sup 
 			then 
				update t_date 
				set 	index_HP = index_HP_inf ,
					index_HC = index_HC_inf,
					conso_HP = conso_HP_inter,
					conso_HC = conso_HC_inter,
					type_enr ='ESTIME'
				where date_cal = date_inf ;
 			else
				update t_date 
				set 	index_HP = index_HP_inf ,
					index_HC = index_HC_inf,
					conso_HP = conso_HP_inter,
					conso_HC = conso_HC_inter,
					type_enr ='RELEVE_MANUEL'
				where date_cal = date_inf ;
		end if;
 
		#
		# on peut boucler
		#
	end while boucle_update;
end while boucle_gen;
 
 
END$$
 
DELIMITER ;

c'est surement trés maladroit et il manque encore pas mal de controles, mais c'est fonctionnel.
il y a encore des variables en trop, cela provient du fait que c'est la reunion de plusieurs petits bouts indépendats à l'origine
c'est avec plaisir que je lirai vos eventuels commentaires pour améliorer mon code
fred

insertion d'enregistrements manquants

Requêtes MySQL

Discussions similaires

Partager

Partager