Requête Moyenne Mobile

J'ai une production de table qui contient la structure suivante: J'ai des données pour chaque rep de 112011 à 812013. Ce que je veux être capable de faire est de créer une moyenne mobile de 12 mois commençant 112012 pour chaque rep, comme suit: où chaque ligne représente La moyenne mobile de 12 mois pour ledit représentant à l'heure indiquée. J'ai trouvé quelques exemples qui étaient vaguement proches et je les ai essayés en vain. Il semble que l'ajout d'un groupe par composante rep soit le principal écart par rapport à d'autres exemples. C'est à peu près aussi loin que j'ai eu: Cette requête semble tirer une moyenne globale ou la somme, car il n'ya pas de regroupement dans la sous-requête corrélée. Lorsque j'essaie de groupe par, je reçois une erreur qu'il ne peut retourner au plus une ligne. A demandé Oct 10 13 à 14: 47Moving 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 tableau 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. Les moyennes mobiles exponentielles sont semblables aux moyennes mobiles pondérées en ce sens qu'elles attribuent moins de poids aux changements il ya longtemps, et plus de poids aux changements récents. Les moyennes mobiles pondérées sont linéaires, mais les moyennes mobiles exponentielles sont exponentielles. C'est-à-dire que le poids peut être exprimé sous forme de courbe: il existe un excellent moyen de calculer des moyennes mobiles exponentielles dans T-SQL en utilisant une fonctionnalité non documentée sur les variables et les totaux en cours d'exécution dans SQL Server. Dans ce billet de blog je vais montrer comment utiliser cette méthode pour calculer la moyenne mobile exponentielle dans T-SQL, mais je vais également présenter une méthode qui utilise des fonctionnalités standard dans SQL Server. Malheureusement, cela signifie utiliser une boucle. Dans les exemples, je calculerai une moyenne mobile exponentielle de 9 jours. Les exemples utilisent la base de données TAdb. Un script pour créer TAdb peut être trouvé ici. Moyenne mobile exponentielle (EMA): Méthode des totaux courants La théorie qui sous-tend les fonctionnalités totales courantes des mises à jour est décrite en détail par Jeff Moden dans son article Résoudre les problèmes d'ordre total et Ordinal Rank. D'autres ressources qui décrivent l'utilisation de cette méthode pour calculer EMA sont le blog Calculer des moyennes mobiles avec T-SQL par Gabriel Priester et le forum post exponentiel Défi mobile moyen. À la fois sur SQL Server Central. Fondamentalement, dans T-SQL, vous pouvez mettre à jour les variables ainsi que les colonnes dans une déclaration de mise à jour. Les mises à jour sont effectuées ligne par ligne en interne par SQL Server. Ce comportement ligne par ligne est ce qui rend le calcul d'un total courant possible. Cet exemple montre comment cela fonctionne: Notez que 8220ColumnRunningTotal8221 est un total de 8220ColumnToSum8221. En utilisant cette méthode nous pouvons calculer EMA9 avec ce T-SQL: Le calcul d'EMA est assez simple. Nous utilisons la ligne courante et la précédente, mais avec plus de poids à la ligne courante. Le poids est calculé par la formule 2 (19), où 822098221 est le paramètre pour la longueur de l'EMA. Pour calculer EMA9 pour la ligne 10 ci-dessus, le calcul est: Dans ce cas, la ligne courante obtient 20 du poids (2 (19) 0,2) et la rangée précédente obtient 80 du poids (1-2 (19) 0.8). Vous trouverez ce calcul dans l'instruction ci-dessus dans l'instruction CASE: Moyenne mobile exponentielle (EMA): Méthode de boucle Pour autant que je sache, à l'exception de la méthode des totaux en cours décrite ci-dessus, il n'y a aucun moyen de calculer EMA en utilisant une instruction SQL . Par conséquent, le T-SQL ci-dessous utilise une boucle while pour calculer EMA9: Les résultats sont les mêmes que dans l'exemple de totaux en cours ci-dessus. Performance Comme prévu, la version des totaux en cours d'exécution est beaucoup plus rapide que la version en boucle. Sur ma machine, la solution basée sur l'ensemble était d'environ 300 ms, comparativement à environ 1200 avec la version de boucle. La version de boucle est plus conforme aux normes de SQL cependant. Donc, le choix entre les méthodes dépend de ce qui est le plus important pour vous, la performance ou les normes. La moyenne mobile exponentielle peut être utilisée dans l'analyse des tendances, comme pour les autres types de moyennes mobiles, la moyenne mobile simple (SMA) et la moyenne mobile pondérée (WMA). Il ya aussi d'autres calculs dans l'analyse technique qui utilise l'EMA, MACD par exemple. Ce billet de blog fait partie d'une série sur l'analyse technique, TA, dans SQL Server. Voir les autres messages ici. Publié par Tomas Lind Tomas Lind - Services de conseil en tant que SQL Server DBA et développeur de bases de données chez High Coast Database Solutions AB.