Calcul De La Moyenne Mobile T Sql

Je travaille avec SQL Server 2008 R2, en essayant de calculer une moyenne mobile. Pour chaque enregistrement à mon avis, je voudrais collecter les valeurs des 250 enregistrements précédents, puis calculer la moyenne pour cette sélection. Mes colonnes de vue sont comme suit: TransactionID est unique. Pour chaque TransactionID. Je voudrais calculer la moyenne de la valeur de la colonne, au cours des 250 derniers enregistrements. Donc, pour TransactionID 300, collecter toutes les valeurs des 250 lignes précédentes (affichage est trié en descendant par TransactionID) et puis dans la colonne MovAvg écrire le résultat de la moyenne de ces valeurs. Je cherche à recueillir des données dans une gamme d'enregistrements. Demandée Oct 28 14 at 20: 58Moving moyenne dans T-SQL Un calcul commun dans l'analyse des tendances est la moyenne mobile (ou roulant). Une moyenne mobile est la moyenne des, par exemple, les 10 dernières rangées. La moyenne mobile montre une courbe plus lisse que les valeurs réelles, plus avec une période plus longue pour la moyenne mobile, ce qui en fait un bon outil pour l'analyse des tendances. Ce blog affichera comment calculer la moyenne mobile dans T-SQL. Différentes méthodes seront utilisées en fonction de la version de SQL Server. Le graphique ci-dessous montre l'effet de lissage (ligne rouge) avec une moyenne mobile de 200 jours. Les cours boursiers sont la ligne bleue. La tendance à long terme est clairement visible. T-SQL Déplacement Avergage 200 jours La démonstration ci-dessous nécessite la base de données TAdb qui peut être créée avec le script situé ici. Dans l'exemple à venir, nous calculons une moyenne mobile pour les 20 derniers jours. Selon la version de SQL Server, il y aura une méthode différente pour effectuer le calcul. Et, comme nous le verrons plus loin, les nouvelles versions de SQL Server ont des fonctions permettant un calcul beaucoup plus efficace. SQL Server 2012 et versions ultérieures Moyenne mobile Cette version utilise une fonction de fenêtre agrégée. Quoi de neuf dans SQL 2012 est la possibilité de restreindre la taille de la fenêtre en spécifiant le nombre de lignes précédant la fenêtre doit contenir: lignes précédentes est 19, car nous allons inclure la ligne actuelle ainsi dans le calcul. Comme vous pouvez le voir, le calcul de la moyenne mobile dans SQL Server 2012 est assez simple. La figure ci-dessous illustre le principe de fenêtrage. La ligne courante est marquée en jaune. La fenêtre est marquée d'un fond bleu. La moyenne mobile est simplement la moyenne de QuoteClose dans les lignes bleues: T-SQL Fenêtre de moyenne mobile. Les résultats des calculs dans les anciennes versions de SQL Server sont identiques, de sorte qu'ils ne seront pas affichés à nouveau. SQL Server 2005 8211 2008R2 Moyenne mobile Cette version utilise une expression de table commune. Le CTE est auto-référencé pour obtenir les 20 dernières lignes pour chaque ligne: Moyenne mobile avant SQL Server 2005 La version antérieure à 2005 utilisera une jointure externe gauche à la même table pour obtenir les 20 dernières lignes. Comparaison des performances Si nous exécutons les trois méthodes différentes simultanément et vérifions le plan d'exécution résultant, il existe une différence spectaculaire de performance entre les méthodes: Comparaison de trois Différentes méthodes pour calculer la moyenne mobile Comme vous pouvez le constater, les améliorations apportées aux fonctionnalités de fenêtrage dans SQL 2012 font une énorme différence dans les performances. Comme mentionné au début de ce post, les moyennes mobiles sont utilisées comme un outil pour illustrer les tendances. Une approche commune consiste à combiner des moyennes mobiles de différentes longueurs, afin de repérer respectivement les variations des tendances à court, moyen et long terme. Un intérêt particulier est le franchissement des lignes de tendance. Par exemple, lorsque la courte tendance se déplace sur la tendance à long ou à moyen terme, cela peut être interprété comme un signal d'achat dans l'analyse technique. Et quand la tendance courte se déplace sous une ligne de tendance plus longue, cela peut être interprété comme un signal de vente. Le graphique ci-dessous présente les devis, Ma20, Ma50 et Ma200. T-SQL Ma20, Ma50, Ma200 acheter et vendre des signaux. Ce billet de blog fait partie d'une série sur l'analyse technique, TA, dans SQL Server. Voir les autres messages ici. Lorsque vous avez besoin de combiner des valeurs sur plusieurs lignes dans SQL, le problème peut être difficile, surtout quand il s'agit de la performance. Nous nous concentrerons sur le problème des totaux sur douze mois, mais nos méthodes peuvent être appliquées à n'importe quelle fenêtre de temps (par exemple 3 mois) ou aux moyennes et autres agrégations à travers ces fenêtres temporelles. Un total de roulement pour un mois est le total pour ce mois plus les mois précédents dans la fenêtre de temps, ou NULL si vous don8217t ont les valeurs pour tous les mois précédents dans la fenêtre de temps. Dans les versions précédentes de SQL Server, vous avez dû sauter par quelques cerceaux pour arriver à une méthode qui fonctionne bien, mais SQL 2012 offre quelques nouvelles fonctionnalités qui le rendent plus simple. Dans les deux cas, il existe plusieurs solutions valables. Qui est le plus rapide et le plus efficace We8217ll essayer de répondre à cette question dans cet article. Nous allons travailler dans SQL 2012. Si vous souhaitez suivre le long, vous pouvez utiliser la ressource Sample consultas. sql you8217ll trouver ci-joint. Configuration des données et état du problème d'affaires Souvent, vous vous retrouvez avec de nombreuses transactions dans un mois, mais dans notre cas, nous supposons que vous avez déjà regroupé vos transactions pour chaque mois. Nous assignerons notre CLÉ PRIMAIRE à un type de données DATE et inclurons des valeurs sur lesquelles nous voulons accumuler des totaux de douze mois. Cela produit également un plan de requête légèrement différent de sorte we8217ll être intéressé à voir comment ses résultats de performance se comparent à d'autres solutions proposées à ce jour. Tant pour les solutions traditionnelles, et mes excuses si je suis arrivé à négliger un de vos favoris, mais n'hésitez pas à le coder et l'ajouter au harnais de test de performance we8217ll présent plus tard pour voir comment il tarifs. Solution 5: Utilisation d'une mise à jour Quirky Si vous n'avez jamais entendu parler de la Quirky Update (QU) et comment elle peut être appliquée à des problèmes tels que les totaux en cours d'exécution, je vous recommande vivement d'avoir lu ce remarquable article par MVP SQL Jeff Moden. Intitulé Résoudre les problèmes de rang total et Ordinal Rank. Avant de continuer, nous devrions noter qu'il ya ceux qui insistent sur la méthode QU représente un comportement non documenté de SQL Server et ne doit donc pas faire confiance. Nous pouvons dire que la syntaxe est clairement décrite par l'entrée MS Books On Line pour l'instruction UPDATE pour les versions SQL 2005, 2008 et 2012. En fait, cela remonte à plus loin. Je l'ai utilisé avec succès dans SQL Server 2000, mais il a été hérité de Sybase et était dans la première version de SQL Server jamais publié. Pour les détracteurs I8217ll dire que le 8220unitedocumented8221 comportement est au moins cohérente à travers toutes les versions et il ya probablement peu de raisons de soupçonner qu'il sera obsolète ou changer dans les futures versions de MS SQL. Considérez-vous averti Si vous envisagez d'utiliser un QU pour résoudre n'importe quel problème, vous devez prendre note attentive des nombreuses règles qui s'appliquent (également inclus dans l'article référencé par Jeff). Les principaux, qui ont été traités dans cette requête, peuvent être résumés comme suit: La table doit avoir un index clusterisé qui indique la commande des lignes source par la période comme vous souhaitez qu'elle soit parcourue. Le tableau doit comporter une colonne dans laquelle vous pouvez placer le cumul total. Lorsque vous effectuez la mise à jour, vous devez verrouiller la table à l'aide de l'astuce de requête TABLOCKX pour vous assurer que personne d'autre n'obtient dans aucun INSERT s, DELETE s ou UPDATE s avant de passer à travers. Vous devez empêcher SQL d'essayer de paralléliser la requête à l'aide de l'indication OPTION (MAXDOP 1). Puisque la moyenne mobile de douze mois est tout simplement un total courant déguisé, nous pouvons ajouter une colonne à notre table et appliquer une requête QU pour faire notre calcul. Je dois avouer que cela ne semble un peu en désordre, avec toutes les variables dont vous avez besoin DECLARE. Fondamentalement, ce que nous faisons est de garder une trace des douze dernières valeurs (lagging), afin d'enlever le 12e (où la colonne Rolling12Months est affecté) de ce qui est sinon un QU courant total comme décrit dans Jeff8217s article. Nous avons de grands espoirs pour sa vitesse étant donné qu'il est connu pour être la méthode la plus rapide pour résoudre le problème des totaux de fonctionnement. Encore une fois, vous devriez vous convaincre que les résultats sont cohérents avec les solutions précédentes, et oui cette solution se comporte toujours de la même dans SQL 2012. Si vous êtes avec moi jusqu'à présent, vous pouvez également vous demander 8220what se passe si je dois calculer plusieurs exécuter Douze mois totaux à travers différentes partitions8221 C'est relativement simple pour toutes les autres solutions présentées, mais propose un peu un défi à l'aide de la QU. La réponse à cette question se trouve dans le fichier de ressources ci-joint: Quirky Update Partitioned. sql. SQL 2012 Solutions Jusqu'à présent, tout ce que nous avons fait fonctionnera dans SQL 2008. La seule chose que we8217ve fait qui n'est pas prise en charge dans SQL 2005 est l'initialisation des variables que nous DÉCLARONS d dans l'approche QU. Maintenant let8217s voir ce que les nouvelles fonctionnalités SQL 2012 a qui peut être appliqué à ce problème. Solution 6: Utilisation d'un cadre de fenêtre Notre première solution SQL 2012 (6) montre comment utiliser un cadre de fenêtre qui démarre 11 lignes avant la ligne en cours, à travers la ligne en cours pour SOMME nos résultats souhaités. Une fois de plus, les résultats retournés sont les mêmes, mais le plan de requête est très différent de celui de la précédente solution SQL 2012, mais nous ne sommes pas particulièrement optimistes que cette approche donnera une alternative raisonnablement performante en raison du nombre de 8220look-backs8221 nécessaires pour le faire fonctionner . Comparaison des performances des méthodes Le test réel pour voir comment des solutions multiples est de vérifier les temps d'exécution réels dans un serveur quiescent en utilisant un harnais de test avec de nombreuses lignes. Notre harnais de test est illustré ainsi que la façon dont les solutions 1 et 2 ont été modifiées (référez-vous aux commentaires dans le code) pour: Insérer les résultats dans une table temporaire afin d'éviter l'impact sur le temps écoulé de renvoyer les lignes aux résultats de SQL Server Management Studio8217 la grille. Supprimez l'arithmétique DATE car, lorsque vous générez des harnais de test de plusieurs millions de lignes, il est difficile de générer de nombreux mois uniques, de sorte que la colonne Date table a été révisée pour être un type de données BIGINT. Pour les solutions restantes (2 8211 6), nous avons représenté graphiquement les résultats de CPU et de temps écoulé de 1M sur 4M lignes. Interpréter les résultats écoulés et temps CPU semblent être cohérentes à travers les différentes méthodes en ce qui concerne leur commande. Tous semblent à l'échelle d'une manière linéaire. La mise à jour Quirky, en supposant que vous pouvez le comprendre et toutes ses règles associées, semble être la solution la plus rapide à résoudre ce problème, même en tenant compte des nouvelles fonctionnalités disponibles dans SQL 2012. Dans SQL 2012, l'approche cadre de fenêtre est certainement soignée, Compact et élégant, mais légèrement sur la solution Quirky Update sur les lignes que nous avons testées. Ces résultats de test semblent se conformer à un test antérieur sur Running Totals dans SQL 8220Denali8221 CTP3 par Microsoft Certified Master Wayne Sheffield dans son blog. Si vous êtes coincé avec une version antérieure de SQL (2005 ou 2008), et pour une raison quelconque, vous pouvez vous conformer à l'aide d'une mise à jour Quirky (par exemple, si vous don8217t confiance à ce comportement non documenté), les solutions les plus rapides à votre disposition sont soit le CROSS APPLY TOP ou En utilisant une sous-requête corrélée, comme les deux semblaient être dans un lien étroit à travers le conseil. Il semble que le 8220traditional8221 INNER JOIN est quelque chose à éviter. Il sera probablement seulement pire si vous avez besoin de faire arithmétique de date dans la clause JOIN8217s ON. De même, l'utilisation d'une table Tally ou de plusieurs LAG (SQL 2012) n'était certainement pas la solution. Nous n'avons pas exploré les solutions basées sur CURSOR, mais vous pouvez revenir en arrière à l'article référencé sur les totaux en cours d'exécution pour avoir une idée de comment ils pourraient effectuer dans ce cas. J'ai également vu certaines solutions qui utilisent une expression de table commune récursive (rCTE), mais je ne parierais certainement pas sur leur performance par rapport aux solutions de QU ou de cadre de fenêtre. Il existe de nombreuses façons de calculer des valeurs dans une fenêtre déroulante dans SQL et il ya des gagnants de performances claires parmi eux. Nous espérons que vous avez trouvé ce guide pour les méthodes disponibles intéressant et instructif. Total: 31 Moyenne: 4.65 Dwain Camps est un gestionnaire de projet depuis de nombreuses années. Parce que la performance des applications peut être un facteur de succès critique pour les projets, il a évangélisé sur la nécessité de développer des SQL très performants. Par le mentorat et la création d'articles sur SQL, il espère former une future génération d'ingénieurs logiciels sur les bons et mauvais moyens de fournir du code SQL. Il a également un intérêt particulier dans le développement de solutions à des problèmes complexes et à forte intensité de données en utilisant un SQL de haute performance car la nature déclarative de SQL permet le développement de solutions algorithmiquement uniques que les langages procéduraux peuvent ne pas être capables de faire. Suivez Dwain sur Twitter Très bon Très bon article J'ai été surpris que LAG () l'ait fait si mal. Je suppose que chaque appel est fait séparément plutôt que factorisé et optimisé comme une fenêtre. Grande explication Je suis d'accord, c'est une excellente explication des différentes façons de calculer les valeurs au sein d'une fenêtre de roulement. Si vous testez ces exemples sur SQL 2012, vous devez modifier MyTable avec RollingTotalsExample. Merci beaucoup, M. Camps Tally méthode Salut Dwain, j'ai remarqué que votre requête table Tally a été la cause d'un opérateur Table Spool et pensé que vous pourriez envisager de faire la partie Tally d'une table Dates comme ceci: SELECT GroupingDate, ValueMAX (CASE GroupingDate WHEN Date THEN NULL ELSE SUM (Valeur) END INTO ResultsSoln2 DE RollingTotalsExample a CROSS APPLY (mdash Supprime les valeurs arithmétiques DATE (Date), (Date1), (Date) (Date2), (Date3), (Date4), (Date5), (Date6), (Date7), (Date8), (Date9), (Date10), (Date11)) c (GroupingDate) GROUP BY GROUPingDate HAVING GroupingDate lt MAX (Date) ORDER BY GroupingDate (Excuse si le formatage est mauvais ndash pas d'aperçu) Ce changement encore wouldnrsquot en faire un concurrent, mais fait une amélioration massive à ce queryhellip Merci pour les commentaires Merci Joe et Nic. Irsquom heureux que vous avez trouvé l'article intéressant. Joe: Moi aussi, j'ai été un peu surpris par les résultats du GAL et cela me fait me demander quel serait le seuil de rentabilité. Peut-être 3 mois pourrait ne pas être aussi mauvais, mais il est encore difficile de croire qu'il pourrait être plus rapide que le QU. Tables de pointage MM: Pour une raison quelconque, j'ai une préférence personnelle pour les tableaux de pointage en ligne, mais vos résultats sont intéressants si seulement à considérer pour les autres cas. Aide au déménagement Total annuel Mon premier poste. Je dois calculer le Total annuel mobile pour la valeur ci-dessus pour les 12 mois précédents, avec ce mois étant le mois 12. Je dois ensuite obtenir le Total annuel mobile pour les 12 mois précédents. L'idée étant de comparer MAT pour ce mois avec le mois correspondant l'année dernière, et pour chaque mois précédant. Ma tentative m'a donné ceci: Avec cte comme (SELECT rNum ROWNUMBER () Plus (order by Date). Date. Valeur Rolling12MonthsCASE WHEN ROWNUMBER () OVER (ORDRE BY Date) gt 11 THEN SOMME (Valeur) OVER (ORDER BY Date ROWS BETWEEN DeMax où rNum entre mRNum ndash 23 et mRNum Avec la possibilité de changer l'instruction Were pour réfléchir si je veux cette année ou le année précédente. Mes données réelles ont la date comme dans Integer 201409 qui je pense que rendra la vie plus facile pour moi comme je peux soustraire 100 pour obtenir l'année précédente. Excellent article et toute aide serait appréciée. Ceci est ma solution de travail (avec un peu de bruit) mdash Rolling 12 mois totaux en utilisant SQL 2012 et un cadre de fenêtre SI OBJECTID (lsquotempdb..PreviousYearrsquo) IS NOT NULL DROP TABLE PreviousYear Avec cte as (SELECT rNum ROWNUMBER () Plus (order by Date ) Valeur Rolling12MonthsCASE WHEN ROWNUMBER () OVER (ORDRE BY Date) gt 11 THEN SOMME (Valeur) OVER (ORDER BY Date ROWS ENTRE 11 ROUTE PRÉCÉDENTE ET COURANTE) END FROM RollingTotalsExemple) Sélectionnez pyRowNum ROWNUMBER () Plus de (ordre par mRNum ). . SStart mRNum ndash 24. EEnd mRNum ndash 12 dans PreviousYear From cte, (Sélectionnez mRNum max (rNum) de cte) deMax où rNum entre mRNum ndash 23 et mRNum ndash 12 mdash Rolling 12 mois totaux en utilisant SQL 2012 et un cadre de fenêtre IF OBJECTID (lsquotempdb..ThisYearrsquo) (NOMBRE DE COMMANDE PAR LA DATE) gt 11 THEN SOMME (Valeur) OVER (ORDER BY Date ROWS ENTRE 11 LIGNE PRÉCÉDENTE ET COURANTE) END FROM RollingTotalsExample) Sélectionnez tyRowNum ROWNUMBER () Plus (ordre par mRNum). . SStart mRNum ndash 11. EEnd mRNum in ThisYear From cte, (Sélectionnez mRNum max (rNum) de cte) deMax où rNum entre mRNum ndash 11 et mRNum Sélectionnez de ThisYear ty Left Join PreviousYear py sur ty. tyRowNum py. pyRowNum Ces peuvent fonctionner Irsquom pas près d'un comp avec Sql accès dès maintenant, donc je canrsquot le tester (il peut y avoir quelques erreurs typossyntax). SELECT T. DateKey, AVG (T. ValueField) OVER (ODER PAR T. DateKey ASC ENTRE 365 PRÉCÉDENTS ET ET COUR ACTUEL) AS YMAValueField FROM DataTable AS T ORDER BY T. DateKey ASC Dans le cas où AVG est l'une des fonctions agrégées non prises en charge Avec la gamme BETWEEN (je sais SUM est pris en charge). SELECT T. DateKey, SUM (T. ValueField) OVER (ODER PAR T. DateKey ASC ENTRE 365 PRÉCÉDENT ET ET COUR ACTUELLE) CAS DATEDIFF (DAY, StartDate, T. DateKey) lt 365 THEN DATEDIFF (DAY, StartDate, T. DateKey) ELSE 365 FIN COMME YMAValueField FROM DataTable AS T ORDER BY T. DateKey ASC Rejoignez Simple Talk Rejoignez plus de 200 000 professionnels de Microsoft et obtenez un accès complet et gratuit à des articles techniques, à notre bulletin mensuel Simple Talk et à des outils SQL gratuits. Visitez notre bibliothèque d'articles pour découvrir les modèles et les pratiques dont vous avez besoin pour aller vers des méthodes plus agiles de livraison de base de données. Découvrez comment automatiser le processus de création, de test et de déploiement des modifications de votre base de données afin de réduire les risques et d'accélérer le cycle de livraison. Avec la montée des bases de données NoSQL qui exploitent des aspects de SQL pour interroger, et qui embrassent la transactionnalité complète, existe-t-il un danger de la nature hiérarchique des modèles de documents de données causant un problème fondamental Conflit avec la théorie relationnelle Nous avons demandé à notre expert relationnel, Hugh Bin-Haad d'exposer une zone difficile pour les théoriciens de la base de données. hellip Lire aussi Aussi dans SQL Server Chaque programmeur de base de données SQL Server doit être familier avec les fonctions système. Ceux-ci vont du sublime (tel que rowcount ou identité) au ridicule (IsNumeric ()) Robert Sheldon fournit un aperçu des plus couramment utilisés de them. hellip En savoir plus Aussi dans T-SQL Programmation Pour être en mesure de faire pleinement usage de Le catalogue du système pour en savoir plus sur une base de données, vous devez être familiarisé avec les fonctions de métadonnées. Ils économisent beaucoup de temps et de saisie lors de l'interrogation des métadonnées. Une fois que vous obtenez le blocage de ces fonctions, le catalogue du système semble soudainement simple à utiliser, comme Robert Sheldon démontre dans cet article. hellip Lire aussi Aussi dans la programmation T-SQL Que vous travailliez dans un langage procédural comme PowerShell ou en T-SQL , Il ya quelque chose de légèrement gênant d'avoir à traiter avec des paramètres qui sont des listes, ou pire avec des gammes parmi les valeurs. En fait, une fois que vous avez une façon de traiter avec eux, ils peuvent être commodes, en particulier en comblant le fossé entre l'application et la base de données. Phil Factor montre comment faire face à them. hellip Lire la suite copie 2005 - 2017 Red Gate Software Ltd Que pensez-vous de la nouvelle Simple Talk Donnez-nous votre avis