Requête SQL un peu tordue

**jkofr** · 09/10/2011, 22h25

Bonsoir,

J'ai une grosse base de turf sous Oracle qui est alimentée automatiquement tous les jours. Je travaille actuellement sur un modèle prédictif pour le jeux. Et oui, ça existe!

J'ai une question SQL un peu tordue. Suis pas très à l'aise avec les queries analytiques.

Je chercher à détecter un jockey qui a réussi à se placer 3 fois dans les 3 premiers de la course dans les 3 derniers jours.
Si cette condition est remplie, envisager de jouer au maximum les 3 prochains chevaux montés par ce jockey. J'ai dit envisager, pas jouer :-) Ensuite il y a d'autres critères...

Voici le jeu d'essai

La requête doit me positionner la colonne play à No ou Yes suivant le cas.

J'ai déjà fait quelques tests avec les fonctions lag et lead mais je coince un peu.

Un grand merci pour votre aide.

Laurent, une idée :-)
Jko

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
 
     Date	Place	 Play
22/01/2011	8	No
29/01/2011	2	No
30/01/2011	2	No
31/01/2011	3	No
05/02/2011	1	yes
06/02/2011	6	yes
07/02/2011	5	yes
07/02/2011	8	No
10/02/2011	6	No
12/02/2011	3	No
13/02/2011	3	No
13/02/2011	2	No
14/02/2011	2	yes
20/02/2011	9	yes
20/02/2011	2	yes
20/02/2011	6	No
21/02/2011	-1	No
22/02/2011	3	No
23/02/2011	2	No
23/02/2011	3	No
24/02/2011	4	yes
24/02/2011	-1	yes
25/02/2011	1	yes
03/03/2011	5	No
05/03/2011	6	No
06/03/2011	4	No
13/03/2011	9	No

Ps: je passe la certif SQL Expert en Novembre!! Ne riez pas!

**ojo77** · 10/10/2011, 09h12

Ca doit pouvoir se faire en utilisant la méthode dite "Tabibitosan" sur une colonne calculée en fonction de la place qui dira si le joueur est placé ou non.

Bon je n'ai pas le temps d'écrire la requête, mais en connaissant la méthode ça devrait aller ...

Pour info les questions de la certif ne sont pas si "complexes".

**Rams7s** · 10/10/2011, 09h18

Vu qu'on a 4jours consecutifs ou est classe parmi les 3premiers, on devrait aussi avoir 4 jours consecutifs ou on envisage de jouer le cheval.

Envoyé par jkofr

Je chercher à détecter un jockey qui a réussi a se placer 3 fois dans les 3 premiers de la course au dans les 3 derniers jours.
Si cette condition est remplie, envisager de jouer au maximum les 3 prochains chevaux monté par ce jockey.

Code :

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

1
2
3
4
5
6
7
8
     Date	Place	 Play
29/01/2011	2	No
30/01/2011	2	No
31/01/2011	3	No
05/02/2011	1	yes
06/02/2011	6	yes
07/02/2011	5	yes
07/02/2011	8	No

Par rapport a votre besoin, je pense qu'il y a un soucis.

La ligne 8, du 7/02, ou il est classe 8 eme on devrait etre a vrai aussi. Vu que que la serie du 30-31-05 correspond a votre critere.
Ou bien, c'est la ligne 4 du 31 qui devrait etre a vrai aussi, a vous de voir, mais il y a un probleme dans tous les cas.

Du coup peut-etre que les requetes que vous avez ecrites fonctionnent en fait

Mais faut les proposer pour le voir

**jkofr** · 10/10/2011, 10h02

Hello Rams7s,

Merci pour ton message,
En fait je n'ai pas précisé un detail, il faut le numéro de la course pour les cas ou la date est la même.

Voici le jeux d'essais avec la modif.

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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
 
WITH Courses AS (
Select to_Date('22/01/2011') Dt, 	8  Place, 7 NumCourse from dual
Union all
Select to_Date('29/01/2011') Dt, 	2  Place, 4 NumCourse from dual
Union all
Select to_Date('30/01/2011') Dt, 	2  Place, 3 NumCourse from dual
Union all
Select to_Date('31/01/2011') Dt, 	3  Place, 1 NumCourse from dual
Union all
Select to_Date('05/02/2011') Dt, 	1  Place, 4 NumCourse from dual
Union all
Select to_Date('06/02/2011') Dt, 	6  Place, 5 NumCourse from dual
Union all
Select to_Date('07/02/2011') Dt, 	5  Place, 7 NumCourse from dual
Union all
Select to_Date('07/02/2011') Dt, 	8  Place, 8 NumCourse from dual
Union all
Select to_Date('10/02/2011') Dt, 	6  Place, 3 NumCourse from dual
Union all
Select to_Date('12/02/2011') Dt, 	3  Place, 2 NumCourse from dual
Union all
Select to_Date('13/02/2011') Dt, 	3  Place, 4 NumCourse from dual
Union all
Select to_Date('13/02/2011') Dt, 	2  Place, 5 NumCourse from dual
Union all
Select to_Date('14/02/2011') Dt, 	2  Place, 7 NumCourse from dual
Union all
Select to_Date('20/02/2011') Dt, 	9  Place, 1 NumCourse from dual
Union all
Select to_Date('20/02/2011') Dt, 	2  Place, 4 NumCourse from dual
Union all
Select to_Date('20/02/2011') Dt, 	6  Place, 7 NumCourse from dual
Union all
Select to_Date('21/02/2011') Dt, 	-1 Place, 6 NumCourse from dual
Union all
Select to_Date('22/02/2011') Dt, 	3  Place, 5 NumCourse from dual
Union all
Select to_Date('23/02/2011') Dt, 	2  Place, 7 NumCourse from dual
Union all
Select to_Date('23/02/2011') Dt, 	3  Place, 8 NumCourse from dual
Union all
Select to_Date('24/02/2011') Dt, 	4  Place, 2 NumCourse from dual
Union all
Select to_Date('24/02/2011') Dt, 	-1 Place, 4 NumCourse from dual
Union all
Select to_Date('25/02/2011') Dt, 	1  Place, 5 NumCourse from dual
Union all
Select to_Date('03/03/2011') Dt, 	5  Place, 1 NumCourse from dual
Union all
Select to_Date('05/03/2011') Dt, 	6  Place, 2 NumCourse from dual
Union all
Select to_Date('06/03/2011') Dt, 	4  Place, 1 NumCourse from dual
Union all
Select to_Date('13/03/2011') Dt, 	9  Place, 6 NumCourse from dual
)

