Get the previous calculated record divided by group

I'm strugging to build two calculated columns (named balance and avg). My original SQLite base is:

name    seq side    price   qnt
groupA  1   B       30      100
groupA  2   B       36      200
groupA  3   S       23      300
groupA  4   B       30      100
groupA  5   B       54      400
                
groupB  1   B       70      300
groupB  2   B       84      300
groupB  3   B       74      600
groupB  4   S       90      100

Rational for the 2 calculated new columns:

balance: the first line of each group (seq = 1), must have the same value of qnt. The next records follow the below formula (Excel-based scheme):

if(side="B"; `previous balance record`   `qnt`; `previous balance record` - `qnt`)

avg: the first line of each group (seq = 1), must have the same value of price. The next records follow the below formula (Excel-based scheme):

if(side="B"; ((`price` \* `qnt`)   (`previous balance record` \* `previous avg record`)) / (`qnt`   `previous balance record`); `previous avg record`)

Example with numbers (the second row of groupA is calculated below):

--> balance: 100   200 = 300
--> avg: ((36 * 200)   (100 * 30)) / (200   100) = 34

I think this problem must be solved with CTE because I need the previous record, which is in being calculated every time.

I wouldn't like to aggregate groups - my goal is to display every record.

Finally, this is what I expect as the output:

name    seq side    price   qnt     balance avg
groupA  1   B         30    100     100     30
groupA  2   B         36    200     300     34
groupA  3   S         23    300     0       34
groupA  4   B         30    100     100     30
groupA  5   B         54    400     500     49,2
                            
groupB  1   B         70    300     300     70
groupB  2   B         84    300     600     77
groupB  3   B         74    600     1200    75,5
groupB  4   S         90    100     1100    75,5

Thank you in advance!

Here is my dbfiddle test: https://dbfiddle.uk/TSarc3Nl

I tried to explain part of the coding (commented) to make things easier.

CodePudding user response：

The balance can be derived from a cumulative sum (using a case expression for when to deduct instead of add).

Then the recursive part just needs a case expression of its own.

WITH
  adjust_table AS
(
  SELECT
    *,
    SUM(
      CASE WHEN side='B'
           THEN qnt
           ELSE -qnt
      END
    ) 
      OVER (
        PARTITION BY name
            ORDER BY seq
      )
        AS balance
  FROM
    mytable
),
  recurse AS
(
  SELECT adjust_table.*, price AS avg FROM adjust_table WHERE seq = 1
 
  UNION ALL
  
  SELECT
    n.*,
    CASE WHEN n.side='B'
         THEN ((n.price * n.qnt * 1.0)   (s.balance * s.avg)) / (n.qnt   s.balance)
         ELSE s.avg
    END
      AS avg
  FROM
    adjust_table   n
  INNER JOIN
    recurse        s
      ON  n.seq  = s.seq   1
      AND n.name = s.name
)
SELECT
  *
FROM
  recurse
ORDER BY
  name,
  seq

https://dbfiddle.uk/mWz945pG

Though I'm not sure what the avg is meant to be doing, so it's possible I got that wrong and/or it could possibly be simplified to not need recursion.

NOTE: Never use , to join tables.