sql - MySQL 表数据转换——如何分解 MySQL 时间数据？

Question

我们正在编写一个 MySQL 数据仓库应用程序，该应用程序存储与时间和生产数量数据(下面第一个表中的输出和时间列)相关的描述性数据(下面第一个表中的用户 ID、工作 ID、机器 ID、开始和结束时间列)。下面的第一个表)应用了聚合(SUM、COUNT、AVG)函数。我们现在希望分解时间数据以进行另一种类型的分析。

我们当前的数据表设计:

+---------+---------+------------+---------------------+---------------------+--------+------+
| User ID | Work ID | Machine ID | Event Start Time    | Event End Time      | Output | Time |
+---------+---------+------------+---------------------+---------------------+--------+------+
| 080025  | ABC123  | M01        | 2008-01-24 16:19:15 | 2008-01-24 16:34:45 |   2120 |  930 | 
+---------+---------+------------+---------------------+---------------------+--------+------+  

我们想要进行的重新处理分解是根据分钟粒度而不是当前生产事件(“事件开始时间”和“事件结束时间”)粒度来转换表内容。对现有表行的重新处理结果如下所示:

+---------+---------+------------+---------------------+--------+  
| User ID | Work ID | Machine ID | Production Minute   | Output |
+---------+---------+------------+---------------------+--------+
| 080025  | ABC123  | M01        | 2010-01-24 16:19    |    133 |
| 080025  | ABC123  | M01        | 2010-01-24 16:20    |    133 |       
| 080025  | ABC123  | M01        | 2010-01-24 16:21    |    133 |       
| 080025  | ABC123  | M01        | 2010-01-24 16:22    |    133 |       
| 080025  | ABC123  | M01        | 2010-01-24 16:23    |    133 |       
| 080025  | ABC123  | M01        | 2010-01-24 16:24    |    133 |       
| 080025  | ABC123  | M01        | 2010-01-24 16:25    |    133 |       
| 080025  | ABC123  | M01        | 2010-01-24 16:26    |    133 |
| 080025  | ABC123  | M01        | 2010-01-24 16:27    |    133 |       
| 080025  | ABC123  | M01        | 2010-01-24 16:28    |    133 |       
| 080025  | ABC123  | M01        | 2010-01-24 16:29    |    133 |       
| 080025  | ABC123  | M01        | 2010-01-24 16:30    |    133 |       
| 080025  | ABC123  | M01        | 2010-01-24 16:31    |    133 |       
| 080025  | ABC123  | M01        | 2010-01-24 16:22    |    133 |       
| 080025  | ABC123  | M01        | 2010-01-24 16:33    |    133 |       
| 080025  | ABC123  | M01        | 2010-01-24 16:34    |    133 |       
+---------+---------+------------+---------------------+--------+  

因此，重新处理将获取以生产事件粒度创建的现有数据行，并将粒度修改为分钟，从而消除冗余(事件结束时间、时间)列。它假定生产率恒定，并将输出除以分钟差加一以填充新表的输出列。

我知道这可以在代码中完成...但是可以完全在 MySQL 插入语句中完成(或者完全在 MySQL 中完成)吗？我正在考虑 INSERT ... INTO 结构，但一直陷入困境。另一个复杂性是，操作中需要包含数百台机器，因此一天中的每一分钟都会有多行(每台机器一行)。

任何想法将不胜感激。谢谢。

Answer 1

您可以创建一个表，其中包含从数据集开始到结束的每分钟一行，并针对该表运行联接:

select user_id, work_id, machine_id, production_minute, output
from prod_event p
join prod_minute m on p.start <= m.production_minute and m.production_minute <= p.end;

填充 prod_minute 表可能很有趣:

create table counter ( i int not null auto_increment primary key );
insert into counter values ( 0 );
insert into counter select NULL from counter;
# ...  repeat until your counter table contains enough minutes

create table prod_minute ( production_minute datetime not null primary key );
insert into prod_minute select date_add( '2000-01-01', interval i minute ) from counter;