# Reuse previous value in window function

Is it possible to use `LAG()` to get the previous value of the current column?

The computation I need to do is the following:

``````value(n) = (value(n-1) + 1) * column(n)
``````

meaning I already have a column, and I want to create a new one by multiplying the column by the previous value of the new column I am creating + 1...

First value would be 1.

Something like:

``````SELECT (LAG(col1) OVER (ORDER BY date) + 1) * col2 AS col1
``````

The current cell value is defined by the previous value times another column.

If I try this I get undefined column col1 (of course since I am creating it in my query...)

In Excel it is extremely easily done with the following formula:

``````B2 = (B1 + 1) * A2
``````

So, if the table has:

``````  date     | col2
------------------
2018-12-01 |  2
2018-12-02 |  3
2018-12-03 |  1
2018-12-04 |  4
2018-12-05 |  1.5
``````

the result should be:

``````  date     | col2  | col1
-----------------------------
2018-12-01 |  2    |    1
2018-12-02 |  3    |    6           (1+1) * 3
2018-12-03 |  1    |    7           (6+1) * 1
2018-12-04 |  4    |   32           (7+1) * 4
2018-12-05 |  1.5  |   49.5        (32+1) * 1.5
``````
• Did you try it? Did it work? – mustaccio Dec 19 '18 at 16:58
• What this `+1` means? Have a look at MariaDB Docs about window functions. – McNets Dec 19 '18 at 20:20
• @McNets +1 is part of the computation I need to perform. I ll update the question to make it clear. – Samuel Dec 20 '18 at 9:18
• I suggest you add a sample of a table with a few rows and the wanted result. – ypercubeᵀᴹ Dec 20 '18 at 11:34
• So what's the value of that new column for the very first row? 0? – Colin 't Hart Dec 20 '18 at 13:19

This likely needs a recursive CTE, since you want the value of the column to depend on the value of the previous (calculated) value of the same column.
(Recursive CTEs are available in MariaDB since version 10.2.2)

I assume that `(date)` is unique, otherwise the result will be non-deterministic:

``````WITH RECURSIVE
t AS
(
SELECT
date, col2,
ROW_NUMBER() OVER (ORDER BY date) AS rn
FROM
tableX
),
cte AS
(
SELECT
date, col2, rn,
CAST(1 AS DECIMAL(10,3)) AS col1     -- starting value
FROM
t
WHERE
rn = 1

UNION ALL

SELECT
t.date, t.col2, t.rn,
(c.col1 + 1) * t.col2                -- calculate col1
FROM
t JOIN cte AS c
ON t.rn = c.rn + 1
)
SELECT date, col2, col1
FROM cte ;
``````

Tested at: dbfiddle.uk