Bonjour j'aimerais savoir comment on fait pour récupérer un enregistrements sur trois, est-ce que quelqu'un a déjà eu ce problème ou sait comment faire

merci d'avance

Respectueusement

-
Edité par JuPe2 4 juin 2018 à 11:09:23

Bonjour,

Quel SGBD utilises-tu ?

Quelle est la structure de la base ? de la (les) table (s) en question ?

Quand tu dis 1/3 c'est selon quels critères de tri ? une date ? un ID ?

Qu'as-tu déjà essayé de faire ?

Bonjour,

J'utilise MySQL
La structure est la suivante : --> 1 seule table

Lorsque je dis 1/3, j'aurais aimé garder une valeur sur 3 pour ensuite l'afficher sur un graphe

J'avais pensé à une boucle for, tant que i < 3, i ++ et on met la requête dans le for mais j'aimerais le faire avec une seule requête

Ok, mais quand tu dis "une valeur sur 3" il y a une notion de tri, d'ordre ? L'ordre d'apparition dans la table ?

Si les "key" sont continues (sans trou), alors tu peux utiliser le modulo :

SELECT *
FROM matable
WHERE MOD( `Key`, 3 ) = 0

Cela ne fonctionne que si les "key" sont continues. Dès qu'il y a un trou dans l'incrémentation, alors le tri est faussé ...

J'ai trouvé çà : https://stackoverflow.com/questions/4329396/mysql-select-10-random-rows-from-600k-rows-fast

Je pense que cela pourrais aussi être une solution. (Récupération de valeur aléatoire sur une quantité choisie).

Oui mais j'ai l'impression que ce n'est pas aléatoire ici ... On dirait qu'il veut récupérer des enregistrements bien particulier ...

Autant pour moi, si il ne souhaite pas avoir de données aléatoirement cela ne conviendra pas.

Benzouye a écrit:

[...] Si les "key" sont continues (sans trou) [...]

Attention, des clef sans trou ça n'existe pas ! On imagine un trou par des DELETE, mais on peut aussi en avoir avec des INSERT ! Tout INSERT crée une incrémentation, même si elle échoue.

CREATE TABLE test (
  id integer primary key auto_increment,
  name varchar(32) unique
);

INSERT INTO test (name) VALUES ('toto');
INSERT INTO test (name) VALUES ('toto'); -- va échouer
INSERT INTO test (name) VALUES ('tata');

SELECT id FROM test; -- Affiche 3 et 1, le 2 n'existe pas

On partira donc toujours du principe que les clef en auto_increment ne se suivent jamais, car on ne peut pas le supposer.

Pour répondre à la question posée, je suppose qu'il veut trier par date, puisque c'est pour créer un graph. Et qu'il vaut une valeur sur trois pour limiter le nombre de donnée à retourner.

Mais c'est une erreur ! Il faut au contraire regrouper sur une base de temps : ex créer 1 point par heure, ou 1 point par jour, suivant la plage de temps que l'on souhaite. En effet, en ne prenant qu'une valeur sur 3, tu risque d'avoir des "trous" dans ton graph si ton capteur n'a pas "parlé" à interval régulier (erreur de transmission etc...)

Il faut donc faire un GROUP BY sur une extraction de la date (l'heure, le jour, etc...) et par capteur, biensur. Et c'est là que vient le drame... Tu utilises deux colonnes Heure et Jour au format varchar. Il faut en utiliser qu'une seule au format DATETIME.

La requête ressemble à quelque chose comme ça :

SELECT id_capteur, DAY(value_date), HOUR(value_date), AVG(Valeur)
FROM valeurs
GROUP BY id_capteur, DAY(value_date), HOUR(value_date)

Maintenant, mon avis personnel. Je travail dans le domaine de l'IoT depuis 2009, et je connais bien les problèmes posés par ce type d'architecture. Un bon conseil: oublie les SGBDR pour sauvegarder les données des capteurs !! Ils ne sont pas adapté pour ça.

J'ai utilisé MySQL pendant 4 ans pour l'IoT avant de l'abandonner. Faire des regroupement par une fonction (comme HOUR() ) ne peut pas être optimisé par un index !! Or les données IoT sont par définition des gros volumes. Les performances ont commencé à chuter à partir de 200 Millions de valeurs (~ 4 ans d'exploitation). Et je suis passé sous PostgreSQL... et ce n'est guère mieux !!

Avec Postgres, les ralentissements ont commencé à survenir 4 années plus tard, avec près de 1.8 milliard de valeurs. Il nous a été possible de repousser encore plus loin les limites en utilisant le partitionnement par "installation" (localisation de plusieurs capteurs au même endroits). Mais jusqu'à quand ?

D'autant plus que certains problème sont très difficilement gérable par les SGBDR, comme la récupération des valeurs en fonction de la timezone. HOUR() fonctionnera très bien tant que la plage à afficher correspond exactement à la même timezone que les données enregistrée. Mais si l'utilisateur n'est pas en France (et 1 heure de décalage c'est vite trouvé en Europe), c'est la merde absolue !

