Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Discussion: JOIN avec DATETIME(3) [MariaDB]

  1. #1
    Membre à l'essai
    Homme Profil pro
    Administrateur systèmes et réseaux
    Inscrit en
    septembre 2013
    Messages
    29
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 33
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur systèmes et réseaux
    Secteur : High Tech - Opérateur de télécommunications

    Informations forums :
    Inscription : septembre 2013
    Messages : 29
    Points : 18
    Points
    18

    Par défaut JOIN avec DATETIME(3)

    Bonjour,

    J'ai deux tables:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
     
    CREATE TABLE T1 (
         the_time datetime(3),
         the_value float,
         PRIMARY KEY (the_time)
    )
     
     
    CREATE TABLE T2 (
         the_time datetime(3),
         the_value float,
         PRIMARY KEY (the_time)
    )
    Je souhaite faire une vue qui me donne the_value1 et the_value2 quand les dates sont identiques.
    Petit détails: il y a entre 1 et 10 millions de lignes dans chaque table.

    J'ai commence a faire une première vue comme ceci:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
     
    CREATE VIEW the_view AS
    SELECT T1.the_time AS the_time, T1.the_value AS a, T2.the_value AS b FROM T1 INNER JOIN T2
    ON T1.the_time = T2.the_time
    Les performances sont très bonnes, mais j'ai besoin de faire la comparaison de date sans utiliser les millisecondes des colonnes the_time. Et la j'ai plus de mal!
    J'ai essayé:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
     
    CREATE VIEW the_view AS
    SELECT T1.the_time AS the_time, T1.the_value AS a, T2.the_value AS b FROM T1 INNER JOIN T2
    ON YEAR(T1.the_time) = YEAR(T2.the_time)
    AND MONTH(T1.the_time) = MONTH(T2.the_time)
    AND DAY(T1.the_time) = DAY(T2.the_time)
    AND HOUR(T1.the_time) = HOUR(T2.the_time)
    AND MINUTE(T1.the_time) = MINUTE(T2.the_time)
    AND SECOND(T1.the_time) = SECOND(T2.the_time)
    Le résultat est bon, mais les performances sont catastrophique.
    Avez vous une idée pour améliorer les choses?
    Peut être créer une vue T1_bis qui copie T1 mais avec un datetime(0)?

    Merci par avance!

  2. #2
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    18 712
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 18 712
    Points : 43 721
    Points
    43 721

    Par défaut

    1) une table devrait TOUJOURS avoir une clef primaire. Sans les performances se dégradent fortement. Ou sont vos clef primaires dans les deux tables ?
    2) si les tantièmes de seconde ne vous intéressent pas, alors ne les stockez pas. Utilisez un DATETIME(0)
    3) indexez vos données pour obtenir des performances
    4) interdisez vous toute utilisation de fonction (en particulier dans votre cas YEAR, MONTH, DAY…) pour avoir des performances.

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  3. #3
    Membre régulier
    Homme Profil pro
    Analyste-programmeur
    Inscrit en
    décembre 2014
    Messages
    44
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 51
    Localisation : France

    Informations professionnelles :
    Activité : Analyste-programmeur
    Secteur : Conseil

    Informations forums :
    Inscription : décembre 2014
    Messages : 44
    Points : 86
    Points
    86

    Par défaut

    1) Peux-tu modifier les tables?
    2) Il est clair que les multiples appels aux fonctions YEAR, MONTH, DAY, etc sont la cause de cette lenteur. Déjà, utiliser 1 seul appel avec DATE() (au lieux de YEAR, MONTH, DAY) pour comparer aiderait! Aussi, partitionner les données avec YEAR(thet_time) aiderait probablement !
    3) Sur quelle version tu es?

  4. #4
    Membre à l'essai
    Homme Profil pro
    Administrateur systèmes et réseaux
    Inscrit en
    septembre 2013
    Messages
    29
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 33
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur systèmes et réseaux
    Secteur : High Tech - Opérateur de télécommunications

    Informations forums :
    Inscription : septembre 2013
    Messages : 29
    Points : 18
    Points
    18

    Par défaut

    Citation Envoyé par SQLpro Voir le message
    1) une table devrait TOUJOURS avoir une clef primaire. Sans les performances se dégradent fortement. Ou sont vos clef primaires dans les deux tables ?
    Erreur de ma part en rédigeant le post, les datetime(3) sont ma clé primaire. Je viens d’éditer le premier message.
    Ça ne me viendrait pas a l'esprit de créer une table sans clé primaire, je vous rassure!
    En plus je fais beaucoup de "LOAD DATA LOCAL INFILE..." en utilisant des sources redondantes: pas besoin de vérifier si les donnes sont déjà dans la base, MySQL le fait pour moi. C'est une des raison pour laquelle j'utilise MySQL et non des fichiers.
    Actuellement j'ai 10To de data en format binaire dans des fichiers a déchiffrer et stocker dans des bases... J’espère n'en sauver que 500Go après avoir fait le tri!

    Citation Envoyé par SQLpro Voir le message
    2) si les tantièmes de seconde ne vous intéressent pas, alors ne les stockez pas. Utilisez un DATETIME(0)
    Malheureusement dans certains cas les millisecondes sont très importantes pour moi.
    En fait je stocke toutes les telemetries de plusieurs satellites, je ne dois pas alterer les data.

    Citation Envoyé par SQLpro Voir le message
    3) indexez vos données pour obtenir des performances
    Avec une cle primaire sur la colonne "the_time" je ne pense pas avoir besoin d'index ou je fais erreur?

    Citation Envoyé par SQLpro Voir le message
    4) interdisez vous toute utilisation de fonction (en particulier dans votre cas YEAR, MONTH, DAY…) pour avoir des performances.
    J'en prend note

    Citation Envoyé par bstjean Voir le message
    1) Peux-tu modifier les tables?
    Oui a volonté si je ne porte pas atteinte a l’intégrité des données sources.

    Citation Envoyé par bstjean Voir le message
    2) Il est clair que les multiples appels aux fonctions YEAR, MONTH, DAY, etc sont la cause de cette lenteur. Déjà, utiliser 1 seul appel avec DATE() (au lieux de YEAR, MONTH, DAY) pour comparer aiderait! Aussi, partitionner les données avec YEAR(thet_time) aiderait probablement !
    Partitionner les donnes... dans quel sens? Un table par année?

    Citation Envoyé par bstjean Voir le message
    3) Sur quelle version tu es?
    MariaDB 5.5.60 sur une Reh Hat Enterprise Linux 7.6

  5. #5
    Membre à l'essai
    Homme Profil pro
    Administrateur systèmes et réseaux
    Inscrit en
    septembre 2013
    Messages
    29
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 33
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur systèmes et réseaux
    Secteur : High Tech - Opérateur de télécommunications

    Informations forums :
    Inscription : septembre 2013
    Messages : 29
    Points : 18
    Points
    18

    Par défaut

    Pour tout de suite j'ai cette solution:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
     
    CREATE VIEW the_view AS
    SELECT T1.the_time AS the_time, T1.the_value AS a, T2.the_value AS b FROM T1 INNER JOIN T2
    ON T1.the_time >= DATE_SUB(NOW(), INTERVAL 1 DAY)
    AND T1.the_time <= T2.the_time
    AND T1.the_time >= DATE_SUB(T2.the_time, INTERVAL 1 SECOND)
    inconvénient : Je m'impose de n'utiliser les data que des dernière 24 heures (a voir en fonction des performances...)
    La requête prend 19 secondes a tourner...


    Question subsidiaire, attention ça va peut être vous choquer : est il possible de forcer le serveur a "calculer" la vue toute les 30 minutes par exemple, et du coup de faire des "SELECT * FROM ma_vue" tres rapide?
    J'ai bien peur que ça aille a l'inverse de principe des vues, mais je le tente!

  6. #6
    Membre régulier
    Homme Profil pro
    Analyste-programmeur
    Inscrit en
    décembre 2014
    Messages
    44
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 51
    Localisation : France

    Informations professionnelles :
    Activité : Analyste-programmeur
    Secteur : Conseil

    Informations forums :
    Inscription : décembre 2014
    Messages : 44
    Points : 86
    Points
    86

    Par défaut

    Pour mieux pouvoir répondre à tes questions, il faudrait savoir quel genre de requêtes tu fais (des exemples réels) ainsi qu'une idée de la répartition des données. Aussi, il faudrait savoir ce que tu essaie de faire (expliqué en MOTS) !

  7. #7
    Expert éminent sénior

    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    4 312
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 4 312
    Points : 10 396
    Points
    10 396
    Billets dans le blog
    1

    Par défaut

    Citation Envoyé par ben500fr Voir le message
    Avec une cle primaire sur la colonne "the_time" je ne pense pas avoir besoin d'index ou je fais erreur?
    L'indexation de la clef primaire est automatique pour MySQL


    Citation Envoyé par ben500fr Voir le message
    Citation Envoyé par SQLpro Voir le message
    4) interdisez vous toute utilisation de fonction (en particulier dans votre cas YEAR, MONTH, DAY…) pour avoir des performances.
    J'en prend note
    Plus précisément, dans votre requête vous utilisez des fonctions de chaque coté des critères de jointure, c'est ce qu'il ne faut pas faire car cela rende votre requête non "SArgAble" (non indexable)
    Par contre, on peut utiliser des fonctions sur l'une des deux tables en jointure : soit deux tables T1 et T2, si ma jointure recherche les lignes dans T2 en correspondance avec les valeurs déjà connues de T1, il faut mettre les fonctions sur les colonnes de T1, pas sur celles de T2


    Citation Envoyé par ben500fr Voir le message
    Partitionner les donnes... dans quel sens? Un table par année?
    Quand on parle de partitionnement on pense en général au partitionnement horizontal. Il s'agit, dans une seule et même table, de répartir les lignes dans plusieurs espaces de stockage, plusieurs tablespace donc, en fonction d'un critère de partitionnement. Cette technique est particulièrement recommandée pour les tables à très forte volumétrie.
    Le partitionnement vertical, lui, est utile dans le cas où des colonnes sont très volumineuses, on crée une deuxième table, dont la PK est la même que la table principale et dans laquelle on déporte ces colonnes.

  8. #8
    Expert éminent sénior Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    février 2011
    Messages
    3 856
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 79
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : février 2011
    Messages : 3 856
    Points : 11 753
    Points
    11 753

    Par défaut

    Salut ben500fr.

    Citation Envoyé par ben500fr
    Petit détails: il y a entre 1 et 10 millions de lignes dans chaque table.
    Un problème de performance !

    Citation Envoyé par ben500fr
    Je souhaite faire une vue qui me donne the_value1 et the_value2 quand les dates sont identiques.
    D'accord.

    Citation Envoyé par ben500fr
    mais j'ai besoin de faire la comparaison de date sans utiliser les millisecondes des colonnes the_time.
    Ce n'est pas possible, si vous désirez la performance.

    Il aurait fallu y penser avant de créer des tables qui ne sont pas conformes à ce que vous essayez de faire.

    Mais si on les modifie, voici ce que vous pouvez faire :
    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
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    133
    134
    135
    136
    137
    138
    139
    140
    141
    142
    143
    144
    145
    146
    147
    148
    149
    150
    151
    152
    153
    154
    155
    156
    157
    158
    159
    160
    161
    162
    163
    164
    165
    166
    167
    168
    169
    170
    171
    172
    173
    174
    175
    176
    177
    178
    179
    180
    181
    182
    183
    184
    185
    186
    187
    188
    189
    190
    191
    192
    193
    194
    195
    196
    197
    198
    199
    200
    201
    202
    203
    204
    205
    206
    207
    208
    209
    210
    211
    212
    213
    214
    215
    216
    217
    218
    219
    220
    221
    222
    223
    224
    225
    226
    227
    228
    229
    230
    231
    232
    233
    234
    235
    236
    237
    238
    239
    240
    241
    242
    243
    244
    245
    246
    247
    --------------
    START TRANSACTION
    --------------
     
    --------------
    DROP DATABASE IF EXISTS `base`
    --------------
     
    --------------
    CREATE DATABASE IF NOT EXISTS `base`
            DEFAULT CHARACTER SET `latin1`
            DEFAULT COLLATE       `latin1_general_ci`
    --------------
     
    --------------
    DROP TABLE IF EXISTS `t1`
    --------------
     
    --------------
    CREATE TABLE `t1`
    ( `the_time`      datetime(3) NOT NULL primary key,
      `the_value`     float       NOT NULL
    ) ENGINE=InnoDB
      DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
      ROW_FORMAT=COMPRESSED
    --------------
     
    --------------
    insert into `t1` (`the_time`,`the_value`) values
      ('2018-12-21 05:44:33.123',125.44),
      ('2018-12-21 05:44:33.456',251.21),
      ('2018-12-21 05:45:17.005',452.12),
      ('2018-12-22 05:45:49.751',128.33),
      ('2018-12-22 05:46:23.789',351.72)
    --------------
     
    --------------
    select * from `t1`
    --------------
     
    +-------------------------+-----------+
    | the_time                | the_value |
    +-------------------------+-----------+
    | 2018-12-21 05:44:33.123 |    125.44 |
    | 2018-12-21 05:44:33.456 |    251.21 |
    | 2018-12-21 05:45:17.005 |    452.12 |
    | 2018-12-22 05:45:49.751 |    128.33 |
    | 2018-12-22 05:46:23.789 |    351.72 |
    +-------------------------+-----------+
    --------------
    DROP TABLE IF EXISTS `t2`
    --------------
     
    --------------
    CREATE TABLE `t2`
    ( `the_time`      datetime(3) NOT NULL primary key,
      `the_value`     float       NOT NULL
    ) ENGINE=InnoDB
      DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
      ROW_FORMAT=COMPRESSED
    --------------
     
    --------------
    insert into `t2` (`the_time`,`the_value`) values
      ('2018-12-21 05:44:33.123',125.44),
      ('2018-12-21 05:44:33.456',251.21),
      ('2018-12-21 05:45:17.005',452.12),
      ('2018-12-22 05:45:49.751',128.33),
      ('2018-12-22 05:46:23.789',351.72)
    --------------
     
    --------------
    select * from `t2`
    --------------
     
    +-------------------------+-----------+
    | the_time                | the_value |
    +-------------------------+-----------+
    | 2018-12-21 05:44:33.123 |    125.44 |
    | 2018-12-21 05:44:33.456 |    251.21 |
    | 2018-12-21 05:45:17.005 |    452.12 |
    | 2018-12-22 05:45:49.751 |    128.33 |
    | 2018-12-22 05:46:23.789 |    351.72 |
    +-------------------------+-----------+
    --------------
    commit
    --------------
     
    --------------
    alter table `t1` add `the_time_bis`  datetime(0) generated always as (`the_time`) stored NOT NULL
    --------------
     
    --------------
    alter table `t1` add index `idx1` (the_time_bis)
    --------------
     
    --------------
    describe `t1`
    --------------
     
    +--------------+-------------+------+-----+---------+------------------+
    | Field        | Type        | Null | Key | Default | Extra            |
    +--------------+-------------+------+-----+---------+------------------+
    | the_time     | datetime(3) | NO   | PRI | NULL    |                  |
    | the_value    | float       | NO   |     | NULL    |                  |
    | the_time_bis | datetime    | NO   | MUL | NULL    | STORED GENERATED |
    +--------------+-------------+------+-----+---------+------------------+
    --------------
    select * from `t1`
    --------------
     
    +-------------------------+-----------+---------------------+
    | the_time                | the_value | the_time_bis        |
    +-------------------------+-----------+---------------------+
    | 2018-12-21 05:44:33.123 |    125.44 | 2018-12-21 05:44:33 |
    | 2018-12-21 05:44:33.456 |    251.21 | 2018-12-21 05:44:33 |
    | 2018-12-21 05:45:17.005 |    452.12 | 2018-12-21 05:45:17 |
    | 2018-12-22 05:45:49.751 |    128.33 | 2018-12-22 05:45:50 |
    | 2018-12-22 05:46:23.789 |    351.72 | 2018-12-22 05:46:24 |
    +-------------------------+-----------+---------------------+
    --------------
    commit
    --------------
     
    --------------
    alter table `t2` add `the_time_bis`  datetime(0) generated always as (`the_time`) stored NOT NULL
    --------------
     
    --------------
    alter table `t2` add index `idx2` (the_time_bis)
    --------------
     
    --------------
    describe `t2`
    --------------
     
    +--------------+-------------+------+-----+---------+------------------+
    | Field        | Type        | Null | Key | Default | Extra            |
    +--------------+-------------+------+-----+---------+------------------+
    | the_time     | datetime(3) | NO   | PRI | NULL    |                  |
    | the_value    | float       | NO   |     | NULL    |                  |
    | the_time_bis | datetime    | NO   | MUL | NULL    | STORED GENERATED |
    +--------------+-------------+------+-----+---------+------------------+
    --------------
    select * from `t2`
    --------------
     
    +-------------------------+-----------+---------------------+
    | the_time                | the_value | the_time_bis        |
    +-------------------------+-----------+---------------------+
    | 2018-12-21 05:44:33.123 |    125.44 | 2018-12-21 05:44:33 |
    | 2018-12-21 05:44:33.456 |    251.21 | 2018-12-21 05:44:33 |
    | 2018-12-21 05:45:17.005 |    452.12 | 2018-12-21 05:45:17 |
    | 2018-12-22 05:45:49.751 |    128.33 | 2018-12-22 05:45:50 |
    | 2018-12-22 05:46:23.789 |    351.72 | 2018-12-22 05:46:24 |
    +-------------------------+-----------+---------------------+
    --------------
    commit
    --------------
     
    --------------
    select      t1.the_time,
                t1.the_value,
                t2.the_time,
                t2.the_value
     
         from  `t1` as t1
     
    inner join `t2` as t2
            on  t2.the_time = t1.the_time
    --------------
     
    +-------------------------+-----------+-------------------------+-----------+
    | the_time                | the_value | the_time                | the_value |
    +-------------------------+-----------+-------------------------+-----------+
    | 2018-12-21 05:44:33.123 |    125.44 | 2018-12-21 05:44:33.123 |    125.44 |
    | 2018-12-21 05:44:33.456 |    251.21 | 2018-12-21 05:44:33.456 |    251.21 |
    | 2018-12-21 05:45:17.005 |    452.12 | 2018-12-21 05:45:17.005 |    452.12 |
    | 2018-12-22 05:45:49.751 |    128.33 | 2018-12-22 05:45:49.751 |    128.33 |
    | 2018-12-22 05:46:23.789 |    351.72 | 2018-12-22 05:46:23.789 |    351.72 |
    +-------------------------+-----------+-------------------------+-----------+
    --------------
    explain
    select      t1.the_time,
                t1.the_value,
                t2.the_time,
                t2.the_value
     
         from  `t1` as t1
     
    inner join `t2` as t2
            on  t2.the_time = t1.the_time
    --------------
     
    +----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
    | id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref              | rows | filtered | Extra |
    +----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
    |  1 | SIMPLE      | t2    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL             |    1 |   100.00 | NULL  |
    |  1 | SIMPLE      | t1    | NULL       | eq_ref | PRIMARY       | PRIMARY | 7       | base.t2.the_time |    1 |   100.00 | NULL  |
    +----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
    --------------
    select      t1.the_time,
                t1.the_value,
                t2.the_time,
                t2.the_value
     
         from  `t1` as t1
     
    inner join `t2` as t2
            on  t2.the_time_bis = t1.the_time_bis
    --------------
     
    +-------------------------+-----------+-------------------------+-----------+
    | the_time                | the_value | the_time                | the_value |
    +-------------------------+-----------+-------------------------+-----------+
    | 2018-12-21 05:44:33.123 |    125.44 | 2018-12-21 05:44:33.123 |    125.44 |
    | 2018-12-21 05:44:33.456 |    251.21 | 2018-12-21 05:44:33.123 |    125.44 |
    | 2018-12-21 05:44:33.123 |    125.44 | 2018-12-21 05:44:33.456 |    251.21 |
    | 2018-12-21 05:44:33.456 |    251.21 | 2018-12-21 05:44:33.456 |    251.21 |
    | 2018-12-21 05:45:17.005 |    452.12 | 2018-12-21 05:45:17.005 |    452.12 |
    | 2018-12-22 05:45:49.751 |    128.33 | 2018-12-22 05:45:49.751 |    128.33 |
    | 2018-12-22 05:46:23.789 |    351.72 | 2018-12-22 05:46:23.789 |    351.72 |
    +-------------------------+-----------+-------------------------+-----------+
    --------------
    explain
    select      t1.the_time,
                t1.the_value,
                t2.the_time,
                t2.the_value
     
         from  `t1` as t1
     
    inner join `t2` as t2
            on  t2.the_time_bis = t1.the_time_bis
    --------------
     
    +----+-------------+-------+------------+------+---------------+------+---------+----------------------+------+----------+-------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref                  | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+------+---------+----------------------+------+----------+-------+
    |  1 | SIMPLE      | t2    | NULL       | ALL  | idx2          | NULL | NULL    | NULL                 |    1 |   100.00 | NULL  |
    |  1 | SIMPLE      | t1    | NULL       | ref  | idx1          | idx1 | 5       | base.t2.the_time_bis |    1 |   100.00 | NULL  |
    +----+-------------+-------+------------+------+---------------+------+---------+----------------------+------+----------+-------+
    --------------
    COMMIT
    --------------
     
    Appuyez sur une touche pour continuer...
    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr

  9. #9
    Membre à l'essai
    Homme Profil pro
    Administrateur systèmes et réseaux
    Inscrit en
    septembre 2013
    Messages
    29
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 33
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur systèmes et réseaux
    Secteur : High Tech - Opérateur de télécommunications

    Informations forums :
    Inscription : septembre 2013
    Messages : 29
    Points : 18
    Points
    18

    Par défaut

    Citation Envoyé par bstjean Voir le message
    Pour mieux pouvoir répondre à tes questions, il faudrait savoir quel genre de requêtes tu fais (des exemples réels) ainsi qu'une idée de la répartition des données. Aussi, il faudrait savoir ce que tu essaie de faire (expliqué en MOTS) !
    Je souahite avoir une interface utilisateur qui affiche des graphs / plots de données satellites. Ma question originelle est ici:
    https://www.developpez.net/forums/d1...s-issus-mysql/

    J'ai, pour chaque paramètre, sa dates (a la milliseconde près) et sa valeur, un FLOAT.
    Pour les stocker, j'ai choisi de faire un database par satellite et une table par paramètre.

    Je crée l'interface en php, et je dessine mes graphs avec Gnuplot, qui me semble super performant, même avec 1.000.000 couples date/valeur en entrée.


    Dans le cas particulier de ce topic, je souhaite faire un graph avec un paramètre sur l'axe x, et un paramètre sur l'axe y. D’où ma jointure.
    Le plus important est que le graph s'affiche vite pour l'utilisateur (temps requete SQL + generation PHP + création du graphe avec gnuplot).
    Bien sure, je ne reçois pas forcement les paramètres de l'axe x et de l'axe y au même moment. D’où ma demande de jointure avec des datetime sans millisecondes, et même a 5 secondes près.
    A terme pour les graphes avec un paramètre sur x au lieu du temps, je pense que je proposerai a l'utilisateur de choisir la "qualité" de la jointure: a la milliseconde, la seconde, 5 secondes ou 15 secondes.

    Voile j’espère que c'est plus claire



    Citation Envoyé par Artemus24 Voir le message

    Il aurait fallu y penser avant de créer des tables qui ne sont pas conformes à ce que vous essayez de faire.

    Mais si on les modifie, voici ce que vous pouvez faire :
    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
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    133
    134
    135
    136
    137
    138
    139
    140
    141
    142
    143
    144
    145
    146
    147
    148
    149
    150
    151
    152
    153
    154
    155
    156
    157
    158
    159
    160
    161
    162
    163
    164
    165
    166
    167
    168
    169
    170
    171
    172
    173
    174
    175
    176
    177
    178
    179
    180
    181
    182
    183
    184
    185
    186
    187
    188
    189
    190
    191
    192
    193
    194
    195
    196
    197
    198
    199
    200
    201
    202
    203
    204
    205
    206
    207
    208
    209
    210
    211
    212
    213
    214
    215
    216
    217
    218
    219
    220
    221
    222
    223
    224
    225
    226
    227
    228
    229
    230
    231
    232
    233
    234
    235
    236
    237
    238
    239
    240
    241
    242
    243
    244
    245
    246
    247
    --------------
    START TRANSACTION
    --------------
     
    --------------
    DROP DATABASE IF EXISTS `base`
    --------------
     
    --------------
    CREATE DATABASE IF NOT EXISTS `base`
            DEFAULT CHARACTER SET `latin1`
            DEFAULT COLLATE       `latin1_general_ci`
    --------------
     
    --------------
    DROP TABLE IF EXISTS `t1`
    --------------
     
    --------------
    CREATE TABLE `t1`
    ( `the_time`      datetime(3) NOT NULL primary key,
      `the_value`     float       NOT NULL
    ) ENGINE=InnoDB
      DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
      ROW_FORMAT=COMPRESSED
    --------------
     
    --------------
    insert into `t1` (`the_time`,`the_value`) values
      ('2018-12-21 05:44:33.123',125.44),
      ('2018-12-21 05:44:33.456',251.21),
      ('2018-12-21 05:45:17.005',452.12),
      ('2018-12-22 05:45:49.751',128.33),
      ('2018-12-22 05:46:23.789',351.72)
    --------------
     
    --------------
    select * from `t1`
    --------------
     
    +-------------------------+-----------+
    | the_time                | the_value |
    +-------------------------+-----------+
    | 2018-12-21 05:44:33.123 |    125.44 |
    | 2018-12-21 05:44:33.456 |    251.21 |
    | 2018-12-21 05:45:17.005 |    452.12 |
    | 2018-12-22 05:45:49.751 |    128.33 |
    | 2018-12-22 05:46:23.789 |    351.72 |
    +-------------------------+-----------+
    --------------
    DROP TABLE IF EXISTS `t2`
    --------------
     
    --------------
    CREATE TABLE `t2`
    ( `the_time`      datetime(3) NOT NULL primary key,
      `the_value`     float       NOT NULL
    ) ENGINE=InnoDB
      DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
      ROW_FORMAT=COMPRESSED
    --------------
     
    --------------
    insert into `t2` (`the_time`,`the_value`) values
      ('2018-12-21 05:44:33.123',125.44),
      ('2018-12-21 05:44:33.456',251.21),
      ('2018-12-21 05:45:17.005',452.12),
      ('2018-12-22 05:45:49.751',128.33),
      ('2018-12-22 05:46:23.789',351.72)
    --------------
     
    --------------
    select * from `t2`
    --------------
     
    +-------------------------+-----------+
    | the_time                | the_value |
    +-------------------------+-----------+
    | 2018-12-21 05:44:33.123 |    125.44 |
    | 2018-12-21 05:44:33.456 |    251.21 |
    | 2018-12-21 05:45:17.005 |    452.12 |
    | 2018-12-22 05:45:49.751 |    128.33 |
    | 2018-12-22 05:46:23.789 |    351.72 |
    +-------------------------+-----------+
    --------------
    commit
    --------------
     
    --------------
    alter table `t1` add `the_time_bis`  datetime(0) generated always as (`the_time`) stored NOT NULL
    --------------
     
    --------------
    alter table `t1` add index `idx1` (the_time_bis)
    --------------
     
    --------------
    describe `t1`
    --------------
     
    +--------------+-------------+------+-----+---------+------------------+
    | Field        | Type        | Null | Key | Default | Extra            |
    +--------------+-------------+------+-----+---------+------------------+
    | the_time     | datetime(3) | NO   | PRI | NULL    |                  |
    | the_value    | float       | NO   |     | NULL    |                  |
    | the_time_bis | datetime    | NO   | MUL | NULL    | STORED GENERATED |
    +--------------+-------------+------+-----+---------+------------------+
    --------------
    select * from `t1`
    --------------
     
    +-------------------------+-----------+---------------------+
    | the_time                | the_value | the_time_bis        |
    +-------------------------+-----------+---------------------+
    | 2018-12-21 05:44:33.123 |    125.44 | 2018-12-21 05:44:33 |
    | 2018-12-21 05:44:33.456 |    251.21 | 2018-12-21 05:44:33 |
    | 2018-12-21 05:45:17.005 |    452.12 | 2018-12-21 05:45:17 |
    | 2018-12-22 05:45:49.751 |    128.33 | 2018-12-22 05:45:50 |
    | 2018-12-22 05:46:23.789 |    351.72 | 2018-12-22 05:46:24 |
    +-------------------------+-----------+---------------------+
    --------------
    commit
    --------------
     
    --------------
    alter table `t2` add `the_time_bis`  datetime(0) generated always as (`the_time`) stored NOT NULL
    --------------
     
    --------------
    alter table `t2` add index `idx2` (the_time_bis)
    --------------
     
    --------------
    describe `t2`
    --------------
     
    +--------------+-------------+------+-----+---------+------------------+
    | Field        | Type        | Null | Key | Default | Extra            |
    +--------------+-------------+------+-----+---------+------------------+
    | the_time     | datetime(3) | NO   | PRI | NULL    |                  |
    | the_value    | float       | NO   |     | NULL    |                  |
    | the_time_bis | datetime    | NO   | MUL | NULL    | STORED GENERATED |
    +--------------+-------------+------+-----+---------+------------------+
    --------------
    select * from `t2`
    --------------
     
    +-------------------------+-----------+---------------------+
    | the_time                | the_value | the_time_bis        |
    +-------------------------+-----------+---------------------+
    | 2018-12-21 05:44:33.123 |    125.44 | 2018-12-21 05:44:33 |
    | 2018-12-21 05:44:33.456 |    251.21 | 2018-12-21 05:44:33 |
    | 2018-12-21 05:45:17.005 |    452.12 | 2018-12-21 05:45:17 |
    | 2018-12-22 05:45:49.751 |    128.33 | 2018-12-22 05:45:50 |
    | 2018-12-22 05:46:23.789 |    351.72 | 2018-12-22 05:46:24 |
    +-------------------------+-----------+---------------------+
    --------------
    commit
    --------------
     
    --------------
    select      t1.the_time,
                t1.the_value,
                t2.the_time,
                t2.the_value
     
         from  `t1` as t1
     
    inner join `t2` as t2
            on  t2.the_time = t1.the_time
    --------------
     
    +-------------------------+-----------+-------------------------+-----------+
    | the_time                | the_value | the_time                | the_value |
    +-------------------------+-----------+-------------------------+-----------+
    | 2018-12-21 05:44:33.123 |    125.44 | 2018-12-21 05:44:33.123 |    125.44 |
    | 2018-12-21 05:44:33.456 |    251.21 | 2018-12-21 05:44:33.456 |    251.21 |
    | 2018-12-21 05:45:17.005 |    452.12 | 2018-12-21 05:45:17.005 |    452.12 |
    | 2018-12-22 05:45:49.751 |    128.33 | 2018-12-22 05:45:49.751 |    128.33 |
    | 2018-12-22 05:46:23.789 |    351.72 | 2018-12-22 05:46:23.789 |    351.72 |
    +-------------------------+-----------+-------------------------+-----------+
    --------------
    explain
    select      t1.the_time,
                t1.the_value,
                t2.the_time,
                t2.the_value
     
         from  `t1` as t1
     
    inner join `t2` as t2
            on  t2.the_time = t1.the_time
    --------------
     
    +----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
    | id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref              | rows | filtered | Extra |
    +----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
    |  1 | SIMPLE      | t2    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL             |    1 |   100.00 | NULL  |
    |  1 | SIMPLE      | t1    | NULL       | eq_ref | PRIMARY       | PRIMARY | 7       | base.t2.the_time |    1 |   100.00 | NULL  |
    +----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
    --------------
    select      t1.the_time,
                t1.the_value,
                t2.the_time,
                t2.the_value
     
         from  `t1` as t1
     
    inner join `t2` as t2
            on  t2.the_time_bis = t1.the_time_bis
    --------------
     
    +-------------------------+-----------+-------------------------+-----------+
    | the_time                | the_value | the_time                | the_value |
    +-------------------------+-----------+-------------------------+-----------+
    | 2018-12-21 05:44:33.123 |    125.44 | 2018-12-21 05:44:33.123 |    125.44 |
    | 2018-12-21 05:44:33.456 |    251.21 | 2018-12-21 05:44:33.123 |    125.44 |
    | 2018-12-21 05:44:33.123 |    125.44 | 2018-12-21 05:44:33.456 |    251.21 |
    | 2018-12-21 05:44:33.456 |    251.21 | 2018-12-21 05:44:33.456 |    251.21 |
    | 2018-12-21 05:45:17.005 |    452.12 | 2018-12-21 05:45:17.005 |    452.12 |
    | 2018-12-22 05:45:49.751 |    128.33 | 2018-12-22 05:45:49.751 |    128.33 |
    | 2018-12-22 05:46:23.789 |    351.72 | 2018-12-22 05:46:23.789 |    351.72 |
    +-------------------------+-----------+-------------------------+-----------+
    --------------
    explain
    select      t1.the_time,
                t1.the_value,
                t2.the_time,
                t2.the_value
     
         from  `t1` as t1
     
    inner join `t2` as t2
            on  t2.the_time_bis = t1.the_time_bis
    --------------
     
    +----+-------------+-------+------------+------+---------------+------+---------+----------------------+------+----------+-------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref                  | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+------+---------+----------------------+------+----------+-------+
    |  1 | SIMPLE      | t2    | NULL       | ALL  | idx2          | NULL | NULL    | NULL                 |    1 |   100.00 | NULL  |
    |  1 | SIMPLE      | t1    | NULL       | ref  | idx1          | idx1 | 5       | base.t2.the_time_bis |    1 |   100.00 | NULL  |
    +----+-------------+-------+------------+------+---------------+------+---------+----------------------+------+----------+-------+
    --------------
    COMMIT
    --------------
     
    Appuyez sur une touche pour continuer...

    Cette modification des tables semble être la solution idéale dans mon cas: l’écriture dans la base sera un peu plus lente, la bases prendront un peu (beaucoup?) plus de place dans les disques, mais les requêtes se feront BEAUCOUP plus vite

    Premier point: chez moi
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    alter table `t1` add `the_time_bis`  datetime(0) generated always as (`the_time`) stored NOT NULL
    produit une erreur "1064" a cause du "stored" et du "NOT NULL".
    Je remplace le "STORED" par "PERSISTENT", je ne suis pas sure de la différence entre les deux. Et je rajouter l’attribut "NOT NULL" avec une seconde requête SQL.

    Second point: Si au lieu de sauvegarder les dates a seconde près, je souhaite les sauvegarder a 5 secondes près par exemple? Je vais creer une colonne avec ces dates arrondi, je vois plusieurs solutions sur les forums:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    from_unixtime(floor(unix_timestamp('2006-10-10 14:26:01')/(60*5))*(60*5))
    FROM_UNIXTIME(UNIX_TIMESTAMP(time) - MOD(UNIX_TIMESTAMP(time),300))
    Vous auriez un conseil a me donner?

    Dernier point: quand je fais ma jointure
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
     
    create view the_view AS
    select      t1.the_time, t1.the_value, t2.the_time, t2.the_value
    from  `t1` as t1
    inner join `t2` as t2
    on  t2.the_time = t1.the_time
    je me retrouve avec la colonne t1.the_time non unique. Normale me direz vous au vue de la requête.
    Dois-je modifier la requetes pour avoir t1.the_time unique (une seule ligne)?
    Ou il faut simplement declarer le champ t1.the_time comme cle primaire de la vue?

  10. #10
    Expert éminent sénior Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    février 2011
    Messages
    3 856
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 79
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : février 2011
    Messages : 3 856
    Points : 11 753
    Points
    11 753

    Par défaut

    Salut ben500fr.

    Citation Envoyé par ben500fr
    Vous auriez un conseil à me donner?
    Vous avez plusieurs contraintes sur ce que vous cherchez à faire :

    1) ne pas trop augmenter la volumétrie de vos tables.
    2) ne pas trop ralentir l'écriture dans vos tables.
    3) obtenir la précision que vous cherchez.
    4) faire des jointures afin de récupérer les paramètres X et Y.
    5) vous ne recevez pas ces paramètres au même moment.
    6) au final, obtenir le couple (X ; Y).
    7) optimiser la requête d'extraction pour obtenir le couple val(X ; Y).

    Citation Envoyé par ben500fr
    je ne reçois pas forcement les paramètres de l'axe x et de l'axe y au même moment.
    Le problème se trouve là. D'où l'impossibilité de faire correctement vos jointures.

    Citation Envoyé par ben500fr
    D’où ma demande de jointure avec des datetime sans millisecondes, et même a 5 secondes près.
    La précision est secondaire quand on a le couple val(X ; Y).

    Citation Envoyé par ben500fr
    Je souhaite faire une vue qui me donne the_value1 et the_value2 quand les dates sont identiques.
    J'espère que vous n'avez pas que la date comme moyen de faire le rapprochement entre votre X et votre Y.
    Vous dites que vous ne les recevez pas en même temps. Mais pourtant, vous faites le rapprochement sur la date (timestamp).
    Vous dites un peu plus loin, que le rapprochement doit se faire sans les millisecondes, voire même à cinq secondes près.
    Vous aurez inévitablement des doublons. Comment alors faire le rapprochement ?

    La date ne sert qu'à trouver le couple val(X ; Y), mais pas à faire le rapprochement entre le X et le Y.

    Dois-je comprendre que vous avez en fait deux flux, l'un pour le X et l'autre pour le Y.
    Le premier X n'ayant pas de Y, recevra automatiquement le prochain Y, et vice-versa.
    Avez-vous besoin de conserver vos deux tables, l'une pour le X et l'autre pour le Y ?
    Je trouve qu'ils ne sont pas utilise et pose le problème du rapprochement.

    La mécanique à mettre en place consiste d'abord à identifier le flux X ou Y en premier.
    Si la ligne n'existe pas alors la créer.
    Si la ligne existe déjà, venir mettre à jour la partie Y si la partie est déjà renseignée, et vice-versa.
    Les attributions vont se faire dans l'ordre d'arrivée, à savoir le premier X avec le premier Y, le deuxième X avec le deuxième Y, et ainsi de suite.
    Voici ce que je propose :
    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
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    133
    134
    135
    136
    137
    138
    139
    140
    141
    142
    143
    144
    145
    146
    147
    148
    149
    --------------
    START TRANSACTION
    --------------
     
    --------------
    DROP DATABASE IF EXISTS `base`
    --------------
     
    --------------
    CREATE DATABASE IF NOT EXISTS `base`
            DEFAULT CHARACTER SET `latin1`
            DEFAULT COLLATE       `latin1_general_ci`
    --------------
     
    --------------
    DROP TABLE IF EXISTS `coordonnee`
    --------------
     
    --------------
    CREATE TABLE `coordonnee`
    ( `id`       integer unsigned  NOT NULL auto_increment primary key,
      `val_X`    float                 NULL,
      `temps_X`  datetime(3)           NULL,
      `val_Y`    float                 NULL,
      `temps_Y`  datetime(3)           NULL,
      index `idx1` (`val_x`,`val_y`,`id`),
      index `idx2` (`val_y`,`val_x`,`id`)
    ) ENGINE=InnoDB
      DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
      ROW_FORMAT=COMPRESSED
    --------------
     
    --------------
    DROP TABLE IF EXISTS `test`
    --------------
     
    --------------
    CREATE TABLE `test`
    ( `id`    integer unsigned  NOT NULL auto_increment primary key,
      `code`  char(01)          NOT NULL,
      `val`   float             NOT NULL,
      `temps` datetime(3)       NOT NULL,
      index `idx` (`code`,`temps`)
    ) ENGINE=InnoDB
      DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
      ROW_FORMAT=COMPRESSED
    --------------
     
    --------------
    DROP TRIGGER IF EXISTS `dispatch`
    --------------
     
    --------------
    CREATE TRIGGER `dispatch`
    BEFORE insert ON `test`
    FOR EACH ROW
    BEGIN
      DECLARE _id  integer DEFAULT NULL;
     
      IF NEW.code = 'X' THEN
         select min(id) into _id from `coordonnee` where val_x is null and val_y is not null;
      ELSE
         select min(id) into _id from `coordonnee` where val_y is null and val_x is not null;
      END IF;
     
      IF _id is not null then
         IF NEW.code = 'X' THEN
            update `coordonnee` set val_x = NEW.val, temps_x = NEW.temps where id = _id;
         ELSE
            update `coordonnee` set val_y = NEW.val, temps_y = NEW.temps where id = _id;
         END IF;
      ELSE
         IF NEW.code = 'X' THEN
            insert into `coordonnee` (`val_x`,`temps_x`) values (NEW.val, NEW.temps);
         ELSE
            insert into `coordonnee` (`val_y`,`temps_y`) values (NEW.val, NEW.temps);
         END IF;
      END IF;
    END
    --------------
     
    --------------
    insert into `test` (`code`,`val`,`temps`) values
      ('X', 17.12, '2019-02-12 10:00:00.123'),
      ('X', 16.23, '2019-02-12 10:00:01.123'),
      ('Y', 15.34, '2019-02-12 10:00:02.123'),
      ('X', 14.45, '2019-02-12 10:00:03.123'),
      ('X', 13.56, '2019-02-12 10:00:04.123'),
      ('Y', 12.67, '2019-02-12 10:00:05.123'),
      ('Y', 11.78, '2019-02-12 10:00:06.123'),
      ('Y', 10.89, '2019-02-12 10:00:07.123'),
      ('Y', 18.90, '2019-02-12 10:00:08.123'),
      ('X', 19.01, '2019-02-12 10:00:09.123')
    --------------
     
    --------------
    select * from `test`
    --------------
     
    +----+------+-------+-------------------------+
    | id | code | val   | temps                   |
    +----+------+-------+-------------------------+
    |  1 | X    | 17.12 | 2019-02-12 10:00:00.123 |
    |  2 | X    | 16.23 | 2019-02-12 10:00:01.123 |
    |  3 | Y    | 15.34 | 2019-02-12 10:00:02.123 |
    |  4 | X    | 14.45 | 2019-02-12 10:00:03.123 |
    |  5 | X    | 13.56 | 2019-02-12 10:00:04.123 |
    |  6 | Y    | 12.67 | 2019-02-12 10:00:05.123 |
    |  7 | Y    | 11.78 | 2019-02-12 10:00:06.123 |
    |  8 | Y    | 10.89 | 2019-02-12 10:00:07.123 |
    |  9 | Y    |  18.9 | 2019-02-12 10:00:08.123 |
    | 10 | X    | 19.01 | 2019-02-12 10:00:09.123 |
    +----+------+-------+-------------------------+
    --------------
    select * from `coordonnee`
    --------------
     
    +----+-------+-------------------------+-------+-------------------------+
    | id | val_X | temps_X                 | val_Y | temps_Y                 |
    +----+-------+-------------------------+-------+-------------------------+
    |  1 | 17.12 | 2019-02-12 10:00:00.123 | 15.34 | 2019-02-12 10:00:02.123 |
    |  2 | 16.23 | 2019-02-12 10:00:01.123 | 12.67 | 2019-02-12 10:00:05.123 |
    |  3 | 14.45 | 2019-02-12 10:00:03.123 | 11.78 | 2019-02-12 10:00:06.123 |
    |  4 | 13.56 | 2019-02-12 10:00:04.123 | 10.89 | 2019-02-12 10:00:07.123 |
    |  5 | 19.01 | 2019-02-12 10:00:09.123 |  18.9 | 2019-02-12 10:00:08.123 |
    +----+-------+-------------------------+-------+-------------------------+
    --------------
    explain select min(id) from `coordonnee` where val_x is null and val_y is not null
    --------------
     
    +----+-------------+------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
    | id | select_type | table      | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
    +----+-------------+------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
    |  1 | SIMPLE      | coordonnee | NULL       | range | idx1,idx2     | idx1 | 10      | NULL |    1 |   100.00 | Using where; Using index |
    +----+-------------+------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
    --------------
    explain select min(id) from `coordonnee` where val_y is null and val_x is not null
    --------------
     
    +----+-------------+------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
    | id | select_type | table      | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
    +----+-------------+------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
    |  1 | SIMPLE      | coordonnee | NULL       | range | idx1,idx2     | idx2 | 10      | NULL |    1 |   100.00 | Using where; Using index |
    +----+-------------+------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
    --------------
    COMMIT
    --------------
     
    Appuyez sur une touche pour continuer...
    Afin de savoir si le rapprochement prend énormément de temps, il faudra faire des tests de performances.

    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr

  11. #11
    Membre à l'essai
    Homme Profil pro
    Administrateur systèmes et réseaux
    Inscrit en
    septembre 2013
    Messages
    29
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 33
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur systèmes et réseaux
    Secteur : High Tech - Opérateur de télécommunications

    Informations forums :
    Inscription : septembre 2013
    Messages : 29
    Points : 18
    Points
    18

    Par défaut

    Citation Envoyé par Artemus24 Voir le message
    J'espère que vous n'avez pas que la date comme moyen de faire le rapprochement entre votre X et votre Y.
    Vous dites que vous ne les recevez pas en même temps. Mais pourtant, vous faites le rapprochement sur la date (timestamp).
    Vous dites un peu plus loin, que le rapprochement doit se faire sans les millisecondes, voire même à cinq secondes près.
    Vous aurez inévitablement des doublons. Comment alors faire le rapprochement ?
    merci infiniment pour votre temps et vos réponses.
    Je suis en train de me rendre compte du gouffre entre un utilisateur SQL occasionnelle et un pro...

    Oui je ne fais le rapprochement que en fonction du temps. Je vous explique:
    Les satellites envoient chaque paramètre a un débit différents, de 10 fois par secondes a une fois toutes les 10 minutes.
    Un des cas qui m’intéresse est l’analyse du roulement a bille sur une roue a inertie (un moteur qui fait tourner une masse: ça crée de la stabilité et permet de pivoter sur un axe).
    J'ai besoin de tracer la fiction de l'axe en fonction de la vitesse, voici deux images trouvées sur google qui donne "en gros" la théorie et le résultat souhaite:
    Nom : Classical-Friction-Theory.png