La ligne 8 ne doit pas être retenue car on s’arrête après 3 chevaux.

Je vais poster quelques essais dans la journée.

Merci a vous
jko

**Waldar** · 10/10/2011, 14h40

Je l'ai fait comme ça :

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
41
select dt, place, numcourse
     , case
         when least(max(case place when -1 then 9 else place end) over(order by dt asc, numcourse asc rows between 3 preceding and 1 preceding)
                  , max(case place when -1 then 9 else place end) over(order by dt asc, numcourse asc rows between 4 preceding and 2 preceding)
                  , max(case place when -1 then 9 else place end) over(order by dt asc, numcourse asc rows between 5 preceding and 3 preceding))
              between 0 and 3
          and max(case place when -1 then 9 else place end) over(order by dt asc, numcourse asc rows between 6 preceding and 4 preceding) > 3
         then 'Yes'
         else 'No'
       end as Play
  from Courses;
 
DT              PLACE  NUMCOURSE PLAY
---------- ---------- ---------- ----
22/01/2011          8          7 No 
29/01/2011          2          4 No 
30/01/2011          2          3 No 
31/01/2011          3          1 No 
05/02/2011          1          4 Yes
06/02/2011          6          5 Yes
07/02/2011          5          7 Yes
07/02/2011          8          8 No 
10/02/2011          6          3 No 
12/02/2011          3          2 No 
13/02/2011          3          4 No 
13/02/2011          2          5 No 
14/02/2011          2          7 Yes
20/02/2011          9          1 Yes
20/02/2011          2          4 Yes
20/02/2011          6          7 No 
21/02/2011         -1          6 No 
22/02/2011          3          5 No 
23/02/2011          2          7 No 
23/02/2011          3          8 No 
24/02/2011          4          2 Yes
24/02/2011         -1          4 Yes
25/02/2011          1          5 Yes
03/03/2011          5          1 No 
05/03/2011          6          2 No 
06/03/2011          4          1 No 
13/03/2011          9          6 No

**jkofr** · 10/10/2011, 15h31

Hello Waldar,

Génial!

Par contre je ne comprends pas la condition d’arrêt qui selon ta ligne 7 est conditionnées par max (place) sur les lignes de 6 a 4 précédentes.

La condition est sur le nombre de coups max a jouer.
Ou alors j'ai pas saisis quelque chose.

Jko

**Waldar** · 10/10/2011, 15h38

C'est pour éviter d'avoir quatre "Yes" d'affilé si le premier "Yes" est aussi classé dans les trois premiers, le cas de figure précisé par Rams7s.

Je m'assure donc avoir au moins un classement moins bon que 3 entre les lignes -6 à -4.

**jkofr** · 10/10/2011, 16h02

Hello,

Alors j'ai encore un peu de travail.
Je m'explique.
La condition de début de jeux est:
Se placer 3 fois dans les 3 premiers de la course dans les 3 derniers jours.
Le cas tordu, Il peut courir le 29 Janvier une course puis 3 courses le 30 Janvier, puis une course le 31 Janvier. Si il se place 3 fois sur cette période, le signal de jeux est donné.

Voici le test case modifié suivi du résultat

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
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
 