Aussi, je préconise pour l'IoT d'utiliser des bases de données NoSQL orienté time series. Elles sont justement faites pour exploiter de gros volumes de manière performante tout en proposant de fonction dédié à la gestion du temps. https://en.wikipedia.org/wiki/Time_series_database Ma préférence actuelle va à InfluxDB (https://www.influxdata.com/ ) qui est open source (et donc gratuit si installé sur un dédié).

Dans tous les cas, même pour un petit projet qui ne contiendrait que quelques milliers de valeurs (c'est à dire seulement quelques jours / semaines d'enregistrements), ta table ne va pas.

Il faut impérativement retirer TOUS les varchar. Actuellement, chaque ligne peut contenir 227 octets (4 + 4 + 100 + 4 + 100 + 15). En admettant que tu ai 5 capteurs, qui envois des données toutes les 5 minutes, sur une semaine tu consommera 11,7 Mo (~50 000 valurs) et sur une année 0.6 Go (~2.6 millions de valeurs). Avec de tels volumes, les requètes MySQL sur des varchar se compteront en minutes, voir dizaines de minutes.

Je recommanderais au moins une table, avec une clef primaire composite :

valeurs ( value_date [pk], id_capteur [pk], id_designation, value)

La clef primaire composite oblige le SGBD à la pose automatique d'un index. Mettre la colonne value_date en premier va permettre les optimisations sur les plage de temps, puis de trier sur cette plage par capteurs. Il sera impératif de borner toutes recherche par une clause temporelle

WHERE value_date BETWEEN start AND end

C'est l'une des rares choses que MySQL arrive à optimiser correctement. Autant en profiter !

Toutefois, parce qu'on est parfois amener à rechercher des valeurs que pour un capteur en particulier, il faudra poser en plus un autre index composite dans l'autre sens (id_capteur, value_date), de façon à chercher les valeurs pour un capteur particulier, sur une plage temporelle donnée.

-
Edité par Sebajuste 14 juin 2018 à 13:12:24

Bonjour tout le monde,

Finalement, je n'ai pas réussi à intégrer 

WHERE MOD( `Key`, 3 ) = 0

dans mes requêtes

$sql04="SELECT * 
FROM 
( 
  SELECT `Valeur`,Heure
  FROM DONNEES 
  WHERE Désignation = 'Température' 
  GROUP BY Heure,`Valeur`
  ORDER BY Heure DESC LIMIT 25
  )
AS SQ 
ORDER BY Heure ASC";

$sql05="SELECT * 
FROM 
( 
  SELECT `Valeur`,`Heure`
  FROM DONNEES 
  WHERE Désignation = 'Humidité' AND MOD( `Key`, 7)=0
  GROUP BY Heure,`Valeur`
  ORDER BY Heure DESC LIMIT 48
  )
AS SQ  
ORDER BY Heure ASC";

$sql06="SELECT * 
FROM 
( 
  SELECT `Valeur`,Heure
  FROM DONNEES 
  WHERE Désignation = 'Lumiere' 
  GROUP BY Heure,`Valeur`
  ORDER BY Heure DESC LIMIT 25
  )
AS SQ 
ORDER BY Heure ASC";

Le résultat que je cherche est que chaque requête me retourne une de ses valeurs espacé de 30 min avec la précédente et la suivante (bref toutes les 30 mins), sachant que mes capteurs envoient leurs données toutes les minutes

-
Edité par JuPe2 14 juin 2018 à 10:30:24

JuPe2 a écrit:

Le résultat que je cherche est que chaque requête me retourne une de ses valeurs espacé de 30 min avec la précédente et la suivante (bref toutes les 30 mins)

Ah bah là on retombe dans les explications de Sebajuste !

Sebajuste a écrit:

Il faut au contraire regrouper sur une base de temps : ex créer 1 point par heure, ou 1 point par jour, suivant la plage de temps que l'on souhaite. En effet, en ne prenant qu'une valeur sur 3, tu risque d'avoir des "trous" dans ton graph si ton capteur n'a pas "parlé" à interval régulier (erreur de transmission etc...)

