MySQL table data transformation -- how can I dis-aggregate MySQL time data?

Question

We are coding for a MySQL data warehousing application that stores descriptive data (User ID, Work ID, Machine ID, Start and End Time columns in the first table below) associated with time and production quantity data (Output and Time columns in the first table below) upon which aggregate (SUM, COUNT, AVG) functions are applied. We now wish to dis-aggregate time data for another type of analysis.

Our current data table design:

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

Reprocessing dis-aggregation that we would like to do would be to transform table content based on a granularity of minutes, rather than the current production event ("Event Start Time" and "Event End Time") granularity. The resulting reprocessing of existing table rows would look like:

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

So the reprocessing would take an existing row of data created at the granularity of production event and modify the granularity to minutes, eliminating redundant (Event End Time, Time) columns while doing so. It assumes a constant rate of production and divides output by the difference in minutes plus one to populate the new table's Output column.

I know this can be done in code...but can it be done entirely in a MySQL insert statement (or otherwise entirely in MySQL)? I am thinking of a INSERT ... INTO construction but keep getting stuck. An additional complexity is that there are hundreds of machines to include in the operation so there will be multiple rows (one for each machine) for each minute of the day.

Any ideas would be much appreciated. Thanks.

Answer 1

You can create a table containing a row for every minute from the start of your dataset to the end, and run joins against that:

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;

Populating the prod_minute table can be fun:

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;