To calculate the moving average in SQL, use the AVG() function combined with the OVER() window function and define the ROWS BETWEEN clause to specify the range of rows participating in the calculation. 1. Use AVG(value) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) to calculate a 7-day moving average including the current day; 2. If the current day is excluded, use ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING; 3. When the data has a day interval, the missing date should be filled first to ensure accuracy; 4. The window size can be adjusted according to different needs, such as 30 days or 52 weeks; 5. For multi-time series data, it can be calculated by group partitioning through the PARTITION BY clause.
Calculating a moving average in SQL is pretty straightforward once you understand how window functions work. The key is to use the AVG() function together with an OVER() clause that defines the window of rows you want to include in the average.
Use the AVG() function with a window frame
To calculate a moving average, you need to define a window of rows relative to the current row. This is done using the ROWS BETWEEN clause inside the OVER() function.
Here's a basic example:
SELECT
date,
value,
AVG(value) OVER (
ORDER BY date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS seven_day_avg
FROM data_table; This gives you a 7-day moving average , including the current day and the previous 6 days.
If you want to exclude the current row (eg, average of the last 7 days not including today), change it to:
ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING
Make sure your data is ordered correctly—usually by date or timestamp.
Handle gaps in dates carefully
If your dataset has missing dates (for example, weekends in business data), using ROWS BETWEEN might skip some actual days and give you an inaccurate average.
A better approach is to convert your data into a daily format first, filling in missing dates with zeros or NULLs, then apply the window function.
You can do this using a date dimension table or generate a series of dates depending on your SQL dialect (like in PostgreSQL with generate_series() or BigQuery with DATE_ADD() and UNNEST() ).
Once your data has no gaps, you can safely use:
AVG(value) OVER ( ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW )
Otherwise, you might end up averaged fewer rows than expected when dates are missing.
Adjust the window size based on your needs
The number of rows you include depends on what kind of smoothing you're after. Common settings include:
- 7-day moving average for weekly trends
- 30-day moving average for monthly patterns
- 52-week moving average for annual comparisons
Just tweak the ROWS BETWEEN clause accordingly. For example:
30-day average:
ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
Rolling 12-month average:
ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
Also consider partitioning by group if you have multiple time series (like per user or product):
AVG(value) OVER ( PARTITION BY category ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW )
This way, the average resets for each group.
Depending on your SQL engine, you might also explore built-in functions like MOVING_AVG() in Oracle or specific time-series extensions, but sticking with standard window functions keeps things portable.
Basically that's it.
The above is the detailed content of How to calculate a moving average in SQL. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undress AI Tool
Undress images for free
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Defining Database Schemas with SQL CREATE TABLE Statements
Jul 05, 2025 am 01:55 AM
In database design, use the CREATETABLE statement to define table structures and constraints to ensure data integrity. 1. Each table needs to specify the field, data type and primary key, such as user_idINTPRIMARYKEY; 2. Add NOTNULL, UNIQUE, DEFAULT and other constraints to improve data consistency, such as emailVARCHAR(255)NOTNULLUNIQUE; 3. Use FOREIGNKEY to establish the relationship between tables, such as orders table references the primary key of the users table through user_id.
Key Differences Between SQL Functions and Stored Procedures.
Jul 05, 2025 am 01:38 AM
SQLfunctionsandstoredproceduresdifferinpurpose,returnbehavior,callingcontext,andsecurity.1.Functionsreturnasinglevalueortableandareusedforcomputationswithinqueries,whileproceduresperformcomplexoperationsanddatamodifications.2.Functionsmustreturnavalu
Using SQL LAG and LEAD functions for time-series analysis.
Jul 05, 2025 am 01:34 AM
LAG and LEAD in SQL are window functions used to compare the current row with the previous row data. 1. LAG (column, offset, default) is used to obtain the data of the offset line before the current line. The default value is 1. If there is no previous line, the default is returned; 2. LEAD (column, offset, default) is used to obtain the subsequent line. They are often used in time series analysis, such as calculating sales changes, user behavior intervals, etc. For example, obtain the sales of the previous day through LAG (sales, 1, 0) and calculate the difference and growth rate; obtain the next visit time through LEAD (visit_date) and calculate the number of days between them in combination with DATEDIFF;
Can You Provide Code Examples Demonstrating Pattern Matching in SQL?
Jul 04, 2025 am 02:51 AM
Pattern matching functions in SQL include LIKE operator and REGEXP regular expression matching. 1. The LIKE operator uses wildcards '%' and '_' to perform pattern matching at basic and specific locations. 2.REGEXP is used for more complex string matching, such as the extraction of email formats and log error messages. Pattern matching is very useful in data analysis and processing, but attention should be paid to query performance issues.
How to find columns with a specific name in a SQL database?
Jul 07, 2025 am 02:08 AM
To find columns with specific names in SQL databases, it can be achieved through system information schema or the database comes with its own metadata table. 1. Use INFORMATION_SCHEMA.COLUMNS query is suitable for most SQL databases, such as MySQL, PostgreSQL and SQLServer, and matches through SELECTTABLE_NAME, COLUMN_NAME and combined with WHERECOLUMN_NAMELIKE or =; 2. Specific databases can query system tables or views, such as SQLServer uses sys.columns to combine sys.tables for JOIN query, PostgreSQL can be used through inf
How to create a user and grant permissions in SQL
Jul 05, 2025 am 01:51 AM
Create a user using the CREATEUSER command, for example, MySQL: CREATEUSER'new_user'@'host'IDENTIFIEDBY'password'; PostgreSQL: CREATEUSERnew_userWITHPASSWORD'password'; 2. Grant permission to use the GRANT command, such as GRANTSELECTONdatabase_name.TO'new_user'@'host'; 3. Revoke permission to use the REVOKE command, such as REVOKEDELETEONdatabase_name.FROM'new_user
What is the SQL LIKE Operator and How Do I Use It Effectively?
Jul 05, 2025 am 01:18 AM
TheSQLLIKEoperatorisusedforpatternmatchinginSQLqueries,allowingsearchesforspecifiedpatternsincolumns.Ituseswildcardslike'%'forzeroormorecharactersand'_'forasinglecharacter.Here'showtouseiteffectively:1)UseLIKEwithwildcardstofindpatterns,e.g.,'J%'forn
How to backup and restore a SQL database
Jul 06, 2025 am 01:04 AM
Backing up and restoring SQL databases is a key operation to prevent data loss and system failure. 1. Use SSMS to visually back up the database, select complete and differential backup types and set a secure path; 2. Use T-SQL commands to achieve flexible backups, supporting automation and remote execution; 3. Recovering the database can be completed through SSMS or RESTOREDATABASE commands, and use WITHREPLACE and SINGLE_USER modes if necessary; 4. Pay attention to permission configuration, path access, avoid overwriting the production environment and verifying backup integrity. Mastering these methods can effectively ensure data security and business continuity.
