Reuse previous value in window function

Question

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

• mustaccio: Did you try it? Did it work?
• McNets: What this +1 means? Have a look at MariaDB Docs about window functions.
• Samuel: @McNets +1 is part of the computation I need to perform. I ll update the question to make it clear.
• ypercubeᵀᴹ: I suggest you add a sample of a table with a few rows and the wanted result.
• Colin 't Hart: So what's the value of that new column for the very first row? 0?

Answer 1

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