Aide pour une table et une requête SQL

Version imprimable

Bonjour,

J'aurais besoin d'aide pour la création d'une table, de ses index et la requête SQL associée dans le cadre d'une utilisation avec MySQL 5.7 sous Windows.
Cette table contient plusieurs dizaine de millions d'enregistrements.

Voici la table:

Code:

1
2
3
4
5
6
7
CREATE TABLE IF NOT EXISTS `TABLE1` (
   `REF1` VARCHAR(16) NOT NULL,
   `REF2` VARCHAR(16) NOT NULL,
   `REF3` VARCHAR(3) NOT NULL,
   `OPEN` BIGINT NOT NULL,
   `CLOSE` BIGINT NOT NULL
);

Voici mes index:

Code:

1
2
3
4
CREATE UNIQUE INDEX `I_1_2_3_OPEN` ON `TABLE1`(`REF1`,`REF2`, `REF3`,`OPEN`);
CREATE INDEX `I_3` ON `TABLE1`(`REF3`);
CREATE INDEX `I_1_2_3` ON `TABLE1`(`REF1`,`REF2`, `REF3`);
CREATE INDEX `I_1_2_3_OPEN_CLOSE` ON `TABLE1`(`REF1`,`REF2`, `REF3`, `OPEN`, `CLOSE`);

Et voici ma requête:

Code:

1
2
3
4
5
6
SELECT (a.close + 1) AS start, (a.open) AS start2, (min(b.open)-1) AS end
FROM table1 AS a, table1 AS b
WHERE b.open > a.open AND a.ref1='XXXX' AND a.ref2='YYYY' AND a.ref3='ZZZZ'
            AND b.ref1='XXXX' AND b.ref2='YYYY' AND b.ref3='ZZZZ'
GROUP BY a.open
HAVING (end - start)  > 0

Et voici quelques valeurs:

Code:

1
2
3
4
5
6
7
8
9
INSERT INTO TABLE1 (REF1,REF2,REF3,OPEN,CLOSE) VALUES ('CAC40','HBK','1HR',1520514900000,1520514959999);
INSERT INTO TABLE1 (REF1,REF2,REF3,OPEN,CLOSE) VALUES ('CAC40','HBK','1HR',1520514960000,1520515019999);
INSERT INTO TABLE1 (REF1,REF2,REF3,OPEN,CLOSE) VALUES ('CAC40','HBK','1HR',1520515020000,1520515079999);
INSERT INTO TABLE1 (REF1,REF2,REF3,OPEN,CLOSE) VALUES ('CAC40','HBK','1HR',1520515140000,1520515199999);
INSERT INTO TABLE1 (REF1,REF2,REF3,OPEN,CLOSE) VALUES ('CAC40','HBK','1HR',1520515200000,1520515259999);
INSERT INTO TABLE1 (REF1,REF2,REF3,OPEN,CLOSE) VALUES ('CAC40','HBK','1HR',1520515260000,1520515319999);
INSERT INTO TABLE1 (REF1,REF2,REF3,OPEN,CLOSE) VALUES ('CAC40','HBK','1HR',1520515320000,1520515379999);
INSERT INTO TABLE1 (REF1,REF2,REF3,OPEN,CLOSE) VALUES ('CAC40','HBK','1HR',1520515380000,1520515429999);
INSERT INTO TABLE1 (REF1,REF2,REF3,OPEN,CLOSE) VALUES ('CAC40','HBK','1HR',1520515440000,1520515499999);

La requête devrait me retourner les intervalles manquants, à savoir:
START-START2-END
1520515080000-1520515080000-1520515139999
1520515380000-1520515430000-1520515440000

Malgré la création de mes index, cette requête est trèèèèèèèès longue.
L'objectif de cette table est de stocker les valeurs de différents actifs. L'objectif de la requête est de trouver les "gaps" entre les instants d' "ouverture" et de "fermeture" de chaque valeur.
Verriez vous un moyen de l'améliorer ?

Merci beaucoup (d'avance) !

Mehdi

14/03/2018, 16h09
Waldar

Il manque quelques données pour vous aider et ce que vous voulez faire.
La jointure en l'état me parait inutile, mais il m'en faut un peu plus.
14/03/2018, 20h14
MehdiD83

J'ai mis à jour le post de départ avec des données d'exemple... et le résultat attendu.
Merci d'y avoir montré un intérêt !

Citation:

Envoyé par Waldar

Il manque quelques données pour vous aider et ce que vous voulez faire.
La jointure en l'état me parait inutile, mais il m'en faut un peu plus.

Réponse

Je crois avoir réussi avec la requête suivante:

Code:

1
2
3
4
5
6
SELECT t1 AS `start`, min(t3.open)-1 AS `end`
FROM table1 AS t1
LEFT OUTER JOIN table1 AS t2 ON t2.ref1=t1.ref1 AND t2.ref2=t1.ref2 AND t2.ref3=t1.ref3 AND t2.open=t1.close+1
LEFT OUTER JOIN table1 AS t3 ON t3.ref1=t1.ref1 AND t3.ref2=t1.ref2 AND t3.ref3=t1.ref3 AND t3.open>t1.open
WHERE t1.ref1='XXXX' AND t1.ref2='YYYY' AND t1.ref3='ZZZZ' AND t2.open IS NULL
GROUP BY t1.open

Si jamais vous voyez une amélioration.... Et merci quoiqu'il en soit.

MySQL c'est un peu limitant en effet c'est dommage.
Regardez si vous pouvez simuler un lead en vous appuyant sur ce lien, l'idée étant de comparer le lead de open avec close +1, comme vous le faites dans la 1ère jointure de votre seconde solution.
Sur un autre SGBD, j'aurai écrit votre requête de cette façon :

Code:

1
2
3
4
5
6
7
8
9
select "start", "start2", "end"
  from (select close + 1 as "start"
             , open      as "start2"
             , lead(open) over(partition by ref1, ref2, ref3 order by open asc) as "end"
          from table1
         where ref1 = 'CAC40'
           and ref2 = 'HBK'
           and ref3 = '1HR') t
 where "end" > "start";

Dans le cas où vous n'avez pas de continuité, est-ce si important de remonter la valeur problématique, car vous avez déjà la valeur de départ et de surcroît c'est ce qui vous coûte assez cher.
Enfin, si c'est le cas rare (<1%), vous pouvez envisager d'utiliser une requête scalaire plutôt qu'une jointure :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
    SELECT a.close + 1     AS "start"
         , a.open          AS "start2"
         , (select min(b.open) - 1 as "end"
              from table1 as b
             where b.open > a.open
               and b.ref1 = a.ref1
               and b.ref2 = a.ref2
               and b.ref3 = a.ref3) as "end"
      from table1 as a
     where not exists (select null
                         from table1 as b
                        where b.open = a.close + 1
                          and b.ref1 = a.ref1
                          and b.ref2 = a.ref2
                          and b.ref3 = a.ref3)
     where a.ref1 = 'CAC40'
       and a.ref2 = 'HBK'
       and a.ref3 = '1HR';

Mais on n'est pas très loin de votre solution.