Calcul conso moyenne

**milka** · 07/10/2010, 10h11

Bonjour,

petit exercice :

j'ai une voiture dont je connais tous les parcours en nombre de km. La plupart du temps j'ai noté après un parcours
-la quantité de fuel à l'arrivée,
-la quantité de fuel pris,
-la quantité de fuel après prise.
Mon objectif est de calculer la consommation de fuel de mon véhicule pour chaque parcours réalisé.
Lorsque je n'ai pas récupéré les informations après un parcours, je dois proratisé en fonction du nombre de km parcouru sur tous les parcours compris entre les 2 récupérations d'informations.
Quelqu'un aurait-il une idée ?

table:

Code :

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

1
2
3
4
5
6
7
8
9
10
 
tb_parcours :
id_parcours number(10),
date date,
nb_km number(4),
info_recuperee number(1),
fuel_avant_prise number(4),
fuel_pris number(4),
fuel_avant_depart number(4),
conso_a_calculer number(6,2)

fuel_avant_prise=fuel_pris+fuel_avant_depart

Les infos fuel sont des infos avant le parcours, elles sont à 0 si je n'ai pas récupéré les informations et info_recupere=0.

je m'acharne actuellement avec la fonction lead mais sans succès.

Merci d'avance pour votre précieuse aide.

Code :

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

1
2
3
4
5
6
7
8
9
10
with tb_parcours as 
(
select To_date ('23/04/2010 07:41:00', 'DD/MM/YYYY hh24:mi:ss') Ma_date, 57 nb_km, 1 info, 3459 fuel_avant_prise, 1344 fuel_pris, 4800 fuel_avant_depart, 0 conso_a_calculer FROM Dual Union ALL
select To_date ('23/04/2010 14:37:00', 'DD/MM/YYYY hh24:mi:ss') Ma_date, 83, 1, 3080, 2919, 6000,0 FROM Dual Union ALL
select To_date ('23/04/2010 16:40:00', 'DD/MM/YYYY hh24:mi:ss') Ma_date, 84, 1, 3634, 2372, 6000,0 FROM Dual Union ALL
select To_date ('23/04/2010 18:47:00', 'DD/MM/YYYY hh24:mi:ss') Ma_date, 58, 1, 3339, 3665, 7000,0 FROM Dual Union ALL
select To_date ('24/04/2010 04:50:00', 'DD/MM/YYYY hh24:mi:ss') Ma_date, 50, 0, 0, 0, 0,0 FROM Dual Union ALL
select To_date ('24/04/2010 06:23:00', 'DD/MM/YYYY hh24:mi:ss') Ma_date, 63, 1, 3606, 3704, 7300,0 FROM Dual Union ALL
select To_date ('24/04/2010 08:32:00', 'DD/MM/YYYY hh24:mi:ss') Ma_date, 69, 1, 3970, 1426, 5400,0 FROM Dual
)

La formule que je souhaite utiliser est :
parcours 1= (4800-(6000-2919))*57/(57)=1720
parcours 4= (7000-(7300-3704))*58/(58+50)=1828
parcours 5= (7000-(7300-3704))*50/(58+50)=1576

**Waldar** · 07/10/2010, 14h48

Il ne manque pas des informations dans le jeu de test ?
Comment identifier parcours 1, 4 et 5 ?

**milka** · 07/10/2010, 15h04

effectivement, j'avais oublié l'id parcours :

Code :

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

