使用MySQL计算时间轴中的更改 [英] Counting changes in timeline with MySQL

问题描述

我是MySQL的新手，需要您的帮助.我有一张数据相似的表

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      |                 
---------------------------------------------------

如您所见，RobotShortestPath列中有重复的值，但是它们很重要.每个数字代表一个特定的任务.如果数字连续重复(例如:1456)，则表示机器人正在执行该任务，而当数字更改(例如:1234)时，则表示它已切换到另一个任务.并且，如果先前的数字(ex:1456)再次出现，则也意味着机器人在完成较早的任务(1234)之后正在执行新任务(1456).

As you can see there are repeated values in the column RobotShortestPath, But they are important. Each number represent a specific task. If the number repeats consecutively(ex: 1456), it means that Robot is performing that task, and when the number changes(ex: 1234) it means that it has switched to another task. And if the previous number(ex:1456) appears again it also means that robot is performing a new task(1456) after done with earlier task(1234).

因此，我无法自拔的是，我无法执行任何任务.我已经使用了至少COUNT，GROUP BY等最低知识，但似乎没有任何效果.

So where I am stuck is I am unable to get no of tasks performed. I have used several things from my minimum knowledge like COUNT, GROUP BY but nothing seem to work.

这里执行的任务实际上是5个，但无论如何我只能得到3个.

Here the no.of tasks performed are 5 actually, but whatever I do I get only 3 as result.

推荐答案

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 a database strcture normailization view, your better of with one table, and have a filed identifing what column is what robot, if that not posible for some reason, you can get that by union 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

这篇关于使用MySQL计算时间轴中的更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！