3

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
  • 1
    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
  • 1
    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
4

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.