汇总新表中的数据 [英] Summarize data in a new table

问题描述

在将数据放入status_table的一天中，问题如下(每天最多可以输入4000个条目

The problem is as follows during the day data is put into status_table (this can reach up to 4000 entries a day

在夜间，我想以更有条理的方式将数据放入存储表中 (status_table_storage)并将其从status_table中删除. 想法是将状态相同的所有条目归为一组，直到状态/错误更改为止.从这一点开始，应该在表中放置一个新组.可以在小提琴中找到一个例子

During the night i want to put the data in a more organized way in a storage table (status_table_storage) and remove it from the status_table. The idea is to group all the entries that have the same status until the status/error changes. from that point on a new group should start an be placed into the table. an example can be found in the fiddle

什么是最好的方法?

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

an example of the table structure can be found here: http://sqlfiddle.com/#!9/488524/1

推荐答案

分步说明:

首先，您通过名称和时间戳对表进行排序，并初始化三个用户定义的变量.

First you order the table by name and timestamp and initialize three 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很重要，因为关系数据库中没有顺序，除非您指定它.

As you can see, we can use a subquery for that. The ORDER BY is important, because there's no order in a relational database, unless you specify it.

现在，MySQL按照指定的顺序评估SELECT子句，因此在此无需更改顺序.

Now, MySQL evaluates the SELECT clause in the specified order, therefore don't change the order here.

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.然后，将当前行的值分配给变量.因此，我们现在可以将当前行与上一行进行比较.如果发生了变化，我们只需增加第三个变量，该变量是我们正在构建的每个组"的数字.

When you execute this statement, you can see, that when we simply select the variables they hold the value of the previous row or NULL when it's the first row, that was read. Then the value of the current row is assigned to the variables. So we can compare now the current row with the previous row. If something changed, we simply increment the third variable, which is a number for each "group" that we are building.

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，如果没有更改，则将变量分配给自身，这样它就不会更改.

So we incremented the @group_number when something changed and assigned the variable to itself if not, so that it doesn't change.

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

Now we can simply use this query as subquery and do a simple grouping.

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
中正常工作
• see it working in this sqlfiddle

这篇关于汇总新表中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！