TLDR: I need a way to limit my select such that only rows needed to fill my new order are returned.
Hey, I'm looking for a better way to do a query on the simplified toy table below. Right now I do a straightforward select then do some additional logic in Python that processes rows that are fully or partially fillable.
id |name | category | price | quantity
---+-------+----------+---------+---------
1 |Robby | buy | 45 | 4
2 |Blane | buy | 48 | 5
3 |Shawn | buy | 43 | 10
4 |Valva | sell | 62 | 9
5 |Chris | buy | 42 | 15
If I'm inserting a new sell order at price $40 and quantity = 12
SELECT id, name, price, category
FROM my_table
WHERE price > 40 AND category = 'buy'
ORDER BY price DESC;
Then in Python my logic looks something like this:
running_total = 0
for order in orders:
running_total += order.quantity
fill_order(order, new_sell_order)
if running_total >= new_sell_order.quantity: # Values is 12 in my example.
break
I'd like a way to limit my select such that all rows are returned in order until a row is returned such that the sum of all quantity columns is greater than some value. Additional columns need not be returned (and thus locked in a repeatable read state).
ID
sequence? If you want to satisfy as many buy orders as possible without going over your sell order's quantity then this is the knapsack problem.