SQLite Forum

More math help and what I've learned
Login

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 [link] [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 [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 !