Rechercher des périodes vérifiant un critère sur des dates consécutives

**f-leb** · 18/05/2011, 22h18

Bonjour à tous,

Je viens de retrouver une archive d’un ancien problème que je voudrais vous soumettre comme contribution…

Pour simplifier, je prends une table de cotation comprenant les cours d’un certain nombre d’actions relevés quotidiennement.

Le problème : retrouver l’action et la période comprenant le plus grand nombre de jours consécutifs de baisse.
D’après mon jeu d’essai, je devrais obtenir :

Pendant cette période l’action ACT1 chute pendant 4 jours consécutivement (passant de 2370 à 2340).

Alors peut-être existe-t-il de meilleures méthodes pour résoudre ce problème mais pour ma part j’ai dû composer faute de mieux.

Je commence par une requête intermédiaire R_Variation :

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT Cotation.CodeAction,
Cotation.DateValeur, 
Cotation.CoursFermeture, 
nz(
Cotation.CoursFermeture-
                      (SELECT TOP 1 C.CoursFermeture FROM Cotation C 
                        WHERE C.CodeAction=Cotation.CodeAction 
                              AND C.DateValeur<Cotation.DateValeur
                        ORDER BY C.DateValeur DESC)
,0
) AS variation
FROM Cotation
ORDER BY Cotation.CodeAction, Cotation.DateValeur;

La colonne calculée [variation] donne la variation du cours de l’action par rapport à la cotation précédente. La variation peut donc être positive, négative ou nulle.

Je reprends la requête précédente pour rédiger la requête RNbJoursConsec :

Code sql :

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

1
2
3
4
5
6
7
SELECT 
R_Variation.CodeAction, 
R_Variation.DateValeur,
 R_Variation.CoursFermeture, 
R_Variation.variation, 
nbJoursConsecutifs([R_Variation].[DateValeur],[R_Variation].[CodeAction]) AS joursTendance
FROM R_Variation;

qui retourne les lignes suivantes :

La colonne calculée [JoursTendance] permet de rendre compte des jours consécutifs avec la même tendance. Sa valeur repart à 1 à chaque fois que la colonne [variation] voit son signe changer.

[JoursTendance] exploite une fonction VBA nommée nbJoursConsecutifs dont je donne le code à recopier dans un module:

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
Public Function nbJoursConsecutifs(DateEnCours As Date, CodeAction As Variant) As Integer
 
Dim strSQL As String
Dim tendance As Integer, tendanceprec As Integer
Dim DatePrec As Date
Dim rs As DAO.Recordset
 
strSQL = "SELECT variation, DateValeur FROM R_Variation WHERE DateValeur<=#" & Format(DateEnCours, "mm/dd/yyyy") & "# AND " & _
         "CodeAction='" & CodeAction & "' ORDER BY DateValeur DESC;"
  'Debug.Print strSQL
Set rs = CurrentDb.OpenRecordset(strSQL)
 
rs.MoveLast
' MsgBox (rs.RecordCount)
If rs.RecordCount < 2 Then
    nbJoursConsecutifs = 1
Else
    rs.MoveFirst
    tendance = Sgn(rs.Fields(0))
    rs.MoveNext
    tendanceprec = Sgn(rs.Fields(0))
    DatePrec = rs.Fields(1)
 
'    MsgBox (DateEnCours & "  " & tendance & "  " & DatePrec & "  " & tendanceprec)
 
    If tendance = tendanceprec Then
          nbJoursConsecutifs = nbJoursConsecutifs(DatePrec, CodeAction) + 1
    Else
     nbJoursConsecutifs = 1
    End If
 
End If
    rs.Close
End Function

Vous noterez en ligne 27, l’appel récursif :

Code :

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

 nbJoursConsecutifs = nbJoursConsecutifs(DatePrec, CodeAction) + 1

Les choses sont maintenant en place :
Le problème : retrouver l’action et la période comprenant le plus grand nombre de jours consécutifs de baisse.

R_Joursdebaisse :

Code sql :

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

1
2
3
4
5
6
7
SELECT RNbJoursConsec.CodeAction, 
DateAdd("d",-JoursTendance+1,DateValeur) AS du, 
RNbJoursConsec.DateValeur AS au
FROM RNbJoursConsec
WHERE RNbJoursConsec.[JoursTendance]=
(SELECT Max(T.JoursTendance) FROM RNbJoursConsec T WHERE T.variation<0)
 AND RNbJoursConsec.[variation]<0;