WITH Courses AS (
Select to_Date('22/01/2011') Dt, 	8  Place, 7 NumCourse from dual
Union all
Select to_Date('29/01/2011') Dt, 	2  Place, 4 NumCourse from dual
Union all
Select to_Date('30/01/2011') Dt, 	7  Place, 3 NumCourse from dual
Union all
Select to_Date('30/01/2011') Dt, 	5  Place, 5 NumCourse from dual
Union all
Select to_Date('30/01/2011') Dt, 	2  Place, 7 NumCourse from dual
Union all
Select to_Date('31/01/2011') Dt, 	3  Place, 1 NumCourse from dual
Union all
Select to_Date('05/02/2011') Dt, 	1  Place, 4 NumCourse from dual
Union all
Select to_Date('06/02/2011') Dt, 	6  Place, 5 NumCourse from dual
Union all
Select to_Date('07/02/2011') Dt, 	5  Place, 7 NumCourse from dual
Union all
Select to_Date('07/02/2011') Dt, 	8  Place, 8 NumCourse from dual
Union all
Select to_Date('10/02/2011') Dt, 	6  Place, 3 NumCourse from dual
Union all
Select to_Date('12/02/2011') Dt, 	3  Place, 2 NumCourse from dual
Union all
Select to_Date('13/02/2011') Dt, 	3  Place, 4 NumCourse from dual
Union all
Select to_Date('13/02/2011') Dt, 	2  Place, 5 NumCourse from dual
Union all
Select to_Date('14/02/2011') Dt, 	2  Place, 7 NumCourse from dual
Union all
Select to_Date('20/02/2011') Dt, 	9  Place, 1 NumCourse from dual
Union all
Select to_Date('20/02/2011') Dt, 	2  Place, 4 NumCourse from dual
Union all
Select to_Date('20/02/2011') Dt, 	6  Place, 7 NumCourse from dual
Union all
Select to_Date('21/02/2011') Dt, 	-1 Place, 6 NumCourse from dual
Union all
Select to_Date('22/02/2011') Dt, 	3  Place, 5 NumCourse from dual
Union all
Select to_Date('23/02/2011') Dt, 	2  Place, 7 NumCourse from dual
Union all
Select to_Date('23/02/2011') Dt, 	3  Place, 8 NumCourse from dual
Union all
Select to_Date('24/02/2011') Dt, 	4  Place, 2 NumCourse from dual
Union all
Select to_Date('24/02/2011') Dt, 	-1 Place, 4 NumCourse from dual
Union all
Select to_Date('25/02/2011') Dt, 	1  Place, 5 NumCourse from dual
Union all
Select to_Date('03/03/2011') Dt, 	5  Place, 1 NumCourse from dual
Union all
Select to_Date('05/03/2011') Dt, 	6  Place, 2 NumCourse from dual
Union all
Select to_Date('06/03/2011') Dt, 	4  Place, 1 NumCourse from dual
Union all
Select to_Date('13/03/2011') Dt, 	9  Place, 6 NumCourse from dual
)
SELECT dt, place, numcourse
     , case
         when least(max(place) over(ORDER BY dt ASC, numcourse ASC rows BETWEEN 3 preceding AND 1 preceding)
                  , max(place) over(ORDER BY dt ASC, numcourse ASC rows BETWEEN 4 preceding AND 2 preceding)
                  , max(place) over(ORDER BY dt ASC, numcourse ASC rows BETWEEN 5 preceding AND 3 preceding))
              <= 3 AND
              max(place) over(ORDER BY dt ASC, numcourse ASC rows BETWEEN 6 preceding AND 4 preceding)
              > 3
         then 'Yes'
         else 'No'
       end AS Play
  FROM Courses;

Le jeux peux démarrer en ligne 10

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
 
DT                        PLACE                  NUMCOURSE              PLAY 
------------------------- ---------------------- ---------------------- ---- 
22-01-2011                8                      7                      No   
29-01-2011                2                      4                      No   
30-01-2011                7                      3                      No   
30-01-2011                5                      5                      No   
30-01-2011                2                      7                      No   
31-01-2011                3                      1                      No   
05-02-2011                1                      4                      No   
06-02-2011                6                      5                      Yes  
07-02-2011                5                      7                      Yes  
07-02-2011                8                      8                      Yes  
10-02-2011                6                      3                      No   
12-02-2011                3                      2                      No   
13-02-2011                3                      4                      No   
13-02-2011                2                      5                      No   
14-02-2011                2                      7                      Yes  
20-02-2011                9                      1                      Yes  
20-02-2011                2                      4                      Yes  
20-02-2011                6                      7                      No   
21-02-2011                -1                     6                      No   
22-02-2011                3                      5                      No   
23-02-2011                2                      7                      No   
23-02-2011                3                      8                      Yes  
24-02-2011                4                      2                      Yes  
24-02-2011                -1                     4                      Yes  
25-02-2011                1                      5                      No   
03-03-2011                5                      1                      No   
05-03-2011                6                      2                      No   
06-03-2011                4                      1                      No   
13-03-2011                9                      6                      No

Je vais essayer d'abord de borner la période (3 jours), puis détecter si jeux ou pas et enfin faire la condition d’arrêt.

A toute
jko

**jkofr** · 10/10/2011, 17h06

Hello

J'ai pose un test mais marche pas

**McM** · 10/10/2011, 17h44

Allez, je me lance..
Si j'ai bien compris, ce sont les résultats sur les 3 derniers jours de course (une date où il n'y a pas de course n'est pas comptée).

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
41
 
