Forums PostgreSQL.fr

damalaan

Membre

Calcul d'écart entre chaque enregistrement

Bonjour,

Voici un extrait de données

trn_date	trn_tr	trn_us	mg_mat	mg_val	écart
90201	140	1711	1003	39.9	
90202	140	1711	1003	41.6	1.7
90203	140	1711	1003	40.2	-1.4
90301	140	1711	1003	39.1	-1.1
90302	140	1711	1003	39.7	0.6
90303	140	1711	1003	40.3	0.6
90201	135	1711	1005	42.2	
90202	135	1711	1005	42.1	-0.1
90203	135	1711	1005	42.1	0
90301	135	1711	1005	40.9	-1.2
90302	135	1711	1005	40.6	-0.3
90303	135	1711	1005	39.9	-0.7
90201	104	1711	1010	42.5	
90202	104	1711	1010	42.8	0.3
90203	104	1711	1010	42.1	-0.7
90301	104	1711	1010	41	-1.1
90302	104	1711	1010	41.5	0.5
90303	104	1711	1010	41.8	0.3
90201	141	1711	1014	41.9	
90202	141	1711	1014	41.4	-0.5
90203	141	1711	1014	43.5	2.1
90301	141	1711	1014	37.9	-5.6
90302	141	1711	1014	39.4	1.5
90303	141	1711	1014	40.9	1.5
90201	132	1711	1016	42.9	
90202	132	1711	1016	41.6	-1.3
90203	132	1711	1016	39.2	-2.4
90301	132	1711	1016	37.5	-1.7
90302	132	1711	1016	37.6	0.1
90303	132	1711	1016	38	0.4
90201	141	1711	1019	44	
90202	141	1711	1019	43.8	-0.2
90203	141	1711	1019	44.4	0.6
90301	141	1711	1019	41.4	-3
90302	141	1711	1019	42	0.6
90303	141	1711	1019	41.2	-0.8

Ce que je veux faire, c'est calculer la différence qu'il y a entre 2 enregistrements consécutifs mg_val tant que mg_mat = mgmat-1, tout cela classer par date

Mon souci est que dans ma base j'ai plus de 400000 lignes et qu'elle grossit de 20000 env par mois; j'ai fait un essai avec des select imbriqués, sur 50 valeurs ça marche, mais évidemment sur 400000 c'est pas la peine.

Actuellement, cette appli tourne sur Access et je fais mon calcul en VBA : à chaque import, je recalcule tout et je stocke la valeur calculée dans la table (pas top!!).

Auriez-vous une solution sous Postgresql un peu plus propre?
Le but final de cette appli étant de faire des stats sur ces écarts calculés sur une durée que l'utilisateur définit lui même.

Merci d'avance

Marc Cousin

Membre

Re : Calcul d'écart entre chaque enregistrement

Ce qu'il vous faut, c'est les fonctions à fenêtrage. Elles sont disponibles à partir de PostgreSQL 8.4. Est-ce votre cas ?

---

Marc.

damalaan

Membre

Re : Calcul d'écart entre chaque enregistrement

je suis sous la version 9.0........et je débute donc je ne connais pas ces fonctions..........

Marc Cousin

Membre

Re : Calcul d'écart entre chaque enregistrement

Pas de souci, je voulais juste être sûr que vous aviez une version suffisante, histoire de ne pas vous allécher inutilement

Pour commencer, voici un tutorial :
http://docs.postgresql.fr/9.0/tutorial-window.html

Vous aurez besoin de lag ou de lead:
http://docs.postgresql.fr/9.0/functions-window.html

Si vous avez besoin de davantage d'aide, n'hésitez pas à demander. Mais commencez par lire ces deux pages.

---

Marc.

damalaan

Membre

Re : Calcul d'écart entre chaque enregistrement

comment dire...............parfait!!!!
quand je pense le temps que j'ai passé à faire ça sous access!! et là ça se fait tout seul (ou presque)
en cherchant sur le net j'ai trouvé ce tuto : http://lalystar.developpez.com/fonction … ques/#L3.2 (c'est pour Oracle, mais les fonctions des 2 sgbd étant proches, cela m'a plus aidé que la doc de postgresql)

pour l'instant je sors les valeurs en un peu plus de 1100 ms, mais je n'ai que 24000 lignes, et je suis sur un netbook; je teste dès que je peux en réel!!

voici quand même mon code :

SELECT 
  tbl_tournee_trn.trn_date_prel, 
  tbl_tournee_trn.trn_tournee, 
  tbl_tournee_trn.trn_usine, 
  tbl_valeur_mg.mg_matricule, 
  tbl_valeur_mg.mg_val,
  tbl_valeur_mg.mg_val - lag (tbl_valeur_mg.mg_val, 1) 
  over (partition by tbl_valeur_mg.mg_matricule
  order by tbl_tournee_trn.trn_date_prel) ecart
FROM 
  tbl_valeur_mg, 
  tbl_tournee_trn
WHERE 
  tbl_tournee_trn.trn_id = tbl_valeur_mg.trn_id ;

encore merci
plus ça va plus j'aime postgreSQL!!

Marc Cousin

Membre

Re : Calcul d'écart entre chaque enregistrement

Bienvenue au club

---

Marc.

meles

Membre

Re : Calcul d'écart entre chaque enregistrement

voici quand même mon code :

SELECT 
  tbl_tournee_trn.trn_date_prel, 
  tbl_tournee_trn.trn_tournee, 
  tbl_tournee_trn.trn_usine, 
  tbl_valeur_mg.mg_matricule, 
  tbl_valeur_mg.mg_val,
  tbl_valeur_mg.mg_val - lag (tbl_valeur_mg.mg_val, 1) 
  over (partition by tbl_valeur_mg.mg_matricule
  order by tbl_tournee_trn.trn_date_prel) ecart
FROM 
  tbl_valeur_mg, 
  tbl_tournee_trn
WHERE 
  tbl_tournee_trn.trn_id = tbl_valeur_mg.trn_id ;

encore merci
plus ça va plus j'aime postgreSQL!!

Bonjour, pouquoi ne pas utiliser une jointure de style inner join plutot que la clause where ?

Est ce que :

SELECT 
  tbl_tournee_trn.trn_date_prel, 
  tbl_tournee_trn.trn_tournee, 
  tbl_tournee_trn.trn_usine, 
  tbl_valeur_mg.mg_matricule, 
  tbl_valeur_mg.mg_val,
  tbl_valeur_mg.mg_val - lag (tbl_valeur_mg.mg_val, 1) 
  over (partition by tbl_valeur_mg.mg_matricule
  order by tbl_tournee_trn.trn_date_prel) ecart
FROM 
  tbl_valeur_mg inner join tbl_tournee_trn using (trn_id);

fonctionne ? mieux ?

Cordialement

Marc Cousin

Membre

Re : Calcul d'écart entre chaque enregistrement

Les deux sont strictement identiques, au niveau sémantique. Je préfère aussi la syntaxe JOIN, mais cela n'a aucun impact sur le plan d'exécution.

---

Marc.

damalaan

Membre

Re : Calcul d'écart entre chaque enregistrement

Effectivement la jointure est plus judicieuse surtout que j'ai conçu ma base de cette manière!!
je suis à 120000 lignes et 8s de temps de réponse, pas mal!