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
精彩评论