WITH Courses AS (
SELECT TO_DATE('22/01/2011','DD/MM/RRRR') Dt, 	8  Place, 7 NumCourse FROM dual
UNION ALL SELECT TO_DATE('29/01/2011','DD/MM/RRRR') Dt, 	2  Place, 4 NumCourse FROM dual
UNION ALL SELECT TO_DATE('30/01/2011','DD/MM/RRRR') Dt, 	7  Place, 3 NumCourse FROM dual
UNION ALL SELECT TO_DATE('30/01/2011','DD/MM/RRRR') Dt, 	5  Place, 5 NumCourse FROM dual
UNION ALL SELECT TO_DATE('30/01/2011','DD/MM/RRRR') Dt, 	2  Place, 7 NumCourse FROM dual
UNION ALL SELECT TO_DATE('31/01/2011','DD/MM/RRRR') Dt, 	3  Place, 1 NumCourse FROM dual
UNION ALL SELECT TO_DATE('05/02/2011','DD/MM/RRRR') Dt, 	1  Place, 4 NumCourse FROM dual
UNION ALL SELECT TO_DATE('06/02/2011','DD/MM/RRRR') Dt, 	6  Place, 5 NumCourse FROM dual
UNION ALL SELECT TO_DATE('07/02/2011','DD/MM/RRRR') Dt, 	5  Place, 7 NumCourse FROM dual
UNION ALL SELECT TO_DATE('07/02/2011','DD/MM/RRRR') Dt, 	8  Place, 8 NumCourse FROM dual
UNION ALL SELECT TO_DATE('10/02/2011','DD/MM/RRRR') Dt, 	6  Place, 3 NumCourse FROM dual
UNION ALL SELECT TO_DATE('12/02/2011','DD/MM/RRRR') Dt, 	3  Place, 2 NumCourse FROM dual
UNION ALL SELECT TO_DATE('13/02/2011','DD/MM/RRRR') Dt, 	3  Place, 4 NumCourse FROM dual
UNION ALL SELECT TO_DATE('13/02/2011','DD/MM/RRRR') Dt, 	2  Place, 5 NumCourse FROM dual
UNION ALL SELECT TO_DATE('14/02/2011','DD/MM/RRRR') Dt, 	2  Place, 7 NumCourse FROM dual
UNION ALL SELECT TO_DATE('20/02/2011','DD/MM/RRRR') Dt, 	9  Place, 1 NumCourse FROM dual
UNION ALL SELECT TO_DATE('20/02/2011','DD/MM/RRRR') Dt, 	2  Place, 4 NumCourse FROM dual
UNION ALL SELECT TO_DATE('20/02/2011','DD/MM/RRRR') Dt, 	6  Place, 7 NumCourse FROM dual
UNION ALL SELECT TO_DATE('21/02/2011','DD/MM/RRRR') Dt, 	-1 Place, 6 NumCourse FROM dual
UNION ALL SELECT TO_DATE('22/02/2011','DD/MM/RRRR') Dt, 	3  Place, 5 NumCourse FROM dual
UNION ALL SELECT TO_DATE('23/02/2011','DD/MM/RRRR') Dt, 	2  Place, 7 NumCourse FROM dual
UNION ALL SELECT TO_DATE('23/02/2011','DD/MM/RRRR') Dt, 	3  Place, 8 NumCourse FROM dual
UNION ALL SELECT TO_DATE('24/02/2011','DD/MM/RRRR') Dt, 	4  Place, 2 NumCourse FROM dual
UNION ALL SELECT TO_DATE('24/02/2011','DD/MM/RRRR') Dt, 	-1 Place, 4 NumCourse FROM dual
UNION ALL SELECT TO_DATE('25/02/2011','DD/MM/RRRR') Dt, 	1  Place, 5 NumCourse FROM dual
UNION ALL SELECT TO_DATE('03/03/2011','DD/MM/RRRR') Dt, 	5  Place, 1 NumCourse FROM dual
UNION ALL SELECT TO_DATE('05/03/2011','DD/MM/RRRR') Dt, 	6  Place, 2 NumCourse FROM dual
UNION ALL SELECT TO_DATE('06/03/2011','DD/MM/RRRR') Dt, 	4  Place, 1 NumCourse FROM dual
UNION ALL SELECT TO_DATE('13/03/2011','DD/MM/RRRR') Dt, 	9  Place, 6 NumCourse FROM dual
), 
	w AS (
  SELECT dt, place, numcourse, DENSE_RANK() OVER(ORDER BY dt) DENSE_RNK,
        CASE WHEN place BETWEEN 1 AND 3 THEN 1 ELSE 0 END gagne
  FROM Courses
  )
SELECT a.dt, a.numcourse, a.place, a.dense_rnk, a.gagne, 
			NVL((SELECT 'Yes' FROM w b WHERE b.dense_rnk BETWEEN a.dense_rnk -3 AND a.dense_rnk - 1 HAVING SUM(gagne) >= 3), 'No') AS Play
FROM w a
ORDER BY dt, numcourse

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
DT	NUMCOURSE	PLACE	DENSE_RNK	GAGNE	PLAY
22/01/2011	7	8	1	0	No
29/01/2011	4	2	2	1	No
30/01/2011	3	7	3	0	No
30/01/2011	5	5	3	0	No
30/01/2011	7	2	3	1	No
31/01/2011	1	3	4	1	No
05/02/2011	4	1	5	1	Yes
06/02/2011	5	6	6	0	Yes
07/02/2011	7	5	7	0	No
07/02/2011	8	8	7	0	No
10/02/2011	3	6	8	0	No
12/02/2011	2	3	9	1	No
13/02/2011	4	3	10	1	No
13/02/2011	5	2	10	1	No
14/02/2011	7	2	11	1	Yes
20/02/2011	1	9	12	0	Yes
20/02/2011	4	2	12	1	Yes
20/02/2011	7	6	12	0	Yes
21/02/2011	6	-1	13	0	Yes
22/02/2011	5	3	14	1	No
23/02/2011	7	2	15	1	No
23/02/2011	8	3	15	1	No
24/02/2011	2	4	16	0	Yes
24/02/2011	4	-1	16	0	Yes
25/02/2011	5	1	17	1	Yes
03/03/2011	1	5	18	0	Yes
05/03/2011	2	6	19	0	No
06/03/2011	1	4	20	0	No
13/03/2011	6	9	21	0	No

