November 18, 2024 at 6:50 pm
I know how to generate fake data (thanks to Jeff Moden's articles!)... the problem is that I'm trying to generate purchase data where the unit price of a product increases on some products over time. Normally, I'd get the changing price information from all the purchase invoices, but I don't have those. So I have to make it up. I was thinking I could do something like select random(ish) records and use a systems table to get just bulk records (that part is fine), but how do I fake price increases (where the changes aren't completely fake, because grains, for example, rise by 50% or more over time.)
I can create dummy values... maybe my multiplier is screwy?
use bakery;
go
DECLARE @Range INT = 500;
DECLARE @StartValue DECIMAL(5,2);
/* some ingredients are only purchased every 6 weeks (grains) */
/* add a 1/0 random number to determine whether to apply the RT_FakePct */
/* JOIN ALL OF THIS TO vwBaseIngredientRecipe */
DECLARE @FirstBakeEventDate DATE;
SELECT @FirstBakeEventDate = MIN(BakeDate) FROM BakeEvents;
WITH cteWeeklyPrices(IngredientID, IngredientName, WeekNo, NewPrice)
AS (
SELECT x.IngredientID
,x.IngredientName
,x.WeekNo
--,x.UnitPrice
--,x.FakePct
--,RT_fakePct = 1 + SUM(x.NewChangePct) OVER (PARTITION BY x.IngredientID ORDER BY x.WeekNo)
--,x.ChangeY
--,x.NewChangePct
,NewPrice2 = CAST(ROUND(x.UnitPrice * (1 + SUM(x.FakePct) OVER (PARTITION BY x.IngredientID ORDER BY x.WeekNo)),2) AS DECIMAL(5,2))
FROM
(
SELECT i.IngredientID
,i.IngredientName
,i.UnitPrice
,PerKgPrice = i.UnitPrice/(i.IngredientWeight * 1000) /* convert from Kg to g */
,t.N AS WeekNo
--,t.Num AS WeekNo
,FakePct = ABS(CHECKSUM(NEWID()))%100 / 10000.0
,ChangeY = ABS(CHECKSUM(NEWID())%2) /* basically T/F to decide whether to apply the change */
,NewChangePct = IIF(ABS(CHECKSUM(NEWID())%2) = 0, 0, ABS(CHECKSUM(NEWID()))%10 / 10000.0 ) --was%100
--, NewPrice = (1 + ABS(CHECKSUM(NEWID()))%100 / 10000.0) * i.UnitPrice
, NewPrice = i.UnitPrice * (1 + IIF(ABS(CHECKSUM(NEWID())%2) = 0, 0, ABS(CHECKSUM(NEWID()))%100 / 10000.0 ))
FROM Ingredient i
--CROSS JOIN Scratch.dbo.Tally t
CROSS APPLY Scratch.dbo.fnTally(1,76) t
WHERE i.IngredientID NOT IN (17,62) /* Foil pans, plastic bags */
) x
)
SELECTbw.WeekNo
,bw.LoafKey
,bw.LoafQty
,ri.Yield
,ri.IngredientName
,ri.RecipeWeight
,wp.NewPrice
FROM
(SELECT LoafKey,
LoafQty,
WeekNo = 1 + DATEDIFF(week,@FirstBakeEventDate,BakeDate)
FROM BakeEvents be) bw
INNER JOIN cteWeeklyPrices wp
ON bw.WeekNo = wp.WeekNo
INNER JOIN vwBaseIngredientRecipe ri
ON ri.RecipeID = bw.LoafKey
ORDER BY LoafKey, WeekNo;
(I was trying to isolate the fake increases in the Common Table Expression... just to keep things from being hideous. The problem isn't that they don't increase, it's that they increase kind of monotonically instead of more randomly as I would like (for things besides grains, that's okay, but for grains the price changes are more dramatic).
I was thinking I could just generate a set of positive random values (easy enough) and then do a running total over Weeks and then multiply, but how do I increase the randomness? ("That's unpossible! There's no such thing as "semi-random"! Something is true or it's not!) Well, increase for some groups of ingredients. (Like the "flour increases in price by 50% in 18 months part.)
Sorry, but I think I'm just missing the blindingly obvious. =(
Pieter
November 18, 2024 at 7:43 pm
You're already generating a series using dbo.fnTally so maybe raise fn.N to an exponent power. Either POWER or EXP function
declare @increase_pct decimal(4,3)=0.025;
select *,
power(1+@increase_pct, fn.n) compound_trend,
exp(@increase_pct * fn.n) AS continuous_compound_trend
from dbo.fnTally(1, 10) fn;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply