Mariadb – Reuse previous value in window function

mariadbwindow functions

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

Best Answer

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

Related Question