SQL Server T-SQL Code to Calculate a Moving Average

By:   |   Updated: 2016-03-08   |   Comments (1)   |   Related: > Functions User Defined UDF


Problem

How can I smooth the data in a column with a moving average in T-SQL? Can you please walk through an example in SQL Server with T-SQL code? How can we validate the results?

Solution

Time-series data can be inherently noisy and a good way to smooth out the data is to calculate a moving average. There are a number of ways to calculate a moving average in T-SQL, but in this tip we will look at a way to calculate a moving average that sets the averaging window x number of rows behind and x number of rows ahead of the current data row. The advantage of this is that there is no lag in the average value returned and the moving average value is on the same row with its current value.

Let's start by creating a table and loading some data using the T-SQL below. We have 361 data points that create a noisy sine wave. 

CREATE TABLE [dbo].[tblSignal](
	[pkID] [int] identity(1,1) Primary key,
	[seconds] int,
	[DataValue] [numeric](5, 4) NULL,
) ON [PRIMARY]

insert into dbo.tblSignal values (0, 0.3771)
insert into dbo.tblSignal values (1, 0.4269)
insert into dbo.tblSignal values (2, 0.465)
insert into dbo.tblSignal values (3, 0.5183)
insert into dbo.tblSignal values (4, 0.5852)
insert into dbo.tblSignal values (5, 0.2818)
insert into dbo.tblSignal values (6, 0.4245)
insert into dbo.tblSignal values (7, 0.4524)
insert into dbo.tblSignal values (8, 0.6996)
insert into dbo.tblSignal values (9, 0.2255)
insert into dbo.tblSignal values (10, 0.5205)
insert into dbo.tblSignal values (11, 0.273)
insert into dbo.tblSignal values (12, 0.7336)
insert into dbo.tblSignal values (13, 0.6406)
insert into dbo.tblSignal values (14, 0.5861)
insert into dbo.tblSignal values (15, 0.6271)
insert into dbo.tblSignal values (16, 0.3868)
insert into dbo.tblSignal values (17, 0.3727)
insert into dbo.tblSignal values (18, 0.6603)
insert into dbo.tblSignal values (19, 0.4222)
insert into dbo.tblSignal values (20, 0.7588)
insert into dbo.tblSignal values (21, 0.6774)
insert into dbo.tblSignal values (22, 0.792)
insert into dbo.tblSignal values (23, 0.5404)
insert into dbo.tblSignal values (24, 0.8659)
insert into dbo.tblSignal values (25, 0.7816)
insert into dbo.tblSignal values (26, 0.7791)
insert into dbo.tblSignal values (27, 0.7099)
insert into dbo.tblSignal values (28, 0.9675)
insert into dbo.tblSignal values (29, 0.7258)
insert into dbo.tblSignal values (30, 0.8822)
insert into dbo.tblSignal values (31, 0.7464)
insert into dbo.tblSignal values (32, 1.0435)
insert into dbo.tblSignal values (33, 0.8251)
insert into dbo.tblSignal values (34, 0.8111)
insert into dbo.tblSignal values (35, 0.8545)
insert into dbo.tblSignal values (36, 1.0684)
insert into dbo.tblSignal values (37, 1.1737)
insert into dbo.tblSignal values (38, 0.804)
insert into dbo.tblSignal values (39, 0.8346)
insert into dbo.tblSignal values (40, 0.8499)
insert into dbo.tblSignal values (41, 0.7992)
insert into dbo.tblSignal values (42, 0.8486)
insert into dbo.tblSignal values (43, 1.0552)
insert into dbo.tblSignal values (44, 0.7473)
insert into dbo.tblSignal values (45, 0.8306)
insert into dbo.tblSignal values (46, 1.2564)
insert into dbo.tblSignal values (47, 0.9992)
insert into dbo.tblSignal values (48, 0.9173)
insert into dbo.tblSignal values (49, 1.078)
insert into dbo.tblSignal values (50, 1.2438)
insert into dbo.tblSignal values (51, 1.1538)
insert into dbo.tblSignal values (52, 1.2856)
insert into dbo.tblSignal values (53, 1.2882)
insert into dbo.tblSignal values (54, 1.1513)
insert into dbo.tblSignal values (55, 0.9406)
insert into dbo.tblSignal values (56, 1.3354)
insert into dbo.tblSignal values (57, 0.9755)
insert into dbo.tblSignal values (58, 0.9186)
insert into dbo.tblSignal values (59, 0.8816)
insert into dbo.tblSignal values (60, 1.0657)
insert into dbo.tblSignal values (61, 1.3783)
insert into dbo.tblSignal values (62, 0.9863)
insert into dbo.tblSignal values (63, 1.1066)
insert into dbo.tblSignal values (64, 0.9999)
insert into dbo.tblSignal values (65, 1.4005)
insert into dbo.tblSignal values (66, 1.0463)
insert into dbo.tblSignal values (67, 1.4459)
insert into dbo.tblSignal values (68, 1.033)
insert into dbo.tblSignal values (69, 1.3765)
insert into dbo.tblSignal values (70, 1.368)
insert into dbo.tblSignal values (71, 1.4625)
insert into dbo.tblSignal values (72, 1.1863)
insert into dbo.tblSignal values (73, 1.3321)
insert into dbo.tblSignal values (74, 1.3762)
insert into dbo.tblSignal values (75, 1.1192)
insert into dbo.tblSignal values (76, 1.2211)
insert into dbo.tblSignal values (77, 1.1272)
insert into dbo.tblSignal values (78, 1.2035)
insert into dbo.tblSignal values (79, 1.1074)
insert into dbo.tblSignal values (80, 1.1343)
insert into dbo.tblSignal values (81, 1.3174)
insert into dbo.tblSignal values (82, 1.2695)
insert into dbo.tblSignal values (83, 1.1693)
insert into dbo.tblSignal values (84, 1.1959)
insert into dbo.tblSignal values (85, 1.1736)
insert into dbo.tblSignal values (86, 1.0943)
insert into dbo.tblSignal values (87, 1.3858)
insert into dbo.tblSignal values (88, 1.2898)
insert into dbo.tblSignal values (89, 1.2045)
insert into dbo.tblSignal values (90, 1.5238)
insert into dbo.tblSignal values (91, 1.441)
insert into dbo.tblSignal values (92, 1.1473)
insert into dbo.tblSignal values (93, 1.0904)
insert into dbo.tblSignal values (94, 1.4802)
insert into dbo.tblSignal values (95, 1.1227)
insert into dbo.tblSignal values (96, 1.4105)
insert into dbo.tblSignal values (97, 1.2044)
insert into dbo.tblSignal values (98, 1.3999)
insert into dbo.tblSignal values (99, 1.2957)
insert into dbo.tblSignal values (100, 1.3291)
insert into dbo.tblSignal values (101, 1.4418)
insert into dbo.tblSignal values (102, 1.2009)
insert into dbo.tblSignal values (103, 1.4454)
insert into dbo.tblSignal values (104, 1.4055)
insert into dbo.tblSignal values (105, 1.2755)
insert into dbo.tblSignal values (106, 1.2186)
insert into dbo.tblSignal values (107, 0.9947)
insert into dbo.tblSignal values (108, 1.2108)
insert into dbo.tblSignal values (109, 1.2853)
insert into dbo.tblSignal values (110, 1.3347)
insert into dbo.tblSignal values (111, 1.0559)
insert into dbo.tblSignal values (112, 1.4971)
insert into dbo.tblSignal values (113, 1.2811)
insert into dbo.tblSignal values (114, 1.3616)
insert into dbo.tblSignal values (115, 1.19)
insert into dbo.tblSignal values (116, 1.1698)
insert into dbo.tblSignal values (117, 0.9984)
insert into dbo.tblSignal values (118, 1.1162)
insert into dbo.tblSignal values (119, 1.2217)
insert into dbo.tblSignal values (120, 1.2557)
insert into dbo.tblSignal values (121, 1.2627)
insert into dbo.tblSignal values (122, 1.0118)
insert into dbo.tblSignal values (123, 1.3298)
insert into dbo.tblSignal values (124, 1.2287)
insert into dbo.tblSignal values (125, 1.1473)
insert into dbo.tblSignal values (126, 1.2239)
insert into dbo.tblSignal values (127, 0.8689)
insert into dbo.tblSignal values (128, 1.0743)
insert into dbo.tblSignal values (129, 1.2055)
insert into dbo.tblSignal values (130, 0.911)
insert into dbo.tblSignal values (131, 0.8509)
insert into dbo.tblSignal values (132, 1.0265)
insert into dbo.tblSignal values (133, 1.0541)
insert into dbo.tblSignal values (134, 1.2538)
insert into dbo.tblSignal values (135, 1.2095)
insert into dbo.tblSignal values (136, 0.7238)
insert into dbo.tblSignal values (137, 0.8915)
insert into dbo.tblSignal values (138, 1.0582)
insert into dbo.tblSignal values (139, 0.9251)
insert into dbo.tblSignal values (140, 0.7637)
insert into dbo.tblSignal values (141, 1.1797)
insert into dbo.tblSignal values (142, 1.1286)
insert into dbo.tblSignal values (143, 1.1098)
insert into dbo.tblSignal values (144, 0.7774)
insert into dbo.tblSignal values (145, 0.8132)
insert into dbo.tblSignal values (146, 0.796)
insert into dbo.tblSignal values (147, 0.9448)
insert into dbo.tblSignal values (148, 0.6813)
insert into dbo.tblSignal values (149, 0.7895)
insert into dbo.tblSignal values (150, 0.7551)
insert into dbo.tblSignal values (151, 0.9101)
insert into dbo.tblSignal values (152, 0.6914)
insert into dbo.tblSignal values (153, 0.5261)
insert into dbo.tblSignal values (154, 0.4941)
insert into dbo.tblSignal values (155, 0.5506)
insert into dbo.tblSignal values (156, 0.6568)
insert into dbo.tblSignal values (157, 0.7158)
insert into dbo.tblSignal values (158, 0.9108)
insert into dbo.tblSignal values (159, 0.6261)
insert into dbo.tblSignal values (160, 0.4265)
insert into dbo.tblSignal values (161, 0.5208)
insert into dbo.tblSignal values (162, 0.5749)
insert into dbo.tblSignal values (163, 0.6814)
insert into dbo.tblSignal values (164, 0.5957)
insert into dbo.tblSignal values (165, 0.5982)
insert into dbo.tblSignal values (166, 0.7687)
insert into dbo.tblSignal values (167, 0.5055)
insert into dbo.tblSignal values (168, 0.4854)
insert into dbo.tblSignal values (169, 0.6266)
insert into dbo.tblSignal values (170, 0.3235)
insert into dbo.tblSignal values (171, 0.5409)
insert into dbo.tblSignal values (172, 0.3573)
insert into dbo.tblSignal values (173, 0.273)
insert into dbo.tblSignal values (174, 0.6048)
insert into dbo.tblSignal values (175, 0.1766)
insert into dbo.tblSignal values (176, 0.5901)
insert into dbo.tblSignal values (177, 0.1958)
insert into dbo.tblSignal values (178, 0.1268)
insert into dbo.tblSignal values (179, 0.2386)
insert into dbo.tblSignal values (180, 0.1361)
insert into dbo.tblSignal values (181, 0.2782)
insert into dbo.tblSignal values (182, 0.3438)
insert into dbo.tblSignal values (183, 0.1664)
insert into dbo.tblSignal values (184, 0.207)
insert into dbo.tblSignal values (185, 0.3855)
insert into dbo.tblSignal values (186, 0.06)
insert into dbo.tblSignal values (187, 0.1377)
insert into dbo.tblSignal values (188, -0.0077)
insert into dbo.tblSignal values (189, 0.0478)
insert into dbo.tblSignal values (190, 0.1074)
insert into dbo.tblSignal values (191, 0.2865)
insert into dbo.tblSignal values (192, 0.071)
insert into dbo.tblSignal values (193, 0.2232)
insert into dbo.tblSignal values (194, -0.06)
insert into dbo.tblSignal values (195, -0.1345)
insert into dbo.tblSignal values (196, -0.0558)
insert into dbo.tblSignal values (197, 0.2141)
insert into dbo.tblSignal values (198, 0.1393)
insert into dbo.tblSignal values (199, 0.0137)
insert into dbo.tblSignal values (200, 0.1765)
insert into dbo.tblSignal values (201, -0.3035)
insert into dbo.tblSignal values (202, -0.0003)
insert into dbo.tblSignal values (203, -0.1759)
insert into dbo.tblSignal values (204, -0.3253)
insert into dbo.tblSignal values (205, -0.1738)
insert into dbo.tblSignal values (206, -0.2831)
insert into dbo.tblSignal values (207, -0.1472)
insert into dbo.tblSignal values (208, -0.1198)
insert into dbo.tblSignal values (209, 0.0185)
insert into dbo.tblSignal values (210, -0.448)
insert into dbo.tblSignal values (211, 0.0179)
insert into dbo.tblSignal values (212, -0.4676)
insert into dbo.tblSignal values (213, -0.2307)
insert into dbo.tblSignal values (214, -0.429)
insert into dbo.tblSignal values (215, -0.1)
insert into dbo.tblSignal values (216, -0.4845)
insert into dbo.tblSignal values (217, -0.5412)
insert into dbo.tblSignal values (218, -0.3929)
insert into dbo.tblSignal values (219, -0.3688)
insert into dbo.tblSignal values (220, -0.4246)
insert into dbo.tblSignal values (221, -0.4452)
insert into dbo.tblSignal values (222, -0.1566)
insert into dbo.tblSignal values (223, -0.6328)
insert into dbo.tblSignal values (224, -0.2301)
insert into dbo.tblSignal values (225, -0.3478)
insert into dbo.tblSignal values (226, -0.3012)
insert into dbo.tblSignal values (227, -0.5608)
insert into dbo.tblSignal values (228, -0.6242)
insert into dbo.tblSignal values (229, -0.5741)
insert into dbo.tblSignal values (230, -0.3676)
insert into dbo.tblSignal values (231, -0.5164)
insert into dbo.tblSignal values (232, -0.6853)
insert into dbo.tblSignal values (233, -0.6605)
insert into dbo.tblSignal values (234, -0.6279)
insert into dbo.tblSignal values (235, -0.3706)
insert into dbo.tblSignal values (236, -0.5354)
insert into dbo.tblSignal values (237, -0.3651)
insert into dbo.tblSignal values (238, -0.5604)
insert into dbo.tblSignal values (239, -0.7316)
insert into dbo.tblSignal values (240, -0.8136)
insert into dbo.tblSignal values (241, -0.4135)
insert into dbo.tblSignal values (242, -0.545)
insert into dbo.tblSignal values (243, -0.8561)
insert into dbo.tblSignal values (244, -0.4762)
insert into dbo.tblSignal values (245, -0.4514)
insert into dbo.tblSignal values (246, -0.333)
insert into dbo.tblSignal values (247, -0.357)
insert into dbo.tblSignal values (248, -0.4145)
insert into dbo.tblSignal values (249, -0.8521)
insert into dbo.tblSignal values (250, -0.5511)
insert into dbo.tblSignal values (251, -0.5649)
insert into dbo.tblSignal values (252, -0.7525)
insert into dbo.tblSignal values (253, -0.8445)
insert into dbo.tblSignal values (254, -0.8701)
insert into dbo.tblSignal values (255, -0.7655)
insert into dbo.tblSignal values (256, -0.7346)
insert into dbo.tblSignal values (257, -0.8028)
insert into dbo.tblSignal values (258, -0.8479)
insert into dbo.tblSignal values (259, -0.5328)
insert into dbo.tblSignal values (260, -0.4388)
insert into dbo.tblSignal values (261, -0.7197)
insert into dbo.tblSignal values (262, -0.5667)
insert into dbo.tblSignal values (263, -0.6864)
insert into dbo.tblSignal values (264, -0.8983)
insert into dbo.tblSignal values (265, -0.5521)
insert into dbo.tblSignal values (266, -0.8473)
insert into dbo.tblSignal values (267, -0.7625)
insert into dbo.tblSignal values (268, -0.9822)
insert into dbo.tblSignal values (269, -0.7773)
insert into dbo.tblSignal values (270, -0.7563)
insert into dbo.tblSignal values (271, -0.663)
insert into dbo.tblSignal values (272, -0.762)
insert into dbo.tblSignal values (273, -0.7541)
insert into dbo.tblSignal values (274, -0.4412)
insert into dbo.tblSignal values (275, -0.7463)
insert into dbo.tblSignal values (276, -0.8597)
insert into dbo.tblSignal values (277, -0.4829)
insert into dbo.tblSignal values (278, -0.7751)
insert into dbo.tblSignal values (279, -0.7288)
insert into dbo.tblSignal values (280, -0.9518)
insert into dbo.tblSignal values (281, -0.5296)
insert into dbo.tblSignal values (282, -0.946)
insert into dbo.tblSignal values (283, -0.5145)
insert into dbo.tblSignal values (284, -0.6696)
insert into dbo.tblSignal values (285, -0.8618)
insert into dbo.tblSignal values (286, -0.6122)
insert into dbo.tblSignal values (287, -0.8496)
insert into dbo.tblSignal values (288, -0.5478)
insert into dbo.tblSignal values (289, -0.6331)
insert into dbo.tblSignal values (290, -0.8953)
insert into dbo.tblSignal values (291, -0.5753)
insert into dbo.tblSignal values (292, -0.4512)
insert into dbo.tblSignal values (293, -0.5599)
insert into dbo.tblSignal values (294, -0.7589)
insert into dbo.tblSignal values (295, -0.4414)
insert into dbo.tblSignal values (296, -0.7998)
insert into dbo.tblSignal values (297, -0.5537)
insert into dbo.tblSignal values (298, -0.8427)
insert into dbo.tblSignal values (299, -0.8093)
insert into dbo.tblSignal values (300, -0.7014)
insert into dbo.tblSignal values (301, -0.7519)
insert into dbo.tblSignal values (302, -0.406)
insert into dbo.tblSignal values (303, -0.5123)
insert into dbo.tblSignal values (304, -0.5369)
insert into dbo.tblSignal values (305, -0.635)
insert into dbo.tblSignal values (306, -0.6944)
insert into dbo.tblSignal values (307, -0.4893)
insert into dbo.tblSignal values (308, -0.502)
insert into dbo.tblSignal values (309, -0.3416)
insert into dbo.tblSignal values (310, -0.4279)
insert into dbo.tblSignal values (311, -0.4795)
insert into dbo.tblSignal values (312, -0.2992)
insert into dbo.tblSignal values (313, -0.7197)
insert into dbo.tblSignal values (314, -0.4745)
insert into dbo.tblSignal values (315, -0.3843)
insert into dbo.tblSignal values (316, -0.4044)
insert into dbo.tblSignal values (317, -0.4249)
insert into dbo.tblSignal values (318, -0.4615)
insert into dbo.tblSignal values (319, -0.4149)
insert into dbo.tblSignal values (320, -0.5604)
insert into dbo.tblSignal values (321, -0.5044)
insert into dbo.tblSignal values (322, -0.1047)
insert into dbo.tblSignal values (323, -0.388)
insert into dbo.tblSignal values (324, -0.1975)
insert into dbo.tblSignal values (325, -0.1061)
insert into dbo.tblSignal values (326, -0.1585)
insert into dbo.tblSignal values (327, -0.5375)
insert into dbo.tblSignal values (328, -0.4407)
insert into dbo.tblSignal values (329, -0.2131)
insert into dbo.tblSignal values (330, 0.0557)
insert into dbo.tblSignal values (331, -0.2901)
insert into dbo.tblSignal values (332, 0.089)
insert into dbo.tblSignal values (333, -0.1802)
insert into dbo.tblSignal values (334, -0.1646)
insert into dbo.tblSignal values (335, -0.078)
insert into dbo.tblSignal values (336, -0.0664)
insert into dbo.tblSignal values (337, -0.2691)
insert into dbo.tblSignal values (338, -0.3317)
insert into dbo.tblSignal values (339, -0.219)
insert into dbo.tblSignal values (340, -0.0348)
insert into dbo.tblSignal values (341, -0.1957)
insert into dbo.tblSignal values (342, -0.177)
insert into dbo.tblSignal values (343, 0.1408)
insert into dbo.tblSignal values (344, 0.1085)
insert into dbo.tblSignal values (345, -0.2296)
insert into dbo.tblSignal values (346, 0.2779)
insert into dbo.tblSignal values (347, 0.2403)
insert into dbo.tblSignal values (348, 0.151)
insert into dbo.tblSignal values (349, 0.0188)
insert into dbo.tblSignal values (350, 0.1259)
insert into dbo.tblSignal values (351, 0.0801)
insert into dbo.tblSignal values (352, -0.0447)
insert into dbo.tblSignal values (353, 0.272)
insert into dbo.tblSignal values (354, 0.0063)
insert into dbo.tblSignal values (355, 0.2424)
insert into dbo.tblSignal values (356, 0.2619)
insert into dbo.tblSignal values (357, 0.2188)
insert into dbo.tblSignal values (358, 0.3627)
insert into dbo.tblSignal values (359, 0.4102)
insert into dbo.tblSignal values (360, 0.4993)

