开发者

Detecting changes within a dataset in mysql

I have a table that has time-ordered values:

id
date
value

value is often the same for hundreds of records at a strech, and I'd like to be able to determine when value changes. In other words, I would like to know when d/dx (the derivative of the data) does not equal 0!

It seems like there should be a fairly common pattern but I can't find any examples or come up with one myself. I did find an example where this change detection was done, but I can't use it because my database adapter pools connections and queries are not necessarily issued on the same connection. Similarly, I'd rather not use a database trigger.

Here's an example table:

id | date             | value
1  | 2011-04-05 12:00 | 33
2  | 2011-04-06 12:00 | 39
3  | 2011-04-07 12:00 | 39
...
72 | 2011-05-16 12:00 | 39
73 | 2011-05-17 12:00 | 37
74 | 2011-05-18 12:00 | 33
75 | 2011-05-19 12:00 | 33
...

I'm looking for a query that could pull back the rows where the values change:

id | date             | value
1  | 2011-04-05 12:00 | 33
2  | 2011-04-06 12:00 | 39
73 | 2011-05-17 12:00 | 37
74 | 2011-05-18 12:00 | 33

Its not necessary that the first row to be included in the开发者_如何学Python result, since a table with identical values would return zero rows (i.e. there are no changes).


SELECT t.id, t.date, t.value, if( (

SELECT td.value AS last_value
FROM tab td
WHERE td.date < t.date
ORDER BY td.date DESC
LIMIT 1
) <> t.value, 'changed', 'unchanged' ) AS cstatus
FROM tab t order by date

Not a very efficient query, slow on large sets, but does the trick. Adding a counter lets say num_repeated as another column, updated on insert would be a better solution.


uh, use a group by?

select min(id), min(date), value
from ...
group by value
order by id asc 
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