Moving Average (Window)
Moving Average (Window) is a SQL function in the Window Functions category. Calculates a moving/rolling average over a specified number of preceding rows. Common in financial analysis. The syntax is AVG(column) OVER (ORDER BY date ROWS BETWEEN N PRECEDING AND CURRENT ROW). It accepts column. It returns moving average per row. A typical example: SELECT trade_date, price, AVG(price) OVER (ORDER BY trade_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7day FROM stock_prices; -- 7-day simple moving average SELECT trade_date, price, AVG(price) OVER (ORDER BY trade_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS ma_30day FROM stock_prices; -- 30-day simple moving average A close relative is ROW_NUMBER(), which assigns a unique sequential integer to each row within its partition (1, 2, 3...). A close relative is RANK(), which similar to ROW_NUMBER but gives the same rank to equal values. Leaves gaps in ranking. A close relative is DENSE_RANK(), which like RANK but without gaps. Equal values get the same rank, and the next number follows sequentially.