Que l’on adaptera facilement pour obtenir la période comprenant le plus grand nombre de jours consécutifs de hausse d’une action.

Je mets un fichier de démo en pièce-jointe.

Vouala…

**vodiem** · 19/05/2011, 05h15

salut f-leb,

j'ai trouvé le sujet amusant. j'ai donc recherché une solution full sql.
je n'y ai pas trouvé de grosse difficulté en particulier à réaliser cela.
quand à coder en vb autant faire un simple parcours dans la requête ordonnée par date, cela me parait plus simple et rapide.

en tout cas ta méthodologie est bien expliquée.

bonne continuation,

**philben** · 21/05/2011, 08h34

salut Diem

,

j'ai donc recherché une solution full sql

On peut la voir ?

@+

Philippe

**vodiem** · 22/05/2011, 20h27

salut Philippe

,

Envoyé par Philippe

On peut la voir ?

je sais pas, c'est intime comme question...

bon, je l'ai refait en détail pour expliquer sinon brut comme ca, ca fait un pavé et ca parait pas aussi simple que ca n'y parait.

j'ai supposé qu'il pouvait avoir des trous dans entre les enregistrements.
ce qui veut dire que je recherche :
la période comprenant le plus grand nombre de jours consécutifs SAISIE de baisse
ce qui complique un peu le sql sinon un j-1 ou j-2 sur la date simplifierait le sql (d'autant qu'il n'y a pas non plus d'indice ordonné par date des enregistrements)

Etape 1: trouver les premières variations descendantes
j'ai opté pour: si la variation du cours à la précédente date est négative et l'antécedant à celle-ci positive

d'abord le cours précédant <last> et antécédant à celui-ci <prevlast>:

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
 
select
	t1.*,
 
	(
	select
		c1.coursfermeture
	from cotation c1
	where
		(c1.datevaleur=
			(
			select max(c2.datevaleur) from cotation c2
			where
				(c2.datevaleur<t1.datevaleur)
				and
				(c2.codeaction=t1.codeaction)
			)
		)
		and
		(c1.codeaction=t1.codeaction)
	) as last,
 
	(
	select
		c1.coursfermeture
	from cotation c1
	where
		(c1.datevaleur=
			(
			select max(c2.datevaleur) from cotation c2
			where
				(c2.datevaleur<
					(
					select max(c2.datevaleur) from cotation c2
					where
						(c2.datevaleur<t1.datevaleur)
						and
						(c2.codeaction=t1.codeaction)
					)
				)
				and
				(c2.codeaction=t1.codeaction)
			)
		)
		and
		(c1.codeaction=t1.codeaction)
	) as prevlast
 
from cotation t1

les tendances des cours :
<tlast> = <coursfermeture> inférieur à <last> ?
<tprevlast> = <last> inférieur à <prevlast> ?
soit:

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
 
select
	t1.*,
 
	(
	t1.coursfermeture
	<
	(select c1.coursfermeture from cotation c1 where (c1.datevaleur=(select max(c2.datevaleur) from cotation c2 where (c2.datevaleur<t1.datevaleur) and (c2.codeaction=t1.codeaction))) and (c1.codeaction=t1.codeaction))
	) as tlast,
 
	(
	(select c1.coursfermeture from cotation c1 where (c1.datevaleur=(select max(c2.datevaleur) from cotation c2 where (c2.datevaleur<t1.datevaleur) and (c2.codeaction=t1.codeaction))) and (c1.codeaction=t1.codeaction))
	<
	(select c1.coursfermeture from cotation c1 where (c1.datevaleur=(select max(c2.datevaleur) from cotation c2 where (c2.datevaleur<(select max(c2.datevaleur) from cotation c2 where (c2.datevaleur<t1.datevaleur) and (c2.codeaction=t1.codeaction))) and (c2.codeaction=t1.codeaction))) and (c1.codeaction=t1.codeaction))
	) as tprevlast
 
from cotation t1

LES PREMIERES TENDANCES A LA BAISSE:
<isPremDesc> = <tlast> and not <tprevlast>
adaptées au cas particuliers où dès le départ le cours baisse:
<isPremDesc> = <tlast> and not nz(<tprevlast>,0)
soit:

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
 