**jkofr** · 10/10/2011, 18h23

Hello McM,

C'est dans les 3 jours soit:
- 3 courses dans la journée si 3 places entre 1 et 3 signal = ok
- 1 course J-2 0 course sur J-1 et 2 Courses sur le jour si 3 places entre 1 et 3 signal = ok
...

Voici une idée pour détecter le signal de jeux. Peut-être que ça va nous donner une piste.

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
 
Create or replace Function IsInPeriod (pDate Date, pNum Number) Return number as
lNb1 Number;
lNb2 Number;
lNb3 Number;
lNb4 Number;
Begin
 Select count (*) into lNb4 From courses where dt = pDate - 3 and place in (1,2,3) ;
 Select count (*) into lNb3 From courses where dt = pDate - 2 and place in (1,2,3) ;
 Select count (*) into lNb2 From courses where dt = pDate - 1 and place in (1,2,3) ;
 if lNb4 + lNb2 + lNb3 >= 3 Then
  return 0;
 End if;
 Select count (*) into lNb1 From courses where dt = pDate  and place in (1,2,3) and numcourse <= pNum;
 if lNb1 >= 3 Then
  return 3;
 End if;
 if lNb1 + lNb2 >= 3 Then
  return 3;
 End if;
 if lNb1 + lNb2 + lNb3 >= 3 Then
  return 3;
 End if;
 return 0;
End;
/

Code :

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

1
2
3
4
 
Select dt,place,numcourse, decode (IsInPeriod (Dt,numcourse),3,'Start Playing',null) tst
from courses
order by dt,numcourse;

Résultat:

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
 
DT                        PLACE                  NUMCOURSE              TST           
------------------------- ---------------------- ---------------------- ------------- 
22-01-2011                8                      7                                    
29-01-2011                2                      4                                    
30-01-2011                7                      3                                    
30-01-2011                5                      5                                    
30-01-2011                2                      7                                    
31-01-2011                3                      1                      Start Playing 
05-02-2011                1                      4                                    
06-02-2011                6                      5                                    
07-02-2011                5                      7                                    
07-02-2011                8                      8                                    
10-02-2011                6                      3                                    
12-02-2011                3                      2                                    
13-02-2011                3                      4                                    
13-02-2011                2                      5                      Start Playing 
14-02-2011                2                      7                                    
20-02-2011                9                      1                                    
20-02-2011                2                      4                                    
20-02-2011                6                      7                                    
21-02-2011                -1                     6                                    
22-02-2011                3                      5                                    
23-02-2011                2                      7                                    
23-02-2011                3                      8                      Start Playing 
24-02-2011                4                      2                                    
24-02-2011                -1                     4                                    
25-02-2011                1                      5                                    
03-03-2011                5                      1                                    
05-03-2011                6                      2                                    
06-03-2011                4                      1                                    
13-03-2011                9                      6

Le bornage et la détection du signal de jeux sont faits, reste le signal d’arrêt...

jko

**Waldar** · 10/10/2011, 21h28

Je n'ai pas le temps de terminer immédiatement, mais voilà un début qui me paraît intéressant :

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
SELECT dt, place, numcourse
     , case count(case when place in (1, 2, 3) then 1 end) over(order by dt + NumCourse/24 range between interval '2' day preceding and current row)
         when 3 then 1
       end as play
  FROM Courses;
 
DT                        PLACE                  NUMCOURSE              PLAY                   
------------------------- ---------------------- ---------------------- ---------------------- 
22/01/2011                8                      7                                             
29/01/2011                2                      4                                             
30/01/2011                7                      3                                             
30/01/2011                5                      5                                             
30/01/2011                2                      7                                             
31/01/2011                3                      1                      1                      
05/02/2011                1                      4                                             
06/02/2011                6                      5                                             
07/02/2011                5                      7                                             
07/02/2011                8                      8                                             
10/02/2011                6                      3                                             
12/02/2011                3                      2                                             
13/02/2011                3                      4                                             
13/02/2011                2                      5                      1                      
14/02/2011                2                      7                      1                      
20/02/2011                9                      1                                             
20/02/2011                2                      4                                             
20/02/2011                6                      7                                             
21/02/2011                -1                     6                                             
22/02/2011                3                      5                                             
23/02/2011                2                      7                                             
23/02/2011                3                      8                      1                      
24/02/2011                4                      2                      1                      
24/02/2011                -1                     4                      1                      
25/02/2011                1                      5                      1                      
03/03/2011                5                      1                                             
05/03/2011                6                      2                                             
06/03/2011                4                      1                                             
13/03/2011                9                      6

Il reste à propager les 1 trois fois exactement à partir de leur première occurrence. Tobibisan effectivement peut s'avérer utile, mais ça va générer deux ou trois sous-requête.

**jkofr** · 10/10/2011, 22h42

