Lag function to create new column-CodePudding

I have a table with two columns and I want to create a new column based on adding both columns plus the result from the previous row. For example

 Xdate          X     Y
 2022-01-01     1     2
 2022-01-02     3     5
 2022-01-03     2     2
 2022-01-04     4     6

The result should look like this:

 Xdate         X      Y     Z
 2022-01-01    1      2     2 (1*2)
 2022-01-02    2      2     8 ((2 2)(from all previous row)) * 2)
 2022-01-03    2      1     12 ((2 8 2) (from all previous rows) * 1)
 2022-01-04    3      1     25 ((3 12 8 2) (from all previous rows) * 1)

So far I have this, but I can't figure out how to finish the query

  select
        *, 
        Lag(X Y, 1, X Y) OVER(ORDER BY Xdate) AS Z
  from temp

CodePudding user response：

You can do this by using a recursive CTE. I added a ROW_NUMBER function just in case that there are gaps in your dates.

WITH cte_rn
AS
(
    SELECT xdate,x,y,ROW_NUMBER() OVER (ORDER BY xdate) as rn
    FROM #temp 
)
,
cte_rec
AS
( 
    SELECT cte_rn.xdate, cte_rn.x,cte_rn.y,cte_rn.rn,cte_rn.x * cte_rn.y as z , cte_rn.x * cte_rn.y as Zsum
    FROM cte_rn 
    WHERE rn = 1
    UNION ALL
    SELECT t.xdate,t.x,t.y,t.rn,(t.x   r.Zsum) * t.y as z ,r.Zsum (t.x   r.Zsum) * t.y as Zsum
    FROM cte_rec r
    JOIN cte_rn t
        ON r.rn   1 = t.rn
)
SELECT *
FROM cte_rec;