Comparing values in mysql based on timestamp and identify trends

Question

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

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.

I would be much appreciate for your help

Thanks

Answer 1

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