After loading the data, we will execute the following T-SQL code to select all of the columns along with the moving average value. In the code below, the moving average window size is 15 (7 rows preceding the current row, plus the current row, plus the 7 following rows). The moving average of the DataValue column is returned as the MovingAverageWindowSize15 column. The ORDER BY clause is extremely important to keep the data in the proper sorted order. 

select pkID, Seconds, DataValue,
avg([DataValue]) OVER (ORDER BY pkID
ROWS BETWEEN 7 PRECEDING AND 7 FOLLOWING) as MovingAverageWindowSize15
from tblSignal
order by pkID;

We can copy and paste the results into Excel to validate the calculation is correct. In the image below, the window starts in cell C3 and ends at C17. The moving average as calculated by the T-SQL in this tip appears in cell D10. The average as calculated by Excel is at the bottom and it is equal to the value in D10.

Validate the results in Excel

In the figure below, we can see the original data values plotted in blue with the moving average plotted in red.

Plot of the DataValue and the moving average
Next Steps

Adjust the size of the moving average window to see how the plot changes. Also, be sure to check out these other tips on T-SQL from mssqltips.com:



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Dallas Snider Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2016-03-08

Comments For This Article




Thursday, August 24, 2017 - 11:13:28 AM - Daniel Bertolini Back To Top (65243)

 Thank you,

 

Can you describe how to acomplish this without using the ROWS argument? That argument is not supported in SQL Server 2008 or older.

 














get free sql tips
agree to terms