Calculating a monthly rolling/moving average in SQL-CodePudding

I am trying to calculate a monthly moving average for a set of data. I have the following code and sample data

SELECT DDA_Account, Current_Balance, Date_Last_Updated,
ROUND(AVG(Current_Balance) OVER (PARTITION BY DDA_Account ORDER BY Date_Last_Updated RANGE INTERVAL 30 DAY preceding),2) as avg_current_balance
FROM trb_acct_3
WHERE DDA_Account = '3921'
ORDER BY Date_Last_Updated ASC;

In the Excel snip, you can see how I am trying to take the average of all days within a specific month, such that on the first of the month (or the first day of the month where a value is available) the average is simply the value in the current balance column. The following day takes the average of the value for that day and the preceding day. This continues for all days of the month, resetting at the beginning of each month (or first day of the month where data is available). How would I go about fixing the code so that it only takes the days the particular month into consideration when computing the average? I thought using a RANGE would work, but I see that it isn't specific enough. Any help would be greatly appreciated.

CodePudding user response：

To reset at the beginning of each month, window function over (partition by dad_account, year-month order by date_last_updated)

with cte_acct as (
select dda_account,
       date_format(date_last_updated,'%Y-%m') as yr_mo,
       date_last_updated,
       current_balance
  from trb_acct_3)
select dda_account,
       yr_mo,
       date_last_updated,
       current_balance,
       round(avg(current_balance) over (partition by dda_account, yr_mo order by date_last_updated),2) as moving_avg
  from cte_acct
 order by date_last_updated;
 order by date_last_updated;