Je tente de compléter...

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
 
 
Select Dt,place,numcourse,play
From
(
Select dt,place,numcourse,play, 
  nvl (lag (play,1) over(ORDER BY dt ASC, numcourse ASC),0) StopPlay
from
(
SELECT dt, place, numcourse
     , case count(case when place IN (1, 2, 3) then 1 end) over(ORDER BY dt + NumCourse/24 range BETWEEN interval '2' day preceding AND current row)
         when 3 then 1
       end AS play
  FROM Courses
)
) where StopPlay <> 1

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
 
 
DT                        PLACE                  NUMCOURSE              PLAY                   
------------------------- ---------------------- ---------------------- ---------------------- 
22/01/11                  8                      7                                             
29/01/11                  2                      4                                             
30/01/11                  7                      3                                             
30/01/11                  5                      5                                             
30/01/11                  2                      7                                             
31/01/11                  3                      1                      1                      
06/02/11                  6                      5                                             
07/02/11                  5                      7                                             
07/02/11                  8                      8                                             
10/02/11                  6                      3                                             
12/02/11                  3                      2                                             
13/02/11                  3                      4                                             
13/02/11                  2                      5                      1                      
20/02/11                  2                      4                                             
20/02/11                  6                      7                                             
21/02/11                  -1                     6                                             
22/02/11                  3                      5                                             
23/02/11                  2                      7                                             
23/02/11                  3                      8                      1                      
05/03/11                  6                      2                                             
06/03/11                  4                      1                                             
13/03/11                  9                      6

**skuatamad** · 10/10/2011, 22h49

J'ai au moins un début de solution (à la spécificité près pas encore envisagée) à base de WITH récursif donc 11GR2... tu n'as pas précisé ta version j'espère que ça peut te servir. Moi au moins ça m'a permis de rentré dans le WITH récursif

J'ai mis des commentaires je pense que c'est à peu près compréhensible :

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
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
89
90
91
92
93
94
SQL>   with courses_rank as (
select /*+ materialize */
       dt, place, NumCourse,
       dense_rank() over (order by dt) as dr,
       row_number() over (order by dt, NumCourse) as rn
  from courses
),
       courses_start as (
select dt, place, NumCourse, dr, rn,
       /*On ne compte que les courses dans les 3 1ers*/
       (select sum(case when t2.place between 1 and 3 
                        then 1 
                    end )
          from courses_rank t2
               /*Pour les jours entre date-2 et date du jour*/
         where t2.dr between t1.dr-2 and t1.dr
               /*Mais pas les courses suivantes de la date du jour*/
           and t2.rn <= t1.rn) as nb_1_3
  from courses_rank t1
),
       /*récursivité pour déterminer les starts pour jouer*/
       recursive_increment (dt, place, NumCourse, dr, rn, ready_to_gamble, day_of_ready) as (
select dt, place, NumCourse, dr, rn, 1, 1
  from courses_start
 where dr = 1
 union all
select cs.dt, cs.place, cs.NumCourse, cs.dr, cs.rn,
       /*On stocke le dr de la ligne désignant le départ pour jouer si :
         - au moins 3 x dans les 3 1ers 
         - Mais pas dans les 3 lignes qui suivent (on reste sur le même dr)
         - Et pas si dans les 2 jours précédents on a déjà commencé à jouer
      */
       case when cs.nb_1_3 >= 3
             and cs.rn > ri.ready_to_gamble + 3
             and cs.dr > ri.day_of_ready + 2
            then cs.rn
            else ri.ready_to_gamble
        end as ready_to_gamble,
            /*On sauvegarde le jour où on commence à jouer pour le 3eme test précédent*/
       case when cs.nb_1_3 >= 3 and cs.rn > ri.ready_to_gamble + 3 and cs.dr > ri.day_of_ready + 2
            then cs.dr
            else ri.day_of_ready
        end as day_of_ready
  from courses_start cs
  join recursive_increment ri on (cs.rn = ri.rn + 1)
)
select dt, place, NumCourse, dr, rn, ready_to_gamble,day_of_ready,
       /* YES si la ligne est dans les 3 courses suivantes d'un start pour jouer
          mais on exclu les faux positifs liés à l'init de la 1ere ligne sans historique pour décider de jouer
          Ce case n'est pas dans recursive_increment car on a besoin du ready_to_gamble de la ligne courante
       */
       case when rn between ready_to_gamble and ready_to_gamble + 2
             and ready_to_gamble <> 1
            then 'YES'
            else 'NO'
        end as play
  from recursive_increment
/
 
DT             PLACE  NUMCOURSE         DR         RN READY_TO_GAMBLE DAY_OF_READY PLA
--------- ---------- ---------- ---------- ---------- --------------- ------------ ---
22-JAN-11          8          7          1          1               1            1 NO
29-JAN-11          2          4          2          2               1            1 NO
30-JAN-11          7          3          3          3               1            1 NO
30-JAN-11          5          5          3          4               1            1 NO
30-JAN-11          2          7          3          5               1            1 NO
31-JAN-11          3          1          4          6               6            4 YES
05-FEB-11          1          4          5          7               6            4 YES
06-FEB-11          6          5          6          8               6            4 YES
07-FEB-11          5          7          7          9               6            4 NO
07-FEB-11          8          8          7         10               6            4 NO
10-FEB-11          6          3          8         11               6            4 NO
12-FEB-11          3          2          9         12               6            4 NO
13-FEB-11          3          4         10         13               6            4 NO
13-FEB-11          2          5         10         14              14           10 YES
14-FEB-11          2          7         11         15              14           10 YES
20-FEB-11          9          1         12         16              14           10 YES
20-FEB-11          2          4         12         17              14           10 NO
20-FEB-11          6          7         12         18              14           10 NO
21-FEB-11         -1          6         13         19              14           10 NO
22-FEB-11          3          5         14         20              14           10 NO
23-FEB-11          2          7         15         21              14           10 NO
23-FEB-11          3          8         15         22              22           15 YES
24-FEB-11          4          2         16         23              22           15 YES
24-FEB-11         -1          4         16         24              22           15 YES
25-FEB-11          1          5         17         25              22           15 NO
03-MAR-11          5          1         18         26              22           15 NO
05-MAR-11          6          2         19         27              22           15 NO
06-MAR-11          4          1         20         28              22           15 NO
13-MAR-11          9          6         21         29              22           15 NO
 
