[A-03] calcul d'allure

**thiefer** · 07/10/2008, 18h10

bonjour à tous,

je migre une application perso depuis Excel vers Access 2003.
Je passe plusieurs paramètres de sélection pour construire une requête SQL de statistiques.
L'un de ces paramètres est l'allure moyenne
c'est à dire
le temps au km

sous Excel, j'avais cette formule qui fonctionnait

Code :

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

ENT(([temps]*60*24)/[distance_km])+(MOD(([temps]*60*24)/[distance_km];1)/100*60)

J'ai remplacé les noms de cellules par celui de mes champs de tables Access pour plus de lisibilité

Je coince et ne parviens pas à la traduire en SQL

Est-ce que quelqu'un pourrait me dépanner svp

Merci par avance

TF

**thiefer** · 08/10/2008, 13h43

Bonjour à tous,

j'ai avancé sur le sujet mais je bloque toujours sur ceci

Code :

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

Round(Avg(Int(temps * 1440 / distance_km) + (0.6 * ((temps * 1440 / distance_km) - Int(temps * 1440 / distance_km)))),2) AS AllureMoy

Le code fonctionne en partie mais comptabilise les secondes en centièmes.
Je trouve ainsi des allures moyennes à 4.71 mn du km

Qu'est ce qui cloche dans ma formule svp

Merci d'avance

TF

**ESVBA** · 08/10/2008, 15h04

Quel est le format de stockage de [Temps] ?

Le champ [Temps]-il un format date ou autre...

**thiefer** · 08/10/2008, 15h26

Salut ESVBA,

"temps" est un champ en type de données date/heure au format heure, complet dans ma table tbl_donnees

Je crains que le problème ne vienne du fait que je veuille faire une moyenne et que la mise en base 60 (*0.6) ne soit alors plus prise en compte.

J'ai donc aussi essayé d'arrondir temps - int(temps) pour éviter d'avoir à traiter des décimales à plus de deux chiffres mais toujours sans succès

Code :

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

Round(Avg(Int(temps * 1440 / distance_km) + Round((0.6 * ((temps * 1440 / distance_km) - Int(temps * 1440 / distance_km))),2)),2)

Merci de ton aide,

TF

**ESVBA** · 08/10/2008, 17h26

Le format stocke les dates et heures sous la formed 'un fraction d'un nombre de jours.
La valeur "1" correspond à 24 heures
"1,5" correspond à 36 heures...

Il te suffit de faire tes calcals simplement et de demander d'afficher dns le bon format type "mm:ss".

A moins que tu veuilles en fraction décimale de minutes. Non explicité de ta part.

Je n'ai pas relu ton premier post

Désolé, je dois m'absneter, je reviendrais

**thiefer** · 08/10/2008, 18h08

Salut ESVBA et merci de ton retour,

même si je ne suis pas certain de tout avoir saisi...
En table, les temps stockés sont par exemple pour juillet 2003 :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
sport          date            temps        distance_km
course         01/07/2003      00:51:13
course         03/07/2003      00:48:30
course         05/07/2003      00:58:45
course         07/07/2003      00:35:18
course         08/07/2003      00:27:20
course         10/07/2003      00:53:48
course         13/07/2003      00:47:26
course         16/07/2003      01:27:42     14,5
course         25/07/2003      01:39:55     15,7
course         28/07/2003      00:28:50      5,95
course         29/07/2003      01:13:59     12
course         31/07/2003      01:23:00

Je cherche à calculer l'allure moyenne, c'est à dire, le nombre de minute moyen par kilomètre parcouru quand temps et distance sont renseignés évidemment pour ne pas fausser les valeurs par des champs vides ou nuls.

Je dois donc travailler depuis la somme des temps concernés et la somme des distance en question.

La valeur moyenne trouvée doit donc être de la forme 4.25mn/km

Les formules que j'ai écrites fonctionne sur un enregistrement mais pas sur la somme de plusieurs enregistrements

Une idée pour simplifier mon calcul s'il faut le simplifier (ce serait tant mieux !!!) ou pour résoudre mon énigme ?

Merci par avance

TF

**ESVBA** · 08/10/2008, 22h18

Je pense avoir la solution (vérifiée sur Excel) :

Il faut pour éliminer les valeurs nulles ou absentes utiliser une fonction de domaine. Dans notre cas "MoyDom()" qui utilise un champ pour le calcul, une table et un critère.

