基于时间戳比较mysql中的值并识别趋势 [英] Comparing values in mysql based on timestamp and identify trends

问题描述

我在 mysql 数据库中有一个表，如下所示

Hi I have a table in mysql database like below

id_indicator value trend date_data
           1     0     0 2011-08-18 09:16:15
           1     2     1 2011-08-18 10:16:15
           1     1    -1 2011-08-18 11:16:15
           1     2     1 2011-08-18 12:16:15
           2    21     0 2011-08-18 13:16:15
           2    21     0 2011-08-18 14:16:15
           2    21     0 2011-08-18 15:16:15
           3     3     0 2011-08-18 16:16:15
           3     4     1 2011-08-18 17:16:15
           3     4     0 2011-08-18 18:16:15
           4     4     0 2011-08-18 19:16:15

该表包含基于时间的指标及其值以及基于值的另一列趋势.最初所有趋势为 0，然后如果下一个指标值上升则趋势应为 1，如果值下降则趋势应为 -1，如果值不变则趋势应为 0.

The table contain indicators and its values based on time and another column trend which is based on values. Initially all the trend is 0 then if next indicator value goes up than trend should be 1, if values goes down than trend should be -1, if value unchanged than trend should be 0.

但我发现某些趋势值没有正确输入表格中.现在我需要帮助来确定有多少趋势值被错误插入并按指标值分组.

But I have figured out that some of the trend values are not correctly entered into the table. Now I need help to identify how many trend value are inserted wrong also count group by indicator value.

非常感谢您的帮助

谢谢

推荐答案

您可以使用相关子查询来查找上一个值.

You could use a correlated subquery to lookup the previous value.

SELECT
     *,
     (SELECT lookup.value
        FROM yourTable  AS lookup
       WHERE lookup.id_indicator = yourTable.id_indicator
         AND lookup.date_data < yourTable.date_data
    ORDER BY lookup.date_data DESC
       LIMIT 1
     )   AS previous_value
FROM
    yourTable

这篇关于基于时间戳比较mysql中的值并识别趋势的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！