29 rows selected.
 
SQL>

Le hint /*+ materialize */ est obligatoire (du moins sur mon PC) pour l'étape :

Code :

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

1
2
/*Pour les jours entre date-2 et date du jour*/
         where t2.dr between t1.dr-2 and t1.dr

Sinon il faut remplacer la clause par :

Code :

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

1
2
/*Pour les jours entre date-2 et date du jour*/
         where t2.dr >= t1.dr-2 and t2.dr < t1.dr + 1

[edit]En fait quand je dis date du jour c'est la date de la ligne courante...

- Et pas si dans les 2 jours précédents on a déjà commencé à jouer

Comprendre :
- Et pas si dans les 2 jours suivants, car on a déjà commencé à jouer

[edit2]

Code :

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

1
2
3
4
recursive_increment (dt, place, NumCourse, dr, rn, ready_to_gamble, day_of_ready) AS (
SELECT dt, place, NumCourse, dr, rn, 1, 1
  FROM courses_start
 WHERE dr = 1

En fait c'est plutôt :

Code :

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

WHERE rn = 1

Pour ne sélectionner qu'une ligne...

**jkofr** · 11/10/2011, 08h31

Hello skuatamad,

Oui c'est une 11g R2 et cela fonctionne bien.

Un grand merci a toi!

Nous avons plusieurs écoles ici, avec ma petite fonction c'est relativement simple a comprendre et a adapter mais ta solution est juste géniale!
Je me demandais si il avais une solution plus jolie. C'est chose faite!
Avec tout mon background SQL je ne suis même pas capable de poser le graphe d’exécution de ta requête

Le recursive_increment (dt, place, NumCourse, dr, rn, ready_to_gamble, day_of_ready) me pose des problèmes de compréhension

En tout cas un grand merci.

Je laisse encore le sujet ouvert pour ceux qui on d'autres solutions plus (simples)

Je vais aussi poster tout a l'heure ma version basée sur 2 fonctions.

Jko

**jkofr** · 11/10/2011, 09h00

Voici une solution, avec une seule fonction.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
Select Dt,NumCourse, Place, decode (Play,1,'Play',null) Play
From
(
 Select dt,numcourse,place, max(Signal) over(ORDER BY dt ASC, numcourse ASC rows BETWEEN 3 preceding AND 1 preceding) Play
 From
 (
  Select dt,place,numcourse, decode (IsInPeriod (Dt,numcourse),3,1,0) Signal
  from courses
  order by dt,numcourse
 )
)

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
 
DT                        NUMCOURSE              PLACE                  PLAY 
------------------------- ---------------------- ---------------------- ---- 
22-01-2011                7                      8                           
29-01-2011                4                      2                           
30-01-2011                3                      7                           
30-01-2011                5                      5                           
30-01-2011                7                      2                           
31-01-2011                1                      3                           
05-02-2011                4                      1                      Play 
06-02-2011                5                      6                      Play 
07-02-2011                7                      5                      Play 
07-02-2011                8                      8                           
10-02-2011                3                      6                           
12-02-2011                2                      3                           
13-02-2011                4                      3                           
13-02-2011                5                      2                           
14-02-2011                7                      2                      Play 
20-02-2011                1                      9                      Play 
20-02-2011                4                      2                      Play 
20-02-2011                7                      6                           
21-02-2011                6                      -1                          
22-02-2011                5                      3                           
23-02-2011                7                      2                           
23-02-2011                8                      3                           
24-02-2011                2                      4                      Play 
24-02-2011                4                      -1                     Play 
25-02-2011                5                      1                      Play 
03-03-2011                1                      5                           
05-03-2011                2                      6                           
06-03-2011                1                      4                           
13-03-2011                6                      9

Je vais travailler sur une petite variante. L'objectif est d’interrompre le jeux dès que un gagnant a été touché. On appel cela le "stop at the winner" dans notre jargon.
Dans la première séquence de jeux, on s’arrête après le gain en ligne 10.

Je poste cela tout bientôt.
jko

**jkofr** · 11/10/2011, 09h25

Hello, skuatamad

Le premier cheval a jouer n'est pas le cheval sur lequel le signal de jeux est déclenché mais le suivant.

jko

**Waldar** · 11/10/2011, 11h00

Voilà j'ai pu terminer ma solution :

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
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
WITH Courses AS
(
SELECT date '2011-01-22' as Dt,  8 as Place, 7 as NumCourse FROM dual UNION ALL
SELECT date '2011-01-29'      ,  2         , 4              FROM dual UNION ALL
SELECT date '2011-01-30'      ,  7         , 3              FROM dual UNION ALL
SELECT date '2011-01-30'      ,  5         , 5              FROM dual UNION ALL
SELECT date '2011-01-30'      ,  2         , 7              FROM dual UNION ALL
SELECT date '2011-01-31'      ,  3         , 1              FROM dual UNION ALL
SELECT date '2011-02-05'      ,  1         , 4              FROM dual UNION ALL
SELECT date '2011-02-06'      ,  6         , 5              FROM dual UNION ALL
SELECT date '2011-02-07'      ,  5         , 7              FROM dual UNION ALL
SELECT date '2011-02-07'      ,  8         , 8              FROM dual UNION ALL
SELECT date '2011-02-10'      ,  6         , 3              FROM dual UNION ALL
SELECT date '2011-02-12'      ,  3         , 2              FROM dual UNION ALL
SELECT date '2011-02-13'      ,  3         , 4              FROM dual UNION ALL
SELECT date '2011-02-13'      ,  2         , 5              FROM dual UNION ALL
SELECT date '2011-02-14'      ,  2         , 7              FROM dual UNION ALL
SELECT date '2011-02-20'      ,  9         , 1              FROM dual UNION ALL
SELECT date '2011-02-20'      ,  2         , 4              FROM dual UNION ALL
SELECT date '2011-02-20'      ,  6         , 7              FROM dual UNION ALL
SELECT date '2011-02-21'      , -1         , 6              FROM dual UNION ALL
SELECT date '2011-02-22'      ,  3         , 5              FROM dual UNION ALL
SELECT date '2011-02-23'      ,  2         , 7              FROM dual UNION ALL
SELECT date '2011-02-23'      ,  3         , 8              FROM dual UNION ALL
SELECT date '2011-02-24'      ,  4         , 2              FROM dual UNION ALL
SELECT date '2011-02-24'      , -1         , 4              FROM dual UNION ALL
SELECT date '2011-02-25'      ,  1         , 5              FROM dual UNION ALL
SELECT date '2011-03-03'      ,  5         , 1              FROM dual UNION ALL
SELECT date '2011-03-05'      ,  6         , 2              FROM dual UNION ALL
SELECT date '2011-03-06'      ,  4         , 1              FROM dual UNION ALL
SELECT date '2011-03-13'      ,  9         , 6              FROM dual
)
  ,  SR as
(
  select dt, place, numcourse
       , case count(case when place in (1, 2, 3) then 1 end) over(order by dt + NumCourse/24 range between interval '2' day preceding and current row)
           when 3
           then 1
           else 0
         end as PrePlay
    from Courses
)
  select dt, place, numcourse
       , case
           when 1 in (lag(preplay, 1) over(order by dt asc, NumCourse asc),
                      lag(preplay, 2) over(order by dt asc, NumCourse asc),
                      lag(preplay, 3) over(order by dt asc, NumCourse asc))
            and 0  =  lag(preplay, 4) over(order by dt asc, NumCourse asc)
           then 'Yes'
           else 'No'
         end as Play
    from SR
order by dt asc, NumCourse asc;
 
DT              PLACE  NUMCOURSE PLAY
---------- ---------- ---------- ----
22/01/2011          8          7 No 
29/01/2011          2          4 No 
30/01/2011          7          3 No 
30/01/2011          5          5 No 
30/01/2011          2          7 No 
31/01/2011          3          1 No 
05/02/2011          1          4 Yes
06/02/2011          6          5 Yes
07/02/2011          5          7 Yes
07/02/2011          8          8 No 
10/02/2011          6          3 No 
12/02/2011          3          2 No 
13/02/2011          3          4 No 
13/02/2011          2          5 No 
14/02/2011          2          7 Yes
20/02/2011          9          1 Yes
20/02/2011          2          4 Yes
20/02/2011          6          7 No 
21/02/2011         -1          6 No 
22/02/2011          3          5 No 
23/02/2011          2          7 No 
23/02/2011          3          8 No 
24/02/2011          4          2 Yes
24/02/2011         -1          4 Yes
25/02/2011          1          5 Yes
03/03/2011          5          1 No 
05/03/2011          6          2 No 
06/03/2011          4          1 No 
13/03/2011          9          6 No

**jkofr** · 11/10/2011, 11h11

Super

Merci beaucoup Waldar.

Jko

**skuatamad** · 11/10/2011, 11h27

Le premier cheval a jouer n'est pas le cheval sur lequel le signal de jeux est déclenché mais le suivant.

J'ai pas de base sous la main, donc je ne suis pas sûr mais que donne cette modification dans ma requête finale :

Code :

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

1
2
3
4
5
case when rn BETWEEN ready_to_gamble + 1 AND ready_to_gamble + 3
      AND ready_to_gamble <> 1
     then 'YES'
     else 'NO'
 end AS play

Sinon je suis d'accord qu'avec du PL/SQL ce sera plus compréhensible, mais j'aurais fait l'inverse.
Détecter les starts en SQL, en utilisant une des idées précédemment proposées, puis utiliser une fonction pipelined pour pousser le nombre de ligne voulue à YES. En plus gérer le "stop at the winner" sera simple.

Bon sinon je pense que la solution de Waldar est plus simple, ou au moins, moins couteuse pour oracle

[edit] oui je viens de la relire, c'est nettement plus simple

Requête SQL un peu tordue

SQL Oracle

Discussions similaires

Partager

Partager