Affichages : 60
Taille : 17,7 Ko
    Nom : Case-2-Pure-Viscous-Friction-Behavior-4.png
Affichages : 58
Taille : 67,7 Ko

    Sachant que la vitesse évolue très lentement, on peut considérer que le vitesse reçu est valable quelques secondes (voir quelques minutes).
    Une autre solution serait même de ne choisir qu'un seul couple de valeur toutes les minutes, disons la valeur MAX sur cette période. Mais je ne suis pas fan.

    Bref, pour faire ce tracé, a chaque fois que je reçois une indication de vitesse:
    • si je reçois une indication de friction dans les 5 secondes qui suivent, alors je veux garder le couple (vitesse,friction).
    • si je reçois deux fois la friction dans les 5 secondes suivantes, alors je ne veux garder qu'un seul couple (vitesse,friction).
    • si je ne reçois pas d'indication de friction dans les 5 secondes suivantes, alors je ne sauvegarde aucune valeur.



    Donc avec le paramétrage de "5 secondes" j'aurai un certain nombre de couples (vitesse,friction).
    Si je veux plus de couples (vitesse,friction), donc plus de points sur mon graphe, alors je peux changer le paramétrage a "15 secondes" par exemple.
    Si j'ai trop de points (c'est très subjectif), alors je peux descendre le paramétrage a "1 seconde"


    Le couple (vitesse,friction) marche très bien avec "5 secondes", mais sur d'autres paramètres il me faudra peut être faire une jointure a la seconde près, voir la milliseconde près si ce sont des paramètres reçu dans le même paquet.
    D’où mon importance de pouvoir laisser l'utilisateur choisir.

    C'est pour ca que j'aime beaucoup la solution de creer des colonnes "the_time_bis" en datetime(0) pour la seconde pres, et aussi uen colonne "the_time_ter" qui contient la date arrondi a 5 secondes, et une dernier colonne a 15 ou 30 secondes.

  12. #12
    Expert éminent sénior Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    février 2011
    Messages
    3 856
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 79
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : février 2011
    Messages : 3 856
    Points : 11 753
    Points
    11 753

    Par défaut

    Salut ben500fr.

    Cela devient compliqué.

    Citation Envoyé par ben500fr
    Les satellites envoient chaque paramètre a un débit différents, de 10 fois par secondes a une fois toutes les 10 minutes.
    Vous avez un flux pour chaque satellite avec une périodicité différente dans les émissions des données.
    Vous avez besoin de récupérer le couple (vitesse ; friction) sachant que la vitesse évolue lentement tandis que la friction évolue très rapidement.

    Ensuite, un utilisateur demande une périodicité qui va lui servir à faire un graphe.
    Sa demande est basée sur une périodicité de 5 secondes, mais elle peut être autre, au choix de l'utilisateur.

    Citation Envoyé par ben500fr
    Bref, pour faire ce tracé, à chaque fois que je reçois une indication de vitesse:
    si je reçois une indication de friction dans les 5 secondes qui suivent, alors je veux garder le couple (vitesse,friction).
    si je reçois deux fois la friction dans les 5 secondes suivantes, alors je ne veux garder qu'un seul couple (vitesse,friction).
    si je ne reçois pas d'indication de friction dans les 5 secondes suivantes, alors je ne sauvegarde aucune valeur.
    La solution est facile à mettre en oeuvre. D'ailleurs vous la donnez en disant que vous devez diviser le temps unix par 5 secondes.
    Autrement dit l'intervalle de temps sera x borne inférieur comprise et x+5 borne supérieure non comprise.
    Et vous recherchez une vitesse et une friction dans cet intervalle de temps.
    La première valeur de vitesse et la première valeur de friction correspondant à votre intervalle seront candidats à la sélection.
    Si l'une des deux est absente, aucune sélection.

    Citation Envoyé par ben500fr
    Donc avec le paramétrage de "5 secondes" j'aurai un certain nombre de couples (vitesse,friction).
    Un couple par intervalle de 5 secondes, si le couple existe. Il faut aussi envisager des trous.

    Citation Envoyé par ben500fr
    Si je veux plus de couples (vitesse,friction), donc plus de points sur mon graphe, alors je peux changer le paramétrage a "15 secondes" par exemple.
    Non, c'est l'inverse. Si vous avez un couple toutes les cinq secondes, si vous en voulez disons cinq fois plus, l'intervalle sera de 1 seconde.
    En passant à 15 secondes vis-à-vis des 5 secondes, vous aurez trois fois moins de couple.

    Citation Envoyé par ben500fr
    Si j'ai trop de points (c'est très subjectif), alors je peux descendre le paramétrage a "1 seconde"
    Et bien non, vous l'augmentez.

    Si vous avez un couple par intervalle est de 5 secondes, en 1 heure vous en avez 720 couples.
    Si maintenant, l'intervalle est de 15 secondes, en 1 heure vous passez à 240 couples, soit 240 / 720 = 1/3, soit trois fois moins.
    Si l'intervalle passe à 1 seconde, en 1 heure vous en avez 3600, soit 3600 / 720 = 5, soit cinq fois plus.

    Citation Envoyé par ben500fr
    D’où mon importance de pouvoir laisser l'utilisateur choisir.
    D'où le problème de la performance pour créer vos couples dans la périodicité demandé.

    Citation Envoyé par ben500fr
    C'est pour ca que j'aime beaucoup la solution de creer des colonnes "the_time_bis" en datetime(0) pour la seconde pres, et aussi uen colonne "the_time_ter" qui contient la date arrondi a 5 secondes, et une dernier colonne a 15 ou 30 secondes.
    Et bien non, car la solution que je vous ai donnée était basée sur une périodicité fixée à l'avance.
    Là, c'est différent car vous demandez différentes périodicité, au choix de l'utilisateur.

    Voilà ce que je propose :
    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
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    133
    134
    135
    136
    137
    138
    139
    140
    141
    142
    143
    144
    145
    146
    147
    148
    149
    150
    151
    152
    153
    154
    155
    156
    157
    158
    159
    160
    161
    162
    163
    164
    165
    166
    167
    168
    169
    170
    171
    172
    173
    174
    175
    176
    177
    178
    179
    180
    181
    182
    183
    184
    185
    186
    187
    188
    189
    190
    191
    192
    193
    194
    195
    196
    197
    198
    199
    200
    201
    202
    203
    204
    205
    206
    207
    208
    209
    210
    211
    212
    213
    214
    --------------
    START TRANSACTION
    --------------
     
    --------------
    DROP DATABASE IF EXISTS `base`
    --------------
     
    --------------
    CREATE DATABASE IF NOT EXISTS `base`
            DEFAULT CHARACTER SET `latin1`
            DEFAULT COLLATE       `latin1_general_ci`
    --------------
     
    --------------
    DROP TABLE IF EXISTS `t1`
    --------------
     
    --------------
    CREATE TABLE `t1`
    ( `the_time`   decimal(21,3)  NOT NULL primary key,
      `the_value`  float          NOT NULL
    ) ENGINE=InnoDB
      DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
      ROW_FORMAT=COMPRESSED
    --------------
     
    --------------
    insert into `t1` (`the_time`,`the_value`) values
      (unix_timestamp('2019-02-12 10:20:20.123'),100.44),
      (unix_timestamp('2019-02-12 10:20:21.123'),105.44),
      (unix_timestamp('2019-02-12 10:20:22.123'),110.44),
      (unix_timestamp('2019-02-12 10:20:23.123'),115.44),
      (unix_timestamp('2019-02-12 10:20:24.123'),120.44),
      (unix_timestamp('2019-02-12 10:20:25.123'),125.44),
      (unix_timestamp('2019-02-12 10:20:26.123'),130.44),
      (unix_timestamp('2019-02-12 10:20:27.123'),135.44),
      (unix_timestamp('2019-02-12 10:20:28.123'),140.44),
      (unix_timestamp('2019-02-12 10:20:29.123'),145.44),
      (unix_timestamp('2019-02-12 10:20:30.123'),150.44),
      (unix_timestamp('2019-02-12 10:20:31.123'),155.44),
      (unix_timestamp('2019-02-12 10:20:32.123'),160.44),
      (unix_timestamp('2019-02-12 10:20:33.123'),165.44),
      (unix_timestamp('2019-02-12 10:20:34.123'),170.44),
      (unix_timestamp('2019-02-12 10:20:35.123'),175.44),
      (unix_timestamp('2019-02-12 10:20:36.123'),180.44),
      (unix_timestamp('2019-02-12 10:20:37.123'),185.44),
      (unix_timestamp('2019-02-12 10:20:38.123'),190.44),
      (unix_timestamp('2019-02-12 10:20:39.123'),195.44)
    --------------
     
    --------------
    select * from `t1`
    --------------
     
    +----------------+-----------+
    | the_time       | the_value |
    +----------------+-----------+
    | 1549963220.123 |    100.44 |
    | 1549963221.123 |    105.44 |
    | 1549963222.123 |    110.44 |
    | 1549963223.123 |    115.44 |
    | 1549963224.123 |    120.44 |
    | 1549963225.123 |    125.44 |
    | 1549963226.123 |    130.44 |
    | 1549963227.123 |    135.44 |
    | 1549963228.123 |    140.44 |
    | 1549963229.123 |    145.44 |
    | 1549963230.123 |    150.44 |
    | 1549963231.123 |    155.44 |
    | 1549963232.123 |    160.44 |
    | 1549963233.123 |    165.44 |
    | 1549963234.123 |    170.44 |
    | 1549963235.123 |    175.44 |
    | 1549963236.123 |    180.44 |
    | 1549963237.123 |    185.44 |
    | 1549963238.123 |    190.44 |
    | 1549963239.123 |    195.44 |
    +----------------+-----------+
    --------------
    DROP TABLE IF EXISTS `t2`
    --------------
     
    --------------
    CREATE TABLE `t2`
    ( `the_time`   decimal(21,3)  NOT NULL primary key,
      `the_value`     float       NOT NULL
    ) ENGINE=InnoDB
      DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
      ROW_FORMAT=COMPRESSED
    --------------
     
    --------------
    insert into `t2` (`the_time`,`the_value`) values
      (unix_timestamp('2019-02-12 10:20:16.456'),200.44),
      (unix_timestamp('2019-02-12 10:20:21.456'),210.44),
      (unix_timestamp('2019-02-12 10:20:26.456'),220.44),
      (unix_timestamp('2019-02-12 10:20:31.456'),230.44),
      (unix_timestamp('2019-02-12 10:20:36.456'),240.44),
      (unix_timestamp('2019-02-12 10:20:41.456'),250.44)
    --------------
     
    --------------
    select * from `t2`
    --------------
     
    +----------------+-----------+
    | the_time       | the_value |
    +----------------+-----------+
    | 1549963216.456 |    200.44 |
    | 1549963221.456 |    210.44 |
    | 1549963226.456 |    220.44 |
    | 1549963231.456 |    230.44 |
    | 1549963236.456 |    240.44 |
    | 1549963241.456 |    250.44 |
    +----------------+-----------+
    --------------
    commit
    --------------
     
    --------------
    drop procedure if exists `trait`
    --------------
     
    --------------
    create procedure `trait` (IN  _periode smallint)
    DETERMINISTIC
    NO SQL
    BEGIN
      drop temporary table if exists `temp1`;
     
      SET @req1 = concat('create temporary table `temp1` ( unique index (`periode`) ) ENGINE=InnoDB as ( select  cast((the_time / ', _periode, ') as unsigned) * ', _periode, ' as periode, min(the_value) as the_value from `t1` group by periode);');
     
      PREPARE            stmt FROM @req1;
      EXECUTE            stmt;
      DEALLOCATE PREPARE stmt;
     
      SET @req2 = concat('create temporary table `temp2` ( unique index (`periode`) ) ENGINE=InnoDB as ( select  cast((the_time / ', _periode, ') as unsigned) * ', _periode, ' as periode, min(the_value) as the_value from `t2` group by periode);');
     
      drop temporary table if exists `temp2`;
     
      PREPARE            stmt FROM @req2;
      EXECUTE            stmt;
      DEALLOCATE PREPARE stmt;
     
    select      t1.periode,
                t1.the_value as val1,
                t2.the_value as val2
     
          from  `temp1`  as t1
     
    inner join  `temp2`  as t2
            on  t2.periode = t1.periode;
     
    explain
    select      t1.periode,
                t1.the_value as val1,
                t2.the_value as val2
     
          from  `temp1`  as t1
     
    inner join  `temp2`  as t2
            on  t2.periode = t1.periode;
     
      drop temporary table if exists `temp1`;
      drop temporary table if exists `temp2`;
    END
    --------------
     
    --------------
    call `trait` (5)
    --------------
     
    +------------+--------+--------+
    | periode    | val1   | val2   |
    +------------+--------+--------+
    | 1549963220 | 100.44 | 210.44 |
    | 1549963225 | 115.44 | 220.44 |
    | 1549963230 | 140.44 | 230.44 |
    | 1549963235 | 165.44 | 240.44 |
    | 1549963240 | 190.44 | 250.44 |
    +------------+--------+--------+
    +----+-------------+-------+------------+------+---------------+---------+---------+-----------------+------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key     | key_len | ref             | rows | filtered | Extra       |
    +----+-------------+-------+------------+------+---------------+---------+---------+-----------------+------+----------+-------------+
    |  1 | SIMPLE      | t1    | NULL       | ALL  | periode       | NULL    | NULL    | NULL            |    5 |   100.00 | Using where |
    |  1 | SIMPLE      | t2    | NULL       | ref  | periode       | periode | 9       | base.t1.periode |    1 |   100.00 | NULL        |
    +----+-------------+-------+------------+------+---------------+---------+---------+-----------------+------+----------+-------------+
    --------------
    commit
    --------------
     
    --------------
    call `trait` (15)
    --------------
     
    +------------+--------+--------+
    | periode    | val1   | val2   |
    +------------+--------+--------+
    | 1549963215 | 100.44 | 200.44 |
    | 1549963230 | 115.44 | 220.44 |
    | 1549963245 | 190.44 | 250.44 |
    +------------+--------+--------+
    +----+-------------+-------+------------+------+---------------+---------+---------+-----------------+------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key     | key_len | ref             | rows | filtered | Extra       |
    +----+-------------+-------+------------+------+---------------+---------+---------+-----------------+------+----------+-------------+
    |  1 | SIMPLE      | t1    | NULL       | ALL  | periode       | NULL    | NULL    | NULL            |    3 |   100.00 | Using where |
    |  1 | SIMPLE      | t2    | NULL       | ref  | periode       | periode | 9       | base.t1.periode |    1 |   100.00 | NULL        |
    +----+-------------+-------+------------+------+---------------+---------+---------+-----------------+------+----------+-------------+
    --------------
    COMMIT
    --------------
     
    Appuyez sur une touche pour continuer...
    Mais cela nécessite de recalculer la périodicité pour les deux tables.

    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr

  13. #13
    Membre à l'essai
    Homme Profil pro
    Administrateur systèmes et réseaux
    Inscrit en
    septembre 2013
    Messages
    29
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 33
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur systèmes et réseaux
    Secteur : High Tech - Opérateur de télécommunications

    Informations forums :
    Inscription : septembre 2013
    Messages : 29
    Points : 18
    Points
    18

    Par défaut

    Bonjour,

    Citation Envoyé par Artemus24 Voir le message
    Un couple par intervalle de 5 secondes, si le couple existe. Il faut aussi envisager des trous.

    Non, c'est l'inverse. Si vous avez un couple toutes les cinq secondes, si vous en voulez disons cinq fois plus, l'intervalle sera de 1 seconde.
    En passant à 15 secondes vis-à-vis des 5 secondes, vous aurez trois fois moins de couple.

    Je comprend votre analyse qui est theoriquement juste, mais il y a un MAIS!

    Imaginons que les données arrivent une fois toutes les 5 secondes. Elles peuvent arriver au meme moment (meme packet = meme milliseconde), ou arriver avec 2.5 secondes d’écart. Sauf que dans la vrai vie ce débit peut varier...
    Donc ce que va faire l'utilisateur: créer un graphe x/y avec une jointure datetime(3). Il risque d'obtenir 0 couples.
    Alors il va tenter le jointure sur le datetime(0): il va obtenir 10.000 couple (a la grosse louche ) vu qu'il ne tombera presque que sur des trous
    Alors il va faire une jointure "a 5 secondes près": il obtiendra 1 millions de couples.
    Mais si il passe "a 15 secondes près" il risque (c'est même sure) de diviser le nombre de couples par 3.


    Potentiellement on veut faire un graphe pour n'importe quel couple de paramètres -> du coup j'aime beaucoup la solution de faire des colonnes the_time_bis en datetime(0), puis une colonne a 5 sec pres. Voir une a 1 minute pres si besoin.
    D’où ma question sur la façon de créer la colonne a 5s près.

    En tous cas je réalise que faire un tracé avec autre chose que tu temps sur l'axe X n'est pas si évident qu'il n'y parait!


    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    ALTER TABLE `T1` ADD `the_time_5_s` DATETIME generated always AS (FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(`the_time`)/(5))*(5)))
    Le code ci-dessus me renvoie l'erreur #1901 - function or expression is not allow for column the_time_5_s

    Pourtant un
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    SELECT FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(`the_time`)/(5))*(5)) FROM T1
    marche parfaitement.

  14. #14
    Expert éminent sénior Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    février 2011
    Messages
    3 856
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 79
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : février 2011
    Messages : 3 856
    Points : 11 753
    Points
    11 753

    Par défaut

    Salut ben500fr.

    Citation Envoyé par ben500fr
    Je comprend votre analyse qui est theoriquement juste, mais il y a un MAIS!
    Je comprends votre analyse car dans une périodicité disons de 1 seconde, vous avez plusieurs milliers de lignes à votre disposition, tandis que je n'extrait qu'une seule ligne.

    Citation Envoyé par ben500fr
    Elles peuvent arriver au meme moment (meme packet = meme milliseconde), ou arriver avec 2.5 secondes d’écart.
    Le flux entrant pose un problème si vos données arrivent par paquet.
    C'est-à-dire ayant le même timestamp à la milliseconde. Pourquoi ?
    Parce que vous aurez des doublons, et seule la première ligne sera prise, les autres seront rejetées.
    De ce fait, vous avez un problème de stockage puisque vous ne récupérez pas tout ce que vos satellite vous transmettent.

    Citation Envoyé par ben500fr
    Sauf que dans la vrai vie ce débit peut varier...
    Ce n'est pas un problème de débit, mais de savoir quoi faire de la totalité du flux entrant, afin d'avoir un graphique conforme à vos attentes.

    Citation Envoyé par ben500fr
    Donc ce que va faire l'utilisateur: créer un graphe x/y avec une jointure datetime(3). Il risque d'obtenir 0 couples.
    Oui, j'avais compris votre problème de mise en relation.

    Citation Envoyé par ben500fr
    Alors il va tenter le jointure sur le datetime(0): il va obtenir 10.000 couple (a la grosse louche ) vu qu'il ne tombera presque que sur des trous.
    Justement, quel est le critère de sélection de la ligne pour une périodicité de 1 seconde ?
    En réduisant vous 10.000 couples à un seul, vous avez une perte importante d'informations liées à la quantité.

    Citation Envoyé par ben500fr
    Alors il va faire une jointure "a 5 secondes près": il obtiendra 1 millions de couples.
    C'est encore pire car vous réduisez à 1 seul couple, la quantité de 1 million.

    Et je reformule le problème. Comment sélectionnez le couple parmi toutes celles qui sont à votre disposition ?
    Le premier arrivé est le premier servie ? Le minimum ? Le maximum ? Autre ?

    Citation Envoyé par ben500fr
    Mais si il passe "a 15 secondes près" il risque (c'est même sure) de diviser le nombre de couples par 3.
    Ok, nous sommes d'accord et dans ce cas que préconisez-vous ?
    Je parle de la perte d'informations liées à la réduction de la quantité de lignes que vous avez pour la périodicité de 15 secondes.

    Citation Envoyé par ben500fr
    Potentiellement on veut faire un graphe pour n'importe quel couple de paramètres
    Quelle est la liste des valeurs de vos paramètres (la périodicité) ?
    --> 1 seconde
    --> 5 secondes
    --> 10 secondes.
    --> 15 secondes.

    La liste risque d'être longue, juste pour satisfaire l'utilisateur dans la finesse de la recherche du graphe parfait.

    Citation Envoyé par ben500fr
    du coup j'aime beaucoup la solution de faire des colonnes the_time_bis en datetime(0), puis une colonne a 5 sec pres. Voir une a 1 minute pres si besoin.
    Je comprends votre satisfaction, sauf à quoi cela sert de stocker autant d'informations si celles-ci seront jamais toutes utilisées ?
    La table risque d'augmenter en volumétrie inutilement.
    Mais cela ne va pas résoudre votre problème si un utilisateur demande une périodicité que vous n'avez pas prévue dans la liste des paramètres.

    Citation Envoyé par ben500fr
    D’où ma question sur la façon de créer la colonne a 5s près.
    Vous ne pouvez plus travailler sur la date + time + milliseconde, mais sur la date au format unix, c'est-à-dire une quantité numérique.
    Après, le stockage peut très bien se faire au format date + time + milliseconde.
    Mais le fait de faire une mise en forme de vos données risque d'allonger le temps de stockage de vos données.

    Citation Envoyé par ben500fr
    En tous cas je réalise que faire un tracé avec autre chose que tu temps sur l'axe X n'est pas si évident qu'il n'y parait!
    Ce n'est pas si évident de laisser le choix à l'utilisateur dans la périodicité, sachant que vous ne disposez pas de cette information au préalable.
    D'où une augmentation de la volumétrie , ou bien une perte dans la performance, et ce, à cause de la préparation en calcul pour répondre au graphe de l'utilisateur.

    Normalement, dans une base de données, on fige les relations que l'on désire faire. D'où la performance dans l'optention du résultat.
    Mais si ce que vous demandez n'a pas été prévue dès le départ, vous aurez une perte en performance.

    On peut améliorer la performance en sélectionnant un intervalle de temps comprenant le minimum et le maximum.

    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr

  15. #15
    Membre à l'essai
    Homme Profil pro
    Administrateur systèmes et réseaux
    Inscrit en
    septembre 2013
    Messages
    29
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 33
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur systèmes et réseaux
    Secteur : High Tech - Opérateur de télécommunications

    Informations forums :
    Inscription : septembre 2013
    Messages : 29
    Points : 18
    Points
    18

    Par défaut

    Bonjour,
    j'ai pris le temps de réfléchir et de discuter avec mes collègues.

    Citation Envoyé par Artemus24 Voir le message
    Ok, nous sommes d'accord et dans ce cas que préconisez-vous ?
    Je parle de la perte d'informations liées à la réduction de la quantité de lignes que vous avez pour la périodicité de 15 secondes.
    Pour afficher un paramètre dans le temps, il faut afficher toutes les valeurs sur une période, sans corruption possible. Ca c'est bon c'est fait.
    Pour un graphe avec un paramètre sur l'axe x, on recherche des tendances, on s'autorise des pertes de donnes. L'outil l'affichera de manière très explicite mais j'ai un "go" des équipes.

    Citation Envoyé par Artemus24 Voir le message
    Quelle est la liste des valeurs de vos paramètres (la périodicité) ?
    --> 1 seconde
    --> 5 secondes
    --> 10 secondes.
    --> 15 secondes.

    La liste risque d'être longue, juste pour satisfaire l'utilisateur dans la finesse de la recherche du graphe parfait.
    La periodicite de reception n'est pas fixe, et peut aller jusqu’à 1 valeur toutes les 10 minutes.
    Je vais proposer a l'utilisateur des jointures a la milliseconde, la secondes, 5s, et 15s. On verra si on en veut plus de possibilités. Le service informatique est prêt a me fournir des bases/serveurs et même des arrays de stockage a volonté si on satisfait notre besoin. Mais pour le moment un serveur sous Linux connecte sur un gros SAN (armoire remplie de disques SSD) me suffis.
    De toutes façons les colonnes avec les arrondi ne seront créées que sur demande, donc pas sur tous les paramètres.

    Citation Envoyé par Artemus24 Voir le message
    Je comprends votre satisfaction, sauf à quoi cela sert de stocker autant d'informations si celles-ci seront jamais toutes utilisées ?
    La table risque d'augmenter en volumétrie inutilement.
    Mais cela ne va pas résoudre votre problème si un utilisateur demande une périodicité que vous n'avez pas prévue dans la liste des paramètres.
    On sauvegarde en l’état TOUTES les telemetries reçu. On considère que c'est le bien le plus précieux que nous possédons.
    Et pour info il n'est pas rare de faire des analyse sur un paramètre avec un "min/max/moyenne par heure" sur une longue période, et au bout de 5 ans, souhaiter analyser les 5 dernières années a la grosse loupe. On ne sait jamais en avance ce qu'on veut voir. Un exemple typique: le voltage de chaque cellule des batteries.




    Je bloque toujours sur ce code qui ne marche pas pour ajouter la colonne a 5s pres:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    ALTER TABLE `T1` ADD `the_time_5_s` DATETIME generated always AS (FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(`the_time`)/(5))*(5)))

  16. #16
    Expert éminent sénior Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    février 2011
    Messages
    3 856
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 79
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : février 2011
    Messages : 3 856
    Points : 11 753
    Points
    11 753

    Par défaut

    Salut ben500fr.

    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
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    --------------
    START TRANSACTION
    --------------
     
    --------------
    DROP DATABASE IF EXISTS `base`
    --------------
     
    --------------
    CREATE DATABASE IF NOT EXISTS `base`
            DEFAULT CHARACTER SET `latin1`
            DEFAULT COLLATE       `latin1_general_ci`
    --------------
     
    --------------
    DROP TABLE IF EXISTS `t1`
    --------------
     
    --------------
    CREATE TABLE `t1`
    ( `the_decimal`  decimal(15,3)                                                                       NOT NULL PRIMARY KEY,
      `the_value`    float                                                                               NOT NULL,
      `the_time`     timestamp(3) generated always AS (FROM_UNIXTIME(      the_decimal       ))  virtual NOT NULL,
      `the_time_05`  timestamp(0) generated always AS (FROM_UNIXTIME(floor(the_decimal/ 5)* 5))  virtual NOT NULL,
      `the_time_15`  timestamp(0) generated always AS (FROM_UNIXTIME(floor(the_decimal/15)*15))  virtual NOT NULL
    ) ENGINE=InnoDB
      DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
      ROW_FORMAT=COMPRESSED
    --------------
     
    --------------
    insert into `t1` (`the_decimal`,`the_value`) values
      (unix_timestamp('2019-02-12 10:20:00.123'),100.44),
      (unix_timestamp('2019-02-12 10:20:01.123'),102.44),
      (unix_timestamp('2019-02-12 10:20:02.123'),104.44),
      (unix_timestamp('2019-02-12 10:20:03.123'),106.44),
      (unix_timestamp('2019-02-12 10:20:04.123'),108.44),
      (unix_timestamp('2019-02-12 10:20:05.123'),110.44),
      (unix_timestamp('2019-02-12 10:20:06.123'),112.44),
      (unix_timestamp('2019-02-12 10:20:07.123'),114.44),
      (unix_timestamp('2019-02-12 10:20:08.123'),116.44),
      (unix_timestamp('2019-02-12 10:20:09.123'),118.44),
      (unix_timestamp('2019-02-12 10:20:10.123'),120.44),
      (unix_timestamp('2019-02-12 10:20:11.123'),122.44),
      (unix_timestamp('2019-02-12 10:20:12.123'),124.44),
      (unix_timestamp('2019-02-12 10:20:13.123'),126.44),
      (unix_timestamp('2019-02-12 10:20:14.123'),128.44),
      (unix_timestamp('2019-02-12 10:20:15.123'),130.44),
      (unix_timestamp('2019-02-12 10:20:16.123'),132.44),
      (unix_timestamp('2019-02-12 10:20:17.123'),134.44),
      (unix_timestamp('2019-02-12 10:20:18.123'),136.44),
      (unix_timestamp('2019-02-12 10:20:19.123'),138.44),
      (unix_timestamp('2019-02-12 10:20:20.123'),140.44),
      (unix_timestamp('2019-02-12 10:20:21.123'),142.44),
      (unix_timestamp('2019-02-12 10:20:22.123'),144.44),
      (unix_timestamp('2019-02-12 10:20:23.123'),146.44),
      (unix_timestamp('2019-02-12 10:20:24.123'),148.44),
      (unix_timestamp('2019-02-12 10:20:25.123'),150.44),
      (unix_timestamp('2019-02-12 10:20:26.123'),152.44),
      (unix_timestamp('2019-02-12 10:20:27.123'),154.44),
      (unix_timestamp('2019-02-12 10:20:28.123'),156.44),
      (unix_timestamp('2019-02-12 10:20:29.123'),158.44),
      (unix_timestamp('2019-02-12 10:20:30.123'),160.44),
      (unix_timestamp('2019-02-12 10:20:31.123'),162.44),
      (unix_timestamp('2019-02-12 10:20:32.123'),164.44),
      (unix_timestamp('2019-02-12 10:20:33.123'),166.44),
      (unix_timestamp('2019-02-12 10:20:34.123'),168.44),
      (unix_timestamp('2019-02-12 10:20:35.123'),170.44),
      (unix_timestamp('2019-02-12 10:20:36.123'),172.44),
      (unix_timestamp('2019-02-12 10:20:37.123'),174.44),
      (unix_timestamp('2019-02-12 10:20:38.123'),176.44),
      (unix_timestamp('2019-02-12 10:20:39.123'),178.44)
    --------------
     
    --------------
    select * from `t1`
    --------------
     
    +----------------+-----------+-------------------------+---------------------+---------------------+
    | the_decimal    | the_value | the_time                | the_time_05         | the_time_15         |
    +----------------+-----------+-------------------------+---------------------+---------------------+
    | 1549963200.123 |    100.44 | 2019-02-12 10:20:00.123 | 2019-02-12 10:20:00 | 2019-02-12 10:20:00 |
    | 1549963201.123 |    102.44 | 2019-02-12 10:20:01.123 | 2019-02-12 10:20:00 | 2019-02-12 10:20:00 |
    | 1549963202.123 |    104.44 | 2019-02-12 10:20:02.123 | 2019-02-12 10:20:00 | 2019-02-12 10:20:00 |
    | 1549963203.123 |    106.44 | 2019-02-12 10:20:03.123 | 2019-02-12 10:20:00 | 2019-02-12 10:20:00 |
    | 1549963204.123 |    108.44 | 2019-02-12 10:20:04.123 | 2019-02-12 10:20:00 | 2019-02-12 10:20:00 |
    | 1549963205.123 |    110.44 | 2019-02-12 10:20:05.123 | 2019-02-12 10:20:05 | 2019-02-12 10:20:00 |
    | 1549963206.123 |    112.44 | 2019-02-12 10:20:06.123 | 2019-02-12 10:20:05 | 2019-02-12 10:20:00 |
    | 1549963207.123 |    114.44 | 2019-02-12 10:20:07.123 | 2019-02-12 10:20:05 | 2019-02-12 10:20:00 |
    | 1549963208.123 |    116.44 | 2019-02-12 10:20:08.123 | 2019-02-12 10:20:05 | 2019-02-12 10:20:00 |
    | 1549963209.123 |    118.44 | 2019-02-12 10:20:09.123 | 2019-02-12 10:20:05 | 2019-02-12 10:20:00 |
    | 1549963210.123 |    120.44 | 2019-02-12 10:20:10.123 | 2019-02-12 10:20:10 | 2019-02-12 10:20:00 |
    | 1549963211.123 |    122.44 | 2019-02-12 10:20:11.123 | 2019-02-12 10:20:10 | 2019-02-12 10:20:00 |
    | 1549963212.123 |    124.44 | 2019-02-12 10:20:12.123 | 2019-02-12 10:20:10 | 2019-02-12 10:20:00 |
    | 1549963213.123 |    126.44 | 2019-02-12 10:20:13.123 | 2019-02-12 10:20:10 | 2019-02-12 10:20:00 |
    | 1549963214.123 |    128.44 | 2019-02-12 10:20:14.123 | 2019-02-12 10:20:10 | 2019-02-12 10:20:00 |
    | 1549963215.123 |    130.44 | 2019-02-12 10:20:15.123 | 2019-02-12 10:20:15 | 2019-02-12 10:20:15 |
    | 1549963216.123 |    132.44 | 2019-02-12 10:20:16.123 | 2019-02-12 10:20:15 | 2019-02-12 10:20:15 |
    | 1549963217.123 |    134.44 | 2019-02-12 10:20:17.123 | 2019-02-12 10:20:15 | 2019-02-12 10:20:15 |
    | 1549963218.123 |    136.44 | 2019-02-12 10:20:18.123 | 2019-02-12 10:20:15 | 2019-02-12 10:20:15 |
    | 1549963219.123 |    138.44 | 2019-02-12 10:20:19.123 | 2019-02-12 10:20:15 | 2019-02-12 10:20:15 |
    | 1549963220.123 |    140.44 | 2019-02-12 10:20:20.123 | 2019-02-12 10:20:20 | 2019-02-12 10:20:15 |
    | 1549963221.123 |    142.44 | 2019-02-12 10:20:21.123 | 2019-02-12 10:20:20 | 2019-02-12 10:20:15 |
    | 1549963222.123 |    144.44 | 2019-02-12 10:20:22.123 | 2019-02-12 10:20:20 | 2019-02-12 10:20:15 |
    | 1549963223.123 |    146.44 | 2019-02-12 10:20:23.123 | 2019-02-12 10:20:20 | 2019-02-12 10:20:15 |
    | 1549963224.123 |    148.44 | 2019-02-12 10:20:24.123 | 2019-02-12 10:20:20 | 2019-02-12 10:20:15 |
    | 1549963225.123 |    150.44 | 2019-02-12 10:20:25.123 | 2019-02-12 10:20:25 | 2019-02-12 10:20:15 |
    | 1549963226.123 |    152.44 | 2019-02-12 10:20:26.123 | 2019-02-12 10:20:25 | 2019-02-12 10:20:15 |
    | 1549963227.123 |    154.44 | 2019-02-12 10:20:27.123 | 2019-02-12 10:20:25 | 2019-02-12 10:20:15 |
    | 1549963228.123 |    156.44 | 2019-02-12 10:20:28.123 | 2019-02-12 10:20:25 | 2019-02-12 10:20:15 |
    | 1549963229.123 |    158.44 | 2019-02-12 10:20:29.123 | 2019-02-12 10:20:25 | 2019-02-12 10:20:15 |
    | 1549963230.123 |    160.44 | 2019-02-12 10:20:30.123 | 2019-02-12 10:20:30 | 2019-02-12 10:20:30 |
    | 1549963231.123 |    162.44 | 2019-02-12 10:20:31.123 | 2019-02-12 10:20:30 | 2019-02-12 10:20:30 |
    | 1549963232.123 |    164.44 | 2019-02-12 10:20:32.123 | 2019-02-12 10:20:30 | 2019-02-12 10:20:30 |
    | 1549963233.123 |    166.44 | 2019-02-12 10:20:33.123 | 2019-02-12 10:20:30 | 2019-02-12 10:20:30 |
    | 1549963234.123 |    168.44 | 2019-02-12 10:20:34.123 | 2019-02-12 10:20:30 | 2019-02-12 10:20:30 |
    | 1549963235.123 |    170.44 | 2019-02-12 10:20:35.123 | 2019-02-12 10:20:35 | 2019-02-12 10:20:30 |
    | 1549963236.123 |    172.44 | 2019-02-12 10:20:36.123 | 2019-02-12 10:20:35 | 2019-02-12 10:20:30 |
    | 1549963237.123 |    174.44 | 2019-02-12 10:20:37.123 | 2019-02-12 10:20:35 | 2019-02-12 10:20:30 |
    | 1549963238.123 |    176.44 | 2019-02-12 10:20:38.123 | 2019-02-12 10:20:35 | 2019-02-12 10:20:30 |
    | 1549963239.123 |    178.44 | 2019-02-12 10:20:39.123 | 2019-02-12 10:20:35 | 2019-02-12 10:20:30 |
    +----------------+-----------+-------------------------+---------------------+---------------------+
    --------------
    COMMIT
    --------------
     
    Appuyez sur une touche pour continuer...
    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr

  17. #17
    Membre à l'essai
    Homme Profil pro
    Administrateur systèmes et réseaux
    Inscrit en
    septembre 2013
    Messages
    29
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 33
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur systèmes et réseaux
    Secteur : High Tech - Opérateur de télécommunications

    Informations forums :
    Inscription : septembre 2013
    Messages : 29
    Points : 18
    Points
    18

    Par défaut

    Citation Envoyé par Artemus24 Voir le message
    Salut ben500fr.

    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
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    --------------
    START TRANSACTION
    --------------
     
    --------------
    DROP DATABASE IF EXISTS `base`
    --------------
     
    --------------
    CREATE DATABASE IF NOT EXISTS `base`
            DEFAULT CHARACTER SET `latin1`
            DEFAULT COLLATE       `latin1_general_ci`
    --------------
     
    --------------
    DROP TABLE IF EXISTS `t1`
    --------------
     
    --------------
    CREATE TABLE `t1`
    ( `the_decimal`  decimal(15,3)                                                                       NOT NULL PRIMARY KEY,
      `the_value`    float                                                                               NOT NULL,
      `the_time`     timestamp(3) generated always AS (FROM_UNIXTIME(      the_decimal       ))  virtual NOT NULL,
      `the_time_05`  timestamp(0) generated always AS (FROM_UNIXTIME(floor(the_decimal/ 5)* 5))  virtual NOT NULL,
      `the_time_15`  timestamp(0) generated always AS (FROM_UNIXTIME(floor(the_decimal/15)*15))  virtual NOT NULL
    ) ENGINE=InnoDB
      DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
      ROW_FORMAT=COMPRESSED
    --------------
     
    --------------
    insert into `t1` (`the_decimal`,`the_value`) values
      (unix_timestamp('2019-02-12 10:20:00.123'),100.44),
      (unix_timestamp('2019-02-12 10:20:01.123'),102.44),
      (unix_timestamp('2019-02-12 10:20:02.123'),104.44),
      (unix_timestamp('2019-02-12 10:20:03.123'),106.44),
      (unix_timestamp('2019-02-12 10:20:04.123'),108.44),
      (unix_timestamp('2019-02-12 10:20:05.123'),110.44),
      (unix_timestamp('2019-02-12 10:20:06.123'),112.44),
      (unix_timestamp('2019-02-12 10:20:07.123'),114.44),
      (unix_timestamp('2019-02-12 10:20:08.123'),116.44),
      (unix_timestamp('2019-02-12 10:20:09.123'),118.44),
      (unix_timestamp('2019-02-12 10:20:10.123'),120.44),
      (unix_timestamp('2019-02-12 10:20:11.123'),122.44),
      (unix_timestamp('2019-02-12 10:20:12.123'),124.44),
      (unix_timestamp('2019-02-12 10:20:13.123'),126.44),
      (unix_timestamp('2019-02-12 10:20:14.123'),128.44),
      (unix_timestamp('2019-02-12 10:20:15.123'),130.44),
      (unix_timestamp('2019-02-12 10:20:16.123'),132.44),
      (unix_timestamp('2019-02-12 10:20:17.123'),134.44),
      (unix_timestamp('2019-02-12 10:20:18.123'),136.44),
      (unix_timestamp('2019-02-12 10:20:19.123'),138.44),
      (unix_timestamp('2019-02-12 10:20:20.123'),140.44),
      (unix_timestamp('2019-02-12 10:20:21.123'),142.44),
      (unix_timestamp('2019-02-12 10:20:22.123'),144.44),
      (unix_timestamp('2019-02-12 10:20:23.123'),146.44),
      (unix_timestamp('2019-02-12 10:20:24.123'),148.44),
      (unix_timestamp('2019-02-12 10:20:25.123'),150.44),
      (unix_timestamp('2019-02-12 10:20:26.123'),152.44),
      (unix_timestamp('2019-02-12 10:20:27.123'),154.44),
      (unix_timestamp('2019-02-12 10:20:28.123'),156.44),
      (unix_timestamp('2019-02-12 10:20:29.123'),158.44),
      (unix_timestamp('2019-02-12 10:20:30.123'),160.44),
      (unix_timestamp('2019-02-12 10:20:31.123'),162.44),
      (unix_timestamp('2019-02-12 10:20:32.123'),164.44),
      (unix_timestamp('2019-02-12 10:20:33.123'),166.44),
      (unix_timestamp('2019-02-12 10:20:34.123'),168.44),
      (unix_timestamp('2019-02-12 10:20:35.123'),170.44),
      (unix_timestamp('2019-02-12 10:20:36.123'),172.44),
      (unix_timestamp('2019-02-12 10:20:37.123'),174.44),
      (unix_timestamp('2019-02-12 10:20:38.123'),176.44),
      (unix_timestamp('2019-02-12 10:20:39.123'),178.44)
    --------------
     
    --------------
    select * from `t1`
    --------------
     
    +----------------+-----------+-------------------------+---------------------+---------------------+
    | the_decimal    | the_value | the_time                | the_time_05         | the_time_15         |
    +----------------+-----------+-------------------------+---------------------+---------------------+
    | 1549963200.123 |    100.44 | 2019-02-12 10:20:00.123 | 2019-02-12 10:20:00 | 2019-02-12 10:20:00 |
    | 1549963201.123 |    102.44 | 2019-02-12 10:20:01.123 | 2019-02-12 10:20:00 | 2019-02-12 10:20:00 |
    | 1549963202.123 |    104.44 | 2019-02-12 10:20:02.123 | 2019-02-12 10:20:00 | 2019-02-12 10:20:00 |
    | 1549963203.123 |    106.44 | 2019-02-12 10:20:03.123 | 2019-02-12 10:20:00 | 2019-02-12 10:20:00 |
    | 1549963204.123 |    108.44 | 2019-02-12 10:20:04.123 | 2019-02-12 10:20:00 | 2019-02-12 10:20:00 |
    | 1549963205.123 |    110.44 | 2019-02-12 10:20:05.123 | 2019-02-12 10:20:05 | 2019-02-12 10:20:00 |
    | 1549963206.123 |    112.44 | 2019-02-12 10:20:06.123 | 2019-02-12 10:20:05 | 2019-02-12 10:20:00 |
    | 1549963207.123 |    114.44 | 2019-02-12 10:20:07.123 | 2019-02-12 10:20:05 | 2019-02-12 10:20:00 |
    | 1549963208.123 |    116.44 | 2019-02-12 10:20:08.123 | 2019-02-12 10:20:05 | 2019-02-12 10:20:00 |
    | 1549963209.123 |    118.44 | 2019-02-12 10:20:09.123 | 2019-02-12 10:20:05 | 2019-02-12 10:20:00 |
    | 1549963210.123 |    120.44 | 2019-02-12 10:20:10.123 | 2019-02-12 10:20:10 | 2019-02-12 10:20:00 |
    | 1549963211.123 |    122.44 | 2019-02-12 10:20:11.123 | 2019-02-12 10:20:10 | 2019-02-12 10:20:00 |
    | 1549963212.123 |    124.44 | 2019-02-12 10:20:12.123 | 2019-02-12 10:20:10 | 2019-02-12 10:20:00 |
    | 1549963213.123 |    126.44 | 2019-02-12 10:20:13.123 | 2019-02-12 10:20:10 | 2019-02-12 10:20:00 |
    | 1549963214.123 |    128.44 | 2019-02-12 10:20:14.123 | 2019-02-12 10:20:10 | 2019-02-12 10:20:00 |
    | 1549963215.123 |    130.44 | 2019-02-12 10:20:15.123 | 2019-02-12 10:20:15 | 2019-02-12 10:20:15 |
    | 1549963216.123 |    132.44 | 2019-02-12 10:20:16.123 | 2019-02-12 10:20:15 | 2019-02-12 10:20:15 |
    | 1549963217.123 |    134.44 | 2019-02-12 10:20:17.123 | 2019-02-12 10:20:15 | 2019-02-12 10:20:15 |
    | 1549963218.123 |    136.44 | 2019-02-12 10:20:18.123 | 2019-02-12 10:20:15 | 2019-02-12 10:20:15 |
    | 1549963219.123 |    138.44 | 2019-02-12 10:20:19.123 | 2019-02-12 10:20:15 | 2019-02-12 10:20:15 |
    | 1549963220.123 |    140.44 | 2019-02-12 10:20:20.123 | 2019-02-12 10:20:20 | 2019-02-12 10:20:15 |
    | 1549963221.123 |    142.44 | 2019-02-12 10:20:21.123 | 2019-02-12 10:20:20 | 2019-02-12 10:20:15 |
    | 1549963222.123 |    144.44 | 2019-02-12 10:20:22.123 | 2019-02-12 10:20:20 | 2019-02-12 10:20:15 |
    | 1549963223.123 |    146.44 | 2019-02-12 10:20:23.123 | 2019-02-12 10:20:20 | 2019-02-12 10:20:15 |
    | 1549963224.123 |    148.44 | 2019-02-12 10:20:24.123 | 2019-02-12 10:20:20 | 2019-02-12 10:20:15 |
    | 1549963225.123 |    150.44 | 2019-02-12 10:20:25.123 | 2019-02-12 10:20:25 | 2019-02-12 10:20:15 |
    | 1549963226.123 |    152.44 | 2019-02-12 10:20:26.123 | 2019-02-12 10:20:25 | 2019-02-12 10:20:15 |
    | 1549963227.123 |    154.44 | 2019-02-12 10:20:27.123 | 2019-02-12 10:20:25 | 2019-02-12 10:20:15 |
    | 1549963228.123 |    156.44 | 2019-02-12 10:20:28.123 | 2019-02-12 10:20:25 | 2019-02-12 10:20:15 |
    | 1549963229.123 |    158.44 | 2019-02-12 10:20:29.123 | 2019-02-12 10:20:25 | 2019-02-12 10:20:15 |
    | 1549963230.123 |    160.44 | 2019-02-12 10:20:30.123 | 2019-02-12 10:20:30 | 2019-02-12 10:20:30 |
    | 1549963231.123 |    162.44 | 2019-02-12 10:20:31.123 | 2019-02-12 10:20:30 | 2019-02-12 10:20:30 |
    | 1549963232.123 |    164.44 | 2019-02-12 10:20:32.123 | 2019-02-12 10:20:30 | 2019-02-12 10:20:30 |
    | 1549963233.123 |    166.44 | 2019-02-12 10:20:33.123 | 2019-02-12 10:20:30 | 2019-02-12 10:20:30 |
    | 1549963234.123 |    168.44 | 2019-02-12 10:20:34.123 | 2019-02-12 10:20:30 | 2019-02-12 10:20:30 |
    | 1549963235.123 |    170.44 | 2019-02-12 10:20:35.123 | 2019-02-12 10:20:35 | 2019-02-12 10:20:30 |
    | 1549963236.123 |    172.44 | 2019-02-12 10:20:36.123 | 2019-02-12 10:20:35 | 2019-02-12 10:20:30 |
    | 1549963237.123 |    174.44 | 2019-02-12 10:20:37.123 | 2019-02-12 10:20:35 | 2019-02-12 10:20:30 |
    | 1549963238.123 |    176.44 | 2019-02-12 10:20:38.123 | 2019-02-12 10:20:35 | 2019-02-12 10:20:30 |
    | 1549963239.123 |    178.44 | 2019-02-12 10:20:39.123 | 2019-02-12 10:20:35 | 2019-02-12 10:20:30 |
    +----------------+-----------+-------------------------+---------------------+---------------------+
    --------------
    COMMIT
    --------------
     
    Appuyez sur une touche pour continuer...
    @+
    Salut,

    Ma version mySQL refuse le "NOT NULL" apres "virtual" mais sinon ton code marche

    Deux choses qui me manquent:
    • je souhaite ajouter les colonnes avec les dates "arrondis" après la création de la table.
    • ma clé primaire est de type datetime(3), et non decimal(15,3).


    Du coup voici ce que j'essaie de faire:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    ALTER TABLE DB1.`T1` ADD `the_time_05` timestamp(0) generated always AS (FROM_UNIXTIME(floor(UNIX_TIMESTAMP(`the_time`)/ 5)* 5)) virtual
    mais ca ne marche pas a cause du "UNIX_TIMESTAMP" qui ne veut pas etre pris ici...
    Du coup j'ai un brin modifie (merci Google...) et avec ceci ça marche très bien:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    ALTER TABLE DB1.`T1` ADD `the_time_05` timestamp(0) generated always AS (FROM_UNIXTIME(floor(TIMESTAMPDIFF(SECOND, '1970-01-01 00:00:00', `the_time`)/ 5)* 5)) persistent
    C'est assez moche je trouve de faire la soustraction manuellement mais bon... A partir de maintenant si je ne touche plus a rien j'obtiens les résultats souhaites avec des performances acceptable.

    Vaut il mieux mettre un virtual ou un persistent a la fin?


    Le probleme actuel: lors de la jointure, avec un "LIMIT 500" a la fin de la requete:
    • si je fais la jointure sue le the_time => query took 0.3s
    • si je fais la jointure sue le the_time_05 => query took 6s


    Les performances sont catastrophiques! De ce que je comprends les valeurs "the_time_05" arrondi a 5s près ne sont pas sauvegardées dans la base, mais sont recalculées a chaque fois que necessaire...
    Comment forcer la base a garder ces valeurs de manière "statique"?

    Merci encore pour vos réponses, vous me sortez vraiment la tete des ronces (pour ne pas dire autre chose)!

  18. #18
    Expert éminent sénior Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    février 2011
    Messages
    3 856
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 79
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : février 2011
    Messages : 3 856
    Points : 11 753
    Points
    11 753

    Par défaut

    Salut ben500fr.

    Citation Envoyé par ben500fr
    Ma version mySQL refuse le "NOT NULL" apres "virtual" mais sinon ton code marche
    Quelle est la version MySql que vous utilisez ? J'utilise la version 8.0.15.

    Citation Envoyé par ben500fr
    je souhaite ajouter les colonnes avec les dates "arrondis" après la création de la table.
    N'est-ce pas ce que je fais dans mon exemple ?

    Citation Envoyé par ben500fr
    ma clé primaire est de type datetime(3), et non decimal(15,3).
    J'ai cherché à comprendre pourquoi votre exemple, ne fonctionnait pas :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    ALTER TABLE `T1` ADD `the_time_5_s` DATETIME generated always AS (FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(`the_time`)/(5))*(5)))
    J'ai fait des recherches et MySql n'accepte pas la fonction "UNIX_TIMESTAMP" dans une colonne calculée (GENERATED ALWAYS).
    De ce fait, la seule astuce que j'ai trouvé est de recevoir la date sous le format unix, à savoir, un nombre décimal.
    A partir de là, la structure de la table change.

    Citation Envoyé par ben500fr
    mais ca ne marche pas a cause du "UNIX_TIMESTAMP" qui ne veut pas etre pris ici...
    Justement, je ne comprends pas pourquoi MySql interdit l'utilisation de cette fonction dans les colonnes calculées.

    Citation Envoyé par ben500fr
    Du coup j'ai un brin modifie (merci Google...) et avec ceci ça marche très bien:
    J'avais trouvé sur le net cette astuce, consistant à passer par la fonction "TIMESTAMPDIFF()", qui est la solution de remplacement de la fonction "unix_timestamp()".
    Mais quand j'ai testé, cela n'a pas fonctionné.

    Citation Envoyé par ben500fr
    Vaut il mieux mettre un virtual ou un persistent à la fin?
    Le "virtual" permet de ne pas stocker le calcul dans la ligne de la table.
    Le "stored", c'est l'inverse, vous stockez le calcul dans la ligne.

    Par contre, je ne vois pas où vous avez trouvé le "persistent" ???
    --> https://dev.mysql.com/doc/refman/8.0...d-columns.html

    J'ai compris, vous êtes dans Mariadb !
    --> https://mariadb.com/kb/en/library/generated-columns/
    On lit :
    Virtual columns could only use deterministic functions. Since MariaDB 10.2.1 this limitation applies only to PERSISTENT columns.
    Only PERSISTENT columns can be indexed.
    Je préfère le "virtual" car la calcul ne sera pas stockée dans la table.
    Sauf que pour des raisons de performances, vous devez les indexer !
    Donc, vous n'avez pas le choix, c'est obligatoirement "persistent".

    Citation Envoyé par ben500fr
    Les performances sont catastrophiques !
    Vous devez indexer votre colonne "the_time_05". D'où l'utilité de mettre "persistent".

    Citation Envoyé par ben500fr
    Comment forcer la base a garder ces valeurs de manière "statique"?
    Si vous les stocker vous aurez un problème de volumétrie. Sinon, en mettant "stored" à la place de "virtual".

    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr

  19. #19
    Membre à l'essai
    Homme Profil pro
    Administrateur systèmes et réseaux
    Inscrit en
    septembre 2013
    Messages
    29
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 33
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur systèmes et réseaux
    Secteur : High Tech - Opérateur de télécommunications

    Informations forums :
    Inscription : septembre 2013
    Messages : 29
    Points : 18
    Points
    18

    Par défaut

    Citation Envoyé par Artemus24 Voir le message
    Salut ben500fr.
    Quelle est la version MySql que vous utilisez ? J'utilise la version 8.0.15.
    MariaDB 5.5.60


    Citation Envoyé par Artemus24 Voir le message
    J'ai fait des recherches et MySql n'accepte pas la fonction "UNIX_TIMESTAMP" dans une colonne calculée (GENERATED ALWAYS).
    De ce fait, la seule astuce que j'ai trouvé est de recevoir la date sous le format unix, à savoir, un nombre décimal.
    A partir de là, la structure de la table change.
    J'avais bien intuité que MariaDB n'accepte pas la fonction "UNIX_TIMESTAMP" dans certains cas, mais je ne l'avais pas trouve dans la doc... Merci de cette confirmation!


    Citation Envoyé par Artemus24 Voir le message
    Vous devez indexer votre colonne "the_time_05". D'où l'utilité de mettre "persistent".
    Je venais justement éditer mon message pour dire que je suis un boulet: je n'ai pas indexé la colonne!
    Comme vous pouvez l'imaginer mes bases sont un poil plus complexe que ce que je vous montre ici, j'ai des scripts bash pour créer/éditer/réinitialiser mes bases/tables/vues a la volée. Et la ligne qui ajoute l'index été commentée!
    Bref une vue qui prenais 20 minutes prend maintenant 0.0007 secondes a être générée avec 330.700 lignes a la sortie. C'est fou! (et ce n'est que le début)

    Je vais déclarer le sujet résolu maintenant. Et vous dire un grand MERCI!
    En plus de quasiment faire le travail a ma place, vous m'avez amené a me poser les bonnes questions et a mieux réfléchir ma façon appréhender mes structures de tables.

  20. #20
    Expert éminent sénior Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    février 2011
    Messages
    3 856
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 79
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : février 2011
    Messages : 3 856
    Points : 11 753
    Points
    11 753

    Par défaut

    Salut ben500fr.

    Citation Envoyé par ben500fr
    En plus de quasiment faire le travail à ma place, vous m'avez amené à me poser les bonnes questions et à mieux réfléchir ma façon appréhender mes structures de tables.
    Le but de nos échanges est justement de réfléchir à une meilleur solution.

    La solution basée sur les colonnes calculées (generated always) à le mérite de faire la calcul virtuellement et de pouvoir l'utiliser par la suite.
    Elle n'alourdie pas la volumétrie de la table, et prépare un tant soit peu le travail à faire.
    L'inconvénient est le nombre de colonnes que l'on doit créer pour chaque test à faire.
    Comme le temps d'exécution est une contrainte importante, je ne vois pas d'autre solution que celle que je vous ai proposé.

    Il existe une autre solution qui consiste à dupliquer les lignes dans votre table en spéciant le type de précisions appliquées sur la colonne "the_time".
    Par exemple :
    --> type = 1, "the_time" est non modifié
    --> type = 2, "the_time" est à 1 seconde près.
    --> type = 3, "the_time" est à 5 secondes près.
    --> type = 4, "the_time" est à 15 secondes près.
    Et ainsi de suite. Elle a surtout le mérite d'être très volumineuse.

    Si vous êtes près à perdre du temps, le mieux aurait été de créer une table temporaire pour la mise en forme de votre précision.

    Après, je ne vois rien d'autre.

    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr

+ Répondre à la discussion
Cette discussion est résolue.
Page 1 sur 2 12 DernièreDernière

Discussions similaires

  1. Réponses: 6
    Dernier message: 06/10/2006, 23h15
  2. JOIN avec plusieurs Items dirigeant vers le même item ?
    Par Fred_76 dans le forum Requêtes
    Réponses: 4
    Dernier message: 05/07/2006, 12h08
  3. INNER JOIN avec des données de sélection
    Par EK1_ dans le forum Langage SQL
    Réponses: 2
    Dernier message: 17/05/2006, 18h40
  4. Réponses: 9
    Dernier message: 21/04/2006, 16h24
  5. LEFT JOIN avec Oracle 8i ne va pas... doit utiliser (+)
    Par loikiloik dans le forum Langage SQL
    Réponses: 10
    Dernier message: 21/04/2004, 16h38

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo