sum values up to current cursor for average saldo analytics

Question

I made really simple example table with columns date and credit, so we can sum all credit to get account saldo of the account. I can sum all credit values to get saldo, but that is not what I want. I want to calculate average saldo, so in order to do that I need to use RangeDate table with date of every day and query with this logic:

SELECT  DRA.date, SUM(ACB.credit)
FROM    AccountBalance  ACB
JOIN    DateRange   DRA ON ACB.date <= DRA.date
GROUP   BY DRA.date

http://sqlfiddle.com/#!18/88afa/10

the problem is when using this program on a larger range of dates, like whole year for example.

Instruction is telling SQL Engine to sum up all rows of credit before the current date including credit of the current row date (where the cursor is in that moment (JOIN... ACB.date <= DRA.date)), in order to get accounts credit for that day.

This is inefficient and slow for big tables because that sum already exists in the row 1 level before, and I would like to tell SQL Engine to take that sum and only add the one row of credit that it is in.

Somone told me that I should use LAG function, but i need an example first.

Answer 1

I think you simply need an analyitc function -

SELECT DRA.date,
       SUM(ACB.saldo) OVER (ORDER BY DRA.date)
  FROM DateRange DRA
  LEFT JOIN AccountBalance ACB ON ACB.date = DRA.date;

Demo.