Obtain largest timespan where sum of values is below threshold
Hi,
For a set of hourly values, i'm trying to obtain the biggest time span, where the sum of the values is less than a threshold.
i.e. for this data:
31-JUL-3000 09.00.00 10
31-JUL-3000 12.00.00 100
31-JUL-3000 14.00.00 1
31-JUL-3000 18.00.00 1
31-JUL-3000 20.30.00 1
31-JUL-3000 22.00.00 10
01-AUG-3000 02.00.00 10
The output would be in a form like:
first_entry, last_entry, total, number_of_entries
e.g. threshold '9':
31-JUL-3000 14.00.00, 31-JUL-3000 20.30.00, 3, 3
threshold '14' would give:
31-JUL-3000 14.00.00, 31-JUL-3000 22.00.00, 13, 4
I don't think I can easily use an analytic function, because I need the window to be the sum of the data. I could write new code for each window size that I'd like to check, but this feels messy. I could also use a subquery to calculate the totals for all windows, but that will become very large, very quickly.
I'm guessing that I might have to use the MODEL clause - does anyone have any suggestions for how to get started? I'm using Oracle 11gR2.
Thanks,
Dan Scott