Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
416 views
in Technique[技术] by (71.8m points)

Comparing values in mysql based on timestamp and identify trends

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...