Discussion :

[Pierre8r]

Bonjour,

Ma database SQL :
http://www.h2database.com/

Quel est le code SQL pour créer une table en cotations 5 minutes à partir d'une table en cotations 1 minute ?

J'ai créé une table en time frame 1mn ayant cette structure :

Code :

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

1
2
3
4
5
6
7
8
9
10
CREATE TABLE QUOTES1MN
(
   DATE TIMESTAMP,
   OPEN DOUBLE,
   HIGH DOUBLE,
   LOW DOUBLE,
   CLOSE DOUBLE,
   VOLUME BIGINT
)
;

Je souhaite constituer une table en time frame 5 mns QUOTES5MNS, à partir des données de ma table en time frame 1mn QUOTES1MN.
Pour constituer ma table en time frame 5 mns QUOTES5MNS, à partir des données de ma table en time frame 1mn QUOTES1MN, je dois procéder comme suis :

La première ligne correspondant aux premières 5 minutes et est constitué ainsi :
DATE : correspond à DATE de la minute 4.
OPEN : correspond à l'OPEN de la minute 0
HIGH : correspond au plus haut des HIGH des minutes 0 à 4.
LOW : correspond au plus bas des LOW des minutes 0 à 4.
CLOSE : correspond au CLOSE de la minute 4
VOLUME : correspond à la somme des VOLUME des minutes de 0 à 4.

La seconde ligne correspondant aux 5 minutes suivantes et est constituée ainsi :
DATE : correspond à DATE de la minute 9.
OPEN : correspond à l'OPEN de la minute 5
HIGH : correspond au plus haut des HIGH des minutes 5 à 9.
LOW : correspond au plus bas des LOW des minutes 5 à 9.
CLOSE : correspond au CLOSE de la minute 9
VOLUME : correspond à la somme des VOLUME des minutes de 5 à 9.

La troisième ligne correspondant aux 5 minutes suivantes et est constituée ainsi :
DATE : correspond à DATE de la minute 14.
OPEN : correspond à l'OPEN de la minute 10
HIGH : correspond au plus haut des HIGH des minutes 10 à 14.
LOW : correspond au plus bas des LOW des minutes 10 à 14.
CLOSE : correspond au CLOSE de la minute 14
VOLUME : correspond à la somme des VOLUME des minutes de 10 à 14.

etc.

Datas de ma table QUOTES1MN.

DATE,OPEN,HIGH,LOW,CLOSE,VOLUME
2007-01-02 12:00:00.0,"1,9715","1,9715","1,97125","1,97125",-1
2007-01-02 12:01:00.0,"1,97125","1,9714","1,9709","1,97095",-1
2007-01-02 12:02:00.0,"1,97095","1,9713","1,9709","1,9713",-1
2007-01-02 12:03:00.0,"1,9713","1,9713","1,9711","1,97115",-1
2007-01-02 12:04:00.0,"1,97115","1,97115","1,9709","1,971",-1
2007-01-02 12:05:00.0,"1,971","1,97105","1,9697","1,97005",-1
2007-01-02 12:06:00.0,"1,97005","1,97005","1,9697","1,9699",-1
2007-01-02 12:07:00.0,"1,9699","1,97005","1,9697","1,9697",-1
2007-01-02 12:08:00.0,"1,9697","1,9698","1,96935","1,9697",-1
2007-01-02 12:09:00.0,"1,9697","1,96985","1,96945","1,9697",-1
2007-01-02 12:10:00.0,"1,9697","1,96975","1,9697","1,96975",-1
2007-01-02 12:11:00.0,"1,96975","1,96975","1,96935","1,96945",-1
2007-01-02 12:12:00.0,"1,96945","1,9695","1,96945","1,9695",-1
2007-01-02 12:13:00.0,"1,9695","1,96955","1,96945","1,96955",-1
2007-01-02 12:14:00.0,"1,96955","1,9697","1,96955","1,9696",-1
2007-01-02 12:15:00.0,"1,9696","1,96975","1,96955","1,96975",-1
2007-01-02 12:16:00.0,"1,96975","1,9702","1,96975","1,97015",-1
2007-01-02 12:17:00.0,"1,97015","1,97065","1,97015","1,97065",-1
2007-01-02 12:18:00.0,"1,97065","1,97075","1,9706","1,9707",-1
2007-01-02 12:19:00.0,"1,9707","1,9709","1,9706","1,9709",-1
2007-01-02 12:20:00.0,"1,9709","1,9711","1,9709","1,971",-1
2007-01-02 12:21:00.0,"1,971","1,971","1,97085","1,9709",-1