1
2
3
4
5
6
7
8
9
10
with tb_parcours as 
(
select 1 id_parcours, to_date ('23/04/2010 07:41:00', 'DD/MM/YYYY hh24:mi:ss') Ma_date, 57 nb_km, 1 info, 3459 fuel_avant_prise, 1344 fuel_pris, 4800 fuel_avant_depart, 0 conso_a_calculer FROM Dual Union ALL
select 2, to_date ('23/04/2010 14:37:00', 'DD/MM/YYYY hh24:mi:ss') Ma_date, 83, 1, 3080, 2919, 6000,0 FROM Dual Union ALL
select 3, to_date ('23/04/2010 16:40:00', 'DD/MM/YYYY hh24:mi:ss') Ma_date, 84, 1, 3634, 2372, 6000,0 FROM Dual Union ALL
select 4, to_date ('23/04/2010 18:47:00', 'DD/MM/YYYY hh24:mi:ss') Ma_date, 58, 1, 3339, 3665, 7000,0 FROM Dual Union ALL
select 5, to_date ('24/04/2010 04:50:00', 'DD/MM/YYYY hh24:mi:ss') Ma_date, 50, 0, 0, 0, 0,0 FROM Dual Union ALL
select 6, to_date ('24/04/2010 06:23:00', 'DD/MM/YYYY hh24:mi:ss') Ma_date, 63, 1, 3606, 3704, 7300,0 FROM Dual Union ALL
select 7, to_date ('24/04/2010 08:32:00', 'DD/MM/YYYY hh24:mi:ss') Ma_date, 69, 1, 3970, 1426, 5400,0 FROM Dual
)

**Waldar** · 07/10/2010, 15h21

Ok, par contre je n'ai pas compris les règles de calcul que vous désirez appliquer !

Si vous pouviez détailler un peu plus !

**milka** · 07/10/2010, 16h07

En fait, il faut pour chaque parcours se servir des infos qu'on a sur le prochain parcours.

Code :

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

1
2
3
4
 
parcours 1= (4800-(6000-2919))*57/(57)=1720
parcours 4= (7000-(7300-3704))*58/(58+50)=1828
parcours 5= (7000-(7300-3704))*50/(58+50)=1576

parcours 1=
4800 est la quantité de fuel avant de réaliser le parcours
6000 est la quantité de fuel avant de réaliser le prochain parcours avec infos
2919 est la quantité de fuel prise avant le prochain parcours avec infos
57 est le nombre de km du parcours
57 est le nombre total de km entre le parcours et le prochain parcours avec infos

comme (fuel à l'arrivée + fuel pris) est légèrement différent de fuel_depart,
je n'utilise pas la formule (fuel au départ - fuel à l'arrivée du prochain parcours).

difficile d'être très clair ...

requete qui fonctionne quand on a les infos sur le parcours suivant:

Code :

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

1
2
3
4
5
6
7
select id_parcours,
       info,
       fuel_avant_depart
       -(lead(fuel_avant_depart,1) over( order by ma_date)
         -lead(fuel_pris,1) over( order by ma_date)
       )conso
from tb_parcours

**milka** · 11/10/2010, 13h48

Mon problème est-il assez bien exposé ?
Je ne parviens toujours pas à calculer la conso lorsqu'il manque des infos sur les 1 à n parcours suivants.
d'avance merci à tous ceux qui pencherons sur le sujet.

**xdescamp** · 11/10/2010, 15h59

