Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Videos          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

SQL Server T-SQL Code to Calculate a Moving Average


By:   |   Read Comments   |   Related Tips: More > 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:



Last Update:






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.

View all my tips


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools