How can I apportion values into buckets and find which is the bucket containing a value?

Question

This is my first question on here so please let me know if I do not follow the best practices!

I have two tables:

+---------+------------+--+
| Version | Time_Taken |  |
| 1       | 10         |  |
| 2       | 15         |  |
| 3       | 20         |  |
+---------+------------+--+

and

+--------+--------------+--+
| Bucket | Time_Allowed |  |
+--------+--------------+--+
|      1 |            5 |  |
|      2 |           10 |  |
|      3 |           10 |  |
|      4 |           10 |  |
|      5 |           10 |  |
+--------+--------------+--+

I would like to be able to see which of the Buckets my version was completed in. The versions must be completed in order, as must buckets. To make it more fun the buckets can be used by multiple versions only if they have some time_allowed left.

So for Version 1 (time_taken = 10), for example, I know that I will take up all of Bucket 1 (time_allowed=5) and half of bucket 2, with 5 left over. I now have 5 left in bucket 2 and can begin to fill that bucket with Version 2.

I am using postgres right now and am still learning the ropes so could really do with some general advice on how to create a structure that would help here!

Apologies if this is vague!

Answer 1

Seems like you want to calculate cumulative sums, easy using Standard SQL's Windowed Aggregate Functions. Following query returns the covered time range for each bucket in table #2:

select *, 
   sum(Time_Allowed) 
   over (order by Bucket
         rows unbounded preceding) - Time_Allowed AS timeFrom,
   sum(Time_Allowed) 
   over (order by Bucket
         rows unbounded preceding) AS timeTo
from tab2

You do the same for Time_Taken in table #1 and then you can join using x between timeFrom and timeTo