Counting changes in history using MySQL

Question

I am new to MySQL and I need your help. I have a table with similar data

--------------------------------------------------- |RobotPosX|RobotPosY|RobotPosDir|RobotShortestPath| --------------------------------------------------- |0.1 | 0.2 | 15 | 1456 | |0.2 | 0.3 | 30 | 1456 | |0.54 | 0.67 | 15 | 1456 | |0.68 | 0.98 | 22 | 1234 | |0.36 | 0.65 | 45 | 1234 | |0.65 | 0.57 | 68 | 1456 | |0.65 | 0.57 | 68 | 2556 | |0.79 | 0.86 | 90 | 1456 | --------------------------------------------------- 

As you can see, the values ​​in the RobotShortestPath column are repeated, but they are important. Each number represents a specific task. If the number is repeated sequentially (for example: 1456), this means that the robot performs this task, and when the number is changed (for example: 1234), this means that it switched to another task. And if the previous number (for example: 1456) appears again, this also means that the robot performs a new task (1456) after completing the previous task (1234).

So, when I'm stuck, I can't complete any tasks. I used a few things from my minimum knowledge, such as COUNT, GROUP BY, but nothing works.

No.of tasks 5 are executed here, but whatever I do, I get only 3. As a result.

Answer 1

 SET @last_task = 0; SELECT SUM(new_task) AS tasks_performed FROM ( SELECT IF(@last_task = RobotShortestPath, 0, 1) AS new_task, @last_task := RobotShortestPath FROM table ORDER BY ?? ) AS tmp 

Update for multiple tables
From the idea of ​​normalizing the database structure, it is best with a single table, and you have an identification of which column is that robot, if for some reason this is not possible, you can get this by joining the tables:

 SET @last_task = 0; SELECT robot_id, SUM(new_task) AS tasks_performed FROM ( SELECT IF(@last_task = RobotShortestPath, 0, 1) AS new_task, @last_task := RobotShortestPath FROM ( SELECT 1 AS robot_id, robot_log_1.* FROM robot_log_1 UNION SELECT 2, robot_log_2.* FROM robot_log_2 UNION SELECT 3, robot_log_3.* FROM robot_log_3 UNION SELECT 4, robot_log_4.* FROM robot_log_4 UNION SELECT 5, robot_log_5.* FROM robot_log_5 ) as robot_log ORDER BY robot_id, robot_log_id ) AS robot_log_history GROUP BY robot_id ORDER BY tasks_performed DESC 

Answer 2

As I understand it, you need to keep track of when RobotShortestPath changed to a different value. For this you can use trigger as follows:

 delimiter | CREATE TRIGGER track AFTER UPDATE ON yourtable FOR EACH ROW BEGIN IF NEW.RobotShortestPath != OLD.RobotShortestPath THEN UPDATE tracktable SET counter=counter+1 WHERE tracker=1; END IF; END; | delimeter ; 

Answer 3

 set @n:=0, @i:=0; select max(sno) from ( select @n:=case when @i=RobotShortestPath then @n else @n+1 end as sno, @i:=RobotShortestPath as dno from table ) as t; 

Answer 4

Try the following query:

 SET @cnt = 0, @r = -1; SELECT IF(armed <> @r, @cnt:= @cnt + 1, 0), @r:= RobotShortestPath, @cnt FROM table; SELECT @cnt AS count;