Voici un premier jet, en avouant que je n'ai testé que les cas de ton jeu d'essai

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
WITH tb_parcours AS 
(
SELECT 1 id_parcours, to_date ('23/04/2010 07:41:00', 'DD/MM/YYYY hh24:mi:ss') Ma_date, 57 nb_km, 1 info, 3459 fuel_avant_prise, 1344 fuel_pris, 4800 fuel_avant_depart, 0 conso_a_calculer FROM Dual Union ALL
SELECT 2, to_date ('23/04/2010 14:37:00', 'DD/MM/YYYY hh24:mi:ss') Ma_date, 83, 1, 3080, 2919, 6000,0 FROM Dual Union ALL
SELECT 3, to_date ('23/04/2010 16:40:00', 'DD/MM/YYYY hh24:mi:ss') Ma_date, 84, 1, 3634, 2372, 6000,0 FROM Dual Union ALL
SELECT 4, to_date ('23/04/2010 18:47:00', 'DD/MM/YYYY hh24:mi:ss') Ma_date, 58, 1, 3339, 3665, 7000,0 FROM Dual Union ALL
SELECT 5, to_date ('24/04/2010 04:50:00', 'DD/MM/YYYY hh24:mi:ss') Ma_date, 50, 0, 0, 0, 0,0 FROM Dual Union ALL
SELECT 6, to_date ('24/04/2010 06:23:00', 'DD/MM/YYYY hh24:mi:ss') Ma_date, 63, 1, 3606, 3704, 7300,0 FROM Dual Union ALL
SELECT 7, to_date ('24/04/2010 08:32:00', 'DD/MM/YYYY hh24:mi:ss') Ma_date, 69, 1, 3970, 1426, 5400,0 FROM Dual
)
select id_parcours, ma_date, nb_km, info, fuel_pris, fuel_avant_depart,
       id_ref_km, nb_km_ref, fuel_avant_depart_ref_km,
       id_ref_fuel, fuel_pris_ref, fuel_avant_depart_ref_fuel,
       fuel_avant_depart_ref_km
       -(lead(fuel_avant_depart_ref_fuel,1) over( ORDER BY ma_date)
         -lead(fuel_pris_ref,1) over( ORDER BY ma_date)
       )conso_fuel,
       ( fuel_avant_depart_ref_km
        -(lead(fuel_avant_depart_ref_fuel,1) over( ORDER BY ma_date)
          -lead(fuel_pris_ref,1) over( ORDER BY ma_date)
         )) * nb_km / nb_km_ref conso_par_km
from (
      select id_parcours, ma_date, nb_km, info, fuel_pris, fuel_avant_depart,
             id_ref_km,
             sum(nb_km) over (partition by (id_ref_km)) nb_km_ref,
             id_ref_fuel,
             sum(fuel_pris) over (partition by (id_ref_fuel)) fuel_pris_ref,
             sum(fuel_avant_depart) over (partition by (id_ref_km)) fuel_avant_depart_ref_km,
             sum(fuel_avant_depart) over (partition by (id_ref_fuel)) fuel_avant_depart_ref_fuel
      from (select id_parcours, ma_date, nb_km, info, fuel_pris, fuel_avant_depart,
                   max(id_ref) over (order by id_parcours asc) id_ref_km,
                   min(id_ref) over (order by id_parcours desc) id_ref_fuel
            from (select id_parcours, ma_date, nb_km, info, fuel_pris, fuel_avant_depart,
                         decode (info, 1, id_parcours, to_number (null)) id_ref
                  from tb_parcours t
                  ) v1
            ) v2
      ) v3
order by id_parcours

Ok, ça n'est pas très lisible, mais je pense que l'idée est là :
- v1 sert ne garder que les id_parcours pour lesquels les infos sont renseignées (id_ref)
- v2 sert à déterminer le dernier id_parcours pour lequel on a les infos (id_ref_km) et le premier pour lequel on retrouve de nouveau les infos (id_ref_fuel) quand il y a un trou (info = 0)
- v3 sert à combler les trous pour les infos manquantes : somme des KM (pour la division plus tard), fuel avant départ des 2 parcours avec info (fuel_avant_depart_ref_km et fuel_avant_depart_ref_fuel) et fuel pris au prochain parcours.
- Ensuite j'applique ta formule avec le résultat de v3.

Désolé pour les id_ref_km et id_ref_fuel dont le nom n'est pas très significatif, mais au départ je n'avais pas percuté qu'il me fallait garder le fuel avant départ de la ligne 4 pour calculer la ligne 5, et je ne gardais de 4 que les KM, d'où le nom. Et j'ai la flemme de changer...

J'espère que ça te donne un bon point de départ pour faire qq chose de propre.

**xdescamp** · 15/10/2010, 11h12

Est-ce que la solution proposée donne des résultats satisfaisants ?

Calcul conso moyenne

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager