More math help and what I've learned
(1) By ThanksRyan on 2021-07-13 20:31:29
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]
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 [link]
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]
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]
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]
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]
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 !