Obtenir la moyenne mobile d'une colonne dans T-SQL

Ok, donc j'ai un tableau et dans une colonne j'ai des données et la deuxième colonne la moyenne des données. Exemple

id|Data|avg 1 |20 |20 2 |4 |12 3 |18 |14

Comment puis-je remplir la colonne avg sur insert avec la moyenne courante de la colonne Data en utilisant T-SQL?

EDIT: Désolé les gars, c'était en fait une erreur stupide que j'ai faite. J'ai supposé que j'avais SQL 2014 mais après avoir essayé le code de Stephan et avoir quelques erreurs, je suis returnné pour confirmer et réaliser que j'utilise SQL 2008. Désolé pour la désinformation. J'ai également mis à jour les tags

SQL Server <= 2008 n'a pas la clause OVER(ORDER BY ...) pour les fonctions d'agrégat.

 CREATE TRIGGER trg_running_avg ON myTable AFTER INSERT, UPDATE, DELETE AS BEGIN UPDATE old SET avg = new_avg FROM myTable old CROSS APPLY ( SELECT AVG(Data) AS new_avg FROM myTable WHERE ID <= old.ID ) new --Skip the full table update. Start from the lowest ID that was changed. WHERE id >= (SELECT MIN(id) FROM (SELECT ID FROM inserted UNION ALL SELECT ID FROM deleted) t) END GO

Utilisez une vue pour cela si vous le pouvez. C'est une mauvaise design pour un changement d'une ligne pour invalider datatables stockées dans d'autres lignes. Les lignes doivent représenter des faits indépendants.

Sur insert, en supposant que id est une identité et que vous ne faites que mettre des data :

 insert into table t(id, data, avg) select @data, @data * (1.0 / n) + avg * (n - 1.0)/n from (select count(*) as cnt, avg(data) as avg from t ) t;

Dans SQL Server 2012+, il est assez facile de l'get en sortie:

 select t.*, avg(data) over (order by id) as cume_avg from table t

Avant SQL Server 2012, vous le feriez avec une sous-requête corrélée ou vous apply :

 select t.*, (select avg(data) from table t2 where t2.id <= t.id ) as cume_avg from table t;

Ici, les performances peuvent être affectées si la table est grande. Cependant, un index sur id, data aideraient.

Gordon Linoff l'a sur l'insertion. Si vous voulez le faire avec un triggersur

Méthode de triggersment

 IF OBJECT_ID('myTable') IS NOT NULL DROP TABLE myTable; CREATE TABLE myTable(ID INT, Data INT,[avg] INT); GO CREATE TRIGGER trg_running_avg ON myTable INSTEAD OF INSERT AS BEGIN INSERT INTO myTable SELECT ID,Data,AVG(Data) OVER (ORDER BY ID ROWS UNBOUNDED PRECEDING) FROM inserted END INSERT INTO myTable(ID,Data) VALUES (1,20),(2,4),(3,18) SELECT * FROM myTable

Voir la méthode

 CREATE VIEW vw_average AS SELECT ID,Data,AVG(Data) OVER (ORDER BY ID ROWS UNBOUNDED PRECEDING) FROM inserted

Mettre à jour les valeurs pré-insérées avec l'auto-jointure

 UPDATE myTable SET avg = running_avg FROM myTable A INNER JOIN (SELECT ID,AVG(Data) OVER (ORDER BY ID ROWS UNBOUNDED PRECEDING) running_avg FROM myTable) B ON A.ID = B.ID

Mettre à jour les valeurs pré-insérées avec CTE

 WITH CTE_Update AS ( SELECT ID, [avg] OldAvg, AVG(Data) OVER (ORDER BY ID) AS NewAvg FROM myTable ) UPDATE CTE_Update SET OldAvg = NewAvg