CODEWITHSUNDEEP
mysqlsql
Eduardo Matos
Eduardo Matos

Reputation: 741

Query changes in MySQL records

I have a table like this:

id | status | user_id | created_at
---:--------:---------:--------------------
1  |    0   |     1   | 2014-01-05 07:23:15
2  |    1   |     1   | 2014-01-05 07:23:16
3  |    1   |     1   | 2014-01-05 07:23:17
4  |    0   |     1   | 2014-01-05 07:23:18
5  |    0   |     1   | 2014-01-05 07:23:19
6  |    1   |     1   | 2014-01-05 07:23:20
7  |    0   |     2   | 2014-01-05 07:23:21
8  |    0   |     1   | 2014-01-05 07:23:22
9  |    0   |     2   | 2014-01-05 07:23:23
10 |    1   |     2   | 2014-01-05 07:23:24
11 |    0   |     2   | 2014-01-05 07:23:25
12 |    1   |     2   | 2014-01-05 07:23:26

I'd like to query the changes on the status field, grouped by user_id, always fetching the last status (based on created_at). The result of the query should be something like this:

id | status | user_id | created_at
---:--------:---------:--------------------
1  |    0   |     1   | 2014-01-05 07:23:15
3  |    1   |     1   | 2014-01-05 07:23:17
5  |    0   |     1   | 2014-01-05 07:23:19
6  |    1   |     1   | 2014-01-05 07:23:20
8  |    0   |     1   | 2014-01-05 07:23:22
9  |    0   |     2   | 2014-01-05 07:23:23
10 |    1   |     2   | 2014-01-05 07:23:24
11 |    0   |     2   | 2014-01-05 07:23:25
12 |    1   |     2   | 2014-01-05 07:23:26

Is there a way to query for changes in SQL in a situation like this? How this query should be written?

Upvotes: 1

Views: 56

Answers (2)

Geordee Naliyath
Geordee Naliyath

Reputation: 1859

Probably it is a good idea to use variables in MySQL in such scenarios.

Here is one quick attempt with steps elaborated. Clean and tweak it up to suit the requirements and performance.

select id, status, user_id, created_at from
 (select id, status, user_id, created_at,
         (case when @user_id != user_id then 'true' else 'false' end) as user_changed,
         (case when @status  != status then 'true' else 'false' end) as status_changed,
         (case when @user_id != user_id then @user_id := user_id end) as new_user_id,
         (case when @status  != status then @status := status end) as new_status
  from (select * from logs order by user_id asc, created_at desc) l
   join (select @user_id := 0) u
   join (select @status := 0) s) q
where user_changed = 'true' or status_changed = 'true'
order by id
;

Upvotes: 1

Strawberry
Strawberry

Reputation: 33935

Consider the following...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,status TINYINT NOT NULL DEFAULT 1
,user_id INT NOT NULL
,created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO my_table VALUES
(1  ,   0   ,    1   ,'2014-01-05 07:23:15'),
(2  ,   1   ,    1   ,'2014-01-05 07:23:16'),
(3  ,   1   ,    1   ,'2014-01-05 07:23:17'),
(4  ,   0   ,    1   ,'2014-01-05 07:23:18'),
(5  ,   0   ,    1   ,'2014-01-05 07:23:19'),
(6  ,   1   ,    1   ,'2014-01-05 07:23:20'),
(7  ,   0   ,    2   ,'2014-01-05 07:23:21'),
(8  ,   0   ,    1   ,'2014-01-05 07:23:22'),
(9  ,   0   ,    2   ,'2014-01-05 07:23:23'),
(10 ,   1   ,    2   ,'2014-01-05 07:23:24'),
(11 ,   0   ,    2   ,'2014-01-05 07:23:25'),
(12 ,   1   ,    2   ,'2014-01-05 07:23:26');

For the solution provided below it actually doesn't matter that the id is contiguous, just that it's sequential. I've broken the solution down into bits so you can see what it's doing...

The first part ranks results by user...

SELECT x.* 
     , COUNT(*) rank
  FROM my_table x
  JOIN my_table y
    ON y.user_id = x.user_id
   AND y.id <= x.id
 GROUP
    BY x.id
 ORDER
    BY x.user_id,rank;

  +----+--------+---------+---------------------+------+
  | id | status | user_id | created_at          | rank |
  +----+--------+---------+---------------------+------+
  |  1 |      0 |       1 | 2014-01-05 07:23:15 |    1 |
  |  2 |      1 |       1 | 2014-01-05 07:23:16 |    2 |
  |  3 |      1 |       1 | 2014-01-05 07:23:17 |    3 |
  |  4 |      0 |       1 | 2014-01-05 07:23:18 |    4 |
  |  5 |      0 |       1 | 2014-01-05 07:23:19 |    5 |
  |  6 |      1 |       1 | 2014-01-05 07:23:20 |    6 |
  |  8 |      0 |       1 | 2014-01-05 07:23:22 |    7 |
  |  7 |      0 |       2 | 2014-01-05 07:23:21 |    1 |
  |  9 |      0 |       2 | 2014-01-05 07:23:23 |    2 |
  | 10 |      1 |       2 | 2014-01-05 07:23:24 |    3 |
  | 11 |      0 |       2 | 2014-01-05 07:23:25 |    4 |
  | 12 |      1 |       2 | 2014-01-05 07:23:26 |    5 |
  +----+--------+---------+---------------------+------+

The second part joins this query to itself, and highlights anomalies...

SELECT a.*
     , b.id
  FROM 
     ( SELECT x.* 
            , COUNT(*) rank
         FROM my_table x
         JOIN my_table y
           ON y.user_id = x.user_id
          AND y.id <= x.id
        GROUP
           BY x.id
     ) a
 LEFT
 JOIN
     ( SELECT x.* 
              , COUNT(*) rank
           FROM my_table x
           JOIN my_table y
             ON y.user_id = x.user_id
            AND y.id <= x.id
          GROUP
             BY x.id
     ) b
    ON b.user_id = a.user_id
   AND b.status  = a.status
   AND b.rank    = a.rank + 1;


    +----+--------+---------+---------------------+------+------+
    | id | status | user_id | created_at          | rank | id   |
    +----+--------+---------+---------------------+------+------+
    |  1 |      0 |       1 | 2014-01-05 07:23:15 |    1 | NULL |
    |  2 |      1 |       1 | 2014-01-05 07:23:16 |    2 |    3 |
    |  3 |      1 |       1 | 2014-01-05 07:23:17 |    3 | NULL |
    |  4 |      0 |       1 | 2014-01-05 07:23:18 |    4 |    5 |
    |  5 |      0 |       1 | 2014-01-05 07:23:19 |    5 | NULL |
    |  6 |      1 |       1 | 2014-01-05 07:23:20 |    6 | NULL |
    |  7 |      0 |       2 | 2014-01-05 07:23:21 |    1 |    9 |
    |  8 |      0 |       1 | 2014-01-05 07:23:22 |    7 | NULL |
    |  9 |      0 |       2 | 2014-01-05 07:23:23 |    2 | NULL |
    | 10 |      1 |       2 | 2014-01-05 07:23:24 |    3 | NULL |
    | 11 |      0 |       2 | 2014-01-05 07:23:25 |    4 | NULL |
    | 12 |      1 |       2 | 2014-01-05 07:23:26 |    5 | NULL |
    +----+--------+---------+---------------------+------+------+

The third and final part is deliberately left as an exercise for the reader, however, one drawback with this solution is that it does not scale particularly well.

Upvotes: 2

Related Questions