mysql - 在新表中汇总数据

Question

白天数据放入status_table的问题如下(一天最多可以达到4000条

晚上我想把数据以更有条理的方式放在存储表中(status_table_storage) 并将其从 status_table 中删除。这个想法是将所有具有相同状态的条目分组，直到状态/错误发生变化。从那时起，一个新的小组应该开始并被放入表格中。可以在 fiddle 中找到一个例子

执行此操作的最佳方法是什么。

可以在此处找到表结构的示例:http://sqlfiddle.com/#!9/488524/1

Answer 1

一步一步的解释:

首先，您按名称和时间戳对表进行排序，并初始化三个 user-defined variables .

SELECT s.* FROM status_table s
, (SELECT @group_number := 0, @prevName := NULL, @prevStatus := NULL) var_init_subquery
ORDER BY name, timestamp

如您所见，我们可以为此使用子查询。 ORDER BY 很重要，因为关系数据库中没有顺序，除非您指定它。

现在，MySQL 以指定的顺序计算 SELECT 子句，因此不要更改此处的顺序。

SELECT 
s.*,
@prevName,
@prevStatus,
@prevName := s.name,
@prevStatus := s.status
FROM status_table s
, (SELECT @group_number := 0, @prevName := NULL, @prevStatus := NULL) var_init_subquery
ORDER BY name, timestamp

当您执行此语句时，您可以看到，当我们简单地选择变量时，它们保存前一行的值，或者当它是读取的第一行时为 NULL。然后将当前行的值分配给变量。所以我们现在可以将当前行与上一行进行比较。如果发生变化，我们只需增加第三个变量，它是我们正在构建的每个“组”的数字。

SELECT 
s.*,
@group_number := IF(@prevName != s.name OR @prevStatus != s.status, @group_number + 1, @group_number) AS group_number,
@prevName := s.name,
@prevStatus := s.status
FROM status_table s
, (SELECT @group_number := 0, @prevName := NULL, @prevStatus := NULL) var_init_subquery
ORDER BY name, timestamp

因此，我们在发生变化时递增 @group_number，如果没有发生变化，则将变量分配给自身，这样它就不会发生变化。

现在我们可以简单地将此查询用作子查询并进行简单的分组。

SELECT 
group_number AS id, 
name, 
status, 
MIN(error) AS error, 
MIN(timestamp) AS firstEntry,
MAX(timestamp) AS lastEntry,
COUNT(*) AS entries
FROM (
    SELECT 
    s.*,
    @group_number := IF(@prevName != s.name OR @prevStatus != s.status, @group_number + 1, @group_number) AS group_number,
    @prevName := s.name,
    @prevStatus := s.status
    FROM status_table s
    , (SELECT @group_number := 0, @prevName := NULL, @prevStatus := NULL) var_init_subquery
    ORDER BY name, timestamp
) sq
GROUP BY 
group_number, 
name, 
status

• 看到它在这个 sqlfiddle 中工作