Partage
  • Partager sur Facebook
  • Partager sur Twitter

select, transfert (insert) et effacement (delete)

[HELP]

    7 juin 2018 à 9:29:50

    Bonjour tout le monde,

    J'aimerais savoir comment on fait pour qu'avec une requête SQL, on puisse :

    - sectionner tous les enregistrements sauf les 240 plus récentes valeurs d'une table 1 (DONNEES)

    -puis, les transférer dans une autre table 2 (DONNEES_ARC) constituée exactement de la même manière

    INSERT INTO DONNEES_ARC (`Key`, `Id_Capteur`, `Désignation`, `Valeur`, `Date`,`Heure`) SELECT `Key`, `Id_Capteur`, `Désignation`, `Valeur`, `Date`, `Heure`  FROM DONNEES

    -et enfin, les effacer de la table 1 (DONNEES)

    Respectueusement,

    -
    Edité par JuPe2 7 juin 2018 à 16:18:57

    • Partager sur Facebook
    • Partager sur Twitter
      Staff 7 juin 2018 à 17:20:31

      Bonjour,

      JuPe2 a écrit:

      les 240 plus récentes valeurs d'une table

      SELECT *
      FROM DONNEES
      ORDER BY `date` DESC
      LIMIT 240

      Donc à partir de là, tu peux trouver le complément des données qui ne sont pas ces 240 :

      SELECT T1.*
      FROM
          DONNEES T1
              LEFT JOIN (
                      SELECT key
                      FROM DONNEES
                      ORDER BY `date` DESC
                      LIMIT 240
                  ) T2
                  ON T1.key = T2.key
      WHERE T2.key IS NULL

      Pour les insérer dans la table d'archive et les supprimer ensuite :

      INSERT INTO DONNEES_ARC
      SELECT T1.*
      FROM
          DONNEES T1
              LEFT JOIN (
                      SELECT key
                      FROM DONNEES
                      ORDER BY `date` DESC
                      LIMIT 240
                  ) T2
                  ON T1.key = T2.key
      WHERE T2.key IS NULL;
      
      DELETE T1
      FROM
          DONNEES T1
              LEFT JOIN (
                      SELECT key
                      FROM DONNEES
                      ORDER BY `date` DESC
                      LIMIT 240
                  ) T2
                  ON T1.key = T2.key
      WHERE T2.key IS NULL;
      • Partager sur Facebook
      • Partager sur Twitter
      Seul on va plus vite, ensemble on va plus loin ... A maîtriser : Conception BDD, MySQL, PHP/MySQL
        8 juin 2018 à 10:15:56

        Salut Benyouze,

        J'ai un problème, ton SELECT affiche toutes les lignes sauf les 240 premieres alors que ce que je veux c'est qu'il affiche toutes les lignes sauf les 240 dernieres (quand je dis premieres ou dernieres, c'est en fonction de la date et de l'heure, soit les 240 plus récentes sont les 240 dernieres)

        Ensuite je n'arrive pas à exécuter le DELETE, je ne comprends pas pourquoi

        Et peux tu m'expliquer à quoi correspond T1 et T2 ?

        • Partager sur Facebook
        • Partager sur Twitter
          Staff 8 juin 2018 à 19:33:43

          JuPe2 a écrit:

          ton SELECT affiche toutes les lignes sauf les 240 premieres

          Pour moi, "dernières" signifie "les plus récentes", et "premières" signifie "les plus anciennes" ...

          En triant par date décroissante, la sous requête doit retourner les 240 plus récentes. Par la jointure externe on récupère le complément : toutes les lignes sauf les 240 plus récentes selon la date ...

          Si tu veux toutes les lignes sauf les 240 plus anciennes, alors il faut inverser le tri :

          INSERT INTO DONNEES_ARC
          SELECT T1.*
          FROM
              DONNEES T1
                  LEFT JOIN (
                          SELECT key
                          FROM DONNEES
                          ORDER BY `date` ASC
                          LIMIT 240
                      ) T2
                      ON T1.key = T2.key
          WHERE T2.key IS NULL;
           
          DELETE T1
          FROM
              DONNEES T1
                  LEFT JOIN (
                          SELECT key
                          FROM DONNEES
                          ORDER BY `date` ASC
                          LIMIT 240
                      ) T2
                      ON T1.key = T2.key
          WHERE T2.key IS NULL;

          JuPe2 a écrit:

          je n'arrive pas à exécuter le DELETE

          C'est à dire ? Une erreur est retournée ? Laquelle ?

          JuPe2 a écrit:

          eux tu m'expliquer à quoi correspond T1 et T2 ?

          Ce sont des alias, des noms de remplacement, qui permettent d'éviter de réécrire le nom de la table à chaque fois, et surtout dans ce cas, d'utiliser plusieurs fois la même table ...

          • Partager sur Facebook
          • Partager sur Twitter
          Seul on va plus vite, ensemble on va plus loin ... A maîtriser : Conception BDD, MySQL, PHP/MySQL
            9 juin 2018 à 7:49:29

            Benzyoue, tu oublie de dire que ces trois opérations doivent être effectuées dans une transaction ! Sans quoi on pourrait avoir des duplications dans la table de destination si le DELETE ne devait pas avoir lieu.

            Mais pourquoi j'ai l'impression de parler dans le vide ?

            https://openclassrooms.com/forum/sujet/importation-de-bdd-necessaire-1#message-92369880 

            https://openclassrooms.com/forum/sujet/donnees-sur-une-semaine#message-92372881

            https://openclassrooms.com/forum/sujet/recup-1-enregistrement-sur-3#message-92412484

            C'est fou cette volonté de se tirer une balle dans le pied à utiliser un SGBDR pour stockée des données métriques horodatées alors que ce n'est pas fait pour ça !

            Tu te fais chier comme pas possible à utiliser une table d'historique... Et pour quoi en plus ? Je ne vois vraiment pas l'utilité dans ce cas là.

            Avec InfluxDB tu peux récupérer n'importe quelle série en bornant avec des dates, ou récupérer uniquement les dernières valeurs (pour avoir les valeurs actuelle). Et tu peux aussi paramétrer le downsampling et la durée de rétention : passé un certains temps, elles sont supprimée. Plutôt que de les perdre totalement, on peut les agréger avec d'autres pour ne garder que des moyenne (ou toute autre fonction d'agrégation) par plage de temps.

            Exemple:

            * on garde toutes les valeurs bruts pour les 30 dernier jours

            * on garde que les moyenne par heure pour les 2 derniers mois

            * on garde que les moyennes par jour pour l'année dernière

            * on supprime définitivement toutes les données de plus d'un an

            Cela permet de gérer de manière optimale la quantité de données à sauvegarder, et donc leur volumes.

            Je parle d'influxDB, mais n'importe quelle base de données timeseries sait faire ce genre de chose.

            Le bon outil pour le bon problème. Vraiment, oublie MySQL ou n'importe quel autre SGBDR pour ce genre de données. Les SGBDR (pour relationnelles), sont fait pour gérer des relations entre les données. Or, il n'y a pas de relation avec une donnée d'un capteur ! Excepté le nom du capteur, mais c'est là la seule, unique, clairement identifiée, et ne pouvant être supprimée (une mesure sans savoir à quel capteur elle appartient n'a plus aucune valeurs).

            Comme je l'ai dit, ça va faire dix ans que je travail dans le domaine. Je connais TOUS les pièges. Je peux même te faire les prévisions des problèmes que tu va rencontrer une fois le programme mis en place :

            - des temps de traitements de recherche long (> ~10s) dues à l'utilisation de deux colonnes Date et Heure en VARCHAR au lieu de DATETIME. Tu finira par changer ça, et avoir de nouveaux des perf corrects.

            - Puis de nouveau des temps de traitement long, sans explications apparente. Il s'agira en fait de la colonne "Désignation", qui prend de la place inutilement dans chaque ligne, et augmente le temps de balayage de page, même quand l'information n'est pas dans le SELECT. Vous finirez par la déplacer dans une autre table avec une jointure, et de nouveau vous récupérez des perfs correct.

            - Vous aurrez des pb de perf aussi pour récupérer les valeurs les plus à jour. Vous ferez donc une table spécifique pour ça, contenant uniquement la dernière valeur de chaque capteur, mise à jour par un trigger complexe.

            - Puis 1 an après la sortie du soft, de nouveau des problèmes de perf (requête de plus de 30s)  dans le calcul d'historique (ex: afficher les moyenne de chaque jour pour un mois donné). C'est tout simplement à cause des limitations de MySQL sur les gros volumes. Vous trouverez la solution avec une nouvelle table historique, que ne conservent que les moyennes par jour. Le mieux serait de les remplir par trigger, mais je ne crois pas que MySQL gère le calcul dans une procédure stockée, et l'insertion dans une autre table, à partir de fonction.

            etc...

            Ou alors tu peux écouter un type qui est passé par là et qui tu dis: évite les emmerde, utiliser des maintenant un outil fait pour solutionner les problème de stockage de données IoT, qui sait gérer nativement tous ces problème sans que tu ais à t'en soucier.

            Mais qu'on soit bien clair, je n'aiderais pas à l'avenir sur des problèmes qui aurait pu être évité avec un peu d'écoute...

            -
            Edité par Sebajuste 9 juin 2018 à 7:51:41

            • Partager sur Facebook
            • Partager sur Twitter

            select, transfert (insert) et effacement (delete)

            × Après avoir cliqué sur "Répondre" vous serez invité à vous connecter pour que votre message soit publié.
            × Attention, ce sujet est très ancien. Le déterrer n'est pas forcément approprié. Nous te conseillons de créer un nouveau sujet pour poser ta question.
            • Editeur
            • Markdown