CODEWITHSUNDEEP
sqlsql-servert-sqlwindow-functionsmoving-average
Tommy Sayugo
Tommy Sayugo

Reputation: 375

Moving average: Compute AVG with Last N data continuosly

I have a table like this:

Name       Poin
================
ANA         5
ANA         10
ANA         3
ANA         8
ANA         7
ANA         5

I want to get AVG for last 2 rows continuosly like this:

Name       Poin    AVG 2
=========================
ANA         5       7.5
ANA         10      6.5 
ANA         3       5.5
ANA         8       7.5
ANA         7        6
ANA         5        5

The "AVG 2" column is average between a row and row -1. How can I do that? Thanks in advance

Upvotes: 0

Views: 649

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

You can try the windowing function with OVER()

DECLARE @table TABLE (Id int, Name nvarchar(10), Poin float)

INSERT INTO @table 
VALUES
(1, N'ANA',5),
(2, N'ANA',10),
(3, N'ANA',3),
(4, N'ANA',8),
(5, N'ANA',7),
(6, N'ANA',5);

SELECT AVG(Poin) OVER(PARTITION BY Name 
                      ORDER BY Id 
                      ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
FROM @table;

The result

7,5
6,5
5,5
7,5
6
5

Important:

Please be aware, that there is no implicit order within your table. Without a specific ORDER BY the result is random!

Upvotes: 2

Backs
Backs

Reputation: 24903

With LEAD function you can get next row ordered by Id:

DECLARE @table TABLE (Id int, Name nvarchar(10), Poin float)

INSERT INTO @table 
VALUES
(1, N'ANA',5),
(2, N'ANA',10),
(3, N'ANA',3),
(4, N'ANA',8),
(5, N'ANA',7),
(6, N'ANA',5)


SELECT
    t.Id,
    (t.Poin + LEAD(t.Poin, 1, t.Poin) OVER (ORDER BY t.Id)) / 2
FROM @table AS t

LEAD(t.Poin, 1, t.Poin)

  • LEAD - get next Nth row
  • t.Poin - column
  • 1 - move forward on 1 row
  • t.Poin - replacement for the last row, when there is no next value

Upvotes: 0

Related Questions