55476

Moving Average / Rolling Average

I have 2 columns in MS SQL one is Serial no. and other is values. I need the thrird column which gives me the sum of the value in that row and the next 2.

Ex

SNo values 1 2 2 3 3 1 4 2 5 6 7 9 8 3 9 2

So I need third column which has sum of 2+3+1, 3+1+2 and So on, so the 8th and 9th row will not have any values:

1 2 6 2 3 6 3 1 4 4 2 5 5 1 6 7 2 7 8 3 9 2

Can the Solution be generic so that I can Varry the current window size of adding 3 numbers to a bigger number say 60.

Answer1:

Here is the <strong>SQL Fiddle</strong> that demonstrates the following query:

WITH TempS as ( SELECT s.SNo, s.value, ROW_NUMBER() OVER (ORDER BY s.SNo) AS RowNumber FROM MyTable AS s ) SELECT m.SNo, m.value, ( SELECT SUM(s.value) FROM TempS AS s WHERE RowNumber >= m.RowNumber AND RowNumber <= m.RowNumber + 2 ) AS Sum3InRow FROM TempS AS m

In your question you were asking to sum 3 consecutive values. You modified your question saying the number of consecutive records you need to sum could change. In the above query you simple need to change the m.RowNumber + 2 to what ever you need.

So if you need 60, then use

m.RowNumber + 59

As you can see it is very flexible since you only have to change one number.

Answer2:

In case the sno field is not sequential, you can use row_number() with aggregation:

with ss as ( select sno, values, row_number() over (order by sno) as seqnum from s ) select s1.sno, s1.values, (case when count(s2.values) = 3 then sum(s2.values) end) as avg3 from ss s1 left outer join ss s2 on s2.seqnum between s1.seqnum - 2 and s1.seqnum group by s1.sno, s1.values;

Answer3:

select one.sno, one.values, one.values+two.values+three.values as thesum from yourtable as one left join yourtable as two on one.sno=two.sno-1 left join yourtable as three on one.sno=three.sno-2

Or, as requested in your comment, you could do this:

select sno, sum(values) over ( order by sno rows between current row and 3 following ) from yourtable

Answer4:

If you need a fully generic solution, where you can sum, for example, current row + next row + 5th following row:

Step 1: Create an table listing the offsets needed. 0 = current row, 1 = next row, -1 = prev row, etc

SELECT * FROM (VALUES (0),(1),(2) ) o(offset)

Step 2: Use that offset table in this template (via CTE or an actual table):

WITH o AS (SELECT * FROM (VALUES (0),(1),(2) ) o(offset)) SELECT t1.sno, t1.value, SUM(t2.Value) FROM @t t1 INNER JOIN @t t2 CROSS JOIN o ON t2.sno = t1.sno + o.offset GROUP BY t1.sno,t1.value ORDER BY t1.sno

Also, if SNo is not sequential, you can fetch ROW_NUMBER() and join on that instead.

WITH o AS (SELECT * FROM (VALUES (0),(1),(2) ) o(offset)), t AS (SELECT *,ROW_NUMBER() OVER(ORDER BY sno) i FROM @t) SELECT t1.sno, t1.value, SUM(t2.Value) FROM t t1 INNER JOIN t t2 CROSS JOIN o ON t2.i = t1.i + o.offset GROUP BY t1.sno,t1.value ORDER BY t1.sno

Recommend

  • How to use Defer in canjs
  • MSBuild config transform issues - desination folder and reverting file back to original state
  • selecting top column1 with matching column2
  • Binding to DataGridView so each cell is a bound object rather than each row
  • Running Total That Changes Each Time A Column Value Changes
  • mongo db update changing the order of object fields
  • Player ranking by categories over months
  • How select second line from top 2 or something similar?
  • How to use a controller in an other file in Angularjs
  • JQuery tablesorter using ajax and dynamic thead and tbody sections
  • Akka.NET not recognising my custom logger and defaulting to BusLogger
  • NSCollectionView does not scroll items past initial visible rect
  • Find a path in Windows relative to another
  • Oracle SQL - Generate and update a column with different random number in every row
  • Row_number() function for Informix
  • Is there a way to pivot a customer ID and a their most recent order dates?
  • SQL Server Nvarchar and Java prepared statement
  • SQL: Getting the physical size of a subset of a table
  • How can I restyle a word when rendering a pdf with pdf.js?
  • R convert summary result (statistics with all dataframe columns) into dataframe
  • Merging rows to columns
  • Approximate Order-Preserving Huffman Code
  • Grails calculated field in SQL
  • Can you perform a UNION without a subquery in SQLAlchemy?
  • PostgreSQL Query without WHERE only ORDER BY and LIMIT doesn't use index
  • Breeze - Deleted Items nav properties bug
  • javaw.exe and eclipse startup problems
  • Is possible to count alias result on mysql
  • Display Images one by one with next and previous functionality
  • SQL merge duplicate rows and join values that are different
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • need help with bizarre java.net.HttpURLConnection behavior
  • Easiest way to encapsulate a HTML5 webpage into an android app?
  • Busy indicator not showing up in wpf window [duplicate]
  • costura.fody for a dll that references another dll
  • Observable and ngFor in Angular 2
  • How to Embed XSL into XML
  • UserPrincipal.Current returns apppool on IIS
  • Conditional In-Line CSS for IE and Others?
  • java string with new operator and a literal