On 2015-07-30 12:41 AM, Simon Slavin wrote:
> On 29 Jul 2015, at 11:32pm, Richard Hipp <drh at sqlite.org> wrote:
>
>> I'm looking for real-world (open-source) use cases for CTEs.
> I would like to see a demonstration of how non-recursive CTEs can be useful
> in simplifying a SQL command. Ideally in a plausible example rather than
> something obviously made up with no real-world equivalent. It seems that
> everyone who mentions CTEs jumps straight to recursion, as if that's the only
> thing they're useful for.
Well, it's hard to show a quick and simple example when the question
specifically presupposes complexity, but if you forgive the verbosity,
here is an example of non-recursive CTE which I don't think is even
possible without CTE (or pre-set-up temporary tables) in a very
real-World situation:
Basic manufacturing company has a data system comprising of stock items
and stockkeeping units (SKU's) aka manufactured products. The method of
turning stock into a manufactured product is commonly referred to as a Job.
Jobs have Bills of material (BOM) that basically is a stock item (sku)
with a parent-child relationship to several other stock items which it
"uses" or consumes to get made.
A stock item can belong to very many Bills of material, or can be used
outside of any (such as surgical gloves that makes part of the
consumables for a job, but isn't part of the manufactured product,
etc.). BOMs can be sub-BOMs of other BOMs, in the way that your car's
Engine is its own manufactured BOM unit but also a sub-unit of the Car's
total BOM. (Recursive CTE's help us a lot in this regard for working out
other things)
Now at any point, in your store there is an amount of stock that can be
used, but the amount of a specific finished product that can be produced
from it will vary widely according to BOM consumption figures. So let's
say you have 10 of Stock X1, 20 of X2 and 5 of X3, but the product you
want to make consumes 8 of each. You will need to buy 3 more X3 to be
able to make even 1 of this product.
That sounds simple enough, but consider that nobody will sell you 3 more
X3, those X3 items comes in minimum orders of 25 (we say MOQ for
minimum-order-quantity). So if I buy a pack of X3, I can now make one
product - but, I will now be left over with 2 of X1, 12 of X2 and 22 of
X3 - that is more stock than I started with!
Of course the MOQ for X1 and X2 are different, I might need to buy 30 of
X1's and 10 of X2's. (There is also a thing called Pan-Size / Pack-Size
that dictates the minimum set to buy - you might need to buy a minimum
of 30, but after that you still need to buy sets of 6, so you can buy 36
or 42, but not 35 or 37, etc. I will ignore this bit for this example,
but note how the complexity grows).
Then, every item has a cost. Some items are very expensive, and some are
very cheap. I won't mind buying 50 extra of cheap item A as long as I
use up expensive item B completely.
One common question is: How much do I need to buy of everything so that
I can manufacture my products and have the least amount of wasted value
left over?.
A final complication to mention is that, we only really care about
balancing after a point - I mean it's all fine and dandy if we find that
making 7235 jobs will use up the stock so that there is zero of
everything left. We can't make that many jobs, we don't have the
capacity and even if we did, nobody will buy them all. In fact, for this
exercise, we don't really wish to make more than 20 jobs at any time.
By now I hopefully don't need to explain any more how this has become a
really complex problem to solve. To answer the question, some CTEs can
be called into service.
First, let's build a table with job counts in the range that we are
comfortable with (so 0 to 20 in this case).
WITH JCount(c) AS (
SELECT 0 UNION ALL SELECT c+1 FROM JCount LIMIT 21
),
(Ignore the recursion here, it only builds a quick little table, it
isn't recursing the main dataset in any way - just another way of saying
SELECT 1 UNION ALL SELECT 2 UNION ALL.. etc etc.)
Next we will add a CTE that lists Job candidates for manufacture,
followed by a CTE that calculates all our candidate BOMs and their stock
needs.
I'll imagine this is a sweets factory and we're making little jelly
things this time.
(I'm not showing the schema because it isn't important and you can
intuit it)
WITH JCount(c) AS (
SELECT 0 UNION ALL SELECT c+1 FROM JCount LIMIT 20
), Candidates(BOMCode, UnitsPerBOM) AS (
SELECT StockCode, QtyPer FROM stock
WHERE Description LIKE '%jelly%' AND StockCode IN (SELECT
ParentPart FROM BomStructure)
), JBOMs(JobQty, BOMCode, TotalUnitsToMake, TotalStockNeeded) AS (
SELECT c, BOMCode, (c * UnitsPerBOM), (QtyPer * c)
FROM JCount, Candidates
JOIN stock ON stock.StockCode IN (SELECT Component FROM
BomStructure WHERE ParentPart = BOMCode)
WHERE stock.QtyPer > 0
),
Now that we have a list of possible BOMs to make against several
possible quantities to make and knowing the stock usage for each of
those, we can continue to see how much we must procure to make them happen:
WITH JCount(c) AS (
SELECT 0 UNION ALL SELECT c+1 FROM JCount LIMIT 20
), Candidates(BOMCode, UnitsPerBOM) AS (
SELECT StockCode, QtyPer FROM stock
WHERE Description LIKE '%jelly%' AND StockCode IN (SELECT
ParentPart FROM BomStructure)
), JBOMs(JobQty, BOMCode, TotalUnitsToMake, TotalStockNeeded) AS (
SELECT c, BOMCode, (c * UnitsPerBOM), (QtyPer * c)
FROM JCount, Candidates
JOIN stock ON stock.StockCode IN (SELECT Component FROM
BomStructure WHERE ParentPart = BOMCode)
WHERE stock.QtyPer > 0
), Procure(c, BOMCode, ProcureQty) AS (
SELECT c, BOMCode, ((CASE
WHEN (WH.QtyOnHand - TotalStockNeeded) >= 0 THEN (WH.QtyOnHand
- TotalStockNeeded)
ELSE MAX(TotalStockNeeded - WH.QtyOnHand, MOQ)
END) * UnitCost)
FROM JBOMs
)
In the real World, Stock will sit in multiple warehouses and have
various lots which may be subject to expiry dates etc - We'll ignore all
that here, but another CTE can take care of those things too.
At this point we are only interested in the quantities of jobs to make
and the Values left over after making them - another query can take care
of calculating the procurement specifics once we have decided on the
quantities.
WITH JCount(c) AS (
SELECT 0 UNION ALL SELECT c+1 FROM JCount LIMIT 20
), Candidates(BOMCode, UnitsPerBOM) AS (
SELECT StockCode, QtyPer FROM stock
WHERE Description LIKE '%jelly%' AND StockCode IN (SELECT
ParentPart FROM BomStructure)
), JBOMs(JobQty, BOMCode, TotalUnitsToMake, TotalStockNeeded) AS (
SELECT c, BOMCode, (c * UnitsPerBOM), (QtyPer * c)
FROM JCount, Candidates
JOIN stock ON stock.StockCode IN (SELECT Component FROM
BomStructure WHERE ParentPart=BOMCode)
WHERE stock.QtyPer > 0
), Procure(c, BOMCode, StockCode, UCost, StockNeeded, QtyOnHand,
ProcureQty) AS (
SELECT c, BOMCode, StockCode, WH.UnitCost, StockNeeded,
WH.QtyOnHand, (CASE
WHEN (WH.QtyOnHand - StockNeeded) >= 0 THEN (WH.QtyOnHand -
StockNeeded)
ELSE MAX(StockNeeded - WH.QtyOnHand, MOQ)
END)
FROM JBOMs
LEFT JOIN Warehouse AS WH ON WH.Warehouse = 'RAWMAT' AND
WH.StockCode=StockCode
), LeftOvers(c, BOMCode, LeftOverVal) AS (
SELECT c, BOMCode, SUM((QtyOnHand + ProcureQty - StockNeeded) *
UCost)
FROM Procure
GROUP BY c, BOMCode
)
Now finally, we can run a standard query against these CTE's and rank
the results to find the lowest 10 Left-over values vs. Qty to make which
will enable a human to best judge which qty to go for:
SELECT c||' x '||BOMCode||' yields a waste value of: '||LeftOverVal
FROM LeftOvers
ORDER BY LeftOverVal DESC
LIMIT 10
;
It might be possible to do without CTE's, but the complexity would be
staggering, and I can't actually think of a way to achieve this without
CTE's or Temp tables, and in the case of temp tables, it definitely
won't be inside of 1 single statement like this. Most importantly: with
CTE's, the above became real easy to solve.
This is just one example of a real-World question that can be made
simple by a divide and conquer philosophy of complex problems by using
CTE's.
In fact, now I think of it, examples of complex problems being helped by
CTE are everywhere, it's finding examples of simple problems that are
helped by CTE that is difficult.
Cheers,
Ryan
