SQL - get rid of the nested aggregate select-CodePudding

There is a table Payment, which for example tracks the amount of money user puts into account, simplified as

===================================
Id | UserId |  Amount |   PayDate |
===================================
 1 |     42 |      11 |  01.02.99 |
 2 |     42 |      31 |  05.06.99 |
 3 |     42 |      21 |  04.11.99 |
 4 |     24 |      12 |  05.11.99 |

What is need is to receive a table with balance before payment moment, eg:

===============================================
Id | UserId |   Amount |   PayDate |  Balance |
===============================================
 1 |     42 |       11 |  01.02.99 |        0 |
 2 |     42 |       31 |  05.06.99 |       11 |
 3 |     42 |       21 |  04.11.99 |       42 |
 4 |     24 |       12 |  05.11.99 |        0 |

Currently the select statement looks something like

SELECT 
    Id,
    UserId,
    Amount,
    PaidDate,
    (SELECT sum(amount) FROM Payments nestedp 
      WHERE nestedp.UserId = outerp.UserId AND
            nestedp.PayDate < outerp.PayDate) as Balance
FROM
    Payments outerp

How can I rewrite this select to get rid of the nested aggregate selection? The database in question is SQL Server 2019.

CodePudding user response：

You need to use cte with some custom logic to handle this type of problem.

WITH PaymentCte
AS (
    SELECT ROW_NUMBER() OVER (
            PARTITION BY UserId ORDER BY Id
            ) AS RowId
        ,Id
        ,UserId
        ,PayDate
        ,Amount
        ,SUM(Amount) OVER (
            PARTITION BY UserId ORDER BY Id
            ) AS Balance
    FROM Payment
    )
SELECT X.Id
    ,X.UserId
    ,X.Amount
    ,X.PayDate
    ,Y.Balance
FROM PaymentCte x
INNER JOIN PaymentCte y ON x.userId = y.UserId
    AND X.RowId = Y.RowId   1

UNION

SELECT X.Id
    ,X.UserId
    ,X.Amount
    ,X.PayDate
    ,0 AS Balance
FROM PaymentCte x
WHERE X.RowId = 1

This provides the desired output

CodePudding user response：

You can try the following using lag with a cumulative sum

 with b as (
  select * , isnull(lag(amount) over (partition by userid order by id),0) Amt
    from t
 )
 select Id, UserId, Amount, PayDate, 
   Sum(Amt) over (partition by userid order by id) Balance
 from b
 order by Id