Скачать или смотреть SQL One-Liner: Moving Average (Rolling Window)

Calculating a moving average (rolling window) is a core requirement in analytics—used for smoothing trends, stock prices, and KPI dashboards.

Long Way (Self-Join):

Joins each row to prior rows to compute a rolling window.

Works but very slow on large datasets.

Shortcut One-Liner (Window Function):

Uses AVG() OVER with ROWS BETWEEN … to directly compute rolling averages.

Efficient, readable, and widely supported in modern SQL engines.

👉 Use Cases:

7-day rolling sales average.

Stock market price trends.

Rolling user engagement metrics.

Queries:

✅ Long Way (Self-Join for 3-Day Moving Avg):
SELECT t1.order_date,
AVG(t2.amount) AS moving_avg
FROM orders t1
JOIN orders t2
ON t2.order_date BETWEEN t1.order_date - INTERVAL '2 day' AND t1.order_date
GROUP BY t1.order_date
ORDER BY t1.order_date;


Explanation:

For each order_date in t1, we join to all rows from t2 within the last 3 days.

Then compute the average over that window.

Correct but inefficient, since it repeatedly scans/join tables for each row.

✅ Shortcut One-Liner (Window Function):
SELECT order_date,
AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM orders
ORDER BY order_date;


Explanation:

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW defines a sliding window of 3 rows (the current day + 2 previous).

AVG(amount) computes the rolling average in one scan.

Much faster and elegant compared to self-joins.