Malheureusement, il n'est pas possible à ma connaisance d'utiliser la fonction de domaine sur des valeurs qui ne sont pas encore calculées. Donc impossible de mettre la formule de conversion (calcul) dans la fonction "MoyDom()".

Je passe donc pas une requête intermédiaire.

La requête 1 "Requete1" (sans accent) va me faire le calcul du temps au kilomètre (résultat dans [min_au_km]) à partir des valeurs dans la table nommée "Table1". Voici la requête SQL :

Code :

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

1
2
3
 
SELECT Table1.*, 24*60*[Temps]/[Distance_km] AS min_au_km
FROM Table1;

La seconde requête "Requete2" (sans accent) va calculer la moyenne sur les valeurs de [min_au_km]. Voici la requête SQL :

Code :

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

1
2
3
 
SELECT Table1.*, DAvg("[min_au_km]","Requete1","[Distance_km] > 0") AS performance
FROM Table1;

La moyenne est obtenue dans la colonne "Performance" (et correspond à ma moyenne dans excel).

En espérant t'être utile.

**thiefer** · 09/10/2008, 11h20

Salut ESVBA,

ta solution est intéressante mais comment l'appliquer dans mon cas ?
En effet, je vais chercher ma formule sql dans une table tbl_formula de manière à construire une chaîne strSQL depuis une boucle for...next d'un formulaire de sélection de paramètres.

Code :

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

