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

,

Citation:

j'ai donc recherché une solution full sql

On peut la voir ?

@+

Philippe

vodiem · 22/05/2011, 20h27

salut Philippe

,

Citation:

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

Citation:

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à...

Citation:

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?

f-leb · 23/05/2011, 20h25

Bonjour Vodiem,

Code :

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

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

J’aimerais que la requête ne retourne que les lignes 2,3 et 5 parce qu’elles comportent 5 séances de baisse à suivre alors que les lignes 1 et 4 n’en comportent que quatre (la durée en nombre de jours de la baisse m’importe peu en fait parce que les "trous" sont rares).
Par contre j’ai besoin de savoir quand a débuté la baisse et quand elle se termine.

Pour tenir compte des quelques jours sans séance de cotation, il faut juste modifier ma dernière requête R_Joursdebaisse comme suit :

R_Joursdebaisse :

Code sql :

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

1
2
3
4
5
6
7
8
9
10
SELECT RNbJoursConsec.CodeAction, 
(
    SELECT Max(U.DateValeur) FROM RNbJoursConsec U
    WHERE U.CodeAction=RNbJoursConsec.CodeAction
    AND U.DateValeur<=RNbJoursConsec.DateValeur
    AND U.JoursTendance=1
)  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;

Merci de ton intérêt pour cet exercice amusant

vodiem · 23/05/2011, 23h18

bon, comme tu voudras...

20/4: B---B-----B--------B
5/3: B-B-B

tu es conscient que 20/4 sera le résultat ?
je décline toute responsabilité quand à l'exploitation de la requête...
c'est à tes risques et péril, tu me signe une décharge... t'es conscient au moins que tu joues avec la bourse???

d'un autre côté ca me facilite le travaille, j'en étais à conjecturer des formules savante pour valoriser la distribution des points dans l'espace... encore un mystère qui ne sera pas élucidé ce soir...

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
SELECT
	tf.codeaction,
	tf.du,
	tf.datevaleur AS au
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
 
	FROM cotation t2
	) tf
 
WHERE
	(SELECT count(*) FROM cotation c1 WHERE (c1.datevaleur BETWEEN du AND tf.datevaleur) AND (c1.codeaction=tf.codeaction)) =
	(
	SELECT
		max(nbJCDescSaisie)
	FROM
		(
		SELECT
			(SELECT count(*) FROM cotation c1 WHERE (c1.datevaleur BETWEEN du AND tf.datevaleur) AND (c1.codeaction=tf.codeaction)) AS nbJCDescSaisie
		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
 
			FROM cotation t2
			) tf
		)
	)

philben · 24/05/2011, 19h36

Chapeau bas pour vos réalisations

Dièm, la requête la plus dingue que je n'ai jamais vu sous Access...

J't'adore !!! (en bonne amitié seulement

...)

Philippe

f-leb · 26/05/2011, 18h44

Citation:

Envoyé par philben

J't'adore !!! (en bonne amitié seulement

...)

très touchant

Exercice résolu, bravo Vodiem

à bientôt...

f-leb · 26/05/2011, 20h26

Bon, revenez je n’ai pas fini !

Voici un nouveau jeu de données et le nouveau résultat souhaité :

Vous voyez l’idée, sur la période du 2 au 16/08/2010, l’action ACT3 a chutée de 100 pts sur 4 séances consécutives de baisse.
Le 06/07/2010, l’action ACT2 a chutée de 80 pts en une seule séance.
Du 2 au 6/07/2010, l’action ACT1 a chutée de 70 pts sur 4 séances consécutives de baisse.
Etc…

Je donnerais ma solution (hybride SQL+VBA) une fois que Vodiem aura pondu sa soluce full SQL en moins de 80 lignes

vodiem · 27/05/2011, 02h03

Citation:

Envoyé par f-leb

très touchant

he oui, on s'adore Philippe et moi

on se "SQrL" depuis un bout de temps ensemble ;P

Citation:

Envoyé par philben

en bonne amitié seulement

c'est quoi ce sous entendu? ^^

Citation:

Envoyé par f-leb

une fois que Vodiem aura pondu sa soluce full SQL en moins de 80 lignes

voilà, le Diem il'a pondu:

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
SELECT
	tf.codeaction,
	tf.du,
	max(tf.au) AS au,
	sum(tf.diff) AS baisse
FROM
	(
	SELECT
		t2.codeaction,
		t2.datevaleur AS au,
 
		(
		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)
		) AS du,
 
		(
		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 diff
 
	FROM cotation t2
	) tf
WHERE tf.diff<0
GROUP BY tf.codeaction, tf.du
ORDER BY sum(tf.diff)

il est encore tout chaud, sortie de l'a... l'a???
l'adsl, bien sur...

bonne appétit! ^^

f-leb · 27/05/2011, 16h02

Citation:

Envoyé par vodiem

he oui, on s'adore Philippe et moi

on se "SQrL" depuis un bout de temps ensemble ;P

du moment que cette relation est consentie

Encore une belle requête qui donne bien le résultat souhaité en tout cas.

