问题描述
我在 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中的值并识别趋势的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!