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 input_table 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 ORDER BY id;
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 | 2010-08-01 | 2010-08-02 | 86400 |
| 1 | 600 | 2010-08-02 | 2010-08-03 | 86400 |
| 1 | 1100 | 2010-08-03 | 2010-08-04 | 86400 |
| 1 | 700 | 2010-08-04 | 2010-08-05 | 86400 |
| 1 | 1100 | 2010-08-05 | 2010-08-06 | 86400 |
| 1 | 1100 | 2010-08-06 | 2010-08-07 | 86400 |
| 1 | 1100 | 2010-08-07 | 2010-08-08 | 86400 |
| 1 | 900 | 2010-08-08 | 2010-08-09 | 86400 |
| 1 | 400 | 2010-08-09 | 2010-08-10 | 86400 |