select
	t1.*,
 
	(
	t1.coursfermeture
	<
	(select c1.coursfermeture from cotation c1 where (c1.datevaleur=(select max(c2.datevaleur) from cotation c2 where (c2.datevaleur<t1.datevaleur) and (c2.codeaction=t1.codeaction))) and (c1.codeaction=t1.codeaction))
	)
	and not
	(nz(
	(select c1.coursfermeture from cotation c1 where (c1.datevaleur=(select max(c2.datevaleur) from cotation c2 where (c2.datevaleur<t1.datevaleur) and (c2.codeaction=t1.codeaction))) and (c1.codeaction=t1.codeaction))
	<
	(select c1.coursfermeture from cotation c1 where (c1.datevaleur=(select max(c2.datevaleur) from cotation c2 where (c2.datevaleur<(select max(c2.datevaleur) from cotation c2 where (c2.datevaleur<t1.datevaleur) and (c2.codeaction=t1.codeaction))) and (c2.codeaction=t1.codeaction))) and (c1.codeaction=t1.codeaction))
	,0)
	) as isPremDesc
 
from cotation t1

Etape 2: définir la requete [baisse] de l'ensemble des enregistrements des premières baisses: (en vue de récupérer la date pour chaque enregistrement)
un truc du genre:

Code SQL :

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

select * from cotation where <isPremDesc>

soit:

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
 
select
	t1.*
from cotation t1
where
	(
	t1.coursfermeture
	<
	(select c1.coursfermeture from cotation c1 where (c1.datevaleur=(select max(c2.datevaleur) from cotation c2 where (c2.datevaleur<t1.datevaleur) and (c2.codeaction=t1.codeaction))) and (c1.codeaction=t1.codeaction))
	)
	and not
	(nz(
	(select c1.coursfermeture from cotation c1 where (c1.datevaleur=(select max(c2.datevaleur) from cotation c2 where (c2.datevaleur<t1.datevaleur) and (c2.codeaction=t1.codeaction))) and (c1.codeaction=t1.codeaction))
	<
	(select c1.coursfermeture from cotation c1 where (c1.datevaleur=(select max(c2.datevaleur) from cotation c2 where (c2.datevaleur<(select max(c2.datevaleur) from cotation c2 where (c2.datevaleur<t1.datevaleur) and (c2.codeaction=t1.codeaction))) and (c2.codeaction=t1.codeaction))) and (c1.codeaction=t1.codeaction))
	,0)
	)

Etape 3: récapitulatif des baisses
requete [récapitulatif] de cotation avec: <du> la date de la baisse et <nbJCDesc> le nombre consécutif de jour de baisse
<du> = date la plus proche inférieur dans [baisse] et où <tlast> est une baisse
<nbJCDesc> = <datevaleur> - <du>
soit:

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
 
select
	t2.*,
 
	(
	select
		max(t1.datevaleur)
	from cotation t1
	where
		(
		t1.coursfermeture
		<
		(select c1.coursfermeture from cotation c1 where (c1.datevaleur=(select max(c2.datevaleur) from cotation c2 where (c2.datevaleur<t1.datevaleur) and (c2.codeaction=t1.codeaction))) and (c1.codeaction=t1.codeaction))
		)
		and not
		(nz(
		(select c1.coursfermeture from cotation c1 where (c1.datevaleur=(select max(c2.datevaleur) from cotation c2 where (c2.datevaleur<t1.datevaleur) and (c2.codeaction=t1.codeaction))) and (c1.codeaction=t1.codeaction))
		<
		(select c1.coursfermeture from cotation c1 where (c1.datevaleur=(select max(c2.datevaleur) from cotation c2 where (c2.datevaleur<(select max(c2.datevaleur) from cotation c2 where (c2.datevaleur<t1.datevaleur) and (c2.codeaction=t1.codeaction))) and (c2.codeaction=t1.codeaction))) and (c1.codeaction=t1.codeaction))
		,0)
		)
		and
		(t1.codeaction=t2.codeaction)
		and
		(t1.datevaleur<=t2.datevaleur)
		and
		(
		t2.coursfermeture
		<
		(select c1.coursfermeture from cotation c1 where (c1.datevaleur=(select max(c2.datevaleur) from cotation c2 where (c2.datevaleur<t2.datevaleur) and (c2.codeaction=t2.codeaction))) and (c1.codeaction=t2.codeaction))
		)
	) as du,
 
	(t2.datevaleur-du+1) as nbJCDesc
 
from cotation t2

Etape 4-Finale: Retrouver l’action et la période comprenant le plus grand nombre de jours consécutifs de baisse.
et bien avec la requete de récapitulatif des baisses cela parait simple, non?
un truc du genre:

Code SQL :

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

select top 1 * from [recapitulatif] order by nbJCDesc desc

he bien non, ca marche pas... si l'enregistrement est bien choisi, les champs calculé <de> et <nbJCDesc> sont vide... merci ACCESS...
donc reste plus qu'à faire un sql du genre:

Code SQL :

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

select * from [recapitulatif] where nbJCDesc=(select max(nbJCDesc) from [recapitulatif])

bon c'est vrai aussi que c'est plus correcte s'il y a plusieurs actions dont <nbJCDesc> est max.

réadaption [recapitulatif] pour avoir uniquement le max(nbJCDesc):
(nos amis sqlien des autres moteurs rigoleraient bien parce qu'il peuvent normalement faire des alias des requetes existants et les reprendres dans le même sql sans avoir à les réécrire...)

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
 
select
	max(
	(
	select
		t2.datevaleur-max(t1.datevaleur)+1
	from cotation t1
	where
		(
		t1.coursfermeture
		<
		(select c1.coursfermeture from cotation c1 where (c1.datevaleur=(select max(c2.datevaleur) from cotation c2 where (c2.datevaleur<t1.datevaleur) and (c2.codeaction=t1.codeaction))) and (c1.codeaction=t1.codeaction))
		)
		and not
		(nz(
		(select c1.coursfermeture from cotation c1 where (c1.datevaleur=(select max(c2.datevaleur) from cotation c2 where (c2.datevaleur<t1.datevaleur) and (c2.codeaction=t1.codeaction))) and (c1.codeaction=t1.codeaction))
		<
		(select c1.coursfermeture from cotation c1 where (c1.datevaleur=(select max(c2.datevaleur) from cotation c2 where (c2.datevaleur<(select max(c2.datevaleur) from cotation c2 where (c2.datevaleur<t1.datevaleur) and (c2.codeaction=t1.codeaction))) and (c2.codeaction=t1.codeaction))) and (c1.codeaction=t1.codeaction))
		,0)
		)
		and
		(t1.codeaction=t2.codeaction)
		and
		(t1.datevaleur<=t2.datevaleur)
		and
		(
		t2.coursfermeture
		<
		(select c1.coursfermeture from cotation c1 where (c1.datevaleur=(select max(c2.datevaleur) from cotation c2 where (c2.datevaleur<t2.datevaleur) and (c2.codeaction=t2.codeaction))) and (c1.codeaction=t2.codeaction))
		)
	)
	) as nbJCDesc
from cotation t2

soit la requete finale:

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
 
select
	tf.codeaction,
	tf.du,
	tf.datevaleur as au,
	tf.nbJCDesc
from
	(
	select
		t2.*,
 
		(
		select
			max(t1.datevaleur)
		from cotation t1
		where
			(
			t1.coursfermeture
			<
			(select c1.coursfermeture from cotation c1 where (c1.datevaleur=(select max(c2.datevaleur) from cotation c2 where (c2.datevaleur<t1.datevaleur) and (c2.codeaction=t1.codeaction))) and (c1.codeaction=t1.codeaction))
			)
			and not
			(nz(
			(select c1.coursfermeture from cotation c1 where (c1.datevaleur=(select max(c2.datevaleur) from cotation c2 where (c2.datevaleur<t1.datevaleur) and (c2.codeaction=t1.codeaction))) and (c1.codeaction=t1.codeaction))
			<
			(select c1.coursfermeture from cotation c1 where (c1.datevaleur=(select max(c2.datevaleur) from cotation c2 where (c2.datevaleur<(select max(c2.datevaleur) from cotation c2 where (c2.datevaleur<t1.datevaleur) and (c2.codeaction=t1.codeaction))) and (c2.codeaction=t1.codeaction))) and (c1.codeaction=t1.codeaction))
			,0)
			)
			and
			(t1.codeaction=t2.codeaction)
			and
			(t1.datevaleur<=t2.datevaleur)
			and
			(
			t2.coursfermeture
			<
			(select c1.coursfermeture from cotation c1 where (c1.datevaleur=(select max(c2.datevaleur) from cotation c2 where (c2.datevaleur<t2.datevaleur) and (c2.codeaction=t2.codeaction))) and (c1.codeaction=t2.codeaction))
			)
		) as du,
 
		(t2.datevaleur-du+1) as nbJCDesc
 
	from cotation t2
	) tf
 
where
	tf.nbJCDesc =
	(
	select
		max(
		(
		select
			t2.datevaleur-max(t1.datevaleur)+1
		from cotation t1
		where
			(
			t1.coursfermeture
			<
			(select c1.coursfermeture from cotation c1 where (c1.datevaleur=(select max(c2.datevaleur) from cotation c2 where (c2.datevaleur<t1.datevaleur) and (c2.codeaction=t1.codeaction))) and (c1.codeaction=t1.codeaction))
			)
			and not
			(nz(
			(select c1.coursfermeture from cotation c1 where (c1.datevaleur=(select max(c2.datevaleur) from cotation c2 where (c2.datevaleur<t1.datevaleur) and (c2.codeaction=t1.codeaction))) and (c1.codeaction=t1.codeaction))
			<
			(select c1.coursfermeture from cotation c1 where (c1.datevaleur=(select max(c2.datevaleur) from cotation c2 where (c2.datevaleur<(select max(c2.datevaleur) from cotation c2 where (c2.datevaleur<t1.datevaleur) and (c2.codeaction=t1.codeaction))) and (c2.codeaction=t1.codeaction))) and (c1.codeaction=t1.codeaction))
			,0)
			)
			and
			(t1.codeaction=t2.codeaction)
			and
			(t1.datevaleur<=t2.datevaleur)
			and
			(
			t2.coursfermeture
			<
			(select c1.coursfermeture from cotation c1 where (c1.datevaleur=(select max(c2.datevaleur) from cotation c2 where (c2.datevaleur<t2.datevaleur) and (c2.codeaction=t2.codeaction))) and (c1.codeaction=t2.codeaction))
			)
		)
		)
	from cotation t2
	)

voilà à quoi j'ai pensé rapidement comme solus,

**f-leb** · 22/05/2011, 22h04

rahhh l’est fort le Vodiem, encore une rasta-requête de compet

Un petit souci cependant car le fait qu’il peut y avoir des jours sans cotation change un peu la donne…

Je précise le cahier des charges avec un jeu de données modifié :

Code :

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

1
2
3
4
5
6
7
8
9
CodeAction	DateValeur	CoursFermeture
ACT1 	      10/07/2010   	   2370
ACT1 	      11/07/2010   	   2360
ACT1 	      13/07/2010   	   2318
ACT1 	      15/07/2010   	   2312
ACT1 	      22/07/2010   	   2350
ACT2 	      05/07/2010   	   4030
ACT2 	      06/07/2010   	   3950
ACT2 	      12/07/2010   	   3920

Ta requête, Vodiem, retourne

Code :

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

1
2
codeaction	du	au
ACT2 	06/07/2010 	12/07/2010

Car cette période de baisse dure 7 jours mais elle ne comprend que 2 séances de cotation (les 06 et 12/07/2010)

Or, j’aimerais qu’elle retourne :

Code :

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

1
2
CodeAction	du	au
ACT1 	11/07/2010 	15/07/2010

La période de baisse ne dure que 5 jours mais elle comprend 3 séances à la baisse (les 11, 13 et 15/07/2010)

Le problème devient :
retrouver l’action et la période comprenant le plus grand nombre de séances consécutives de baisse.

Je regarde à comment adapter ma méthode.
Je ne doute pas que tu réussisses à adapter ton SQL en conséquence…

A plus tard…

**vodiem** · 23/05/2011, 04h50

Envoyé par f-leb

Un petit souci cependant car le fait qu’il peut y avoir des jours sans cotation change un peu la donne…

je sais pas mais je m'y attendais à celle là...

Envoyé par f-leb

Le problème devient :
retrouver l’action et la période comprenant le plus grand nombre de séances consécutives de baisse.

mon avis que c'est pas ca que tu veux...

sinon le résultat d'après le dernier exemple ne serait pas du 11/07/2010 au 15/07/2010, vu que les séances ne sont pas consécutives. ;P
je me permets de reformuler ton idée:
retrouver l'action et la période qui a la plus grande période de baisse avec le plus grand nombre de séance.

mais même avec ca:
qu'es ce qui est plus représentatif? une grande période avec une faible densité ou une petite période à grande densité?
je dirais même : à quel moment l'un prévaut sur l'autre?

par exemple:
légende:
B: jour de baisse
-: pas de saisie
N/X: N jours de baisse / X valeur saisie sur la période

7/4: B--BB-B
5/5: BBBBB
6/5: BBBB-B
15/4: B---B---B-----B
15/5: BB----------BBB

lequel est le plus représentatif de la baisse? quel est la formulation la plus pertinente dans tous les cas de figure?

Rechercher des périodes vérifiant un critère sur des dates consécutives

Contribuez

Vue hybride

Discussions similaires

Partager

Partager