How to query the number of column changes in MySQL

Question

I have a table that stores items with two properties. Thus, the table has three columns:

item_id | property_1 | property_2 | insert_time 1 | 10 | 100 | 2012-08-24 00:00:01 1 | 11 | 100 | 2012-08-24 00:00:02 1 | 11 | 101 | 2012-08-24 00:00:03 2 | 20 | 200 | 2012-08-24 00:00:04 2 | 20 | 201 | 2012-08-24 00:00:05 2 | 20 | 200 | 2012-08-24 00:00:06 

That is, every time a property of any element changes, a new line is inserted. There is also a column storing insert time. Now I want to get the number of changes in property_2 . For the table above, I should get

 item_id | changes_in_property_2 1 | 2 2 | 3 

How can i get this?

Answer 1

This will tell you how many different values ​​have been entered. If it were changed to its previous value, it would not be considered a new change. Without a timeline for your data, it’s hard to do much more.

 select item_id, count(distinct property_2) from Table1 group by item_id 

Answer 2

Here is the closest that I could achieve the desired result. However, I should note that you are requesting the number of changes to property_2 based on item_id . If you analyze strictly these two columns, then for item_id 1 and 2 changes for item_id 2 there is only 1 change. You will need to increase your result to the aggregate by property_1 . Hope this fiddle will show you why.

 SELECT a.item_id, SUM( CASE WHEN a.property_2 <> (SELECT property_2 FROM tbl b WHERE b.item_id = a.item_id AND b.insert_time > a.insert_time LIMIT 1) THEN 1 ELSE 0 END) AS changes_in_property_2 FROM tbl a GROUP BY a.item_id 

Answer 3

My welcome:

 SELECT i.item_id, SUM(CASE WHEN i.property_1 != p.property_1 THEN 1 ELSE 0 END) + 1 AS changes_1, SUM(CASE WHEN i.property_2 != p.property_2 THEN 1 ELSE 0 END) + 1 AS changes_2 FROM items i LEFT JOIN items p ON p.time = (SELECT MAX(q.insert_time) FROM items q WHERE q.insert_time < i.insert_time AND i.item_id = q.item_id) GROUP BY i.item_id; 

There is one entry for each item that is not selected in i , which does not have a predecessor. He expects a change, however, why the amounts are increasing.

Answer 4

I would do it this way with custom variables to keep track of the previous value of the string.

 SELECT item_id, MAX(c) AS changes_in_property_2 FROM ( SELECT IF(@i = item_id, IF(@p = property_2, @c, @c: =@c +1), @c:=1) AS c, (@i:=item_id) AS item_id, (@p:=property_2) FROM `no_one_names_their_table_in_sql_questions` AS t, (SELECT @i:=0, @p:=0) AS _init ORDER BY insert_time ) AS sub GROUP BY item_id;