Sql Moving Average

SQL Query for 7 Day Rolling Average in SQL Server

Try:

select x.*,
       avg(dailyusage) over(partition by productid order by productid, date rows between 6 preceding and current row) as rolling_avg
  from (select productid, date, sum(usagecount) as dailyusage
          from tbl
         group by productid, date) x

Fiddle:

http://sqlfiddle.com/#!6/f674a7/4/0

Replace "avg(dailusage) over...." with sum (rather than avg) if what you really want is the sum for the past week. In your title you say you want the average but later you say you want the sum. The query should be the same other than that, so use whichever you actually want.

As was pointed out by Gordon this is basically the average of the past 6 dates in which the product was used, which might be more than just the past 6 days if there are days without any rows for that product on the table because it wasn't used at all. To get around that you could use a date table and your products table.

Calculating a monthly rolling/moving average in SQL

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;

rolling moving average in SQL

I think you can simplify the logic:

select date, avg(col) over (order by date rows between 21 preceding and 1 preceding)
from eurusd_ohlc

Use a case expression to get the nulls:

select date,
       (case when row_number() over (order by date) >= 21
             then avg(col) over (order by date rows between 21 preceding and 1 preceding)
        end) as mov_avg
from eurusd_ohlc

Getting moving average with window function

You may try this version:

WITH cte AS (
    SELECT visited_on, SUM(amount) AS amount
    FROM Customer
    GROUP BY visited_on
)

SELECT
    visited_on,
    SUM(amount) OVER (ORDER BY visited_on
                      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS amount,
    ROUND(AVG(amount) OVER (ORDER BY visited_on
                      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS average_amount
FROM cte
ORDER BY
    visited_on;

The logic here is to first aggregate the customers table by the visited date, to get the sum of the amounts per day. Then, we use your current logic to get the rolling 7 day sums and averages using analytic functions. The CTE I have used above is critical, without which you might not be taking the previous 7 days. For example, if a given day had 7 customers active, then your current logic would only take a single 7 for the rolling sum/average, not an actual 7 day window.

Calculating the moving average in Vertica for all stocks

Add a PARTITION BY clause to your call to AVG():

SELECT ts, bid,
       AVG(bid) OVER (PARTITION BY stock ORDER BY ts
                      RANGE BETWEEN INTERVAL '40 seconds' PRECEDING AND
                      CURRENT ROW)
FROM ticks
ORDER BY ts;

---

Related Topics