Il faut donc faire un GROUP BY sur une extraction de la date (l'heure, le jour, etc...) et par capteur, biensur. Et c'est là que vient le drame... Tu utilises deux colonnes Heure et Jour au format varchar. Il faut en utiliser qu'une seule au format DATETIME.

Mais je suppose que tu n'avais pas pris en compte ses remarques pourtant plus que pertinentes ...

Donc commence par changer tes VARCHAR et regrouper tes colonnes en DATETIME !

Ensuite, son conseil était quand même :

Sebajuste a écrit:

Aussi, je préconise pour l'IoT d'utiliser des bases de données NoSQL orienté time series. Elles sont justement faites pour exploiter de gros volumes de manière performante tout en proposant de fonction dédié à la gestion du temps. https://en.wikipedia.org/wiki/Time_series_database Ma préférence actuelle va à InfluxDB (https://www.influxdata.com/ ) qui est open source (et donc gratuit si installé sur un dédié).

-
Edité par Benzouye 14 juin 2018 à 11:13:54

Il m'a répondu par MP que c'était pour un projet qui doit se terminer ce mois-ci. Donc j'en conclu un projet étudiant. Le système ne sera donc jamais utilisé en production, autant garder un SGBDR vu le temps restant.

Par contre, il y quand même un minimum de chose à faire : retirer les varchar (ce qui implique un minimum de normalisation), utiliser DATETIME.

D'une part tes colonnes id_capteur et désignation sont redondantes ! Il faut retirer désignation. Ensuite on peut gagner de la place en supprimant la colonne "Key", et créer une clef primaire composite avec le datetime et l'id du capteur (il ne peut pas y avoir deux valeurs au même instant pour le même capteur).

Le datetime est en DEFAULT NOW(), permettant ainsi de ne pas définir soit même l'heure d'insertion, ce sera par défaut l'heure du système (et donc l'heure de la réception de la valeur du capteur).

Voici les tables nécessaires : 

CREATE TABLE SENSOR_TYPE (
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(32) UNIQUE
);

INSERT INTO SENSOR_TYPE (name) VALUES ('Lumiere'), ('Temperature'), ('Humidite');

CREATE TABLE SENSOR_VALUE (
  sensor_date DATETIME NOT NULL DEFAULT NOW(),
  id_sensor INTEGER NOT NULL,
  sensor_value FLOAT NOT NULL,
  PRIMARY KEY(sensor_date, id_sensor),
  FOREIGN KEY(id_sensor) REFERENCES SENSOR_TYPE(id)
);

Et voici un exemple d'enregistrement de valeurs :

INSERT INTO SENSOR_VALUE (id_sensor, sensor_value) SELECT id, 28.5 FROM SENSOR_TYPE WHERE name = 'Temperature';

Pour ressortir les données, il y a la première solution : faire une moyenne de toutes les valeurs sur une heure :

SELECT SENSOR_TYPE.name, sensor_value
FROM (
  SELECT id_sensor, DATE(sensor_date) as date, HOUR(sensor_date) as hour, AVG(sensor_value) as sensor_value
  FROM SENSOR_VALUE
  WHERE sensor_date BETWEEN '2018-06-14' AND '2018-06-15'
  GROUP BY id_sensor, DATE(sensor_date), HOUR(sensor_date), ( MINUTE(sensor_date) % 30 )
) as TEMP
  INNER JOIN SENSOR_TYPE
    ON SENSOR_TYPE.id = TEMP.id_sensor
ORDER BY SENSOR_TYPE.id, TEMP.date ASC, TEMP.hour ASC

La deuxième, qui je pense correspond plus à te besoin, c'est de récupérer seulement une valeur par heure, mais la plus proche possible de chaque "heure". Là, on va utiliser MIN() sur la date, de façon à récupérer la date la plus proche de chaque heure :

SELECT SENSOR_TYPE.name, TEMP.sensor_date, SENSOR_VALUE.sensor_value
FROM (
  SELECT id_sensor, MIN(sensor_date) as sensor_date
  FROM SENSOR_VALUE
  WHERE sensor_date BETWEEN '2018-06-14' AND '2018-06-15'
  GROUP BY id_sensor, DATE(sensor_date), HOUR(sensor_date)
) as TEMP
  INNER JOIN SENSOR_VALUE
    ON SENSOR_VALUE.sensor_date = TEMP.sensor_date AND SENSOR_VALUE.id_sensor = TEMP.id_sensor
  INNER JOIN SENSOR_TYPE
    ON TEMP.id_sensor = SENSOR_TYPE.id
ORDER BY SENSOR_VALUE.id_sensor, SENSOR_VALUE.sensor_date ASC

Pour avoir la même chose par demi-heure, il faut rajouter un GROUP BY sur la valeur entière du résultat de minutes / 30 :

SELECT name, TEMP.sensor_date, SENSOR_VALUE.sensor_value
FROM (
  SELECT id_sensor, MIN(sensor_date) as sensor_date
  FROM SENSOR_VALUE
  WHERE sensor_date BETWEEN '2018-06-14' AND '2018-06-15'
  GROUP BY id_sensor, DATE(sensor_date), HOUR(sensor_date), FLOOR(MINUTE(sensor_date) / 30)
) as TEMP
   INNER JOIN SENSOR_VALUE
    ON SENSOR_VALUE.sensor_date = TEMP.sensor_date AND SENSOR_VALUE.id_sensor = TEMP.id_sensor
  INNER JOIN SENSOR_TYPE
    ON SENSOR_TYPE.id = TEMP.id_sensor
ORDER BY SENSOR_TYPE.id, TEMP.sensor_date ASC

-
Edité par Sebajuste 14 juin 2018 à 14:10:45