More math help and what I've learned
(1) By ThanksRyan on 2021-07-13 20:31:29 [link] [source]
Hi,
Thanks for all the help you friends have provided in my silliness to get the right answers from my data. I appreciate the time each and one of you take to help out.
the table:
CREATE TABLE "calculategrowth" (
"transactionId" int,
"pig" int,
"growthpool" int,
"GrowthCarry" int
);
sample data:
INSERT INTO calculategrowth(TransactionId,pig,GrowthPool,GrowthCarry) VALUES(464,1000065,269250000,250000);
INSERT INTO calculategrowth(TransactionId,pig,GrowthPool,GrowthCarry) VALUES(645,1000065,134500000,1500000);
INSERT INTO calculategrowth(TransactionId,pig,GrowthPool,GrowthCarry) VALUES(3880,1000065,2430000000,0);
INSERT INTO calculategrowth(TransactionId,pig,GrowthPool,GrowthCarry) VALUES(9668,1000065,4338750000,750000);
INSERT INTO calculategrowth(TransactionId,pig,GrowthPool,GrowthCarry) VALUES(9949,1000065,210750000,1750000);
Almost working query:
SELECT *,
(GrowthPool-GCOVAW)/1000000-EstGrowth as Diff
FROM (
SELECT
TransactionId,
GrowthPool,
lag(TransactionId,1) OVER
w previous_transId, TransactionId as current_transId,
((TransactionId) - (lag(TransactionId,1,1) OVER w))*0.003*250 EstGrowth,
lag(GrowthCarry) OVER
w Previous_GCOV, GrowthCarry as Current_GCOV,
(lag(GrowthCarry) OVER w) as GCOVAW
FROM CalculateGrowth
WINDOW w AS (PARTITION BY PIG ORDER BY PIG)
) e
WHERE EstGrowth IS NOT NULL;
Results:
┌───────────────┬────────────┬──────────────────┬─────────────────┬───────────┬───────────────┬──────────────┬─────────┬───────┐
│ TransactionId │ GrowthPool │ previous_transId │ current_transId │ EstGrowth │ Previous_GCOV │ Current_GCOV │ GCOVAW │ Diff │
├───────────────┼────────────┼──────────────────┼─────────────────┼───────────┼───────────────┼──────────────┼─────────┼───────┤
│ 464 │ 269250000 │ │ 464 │ 347.25 │ │ 250000 │ │ │
│ 645 │ 134500000 │ 464 │ 645 │ 135.75 │ 250000 │ 1500000 │ 250000 │ -1.75 │
│ 3880 │ 2430000000 │ 645 │ 3880 │ 2426.25 │ 1500000 │ 0 │ 1500000 │ 1.75 │
│ 9668 │ 4338750000 │ 3880 │ 9668 │ 4341.0 │ 0 │ 750000 │ 0 │ -3.0 │
│ 9949 │ 210750000 │ 9668 │ 9949 │ 210.75 │ 750000 │ 1750000 │ 750000 │ -0.75 │
└───────────────┴────────────┴──────────────────┴─────────────────┴───────────┴───────────────┴──────────────┴─────────┴───────┘
Problem:
Since the very first record has no previous record to compare against, it needs a different formula for it.
EstGrowth: TransactionId*0.003*250
for 348.00. You will notice mine is close,
347.25
Also, since the diff is incorrect on the first one, the rest of the records are also wrong. The first needs this: GrowthPool-EstGrowth
, which is -78.75
Expected results:
TransactionId,pig,GrowthPool,GrowthCarry,EstGrowth,Diff
464,1000065,269250000,250000,348,-78.75
645,1000065,134500000,1500000,135.75,-1.5
3880,1000065,2430000000,0,2426.25,2.25
9668,1000065,4338750000,750000,4341,-2.25
9949,1000065,210750000,1750000,210.75,-0.75
What needs to change to target the very first record? Do I need a where
in a nested select or something?
Thanks for the teaching!
(2) By Tim Streater (Clothears) on 2021-07-13 21:14:25 in reply to 1 [link] [source]
Would putting in a dummy first row with all zeros help, as part of the initialisation?
(3.1) By ThanksRyan on 2021-07-13 23:15:13 edited from 3.0 in reply to 2 [source]
hmmm
The next record, with the real value is even further off than before.
┌───────────────┬────────────┬──────────────────┬─────────────────┬───────────┬───────────────┬──────────────┬────────┬──────┐
│ TransactionId │ GrowthPool │ previous_transId │ current_transId │ EstGrowth │ Previous_GCOV │ Current_GCOV │ GCOVAW │ Diff │
├───────────────┼────────────┼──────────────────┼─────────────────┼───────────┼───────────────┼──────────────┼────────┼──────┤
│ 0 │ 0 │ │ 0 │ -0.75 │ │ 0 │ │ │
│ 464 │ 269250000 │ │ 464 │ 347.25 │ │ 250000 │ │ │
└───────────────┴────────────┴──────────────────┴─────────────────┴───────────┴───────────────┴──────────────┴────────┴──────┘
edit..actually the estgrowth is the same as mine.
(4) By ThanksRyan on 2021-07-15 15:12:10 in reply to 1 [link] [source]
Is my request unsolvable in SQL?
Re-stating it:
The very first record needs a different way to calculate EstGrowth and the diff than all the other following records.
Very first record for EstGrowth needs this calculation:
TransactionId*0.003*250
Very first record for diff needs this calculation:
growthpool/1000000-EstGrowth
Thanks for any pointers!
(5) By MBL (RoboManni) on 2021-07-15 16:44:57 in reply to 4 [link] [source]
Hello, you will realize that only the first row in your table has no value for the previous_transId. But then it is easy to not only show this in the table but also use it to select between the different formulas. If there is a previous then use the formula using it and else use the formula for the first record. I think the case will be your future helper!
SELECT *,
(GrowthPool-GCOVAW)/1000000-EstGrowth as Diff
FROM (
SELECT
TransactionId,
GrowthPool,
TransactionId as current_transId,
lag(TransactionId,1) OVER w as previous_transId,
case when lag(TransactionId,1) OVER w
then ((TransactionId) - (lag(TransactionId,1,1) OVER w))*0.003*250
else TransactionId *0.003*250
end as EstGrowth,
lag(GrowthCarry) OVER
w Previous_GCOV, GrowthCarry as Current_GCOV,
(lag(GrowthCarry) OVER w) as GCOVAW
FROM CalculateGrowth
WINDOW w AS (PARTITION BY PIG ORDER BY PIG)
) e
WHERE EstGrowth IS NOT NULL;
(6) By ThanksRyan on 2021-07-15 22:24:35 in reply to 5 [link] [source]
Hello MBL! My goodness, that is so awesome. Thank you for assisting and showing me the else
usage. I didn't consider it or know about it.
This now gets me the diff column correctly! The very minor thing that was aggravating was realizing I needed to do 1000000.0
, otherwise the values were 0.25 off.
SELECT *,
CASE
WHEN previous_transId IS NULL THEN growthpool/1000000.0-EstGrowth
ELSE (growthpool-Previous_GCOV)/1000000-EstGrowth
END AS diff
FROM
(SELECT GrowthPool,
TransactionId AS current_transId,
lag(GrowthCarry) OVER w Previous_GCOV,
GrowthCarry AS Current_GCOV,
(lag(GrowthCarry) OVER w) AS GCOVAW,
lag(TransactionId) OVER w AS previous_transId,
CASE
WHEN lag(TransactionId, 1) OVER w THEN ((TransactionId) - (lag(TransactionId) OVER w))*0.003*250
ELSE TransactionId *0.003*250
END AS EstGrowth
FROM CalculateGrowth2 WINDOW w AS (PARTITION BY PIG
ORDER BY PIG)) e
WHERE EstGrowth IS NOT NULL
Thank you for your help!
(7) By MBL (RoboManni) on 2021-07-16 07:08:19 in reply to 6 [link] [source]
you could probably get rid of the case exceptions if you provide a value 0.0 instead of the NULL for the case where you need the other formulas. Both would work as expected when a value of 0.0 would substitute the NULL ... isn't it?
select null as V1, ifnull(NULL,0) as V2good, ifnull(3.14,0) as V3, 5-null as V4, 5-ifnull(null,0) as V5
would produce as output:
V1 V2good V3 V4 V5
null 0 3.14 null 5
as you can see, with value 0 you would get the found value reduced by nothing (0.0) while the null would not (and did not) give you the expected results.
ifnull will also be your friend for future queries like case is already, I guess... have fun with SQL and SQLite3 !