Je donne ma solution, histoire de contribuer un peu quand même

…

Je reprends ma requête intermédiaire R_Variation du premier message :

Code sql :

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

1
2
3
4
5
6
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;

Dans la même idée, j’utilise ensuite la requête RSeancesConsec, avec une fonction récursive SeancesConsecutives.

RSeancesConsecutives :

Code sql :

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

1
2
SELECT R_Variation.CodeAction, R_Variation.DateValeur, R_Variation.CoursFermeture, R_Variation.variation, SeancesConsecutives([R_Variation].[DateValeur],[R_Variation].[CodeAction]) AS PeriodeTendance
FROM R_Variation;

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
Public Function SeancesConsecutives(DateEnCours As Date, CodeAction As Variant) As Integer
 
Dim strSQL As String
Dim tendance As Integer, tendanceprec As Integer
Dim DatePrec As Date, DateActuelle 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
 
If rs.RecordCount < 2 Then
    SeancesConsecutives = 1
Else
    rs.MoveFirst
    tendance = Sgn(rs.Fields(0))
    DateActuelle = rs.Fields(1)
    rs.MoveNext
    tendanceprec = Sgn(rs.Fields(0))
    DatePrec = rs.Fields(1)
 
    If tendance = tendanceprec Then
          SeancesConsecutives = SeancesConsecutives(DatePrec, CodeAction)
    Else
     SeancesConsecutives = SeancesConsecutives(DatePrec, CodeAction) + 1
    End If
 
End If
    rs.Close
End Function

Voici le résultat intermédiaire obtenu :

Et la requête finale R_f-leb

:

Code sql :

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

1
2
3
4
5
SELECT RSeancesConsec.CodeAction, Min(RSeancesConsec.DateValeur) AS Du, Max(RSeancesConsec.DateValeur) AS Au, Sum(RSeancesConsec.variation) AS Baisse
FROM RSeancesConsec
WHERE RSeancesConsec.variation<0
GROUP BY RSeancesConsec.CodeAction, RSeancesConsec.PeriodeTendance
ORDER BY Sum(RSeancesConsec.variation);

Résultat en pièce-jointe où j’ai également inclue la fameuse requête R_Vodiem

.

Je pense que j’en ai maintenant terminé avec cette modeste contribution, à bientôt.

User · 27/05/2011, 23h12

Bravo à vodiem et Fabien pour cette contribution

27/05/2011, 23h12	#14
User Rédacteur/Modérateur Denis Développeur informatique Inscription : août 2004 Messages : 3 206 Détails du profil Informations personnelles : Nom : Denis Âge : 42 Localisation : France Informations professionnelles : Activité : Développeur informatique Informations forums : Inscription : août 2004 Messages : 3 206 Points : 5 258 Points : 5 258	Bravo à vodiem et Fabien pour cette contribution __________________ Merci de ne pas poster sur mon profil pour des problèmes techniques. Pour celà vous pouvez utiliser le forum ou m'envoyer un mp. Bon développement ! Mes tutoriels et contributions sur ma page perso: Ma page personnelle
	00

19/05/2011, 05h15	#2
vodiem Expert Confirmé Diem VO Vivre Inscription : avril 2006 Messages : 2 644 Détails du profil Informations personnelles : Nom : Diem VO Âge : 40 Localisation : France, Bouches du Rhône (Provence Alpes Côte d'Azur) Informations professionnelles : Activité : Vivre Secteur : Conseil Informations forums : Inscription : avril 2006 Messages : 2 644 Points : 3 895 Points : 3 895	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,
	00

24/05/2011, 19h36	#9
philben Membre Expert Inscription : avril 2006 Messages : 1 318 Détails du profil Informations forums : Inscription : avril 2006 Messages : 1 318 Points : 1 586 Points : 1 586	Chapeau bas pour vos réalisations Dièm, la requête la plus dingue que je n'ai jamais vu sous Access... J't'adore !!! (en bonne amitié seulement ...) Philippe
	10

26/05/2011, 20h26	#11
f-leb Expert Confirmé Sénior Fabien Enseignant Inscription : janvier 2009 Messages : 2 415 Détails du profil Informations personnelles : Nom : Fabien Âge : 41 Localisation : France, Sarthe (Pays de la Loire) Informations professionnelles : Activité : Enseignant Informations forums : Inscription : janvier 2009 Messages : 2 415 Points : 4 446 Points : 4 446	Bon, revenez je n’ai pas fini ! Voici un nouveau jeu de données et le nouveau résultat souhaité : Vous voyez l’idée, sur la période du 2 au 16/08/2010, l’action ACT3 a chutée de 100 pts sur 4 séances consécutives de baisse. Le 06/07/2010, l’action ACT2 a chutée de 80 pts en une seule séance. Du 2 au 6/07/2010, l’action ACT1 a chutée de 70 pts sur 4 séances consécutives de baisse. Etc… Je donnerais ma solution (hybride SQL+VBA) une fois que Vodiem aura pondu sa soluce full SQL en moins de 80 lignes
	00

Outils de la discussion
Afficher une version imprimable Envoyer un lien vers cette page par email