Merci,

[Pierre8r]

Je crois avoir trouvé la solution.
Si vous avez plus simple merci de me le signaler.

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
DROP TABLE IF EXISTS QUOTES1MN;
 
CREATE TABLE QUOTES1MN
(
   Q1_DATE TIMESTAMP,
   Q1_OPEN DOUBLE,
   Q1_HIGH DOUBLE,
   Q1_LOW DOUBLE,
   Q1_CLOSE DOUBLE,
   Q1_VOLUME BIGINT
);
 
INSERT INTO QUOTES1MN
SELECT ParseDateTime(CONCAT(Q1_DATE,' ',Q1_TIME), 'MM/dd/yyyy HH:mm'),
Q1_OPEN, Q1_HIGH, Q1_LOW, Q1_CLOSE, Q1_VOLUME from CSVREAD
(
   'K:\\12-H2-Data\\GBPUSD-1mn.txt',
   'Q1_DATE,Q1_TIME, Q1_OPEN, Q1_HIGH, Q1_LOW, Q1_CLOSE, Q1_VOLUME',
   null,
   ','
);
 
DROP TABLE IF EXISTS QUOTES5MNS;
 
CREATE TABLE QUOTES5MNS
(
   Q5_TF5  TIMESTAMP,
   Q5_DATE TIMESTAMP,
   Q5_OPEN DOUBLE,
   Q5_HIGH DOUBLE,
   Q5_LOW DOUBLE,
   Q5_CLOSE DOUBLE,
   Q5_VOLUME BIGINT
);
 
DROP TABLE IF EXISTS TEMPO;
CREATE TABLE TEMPO
(
   TP_TF5 TIMESTAMP,
   TP_TF5MN INT, 
   TP_DATE TIMESTAMP,
   TP_OPEN DOUBLE,
   TP_HIGH DOUBLE,
   TP_LOW DOUBLE,
   TP_CLOSE DOUBLE,
   TP_VOLUME BIGINT
);
 
 
INSERT INTO TEMPO 
SELECT  
  *
FROM 
( SELECT   DATEADD('MINUTE', -(MOD(MINUTE(Q1_DATE),5)), Q1_DATE ) AS TF5, (MOD(MINUTE(Q1_DATE),5)) AS TF5MN,  *
FROM QUOTES1MN 
  )
;
 
INSERT INTO QUOTES5MNS (  Q5_TF5, Q5_HIGH, Q5_LOW,Q5_VOLUME)
SELECT  
 TF5,  MAX(Q1_HIGH), MIN(Q1_LOW), SUM(Q1_VOLUME) 
FROM 
( SELECT  (MOD(MINUTE(Q1_DATE),5)) AS TF5MN, DATEADD('MINUTE', -(MOD(MINUTE(Q1_DATE),5)), Q1_DATE ) AS TF5, *
FROM QUOTES1MN  )
GROUP BY TF5 
ORDER BY TF5
;
 
UPDATE QUOTES5MNS AS Q5
SET Q5.Q5_DATE = (SELECT TP.TP_DATE  
                FROM TEMPO AS TP
                WHERE  TP.TP_TF5 = Q5.Q5_TF5 AND TP.TP_TF5MN  = 4),
    Q5.Q5_OPEN  = (SELECT TP.TP_OPEN  
                FROM TEMPO AS TP
                WHERE  TP.TP_TF5 = Q5.Q5_TF5 AND TP.TP_TF5MN  = 0),
    Q5.Q5_CLOSE  = (SELECT TP.TP_CLOSE  
                FROM TEMPO AS TP
                WHERE  TP.TP_TF5 = Q5.Q5_TF5 AND TP.TP_TF5MN  = 4);