SELECT Year(date) AS Annee, Month(date) AS Mois, Int(((Avg((temps*86400) / (distance_km))) Mod 3600)/60) & "," & Format(((Avg((temps*86400) / (distance_km))) Mod 3600/60),"00") As AllureMoy FROM tbl_donnees WHERE ((date >=#01/07/2003# AND date <=#31/07/2003# AND sport Like "course")) GROUP BY Year(date), Month(date);

J'ai donc essayé de travailler directement en secondes plutôt que de rester en minute, puis de reconvertir à l'aide des instructions format.
Cela semble donner de meilleurs résultats que précédemment

Code :

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

Int(((Avg((temps*86400) / (distance_km))) Mod 3600)/60) & "," & Format(((Avg((temps*86400) / (distance_km))) Mod 3600/60),"00") As AllureMoy

Sur Juillet 2003, je sors une allure moyenne de 5,51 (mn au km)

Cependant, lorsque je check sur Excel, je trouve pour les valeurs non nulles de distance un temps total de 04:50:26 ce qui semble correspondre pour les 48.15km concernés à une allure de 6.02 mn au km.

Qu'est ce qui te semblerait pouvoir justifier ces 11 seoncdes d'écart ?
Qui a t'il de faux dans mon raisonnement et dans le formule appliquée ?

Merci par avance

TF

**ESVBA** · 09/10/2008, 23h26

La structure serait du type pour une réponse en minute et fraction de minute :

Code :

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

1
2
3
4
 
SELECT Avg(24*60*Temps/Distance_km) AS min_au_km
FROM tbl_donnees
WHERE (((tbl_donnees.[Distance_km])>0));

Remarque :
Il serait intéressant de remplacer dans la requête le "," par ":" car la virgule exprime une fraction de l'unité. "10,5 min" équivaut à "0:10:30"

Est-ce bon ?
Avec Excel, je trouve "5,85" soit "0:5:51" en réalisant la moyenne des "min/km" (comme fait Access dans nos requêtes, calcul de la division puis la moyenne) et "6:02" en réalisant la moyenne des temps divisé par la distance totale parcourue.

Je ne veux pas trop m'avancer mais par analogie à la physique : la valeur calculée n'est autre que l'inverse d'une vitesse moyenne.

Le "5,85" correspond à la moyenne à chaque instant (pour chaque jour).
Et le "6:02" à la moyenne globale (du mois). Ce que que tu recherches ?

De plus, on ne peut pas faire machine arrière, la moyenne n'est qu'une représentation d'une population de valeurs pas forcément proches. On peut avoir rouler en moyenne à 90km/h (très bien!) mais avoir fait des pointes à 160 km/h et avoir subit des bouchons.

Pour ce qui est de la précision de la mesure, je n'ai pas de réponse.
En testant dans Excel :

0:00:01 = 0,0000115740740740741 (double précision mais dans Access ?)
les fractions de seconde n'existe pas (je croyais à un format "0:00:00.012")
Donc tout nombre en dessous d'une seconde est arrondi. Et comme dans le cas de "5:51" on fait autant de division qu'il y a de valeurs... Bonjour la précision

En espérant avoir répondu sans poser un autre problème. Bonne nuit.

**thiefer** · 10/10/2008, 14h45

Ouch !!!
Compris !!! Mais j'avoue avoir eu du mal ;-)))

Il effectue donc la moyenne pour tous les enregistrements de la somme des produits temps sur distance par enregistrement
c'est à dire :

Code :

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

1
2
3
4
5
6
7
 Temps     Distance  Temps*24*60/Distance
01:27:42    14,500        6,04
01:39:55    15,700        6,36
00:28:50    5,950         4,84
01:13:59    12,000        6,16

               Moyenne    5,85(...59027)

J'ai donc un problème bigger than expected...

En effet, voici une requête générée pour stats sur temps total et moyen, distance totale et moyenne, vitesse et allure sur juillet 2003

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
SELECT 
Year(date) AS Annee, 
Month(date) AS Mois, 
Int((Sum(temps*86400))/3600) & ":" & Format(Int(((Sum(temps*86400)) Mod 3600)/60),"00") & ":" & Format(((Sum(temps*86400)) Mod 3600/60),"00") AS TempsTotal, 
Int((Avg([temps]*86400))/3600) & ":" & Format(Int(((Avg([temps]*86400)) Mod 3600)/60),"00") & ":" & Format(((Avg([temps]*86400)) Mod 3600/60),"00") AS TempsMoy, 
Sum(Distance_km) AS DistTot, 
Round(Avg(Distance_km),3) AS DistMoy, 
Round(Avg([Distance_km]/([Temps]*24)),2) AS VitesseMoy, 
Int(((Avg((temps*86400) / (distance_km))) Mod 3600)/60) & "," & Format(((Avg((temps*86400) / (distance_km))) Mod 3600/60),"00") As AllureMoy 
FROM 
tbl_donnees 
WHERE 
((date >=#01/07/2003# AND date <=#31/07/2003# AND sport Like "course")) 
GROUP BY 
Year(date), Month(date);

Cette requête me retourne ceci

Code :

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

1
2
Annee    Mois    TempsTotal    TempsMoy    DistTot    DistMoy    VitesseMoy    AllureMoy       
2003      7      11:35:46      0:57:59      48,15      12,038      10,37        5,51

Vitesse moyenne et allure moyenne sont donc calculées pareillement et ne considèrent pas simplement les valeurs de temps pour lesquelles distance est non nulle.

Tu l'auras compris, je ne peux pas utiliser la condition where distance<>0 car sinon je plante mes calculs temps total et autres !!!

Donc il me faut trouver une solution en ne jouant que (je crois en tout cas) sur la clause select qui est un champ que je vais chercher dans une table

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
    ' construit les clauses "Select" via strChamps et "group by" via strGroup
    Dim strChamps   As String
    Dim strGroup    As String
    Dim strSELECT   As String
    Dim strgroupby  As String
 
    Dim ctl As Control
 
    For Each ctl In Me.Controls
 
        Select Case Left(ctl.Name, 6)
            Case "chk_rg"
                If ctl.Value = -1 Then
                    nbChoixRgpt = nbChoixRgpt + 1
                    strSELECT = CurrentProject.Connection.Execute("select str_select from tbl_formula where control_name = '" & ctl.Name & "'")!str_select
                    strgroupby = CurrentProject.Connection.Execute("select str_groupby from tbl_formula where control_name = '" & ctl.Name & "'")!str_groupby
 
                    If nbChoixRgpt = 1 Then
                        strChamps = strChamps & strSELECT
                        strGroup = strGroup & strgroupby
                    Else
                        strChamps = strChamps & ", " & strSELECT
                        strGroup = strGroup & ", " & strgroupby
                    End If
 
                End If
 
            Case "chk_st"
                If ctl.Value = -1 Then
                    nbChoixStat = nbChoixStat + 1
                    strSELECT = CurrentProject.Connection.Execute("select str_select from tbl_formula where control_name = '" & ctl.Name & "'")!str_select
 
                    If strSELECT <> "" Then
                        strChamps = strChamps & ", " & strSELECT
                    End If
                End If
 
        End Select
 
    Next ctl

Arrachage des trois cheveux qu'il me reste !!!

HELP

TF

**thiefer** · 13/10/2008, 18h34

Bonsoir à tous,

j'ai fait de nouvelles tentatives en incluant les fonctions de domaines de la façon suivante

Code :

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

1
2
3
4
5
6
7
SELECT 
Year(date) AS Annee, 
Month(date) AS Mois, 
((DSum("[temps]","tbl_donnees","[distance_km]>0"))*24*60) / (DSum("[distance_km]","tbl_donnees","[temps]>0")) As AllureMoy 
FROM tbl_donnees 
WHERE ((date >=#01/07/2003# AND date <=#31/07/2003# AND sport Like "course")) 
GROUP BY Year(date), Month(date);

Mais encore une fois, il reste un problème.
Sans parler du format d'allure en centiminute que je sais régler mais qui rend la fomule totalement illisible, il semble que les clauses de restriction des fonctions de domaine DSum ne font aucun cas des clauses where et group by qui suivent.
Le calcul d'allure moyenne est donc réalisé pour l'ensemble des valeurs temps et distance_km en base et non pas sur le périmètre que j'ai défini.

Est-ce que quelqu'un saurait m'aider à me sortir de ce sac de noeud svp,

Merci par avance,

TF

**Pierre Fauconnier** · 13/10/2008, 20h05

Bonsoir

Je ne comprends pas pourquoi utiliser des fonctions de domaine à l'intérieur de la requête.

Voici une requête qui, hors la clause where, semble retourner les bonnes valeurs.

Code :

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

1
2
3
SELECT year(date) as Annee, month(date) as Mois, Sum(temps) AS [Temps total], Avg(temps) AS [temps moyen], Sum(distance_km) AS [Distance totale], Avg(distance_km) AS [distance moyenne], Avg([distance_km]/([temps]*24)) AS [Vitesse moyenne], Avg([temps]*60*24/[distance_km]) AS [Allure moyenne]
FROM Feuil1 where year(date)=2003 and month(date)=7 
GROUP BY year(date), month(date);

**thiefer** · 13/10/2008, 20h43

Bonsoir,

ce qui se passe, c'est que je cherche à ce que vitesse et allure moyennes soient calculées seulement sur les enregistrements pour lesquels distance et temps soient renseignés.
Or la formule proposée, qui était celle sur laquelle j'avais débuté réalise sur la base de données suivante

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
sport          date            temps        distance_km
course         01/07/2003      00:51:13
course         03/07/2003      00:48:30
course         05/07/2003      00:58:45
course         07/07/2003      00:35:18
course         08/07/2003      00:27:20
course         10/07/2003      00:53:48
course         13/07/2003      00:47:26
course         16/07/2003      01:27:42     14,5
course         25/07/2003      01:39:55     15,7
course         28/07/2003      00:28:50      5,95
course         29/07/2003      01:13:59     12
course         31/07/2003      01:23:00

le résultat suivant

Code :

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

1
2
3
4
5
6
7
 Temps     Distance  Temps*24*60/Distance
01:27:42    14,500        6,04
01:39:55    15,700        6,36
00:28:50     5,950        4,84
01:13:59    12,000        6,16
 
               Moyenne    5,85(...59027)

et non pas

Code :

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

1
2
3
4
5
6
7
 Temps     Distance  Temps*24*60/Distance
01:27:42    14,500        
01:39:55    15,700        
00:28:50     5,950        
01:13:59    12,000        
========    ======
04:50:26    48,150        6,03

C'est exactement le même problème pour la vitesse et on a fait pas mal d'essai dans un sens puis dans l'autre sans trouver l'issue...

TF

**Pierre Fauconnier** · 13/10/2008, 20h49

Le fichier allures.mdb avec la requête qui donne 5.85...

Mais, si j'ai bien compris, tu veux l'allure moyenne et pas la moyenne des allures. C'est cela? Pour moi, statistiquement, c'est moins correct...

Voila le fichier allures1.mdb qui donne 6.03

**thiefer** · 14/10/2008, 10h51

Bonjour et merci de ton aide,

peut-être un fichier attaché sera t'il plus parlant !!!

Nous avions précédemment également envisagé de placer en clause "where" la condition mais cela ne s'applique pas à mon cas de figure pour les raisons expliquées en détail au post n°10 (car il y d'autres indicateurs calculés, eux, sur l'ensemble des enregistrements en base).
Je souhaite donc savoir s'il m'est possible de paramétrer dans ma seule clause "Select" le fait de ne pas considérer tous les enregistrements de la base.
C'est pour cela que je m'étais aventuré sur les terrains du Dsum afin d'ajouter une clause de restriction dans le select, mais là, je perds les clauses de restrictions générales de la clause "where" de l'instruction SQL

Question fiabilité de la stat, 6.03 semble être plus appropriée que 5.85 pour un global, cad la somme de mes temps sur la somme de mes distances, car en calculant la moyenne des allures, le calcul n'est pas pondéré par la distance parcourue, alors qu'il l'est pour le calcul de l'allure moyenne.
Tout dépend donc de la réponse attendue :
- Quelle sont mes vitesse/allure moyenne à chacune de mes sorties => vitesse/allure moyenne des vitesse/allures ; cela ne prend pas en compte la durée de la sortie
- Quelle sont mes vitesse/allure moyenne => vitesse/allure moyenne ; cela prend en compte la durée de la sortie.
Dans le même temps, si je veux mon allure sur l'une ou l'autre sortie, je consulte ma base par enregistrement et non plus pas somme d'enregistrements.

Peut-être est-ce le principe de fonctionnement qui n'est pas le bon.
J'ai réalisé un formulaire de sélection pour définir mes critères de sélection, de regroupement et de calculs statistiques.
De là, je souhaite écrire une requête SQL de création/maj de table tbl_stats avec l'ensemble des champs calculés retournés.
Puis, je pensais (pas encore fait) pointer un sous-formulaire résultant en mode formulaire croisé dynamique pour permettre la mise ne page et l'analyse de ces informations.

C'est peut-être cette construction qui est à revoir s'il n'est pas possible deparvenir à résoudre mon problème de calcul...

TF

**thiefer** · 16/10/2008, 15h15

Bonjour à tous,

j'ai essayé une nouvelle solution qui aurait consisté à créer une seconde table temporaire en parallèle depuis une requête création de table faisant mention des seuls enregistrements pour lesquels distance_km et temps seraient tous deux renseignés et fonction des clauses de la première requête.
Malheureusement, je n'ai pas trouvé de moyen de faire le rapprochement entre les enregistrements de mes deux requêtes.

Est-ce que sur la base du fichier attaché au post précédent ou sur une autre idée de traitement différent, quelqu'un saurait me dire comment obtenir le résultat que je cherche à atteindre sur mes calculs de vitesse et allure moyennes ?

Merci par avance de votre aide,

TF

**thiefer** · 20/10/2008, 10h18

Bonjour à tous,

après maintes réflexion, je pense que ma solution passe par une requête création de table (temporaire) dans laquelle je puisse disposer de deux champs différents :
la somme de tous les temps (TempsTotal)
la somme de tous les temps pour une distance non nulle (TempsDist)

pour le premier, pas de problème, pour le second champ, c'est déjà plus délicat...

Code :

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

1
2
3
4
5
6
7
8
9
SELECT 
Year([date]) AS Annee, 
Month([date]) AS Mois, 

Sum(tbl_donnees.temps) AS TempsTotal

FROM tbl_donnees 

GROUP BY Year([date]), Month([date]);

Quelqu'un aurait-il une petite idée pour m'aider svp ?

Merci d'avance,

TF

**thiefer** · 20/10/2008, 15h13

Salut à tous,

je suis enfin parvenu à mes fins.
Au bord du désespoir absolu, l'illumination m'est enfin parvenue.

Débutant que je suis en SQL et Access, je ne connais par toutes les subtilités de langages donc, ce problème m'a permis d'en découvrir plusieurs comme les fonctions de domaine par exemple, mais aussi et surtout, la solution à mon problème passant par l'instruction IIf !!!

J'ai donc pu ajouter une condition hors clause where dans ma requête SQL, directement dans mon select en créant deux nouveaux champs calculés et en calculant mes champs résultants depuis ces deux nouveaux champs calculés.
Ce qui donne

Code :

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

1
2
3
4
5
Select
Sum(IIf(IsNull(distance_km),0,temps)) AS TpsDist, 
Sum(IIf(IsNull(temps),0,distance_km)) AS DistTps,
Round(([DistTps]/([TpsDist]*24)),3) AS VitesseMoy, 
Format(Int(((([TpsDist]*86400)/[DistTps]) Mod 3600)/60),"00") & ":" & Format(((([TpsDist]*86400)/[DistTps]) Mod 3600/60),"00") AS AllureMoy

Merci à tous ceux qui m'ont donné la main sur ce point qui m'en a fait bavé des ronds de cuir !!!

@+ tard

TF

[A-03] calcul d'allure

Requêtes et SQL.

Discussions similaires

Partager

Partager