La façon la plus efficace de déterminer les modifications entre les lignes à l'aide de SQL

J'ai une très grande table MySQL contenant les données lues à partir d'un certain nombre de capteurs. Essentiellement, il y a un timbre et une valeur de la colonne. Je vais omettre l'id du capteur, d'autres indices plus de détails ici:

CREATE TABLE `data` (
  `time` datetime NOT NULL,
  `value` float NOT NULL
)

La value colonne change rarement, et j'ai besoin de trouver les points dans le temps lorsque ces changements se produisent. Supposons qu'il y a une valeur à chaque minute, la requête suivante renvoie exactement ce dont j'ai besoin:

SELECT d.*, 
  (SELECT value FROM data WHERE time<d.time ORDER by time DESC limit 1) 
    AS previous_value 
FROM data d 
HAVING d.value<>previous_value OR previous_value IS NULL;

+---------------------+-------+----------------+
| time                | value | previous_value |
+---------------------+-------+----------------+
| 2011-05-23 16:05:00 |     1 |           NULL |
| 2011-05-23 16:09:00 |     2 |              1 |
| 2011-05-23 16:11:00 |   2.5 |              2 |
+---------------------+-------+----------------+

Le seul problème est que c'est très inefficace, en raison principalement de la charge de la sous-requête. Quelle serait la meilleure façon d'optimiser, ce en utilisant les outils MySQL 5.1 a à offrir?

Une dernière contrainte est que les valeurs ne sont pas commandés avant qu'ils sont insérés dans la table de données et qu'ils pourraient être mis à jour plus tard. Cela peut affecter toute possibilité de normalisation des stratégies.

Quels indices sont là, dans le tableau?
Note: c'est une mauvaise habitude d'avoir des tables ou des champs nommés time ou datetime ou date ou float, etc.
Hormis les touches sur les colonnes qui n'apparaissent pas dans cet exemple (comme une synthèse de la clé primaire), il y a une clé unique sur la colonne du temps.
Merci pour l'astuce, mais ce n'est pas mon code. Je l'ai raccourcie et condensée pour faire de ma question de façon plus concise. Je pourrais avoir trop fait avec le respect de la colonne du temps. 🙂

OriginalL'auteur cg. | 2011-05-24

3

Vous pouvez essayer ce - que je ne vais pas garantir qu'il fonctionnera mieux, mais c'est ma façon habituelle de corréler une ligne avec un "précédent" de ligne:
```
SELECT
    * --TODO, list columns
FROM
    data d
       left join
    data d_prev
       on
           d_prev.time < d.time --TODO - Other key columns?
       left join
    data d_inter
       on
           d_inter.time < d.time and
           d_prev.time < d_inter.time --TODO - Other key columns?
WHERE
    d_inter.time is null AND
    (d_prev.value is null OR d_prev.value <> d.value)
```
(Je pense que c'est de droite pourrait faire avec quelques exemples de données pour la valider).

En gros, l'idée est de rejoindre la table pour lui-même, et pour chaque ligne (dans d), de trouver des candidats lignes (dans d_prev) pour le "précédent" de ligne. Puis faire un plus de le rejoindre, pour essayer de trouver une ligne (dans d_inter) qui existe entre la ligne actuelle (en d) et le candidat à la ligne (dans d_prev). Si nous ne pouvons pas trouver une telle ligne (d_inter.time is null), puis que le candidat a été en effet la rangée précédente.

Super! C'est effectivement le genre de "truc" que je cherchais. Vous de requête est beaucoup plus rapide que l'originale. Il n'est pas encore assez rapide pour être utilisé directement, mais il pourrait être la base de l'agrégation de données dont j'ai besoin. Merci beaucoup pour votre réponse.
Je vais voter jusqu'à maintenant et de l'accepter dans quelques jours si pas de meilleure solution.
Je pense que vous pouvez aussi techniquement besoin OU d.une valeur null dans ce dernier entre crochets déclaration de la clause where.
à partir de la question - value float NOT NULL. On ne fait que générer des NULLs dans les colonnes de droite de LEFT JOINs. Mais d est sur le côté gauche de ces jointures. Par conséquent, il est value ne peut jamais être NULL.

OriginalL'auteur Damien_The_Unbeliever
3

Je suppose que ce n'est pas une option vous permettant de basculer le moteur DB. Dans le cas où il pourrait être, alors les fonctions de la fenêtre vous permettra d'écrire des choses comme ceci:
```
SELECT d.*
FROM (
    SELECT d.*, lag(d.value) OVER (ORDER BY d.time) as previous_value 
    FROM data d
  ) as d
WHERE d.value IS DISTINCT FROM d.previous_value;
```
Si non, vous pouvez essayer de réécrire la requête comme ceci:
```
select data.*
from data
left join (
    select data.measure_id,
           data.time,
           max(prev_data) as prev_time
    from data
    left join data as prev_data
    on prev_data.time < data.time
    group by data.measure_id, data.time, data.value
    ) as prev_data_time
on prev_data_time.measure_id = data.measure_id
and prev_data_time.time = data.time
left join prev_data_value
on prev_data_value.measure_id = data.measure_id
and prev_data_value.time = prev_data_time.prev_time
where data.value <> prev_data_value.value or prev_data_value.value is null
```
notez que group by déjà commander les éléments énumérés dans celui-ci, de sorte que le dernier order by .. n'est pas nécessaire.
Vrai, mais que la commande est mise en œuvre des effets secondaires, plutôt que la norme SQL. Vous ne savez jamais quand MySQL va réduire les effets secondaires (Oracle). 🙂
Vous pouvez également expérimenter avec des index sur (value,time) ou (sensor_id,value,time) et voir le plan de requête à l'aide de cet indice.
Merci beaucoup pour votre temps! Pourriez-vous expliquer la colonne measure_id dans votre exemple? Est censé être la clé primaire de la table de données ou d'une clé étrangère?
la clé primaire de la table de données.

OriginalL'auteur Denis de Bernardy

Vous devez vous connecter pour publier un commentaire.