Mysql script to roll up individual incoming results into another table.

There is a continuous process writing raw results into our table. The base concept of the service is two audio streams are being compared. There is a stream (radio station) that is matched against tracks. The raw results will have: - stream_id (int) - track_id (int) - client_id (int) - stream_match_time (datetime) - track_match_time (datetime) Each individual result represents 2 seconds of time. The end goal of the edited results is to provide the following: - external_client_id (int) - stream_id (int) - track_id (int) -stream_match_time_start to stream_match_time_end -track_match_time_start to track_match_time_end -total_actual_duration There are the rules to apply: 1. rolled up results are unique to external_client_id and track_id 2. rolled up results are based off of time_delta in seconds which is (stream_match_time - track_match_time) 3. If possible some jitter allowed in the delta which can be a variable (as an example +-1 second) 4. Each client will have an individual rule set to be applied to the rolled up results a) min_duration is Minimum match duration. As an example, if set to 10 seconds results will not be reported if the roller up result does not span 10 seconds or more in the track. b) max_gap is the maximum time gap we bridge between matches. As an example there are matches to second 0 2 8 10 12 16 in the track. If the max_gap is defined as 2 seconds and the min_duration is 10 seconds., There is a gap of 4 seconds between 2-8 (remember match at 2 spans 2 seconds) that is not bridged . There is a gap of 2 seconds between 12-16 that we bridge. There would be 2 bridged results: 1. 0-4 seconds (0 2) 2. 8-18 (8,10,12,(14 bridged), 16) Result 1 is less than min_duration so it is not reported in the rolled up results. Results 2 is 18-8 = 10 seconds which satisfies min_duration so its reported. The total_actual_duration would be total time 10 seconds minus any gaps bridged (this example 2 seconds) which would make it 8 seconds. Keep in mind you can have multiple bridges across a rolled up match. The query should run on every raw match insertion if possible or have a method of knowing what has been processed and what has not been process. I'm open to the possibility of doing this in python but prefer to do this as an sql script to limit moving parts. There are two tables in the sample csv provided.