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

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;

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 ?

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 ...

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