IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
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

Requêtes MySQL Discussion :

Splitter un enregistrement en N enregistrements en fonction de 2 dates [MySQL-8.0]


Sujet :

Requêtes MySQL

  1. #1
    Membre régulier
    Profil pro
    Inscrit en
    avril 2003
    Messages
    253
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : avril 2003
    Messages : 253
    Points : 93
    Points
    93
    Par défaut Splitter un enregistrement en N enregistrements en fonction de 2 dates
    Bonjour à tous,

    Je bloque sur un problème SQL : j'ai un enregistrement ayant 3 champs :
    - start date (datetime) : 2021-01-01 13:01:30
    - end date (datetime) : 2021-01-01 13:05:15
    - nombre : 500

    je voudrais splitter cet enregistrement en autant d'enregistrement que l'on trouve de minutes entre start_date et end_date, et diviser le nombre par le nombre d'enregistrements possibles (ici : 5 sous-enregistrements) :

    Enregistrement 1 :
    - start date (datetime) : 2021-01-01 13:01:00
    - end date (datetime) : 2021-01-01 13:01:59
    - nombre : 500/5 = 100

    Enregistrement 2 :
    - start date (datetime) : 2021-01-01 13:02:00
    - end date (datetime) : 2021-01-01 13:02:59
    - nombre : 500/5 = 100

    Enregistrement 3 :
    - start date (datetime) : 2021-01-01 13:03:00
    - end date (datetime) : 2021-01-01 13:03:59
    - nombre : 500/5 = 100

    Enregistrement 4 :
    - start date (datetime) : 2021-01-01 13:04:00
    - end date (datetime) : 2021-01-01 13:04:59
    - nombre : 500/5 = 100

    Enregistrement 5 :
    - start date (datetime) : 2021-01-01 13:05:00
    - end date (datetime) : 2021-01-01 13:05:15
    - nombre : 500/5 = 100

    Ceci n'est qu'un exemple, je voudrais que cela se fasse automatiquement entre start_date et end_date, quelles que soient leurs valeurs.
    J'ai bien cherché sur internet mais je n'ai pas trouvé de manière dynamique de splitter ces enregistrements.

    Je vous remercie pour vos lumières !

  2. #2
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    20 943
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 20 943
    Points : 49 759
    Points
    49 759
    Billets dans le blog
    1
    Par défaut
    Il faut faire une CTE récursive qui présentera autant de lignes que de segment à créer, diviser l'intervalle de temps par la mesure demandée et faire un produit cartésien final pour réajuster le temps initial à la quantité d'intervalle de temps.

    Néanmoins, je remarque que vos données sont anormales... En effet lorsque l'on travaille avec des intervalles, il faut définir que l'intervalle
    • soit fermé à gauche et ouverte à droite ([...[)
    • soit ouvert à gauche et fermé à droite (]...])


    Sinon tous vos calculs seront faux.

    Pour de plus amples explication, merci de respecter la règle de postage et donner le DDL de vos tables ainsi qu'un jeu d'essais sous forme INSERT.

    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...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  3. #3
    Membre régulier
    Profil pro
    Inscrit en
    avril 2003
    Messages
    253
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : avril 2003
    Messages : 253
    Points : 93
    Points
    93
    Par défaut
    Bonjour SQLPro et merci pour votre réponse !

    Voici les éléments demandés :

    La table :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    CREATE TABLE `STEPS` (
      `start_date` datetime NOT NULL,
      `end_date` datetime NOT NULL,
      `steps` int DEFAULT NULL,
      `year` int DEFAULT NULL,
      `day` int DEFAULT NULL,
      `month` int DEFAULT NULL,
      `week_number` int DEFAULT NULL,
      `week_day` int DEFAULT NULL,
      PRIMARY KEY (`start_date`,`end_date`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
    Un jeu de données sous forme d'INSERT :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    INSERT INTO STEPS VALUES(
    ('2021-08-11 19:04:40','2021-08-11 19:04:45',11,2021,11,8,32,3)
    ,('2021-08-11 19:04:45','2021-08-11 19:04:50',10,2021,11,8,32,3)
    ,('2021-08-11 19:04:50','2021-08-11 19:04:55',10,2021,11,8,32,3)
    ,('2021-08-11 19:04:55','2021-08-11 19:05:01',11,2021,11,8,32,3)
    );
    C'est pour un projet personnel. Je fais un export des données santé de l'iphone, qui est disponible au format XML, je l'importe dans un site et je fais des statistiques sur les pas effectués.

    Je pose ma question car je voudrais faire un graphique avec une journée type. Donc je dois lisser les informations (par demi-heure) pour connaître le nombre de pas moyen pour chaque demi-heure de la journée.

    Je vais faire des recherches sur la CTE récursive, je ne connais pas du tout.

    Je suis vraiment désolé si je n'ai pas respecté les règles de postage, je ne pensais pas avoir commis d'erreur en posant ma question Qu'ai-je dit ou fait de non conforme ?

    Un grand merci et une excellente soirée.

  4. #4
    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
    5 145
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    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 : 5 145
    Points : 15 302
    Points
    15 302
    Par défaut
    Salut à tous.

    Code mysql : 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
    --------------
    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 `test`
    --------------
     
    --------------
    CREATE TABLE IF NOT EXISTS `test`
    ( `id`          integer unsigned  NOT NULL auto_increment primary key,
      `start_date`  datetime          NOT NULL,
      `end_date`    datetime          NOT NULL,
      `steps`       integer unsigned  NOT NULL
    ) ENGINE=InnoDB
      DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
      ROW_FORMAT=COMPRESSED
    --------------
     
    --------------
    DROP TABLE IF EXISTS `split`
    --------------
     
    --------------
    CREATE TABLE `split` LIKE `test`
    --------------
     
    --------------
    describe `split`
    --------------
     
    +------------+--------------+------+-----+---------+----------------+
    | Field      | Type         | Null | Key | Default | Extra          |
    +------------+--------------+------+-----+---------+----------------+
    | id         | int unsigned | NO   | PRI | NULL    | auto_increment |
    | start_date | datetime     | NO   |     | NULL    |                |
    | end_date   | datetime     | NO   |     | NULL    |                |
    | steps      | int unsigned | NO   |     | NULL    |                |
    +------------+--------------+------+-----+---------+----------------+
    --------------
    drop procedure if exists `trait`
    --------------
     
    --------------
    create procedure `trait` ()
    DETERMINISTIC
    NO SQL
    BEGIN
      DECLARE _start  DATETIME DEFAULT NULL;
      DECLARE _end    DATETIME DEFAULT NULL;
      DECLARE _step   INTEGER  DEFAULT NULL;
     
      DECLARE _inter  DATETIME DEFAULT NULL;
      DECLARE _nbre   INTEGER  DEFAULT NULL;
      DECLARE _loop   INTEGER  DEFAULT NULL;
     
      DECLARE _fin    INTEGER  DEFAULT 1;
     
      DECLARE _tab  CURSOR FOR SELECT `start_date`,`end_date`,`steps` FROM `test`;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET _fin = 0;
     
      OPEN  _tab;
      FETCH _tab INTO _start, _end, _step;
     
      SET _inter = date_format(_start,'%y-%m-%d %H:%i:59');
     
      WHILE (_fin)
      DO
        SET _nbre  = TIMESTAMPDIFF(MINUTE,date_format(_start,'%y-%m-%d %H:%i:00'),date_format(_end ,'%y-%m-%d %H:%i:00')) + 1;
        SET _loop  = _nbre;
     
        WHILE (_loop > 0)
        DO
          IF (_loop = 1) THEN SET _inter = _end;
          ELSE                SET _inter = date_format(_start,'%y-%m-%d %H:%i:59');
          END IF;
     
          INSERT INTO `split` (`start_date`,`end_date`,`steps`) VALUE (_start,_inter,_step/_nbre);
     
          SET _start = DATE_ADD(DATE_FORMAT(_start,'%y-%m-%d %H:%i:00'), INTERVAL 1 MINUTE);
          SET _loop  = _loop - 1;
        END WHILE;
     
        FETCH _tab INTO _start, _end, _step;
      END WHILE;
     
      CLOSE _tab;
    END
    --------------
     
    --------------
    INSERT INTO `test` (`start_date`,`end_date`,`steps`) VALUES
      ('2021-01-01 13:01:30','2021-01-01 13:05:15',500)
    --------------
     
    --------------
    CALL trait()
    --------------
     
    --------------
    select * from `test`
    --------------
     
    +----+---------------------+---------------------+-------+
    | id | start_date          | end_date            | steps |
    +----+---------------------+---------------------+-------+
    |  1 | 2021-01-01 13:01:30 | 2021-01-01 13:05:15 |   500 |
    +----+---------------------+---------------------+-------+
    --------------
    select * from `split`
    --------------
     
    +----+---------------------+---------------------+-------+
    | id | start_date          | end_date            | steps |
    +----+---------------------+---------------------+-------+
    |  1 | 2021-01-01 13:01:30 | 2021-01-01 13:01:59 |   100 |
    |  2 | 2021-01-01 13:02:00 | 2021-01-01 13:02:59 |   100 |
    |  3 | 2021-01-01 13:03:00 | 2021-01-01 13:03:59 |   100 |
    |  4 | 2021-01-01 13:04:00 | 2021-01-01 13:04:59 |   100 |
    |  5 | 2021-01-01 13:05:00 | 2021-01-01 13:05:15 |   100 |
    +----+---------------------+---------------------+-------+
    --------------
    truncate `test`
    --------------
     
    --------------
    truncate `split`
    --------------
     
    --------------
    INSERT INTO `test` (`start_date`,`end_date`,`steps`) VALUES
      ('2021-08-11 19:04:40','2021-08-11 19:04:45',11),
      ('2021-08-11 19:04:45','2021-08-11 19:04:50',10),
      ('2021-08-11 19:04:50','2021-08-11 19:04:55',10),
      ('2021-08-11 19:04:55','2021-08-11 19:05:01',11)
    --------------
     
    --------------
    CALL trait()
    --------------
     
    --------------
    select * from `test`
    --------------
     
    +----+---------------------+---------------------+-------+
    | id | start_date          | end_date            | steps |
    +----+---------------------+---------------------+-------+
    |  1 | 2021-08-11 19:04:40 | 2021-08-11 19:04:45 |    11 |
    |  2 | 2021-08-11 19:04:45 | 2021-08-11 19:04:50 |    10 |
    |  3 | 2021-08-11 19:04:50 | 2021-08-11 19:04:55 |    10 |
    |  4 | 2021-08-11 19:04:55 | 2021-08-11 19:05:01 |    11 |
    +----+---------------------+---------------------+-------+
    --------------
    select * from `split`
    --------------
     
    +----+---------------------+---------------------+-------+
    | id | start_date          | end_date            | steps |
    +----+---------------------+---------------------+-------+
    |  1 | 2021-08-11 19:04:40 | 2021-08-11 19:04:45 |    11 |
    |  2 | 2021-08-11 19:04:45 | 2021-08-11 19:04:50 |    10 |
    |  3 | 2021-08-11 19:04:50 | 2021-08-11 19:04:55 |    10 |
    |  4 | 2021-08-11 19:04:55 | 2021-08-11 19:04:59 |     6 |
    |  5 | 2021-08-11 19:05:00 | 2021-08-11 19:05:01 |     6 |
    +----+---------------------+---------------------+-------+
    --------------
    COMMIT
    --------------
     
    Appuyez sur une touche pour continuer...

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

  5. #5
    Membre régulier
    Profil pro
    Inscrit en
    avril 2003
    Messages
    253
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : avril 2003
    Messages : 253
    Points : 93
    Points
    93
    Par défaut
    Bonjour Artémus,

    Un grand merci pour ce code, je ne m'imaginais pas recevoir une réponse aussi claire et précise !
    Je trouve des erreurs à l'exécution du code mais je crois que je dois l'adapter pour que ça fonctionne chez moi.

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5
    Etant donné que je n'ai pas pratiqué les procédures mysql, je dois chercher de mon côté pourquoi j'ai ces erreurs. Je reviendrai vers vous pour vous dire ce qu'il en est.

    Encore merci et à très vite !

  6. #6
    Membre régulier
    Profil pro
    Inscrit en
    avril 2003
    Messages
    253
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : avril 2003
    Messages : 253
    Points : 93
    Points
    93
    Par défaut
    Bonjour Artémus,

    Votre code fonctionnement parfaitement, conformément à ce que j'avais demandé. Je vous remercie énormément pour tout le travail que vous avez accompli pour pouvoir m'aider.

    Un grand merci à vous.

    A++

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Réponses: 2
    Dernier message: 17/03/2016, 00h06
  2. Réponses: 2
    Dernier message: 13/06/2013, 10h14
  3. [AC-2010] Atteindre enregistrement en fonction de la date du jour
    Par sardaucar dans le forum IHM
    Réponses: 7
    Dernier message: 11/01/2013, 11h19
  4. [XL-2007] Enregistrer en fonction de la date
    Par nelly10 dans le forum Macros et VBA Excel
    Réponses: 1
    Dernier message: 27/09/2009, 20h36
  5. Trier les enregistrements en fonction de la date
    Par mpereg dans le forum XSL/XSLT/XPATH
    Réponses: 3
    Dernier message: 12/09/2004, 15h16

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