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.
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
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.
EDIT: Recursion-less version
Use window functions to accumulate the balance, and also the total spent.
Then, use that to a enable the use of another window function to accumulate how much 'spend' is being 'recouped' by sales.
Your avg is then the adjusted spend divided by the current balance.
WITH
accumulate AS
(
SELECT
*,
SUM(
CASE WHEN side='B' THEN qnt ELSE -qnt END
)
OVER (
PARTITION BY name
ORDER BY seq
)
AS balance,
1.0
*
SUM(
CASE WHEN side='B' THEN price * qnt END
)
OVER (
PARTITION BY name
ORDER BY seq
)
AS total_spend
FROM
mytable
)
SELECT
*,
(
total_spend
-
SUM(
CASE WHEN side='S'
THEN qnt * total_spend / (balance+qnt)
ELSE 0
END
)
OVER (
PARTITION BY name
ORDER BY seq
)
-- cumulative sum for amount 'recouped' by sales
)
/ NULLIF(balance, 0)
AS avg
FROM
accumulate
Note: I still don't understand why you're calculating the avg price this way, but it matched your desired results/formulae, without recursion.