[H2] Quel code SQL pour créer une table en cotations 5 minutes à partir d'une table

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:

---

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.

Citation:

---

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,

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

Code:

---

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);

---