If you have a steady consumption rate, it is easy to forecast how far you can go with that rate – for example, if you know your car on average drives 20 kilometers per liter fuel and it has 30 liters left in the tank, you can simply multiply to know that you can drive 600 kilometers before you run out of fuel.
But if the consumption is not steady, you need something else. If the Good Beer Trading Co sells a particular seasonal Christmas beer, it is not simply a steady 100 beers sold per month – June will sell very few of those beers, while December sells hundreds. For such a case, you estimate (perhaps using the techniques of the previous chapter) what you think you are going to sell and store it as a forecast or sales budget.
Once you have forecast you are going to sell 150 in January, 100 in February, 250 in March, and so on, you need to figure out that the 400 you have in stock in your inventory will dwindle to 250 by the end of January and to 150 by the end of February and be sold out a little later than the middle of March. Figuring this out is the topic of this chapter.
Inventory, budget, and order
In the Good Beer Trading Co example, I’m going to demonstrate the case of forecasting when the inventory reaches zero (or a minimum) given that I know how many beers are in order (waiting to be picked from the inventory) and how many beers are budgeted to be sold (assumed to be picked at some point).
I’ll use month as the time granularity, budgeting sales quantities per month. For this demonstration purpose, I don’t need to go to weekly or daily data, but you can easily adapt the methods to finer time granularity if you need it. I will use the data in the tables shown in Figure 16-1.
Figure 16-1
The tables used in the examples of this chapter
From table inventory, I know what quantity of each beer is in stock, table monthly_budget shows me the quantity each beer is expected to sell per month, and how much has been ordered (but not yet picked and therefore not yet taken from the stock) is in table orderlines. Table product_minimums I’ll get back to later in the chapter.
You’ll notice the inventory table contains quantities per location (I used the table in the FIFO picking in Chapter 13), but for this purpose, I just need the total quantity in stock per beer. To make that easier, I create the view inventory_totals in Listing 16-1 aggregating the inventory per beer.
SQL> create or replace view inventory_totals
2 as
3 select
4 i.product_id
5 , sum(i.qty) as qty
6 from inventory i
7 group by i.product_id;
View INVENTORY_TOTALS created.
Listing 16-1
View of total inventory per product
Similarly for the quantities in order, I do not need specific orderlines. I just need how many of each beer each month, so I aggregate those figures in view monthly_orders in Listing 16-2.
SQL> create or replace view monthly_orders
2 as
3 select
4 ol.product_id
5 , trunc(o.ordered, 'MM') as mth
6 , sum(ol.qty) as qty
7 from orders o
8 join orderlines ol
9 on ol.order_id = o.id
10 group by ol.product_id, trunc(o.ordered, 'MM');
View MONTHLY_ORDERS created.
Listing 16-2
View of monthly order totals per product
Those are the tables and views I’m going to be using; now I’ll show the data in them.
The data
I’ll use two beers for the examples of this chapter: Der Helle Kumpel and Hazy Pink Cloud. They have the total inventory shown in Listing 16-3.
SQL> select it.product_id, p.name, it.qty
2 from inventory_totals it
3 join products p
4 on p.id = it.product_id
5 where product_id in (6520, 6600)
6 order by product_id;
PRODUCT_ID NAME QTY
6520 Der Helle Kumpel 400
6600 Hazy Pink Cloud 100
Listing 16-3
The inventory totals for two products
This is totals in stock as of January 1, 2019. Then I have a monthly sales budget for the year 2019 (Listing 16-4).
SQL> select mb.product_id, mb.mth, mb.qty
2 from monthly_budget mb
3 where mb.product_id in (6520, 6600)
4 and mb.mth >= date '2019-01-01'
5 order by mb.product_id, mb.mth;
PRODUCT_ID MTH QTY
6520 2019-01-01 45
6520 2019-02-01 45
6520 2019-03-01 50
...
6520 2019-10-01 50
6520 2019-11-01 40
6520 2019-12-01 40
6600 2019-01-01 20
6600 2019-02-01 20
6600 2019-03-01 20
...
6600 2019-10-01 20
6600 2019-11-01 20
6600 2019-12-01 20
24 rows selected.
Listing 16-4
The 2019 monthly budget for the two beers
Product 6520 is expected to sell a bit more in the summer months, while product 6600 is expected to sell a steady 20 per month.
But I don’t just have the expected quantities; I also have in Listing 16-5 the quantities that have already been ordered in the first months of 2019.
SQL> select mo.product_id, mo.mth, mo.qty
2 from monthly_orders mo
3 where mo.product_id in (6520, 6600)
4 order by mo.product_id, mo.mth;
PRODUCT_ID MTH QTY
6520 2019-01-01 260
6520 2019-02-01 40
6600 2019-01-01 16
6600 2019-02-01 40
Listing 16-5
The current monthly order quantities
The thing to note here is that in January, product 6520 has been ordered much more than what was expected.
Given these data, I’ll now make some SQL to find out when we run out of beers for those two products.
Accumulating until zero
One of the really useful things you can do with analytic functions is the rolling (accumulated) sum that I’ve shown before. In Listing 16-6, I use it again.
SQL> select
2 mb.product_id as p_id, mb.mth
3 , mb.qty b_qty, mo.qty o_qty
4 , greatest(mb.qty, nvl(mo.qty, 0)) as qty
5 , sum(greatest(mb.qty, nvl(mo.qty, 0))) over (
6 partition by mb.product_id
7 order by mb.mth
8 rows between unbounded preceding and current row
9 ) as acc_qty
10 from monthly_budget mb
11 left outer join monthly_orders mo
12 on mo.product_id = mb.product_id
13 and mo.mth = mb.mth
14 where mb.product_id in (6520, 6600)
15 and mb.mth >= date '2019-01-01'
16 order by mb.product_id, mb.mth;
Listing 16-6
Accumulating quantities
In line 4, I calculate the monthly quantity as whichever is the greatest of either the budgeted quantity or the ordered quantity. In the following output, you see January for product 6520 has o_qty as the greatest (making qty = 260), while January for product 6600 has b_qty as the greatest (making qty = 20.)
The idea is that if the ordered quantity is the smallest, there hasn’t yet been orders to match the budget, but it’s still expected to rise until budget is reached. But when the ordered quantity is the greatest, I know the budget has been surpassed, so I don’t expect it to become greater yet.
So this quantity is then what I accumulate with the analytic sum in lines 5–9, so I end up with column acc_qty that shows me accumulated how much I expect to pick from the inventory:
P_ID MTH B_QTY O_QTY QTY ACC_QTY
6520 2019-01-01 45 260 260 260
6520 2019-02-01 45 40 45 305
6520 2019-03-01 50 50 355
...
6520 2019-11-01 40 40 775
6520 2019-12-01 40 40 815
6600 2019-01-01 20 16 20 20
6600 2019-02-01 20 40 40 60
6600 2019-03-01 20 20 80
...
6600 2019-11-01 20 20 240
6600 2019-12-01 20 20 260
In Listing 16-7, I use this accumulated quantity to calculate what’s the expected inventory for each month (if I don’t restock along the way).
SQL> select
2 mb.product_id as p_id, mb.mth
3 , greatest(mb.qty, nvl(mo.qty, 0)) as qty
4 , greatest(
5 it.qty - nvl(sum(
6 greatest(mb.qty, nvl(mo.qty, 0))
7 ) over (
8 partition by mb.product_id
9 order by mb.mth
10 rows between unbounded preceding and 1 preceding
11 ), 0)
12 , 0
13 ) as inv_begin
14 , greatest(
15 it.qty - sum(
16 greatest(mb.qty, nvl(mo.qty, 0))
17 ) over (
18 partition by mb.product_id
19 order by mb.mth
20 rows between unbounded preceding and current row
21 )
22 , 0
23 ) as inv_end
24 from monthly_budget mb
25 left outer join monthly_orders mo
26 on mo.product_id = mb.product_id
27 and mo.mth = mb.mth
28 join inventory_totals it
29 on it.product_id = mb.product_id
30 where mb.product_id in (6520, 6600)
31 and mb.mth >= date '2019-01-01'
32 order by mb.product_id, mb.mth;
Listing 16-7
Dwindling inventory
Lines 4–13 calculate how much quantity was in stock at the beginning of the month, while lines 14–23 calculate how much at the end of the month:
P_ID MTH QTY INV_BEGIN INV_END
6520 2019-01-01 260 400 140
6520 2019-02-01 45 140 95
6520 2019-03-01 50 95 45
6520 2019-04-01 50 45 0
6520 2019-05-01 55 0 0
...
6600 2019-01-01 20 100 80
6600 2019-02-01 40 80 40
6600 2019-03-01 20 40 20
6600 2019-04-01 20 20 0
6600 2019-05-01 20 0 0
...
You see how the inventory dwindles until it reaches zero. As I use month for time granularity, in principle I can only state that the inventory will reach zero at some point during that month. But if I assume that the budgeted sales will be evenly distributed throughout the month, I can also in Listing 16-8 make a guesstimation of which day that zero will be reached.
I wrap Listing 16-7 in an inline view and use inv_begin / qty in line 5 to figure out how large a fraction of the estimated monthly sales can be fulfilled by the inventory at hand at the beginning of the month. When I assume evenly distributed sales, this is then the fraction of the number of days in the month that I have sufficient stock for.
Filtering in line 42 gives me as output just the rows where the inventory becomes zero:
P_ID MTH INV_BEGIN INV_END ZERO_DAY
6520 2019-04-01 45 0 2019-04-27
6600 2019-04-01 20 0 2019-04-30
In reality, however, I wouldn’t let the inventory reach zero. I’d set up a minimum quantity that I mustn’t get below of (as a buffer in case I underestimated sales), and every time I get to the minimum quantity, I must buy more beer and restock the inventory.
Restocking when minimum reached
In table product_minimums, I have parameters for the inventory handling of each product. Listing 16-9 shows the table content for the two beers I use for demonstration.
SQL> select product_id, qty_minimum, qty_purchase
2 from product_minimums pm
3 where pm.product_id in (6520, 6600)
4 order by pm.product_id;
Listing 16-9
Product minimum restocking parameters
Column qty_minimum is my inventory buffer – I plan that the inventory should never get below this. Column qty_purchase is the number of beers I buy every time I restock the inventory:
PRODUCT_ID QTY_MINIMUM QTY_PURCHASE
6520 100 400
6600 30 100
With this I am ready to write SQL that can show me when I need to purchase more beer and restock throughout 2019.
This is not simply done with analytic functions, since I cannot use the result of an analytic function inside the analytic function itself to add more quantity. This would mean an unsupported type of recursive function call; it cannot be done. But I can do it with recursive subquery factoring instead of analytic functions as shown in Listing 16-10.
SQL> with mb_recur(
2 product_id, mth, qty, inv_begin, date_purch
3 , p_qty, inv_end, qty_minimum, qty_purchase
4 ) as (
5 select
6 it.product_id
7 , date '2018-12-01' as mth
8 , 0 as qty
9 , 0 as inv_begin
10 , cast(null as date) as date_purch
11 , 0 as p_qty
12 , it.qty as inv_end
13 , pm.qty_minimum
14 , pm.qty_purchase
15 from inventory_totals it
16 join product_minimums pm
17 on pm.product_id = it.product_id
18 where it.product_id in (6520, 6600)
19 union all
20 select
21 mb.product_id
22 , mb.mth
23 , greatest(mb.qty, nvl(mo.qty, 0)) as qty
24 , mbr.inv_end as inv_begin
25 , case
26 when mbr.inv_end - greatest(mb.qty, nvl(mo.qty, 0))
27 < mbr.qty_minimum
28 then
29 trunc(
30 mb.mth
31 + numtodsinterval(
32 (add_months(mb.mth, 1) - 1 - mb.mth)
33 * (mbr.inv_end - mbr.qty_minimum)
34 / mb.qty
35 , 'day'
36 )
37 )
38 end as date_purch
39 , case
40 when mbr.inv_end - greatest(mb.qty, nvl(mo.qty, 0))
46 when mbr.inv_end - greatest(mb.qty, nvl(mo.qty, 0))
47 < mbr.qty_minimum
48 then mbr.qty_purchase
49 else 0
50 end as inv_end
51 , mbr.qty_minimum
52 , mbr.qty_purchase
53 from mb_recur mbr
54 join monthly_budget mb
55 on mb.product_id = mbr.product_id
56 and mb.mth = add_months(mbr.mth, 1)
57 left outer join monthly_orders mo
58 on mo.product_id = mb.product_id
59 and mo.mth = mb.mth
60 )
61 select
62 product_id as p_id, mth, qty, inv_begin
63 , date_purch, p_qty, inv_end
64 from mb_recur
65 where mth >= date '2019-01-01'
66 and p_qty is not null
67 order by product_id, mth;
Listing 16-10
Restocking when a minimum is reached
I start in lines 5–18 by setting up one row per product containing what is the inventory when I start, along with the parameters for minimum quantity and how much to purchase. I set this row as being in December 2018 with the inventory in the inv_end column – that way it will function as a “primer” row for the recursive part of the query in lines 20–59.
In the recursive part I do:
Join to the monthly budget for the next month in line 56. The first iteration here will find January 2019 (since my “primer” row was December 2018), and then each iteration will find the next month until there are no more budget rows.
The inv_begin of this next month in the iteration is then equal to the inv_end of the previous month, so that’s a simple assignment in line 24.
Lines 44–50 calculate the inv_end, which is the beginning inventory (previous inv_end) minus the quantity picked that month plus a possible restocking. If the beginning inventory minus the quantity would become less than the minimum, I add the quantity I will be purchasing for restocking.
To show on the output how much I need to purchase for restocking, I separate this case structure out in lines 39–43.
And in lines 25–28, I use the same case condition to calculate an estimated date of the month where the restocking by purchasing more beer should take place.
Line 65 removes the “primer” rows from the output (they are not interesting), and line 66 gives me just those months where I need to restock:
P_ID MTH QTY INV_BEGIN DATE_PURCH P_QTY INV_END
6520 2019-02-01 45 140 2019-02-25 400 495
6520 2019-10-01 50 115 2019-10-10 400 465
6600 2019-03-01 20 40 2019-03-16 100 120
6600 2019-08-01 20 40 2019-08-16 100 120
I am now able to plan when I need to purchase more beers to restock the inventory.
In Listing 16-10, I used recursive subquery factoring. The way I did it means that for the budget and orders, there will be a series of repeated small lookups to the tables for each month. Depending on circumstances, this might be perfectly fine, but in other cases, it could be bad for performance.
Listing 16-11 shows an alternative method of recursion (or rather, iteration) with the model clause instead, where a different access plan can be used by the optimizer.
With this method I do not need “primer” rows and repeated monthly lookups. Instead I grab all the data I need in one go in lines 5–15, rather like if I was using analytic functions. And then I can use model:
Lines 19–21 create a consecutive numbering that I can use as dimension (“index”) in my measures. I deliberately make it have the values 0–11 instead of 1–12, because that fits how iteration_number is filled when using iteration.
In the measures in lines 24–32, I set up the “variables” I need to work with.
In the rules clause, I can then perform all my calculations. In line 34, I specify that I want my calculations to be performed in the order I have typed them, and they should be performed 12 times. That means that within each of the 12 iterations, I can use the pseudocolumn iteration_number, and it will increase from 0 to 11.
The first rule to be executed is lines 35–36, where I set inv_begin to the inv_end of the previous month (in the first iteration, this will be null, so with nvl I set it to the original inventory in the first month).
If the inventory minus the quantity is less than the minimum, then in lines 37–42, I set p_qty to the quantity I need to purchase.
If I did find a p_qty (line 45), the rule in lines 43–56 calculates the day I need to purchase and restock.
And lines 57–68 calculate the inv_end by using the other measures.
The 12 iterations and calculations are quite similar to what I did in the recursive subquery factoring, except that I use measures indexed by a dimension where the data in those measures have all been filled initially before I start iterating and calculating.
This method will for some cases enable more efficient access of the tables – but at the cost of using more memory to keep all the data and work with them in the model clause (potentially needing to spill some to disk if you have huge amounts of data here.) Whether Listing 16-10 or 16-11 is the best will depend on the case – you’ll need to test the methods yourself.
Lessons learned
Analytic functions are extremely useful and can solve a lot of things, including rolling sums to find when you reach some minimum. But it cannot do all, so in this chapter, I showed you a mix of
Subtracting a rolling sum from a starting figure to discover when a minimum (or zero) has been reached
Using recursive subquery to repeatedly replenish the dwindling figure whenever minimum has been reached
Using the model clause to accomplish the same with an alternative data access plan
Though it’s a mix of techniques, all in all they should help you solve similar cases in the future.
