How to Calculate a 7-Day Moving Average in SQL?
7-Day Rolling Average is a SQL query that this sql query calculates the average of a specified value over the last 7 days, including the current day.. Formula Genius generates and validates this formula automatically from a plain-English prompt.
Rolling averages help smooth out data trends over time. This guide shows you how to compute a 7-day moving average using SQL.
The Formula
"Calculate a 7-day rolling average using AVG() OVER(ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)"
SELECT date, AVG(value) OVER(ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_average FROM your_table;This SQL query calculates the average of a specified value over the last 7 days, including the current day.
Step-by-Step Breakdown
- SELECT specifies the columns to retrieve, including the date and the moving average.
- AVG(value) calculates the average of the specified column over the defined window.
- OVER(ORDER BY date) defines the order of the rows based on the date column.
- ROWS BETWEEN 6 PRECEDING AND CURRENT ROW specifies the range of rows to include in the average calculation.
Edge Cases & Warnings
- If there are fewer than 7 days of data, the average will be calculated using the available data.
- Dates with no corresponding values will result in NULL in the moving average.
- If the dataset is empty, the query will return no results.
Examples
"Date: 2023-01-01, Value: 10; Date: 2023-01-02, Value: 20; Date: 2023-01-03, Value: 30; Date: 2023-01-04, Value: 40; Date: 2023-01-05, Value: 50; Date: 2023-01-06, Value: 60; Date: 2023-01-07, Value: 70"
Moving Average on 2023-01-07: 40"Date: 2023-01-01, Value: 5; Date: 2023-01-02, Value: 15; Date: 2023-01-03, Value: 25; Date: 2023-01-04, Value: 35; Date: 2023-01-05, Value: 45; Date: 2023-01-06, Value: 55; Date: 2023-01-07, Value: NULL"
Moving Average on 2023-01-07: 25Frequently Asked Questions
What is a moving average?
A moving average is a statistical calculation used to analyze data points by creating averages of different subsets of the complete dataset.
How does the window function work?
The window function allows you to perform calculations across a set of table rows that are related to the current row.
Can I change the number of days for the moving average?
Yes, you can adjust the number of preceding rows in the ROWS clause to calculate moving averages over different time frames.
Can't find what you need?
Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.