Calculer une moyenne roulante récursive dans SQL Server

Nous essayons de calculer une moyenne mobile et avons essayé de convertir de nombreuses réponses SO pour résoudre le problème. À ce stade, nous sums toujours infructueux.

Ce que nous avons essayé :

Voici quelques-unes des réponses SO que nous avons considérées.

• SQL Server: Comment get une sum mobile sur 3 jours pour différents clients au sein d'une même table
• Requête SQL pour la moyenne sur 7 jours dans SQL Server
• T-SQL calcule la moyenne mobile

Notre dernière tentative a été de modifier l'une des solutions (# 4) trouvées ici. https://www.red-gate.com/simple-talk/sql/t-sql-programming/calculating-values-within-a-window-in-transact-sql/

• SQL Server "Il y a déjà un object nommé"
• Comment faire un changement de string en SQL?
• Comment merge des données à l'aide de SQL Server?
• Requête SQL sur une table avec une colonne de géographie à searchr s'il existe une ligne située à une certaine distance d'une autre ligne de la même table
• Faire INSERT sur une table avec une colonne RowVersion requirejs une valeur nulle si la list de colonnes n'est pas fournie. Pourquoi?

Exemple :

Voici un exemple dans SQL Fiddle: http://sqlfiddle.com/#!6/4570a/17

Dans le violon, nous essayons toujours de faire fonctionner correctement la SUM, mais finalement nous essayons d'get la moyenne.

L'objective final

En utilisant l'exemple Fiddle, nous devons find la différence entre Value1 et ComparisonValue1 et la présenter comme Diff1. Lorsqu'une ligne n'a pas de Value1 disponible, nous devons l'estimer en prenant la moyenne des deux dernières valeurs Diff1, puis l'append à ComparisonValue1 pour cette ligne.

Avec la requête correcte, le résultat ressemblerait à ceci:

GroupID Number ComparisonValue1 Diff1 Value1 5 10 54.78 2.41 57.19 5 11 55.91 2.62 58.53 5 12 55.93 2.78 58.71 5 13 56.54 2.7 59.24 5 14 56.14 2.74 58.88 5 15 55.57 2.72 58.29 5 16 55.26 2.73 57.99 

Question : est-il possible de calculer cette moyenne quand elle pourrait potentiellement entrer dans la moyenne des lignes suivantes?

Mise à jour :

• Ajout d'une vue au schéma Fiddle pour simplifier la requête finale.
• Mise à jour de la requête pour inclure la nouvelle moyenne mobile pour Diff1 (colonne Diff1Last2Avg). Cette moyenne glissante fonctionne très bien jusqu'à ce que nous obtenions des valeurs nulles dans la colonne Valeur1. C'est ici que nous devons insert l'estimation.
• Mise à jour de la requête pour inclure l'estimation à utiliser en l'absence de Value1 (colonne Value1Estimate). Cela fonctionne très bien et serait parfait si nous pouvions utiliser l'estimation à la place de NULL dans la colonne Value1. Puisque la colonne Diff1 reflète la différence entre Value1 (ou son estimation) et ComparisonValue1, y compris l'estimation remplirait toutes les valeurs NULL dans Diff1. Cela permettrait à son tour de continuer à calculer les estimations des futures lignes. Il devient confus à ce stade, mais toujours en train de le pirater. Des idées?

• Comment réduire le time d'exécution de la requête avec la search de text intégral et d'énormes données
• Pouvez-vous utiliser un résultat de sous-requête en tant que type de conversion?
• Quelle déclaration sql me donnera ce résultat?
• comment afficher une colonne deux fois liée par deux ID
• Migration SQL Server, jointure externe gauche, où placer la condition, ON ou WHERE

 ;WITH cte AS ( -- This is similar to your ItemWithComparison view SELECT i.Number, i.Value1, i2.Value1 AS ComparisonValue1, -- Calculated Differences; NULL will be returned when i.Value1 is NULL CONVERT( DECIMAL( 10, 3 ), i.Value1 - i2.Value1 ) AS Diff FROM Item AS i LEFT JOIN [Group] AS G ON g.ID = i.GroupID LEFT JOIN Item AS i2 ON i2.GroupID = g.ComparisonGroupID AND i2.Number = i.Number WHERE NOT i2.Id IS NULL ), cte2 AS( /* Start with the first number Note if you do not have at least 2 consecutive numbers (in cte) with non-NULL Diff value and therefore Diff1Ago or Diff2Ago are NULL then everything else will not work; You may need to add additional logic to handle these cases */ SELECT TOP 1 -- start with the 1st number (see ORDER BY) a.Number, a.Value1, a.ComparisonValue1, a.Diff, b.Diff AS Diff1Ago FROM cte AS a -- "1 number ago" LEFT JOIN cte AS b ON a.Number - 1 = b.Number WHERE NOT a.Value1 IS NULL ORDER BY a.Number UNION ALL SELECT b.Number, b.Value1, b.ComparisonValue1, ( CASE WHEN NOT b.Value1 IS NULL THEN b.Diff ELSE CONVERT( DECIMAL( 10, 3 ), ( a.Diff + a.Diff1Ago ) / 2.0 ) END ) AS Diff, a.Diff AS Diff1Ago FROM cte2 AS a INNER JOIN cte AS b ON a.Number + 1 = b.Number ) SELECT *, ( CASE WHEN Value1 IS NULL THEN ComparisonValue1 + Diff ELSE Value1 END ) AS NewValue1 FROM cte2 OPTION( MAXRECURSION 0 );