CODEWITHSUNDEEP
mysqlsql
karephul
karephul

Reputation: 1483

Detect when data changed

Here is my data structure :

name          value          date_received
foo           100            2013-09-19 10:00:00
bar           200            2013-09-19 10:00:00
foo           100            2013-09-19 10:05:00  //no change
bar           200            2013-09-19 10:05:00  //no change
foo           110            2013-09-19 10:08:00  // foo changed          
bar           200            2013-09-19 10:08:00  // no change
......

Question:
I want a query (mysql) which can do something like:

select date_received where anyOf(foo, bar) changed from the previous 
specified value in the past N hours.

There could be other names in the table but we are only interested in foo and bar.

Any pointers. To me it looks like we'll need a self join - but don't know how.

EDIT: looks like the below query is just a good starting point.

select date_received from (SELECT DISTINCT name, value from data) a 
  INNER JOIN (select DISTINCT name, value, date_received from data)b 
    on (a.name=b.name and a.value=b.value)

update Looks like below query works - easier than I thought it would be.

SELECT DISTINCT a.tr FROM (
   SELECT name, value, MAX(date_received) dr from data 
    where date_received > now() - INTERVAL 2 hour 
    GROUP BY name, value order by dr desc)a;

Upvotes: 2

Views: 257

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

I do not see how your edited query solves the problem. Where does the "last N hours" come in, for instance?

I would approach this by looking at the previous value, then using logic around the datetime constraints and value changes to see if there has been a change. Your question is ambiguous: Are you looking for changes only in the last N hours? Are you looking for a change from the last value before N hours? What happens if the value changes back?

All of these, though, could be answered by having the previous value and previous time on each row. Here is an example of how to get this:

select t.*,
       (select t.date_received
        from t t2
        where t2.date_received < t.date_received and
              t2.name = t.name
        order by t2.date_received desc
        limit 1
       ) as prev_date_received,
       (select t.value
        from t t2
        where t2.date_received < t.date_received and
              t2.name = t.name
        order by t2.date_received desc
        limit 1
       ) as prev_value
from t
having <your logic goes here for the date time and changes you care about>;

This is using the having clause instead of a subquery, just out of convenience (this is not supported by other databases).

For instance, if you want any changes in the last N hours:

having date_received > now() - interval N hour and prev_value <> value

Upvotes: 1

Related Questions