Input
id | start_time_column | end_time_column | num_custs |
---|---|---|---|
1 | 2010-08-05 | 2010-08-10 | 400 |
1 | 2010-08-03 | 2010-08-09 | 500 |
1 | 2010-08-04 | 2010-08-08 | 200 |
1 | 2010-08-01 | 2010-08-04 | 600 |
SQL Call
The SQL call bursts the data for a duration of 1 day (86400 seconds).
SELECT * FROM Burst( ON burst_example AS InputTable PARTITION BY id ORDER BY id USING TimeColumn ('start_time_column', 'end_time_column') TimeInterval (86400) TargetColumns ('num_custs') StartTime ('08/01/2010') EndTime ('08/10/2010') SplitCriteria ('nosplit') Accumulate ('id') ) AS dt ;
Output
The value assigned to each subinterval is the sum of all rows contributing to the subinterval. For example, 2010-08-06 is covered by the first 3 rows of the input table: 400 + 500 + 200 = 1100.
id num_custs burst_start burst_end burst_duration -- --------- ----------- ---------- -------------- 1 600.0 2010-08-01 2010-08-02 86400.0 1 600.0 2010-08-02 2010-08-03 86400.0 1 1100.0 2010-08-03 2010-08-04 86400.0 1 700.0 2010-08-04 2010-08-05 86400.0 1 1100.0 2010-08-05 2010-08-06 86400.0 1 1100.0 2010-08-06 2010-08-07 86400.0 1 1100.0 2010-08-07 2010-08-08 86400.0 1 900.0 2010-08-08 2010-08-09 86400.0 1 400.0 2010-08-09 2010-08-10 86400.0
Download a zip file of all examples and a SQL script file that creates their input tables.