Moyenne mobile pondérée dans T-SQL Dans un post antérieur, j'ai démontré le calcul des moyennes mobiles dans T-SQL. Cependant, il ya un inconvénient majeur avec des moyennes mobiles simples. Les changements de prix au début de la période ont la même importance que les changements de prix plus récents. D'une certaine manière, vous aimeriez attribuer des poids différents aux changements de prix, de sorte que les changements les plus récents obtiennent le plus de poids. À cette fin, une moyenne mobile pondérée (WMA) peut être calculée. Dans ce billet de blog je vais montrer deux méthodes différentes pour calculer WMA, qui peut être utilisé sur SQL Server 2005 et plus tard, et l'autre version est pour SQL Server versions antérieures à 2005. Pour calculer le poids relatif de chaque changement de prix, nous Besoin de connaître la position de chaque changement de prix par rapport au jour calculé. Pour cette raison, une fonction de fenêtre ne peut pas être utilisée. Il n'est pas possible d'obtenir des informations à partir des différentes lignes dans la fenêtre. Dans l'exemple ci-dessous, nous calculons la moyenne mobile pondérée pour 9 jours (WMA9). L'exemple utilise le TAdb. Un script pour créer TAdb peut être trouvé ici. Indépendamment de la version de SQL Server, pour chaque ligne nous devons accéder aux 8 lignes précédentes, 9 avec la ligne courante incluse. Ces 9 lignes seront la fenêtre qui contient nos changements de prix. Chaque ligne dans cette fenêtre se verra attribuer un poids linéaire qui augmente avec la même quantité pour chaque rangée jusqu'à la ligne courante. Le poids de chaque ligne sera calculé en utilisant la position de la ligne de fenêtre par rapport à la ligne en cours. Let8217s dire que nous voulons calculer WMA pour la 9 ème ligne (citations de TAdb StockId 1): 1 30,02 30,02 2 30,33 60,66 3 30,33 90,99 4 30,44 121,76 5 30,24 151,20 6 30,27 181,62 7 29,87 209,09 8 30,00 240,00 9 30,02 270,18 La somme de ce qui précède est de 1355,52. Ceci est alors divisé par la somme des poids, c'est-à-dire 123456789 45. Le WMA9 pour la rangée 9 est 1355,52 45 30,12. Si vous voulez calculer WMA autre que pendant 9 jours, utilisez le T-SQL suivant (obtenez la fonction GetNums2 ici) pour obtenir le diviseur pour la longueur de période (par exemple 45 pour 9 jours WMA): Diviseurs de moyenne mobile pondérée (WMA) Moyenne mobile pondérée SQL Server 2005 et versions ultérieures Cette version utilise un CTE pour calculer le WMA: Résultat pour 9 jours Moyenne mobile pondérée (WMA9) Dans les résultats ci-dessus, vous pouvez voir le WMA9 pour la ligne 9 est 30,12, comme calculé précédemment. Moyenne mobile pondérée avant SQL Server 2005 La seule différence entre la version SQL Server 2005 et celle-ci est l'utilisation d'une expression de table commune. La version antérieure à 2005 utilise des tableaux réels au lieu de CTE8217s: Performances Lors du calcul d'une moyenne mobile simple et de l'utilisation de SQL Server 2012 ou ultérieur, une amélioration considérable de la performance peut être observée lors de l'utilisation des fonctions de fenêtre par rapport aux méta-options utilisées dans les anciennes versions de SQL Server . Cependant, les calculs des moyennes mobiles pondérées peuvent utiliser les fonctions de fenêtrage de la même manière. Une comparaison entre la version SQL Server 2005 de WMA montre une légère amélioration par rapport aux versions utilisées dans les anciennes versions de SQL Server: T-SQL WMA SQL Server 2005 par rapport à la version antérieure à 2005. En raison des calculs coûteux impliqués dans WMA, il pourrait être une bonne idée de persister les résultats. WMA sont utilisés de la même manière que SMA, dans l'analyse des tendances. WMA a plus de poids sur les changements de prix récents cependant. 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. Avant de discuter de la façon d'écrire des moyennes mobiles dans Postgres. Par la demande populaire ont été vous montrer comment faire la même chose dans MySQL et SQL Server. Eh bien couvrir comment annoter des graphiques bruyants comme ceci: Avec une moyenne 7 jours précédant la ligne comme ceci: La grande idée Notre premier graphique ci-dessus est assez bruyant et difficile d'obtenir des informations utiles à partir. Nous pouvons le lisser en traçant une moyenne sur 7 jours en plus des données sous-jacentes. Cela peut être fait avec des fonctions de fenêtre, auto-joint, ou sous-requêtes corrélées - bien couvrir les deux premiers. Bien commencer par une moyenne précédente, ce qui signifie que le point moyen le 7 du mois est la moyenne des sept premiers jours. Visuellement, cela déplace les pointes dans le graphique vers la droite, car un gros pic est calculé sur les sept jours suivants. Tout d'abord, créer un tableau de comptage intermédiaire Nous voulons calculer une moyenne sur le total des inscriptions pour chaque jour. En supposant que nous avons une table d'utilisateurs typique avec une ligne par nouvel utilisateur et un timestamp createdat, nous pouvons créer notre agréger notre table d'inscriptions comme ceci: Dans Postgres et SQL Server, vous pouvez utiliser cela comme un CTE. Dans MySQL vous pouvez l'enregistrer comme une table temporaire. Postgres Rolling Average Heureusement Postgres a des fonctions de fenêtre qui sont la façon la plus simple de calculer une moyenne courante. Cette requête suppose que les dates n'ont pas de lacunes. La requête est une moyenne sur les sept dernières lignes, pas les sept dernières dates. Si vos données présentent des lacunes, complétez-les en générant des séries ou en vous joignant à une table avec des lignes de date denses. MySQL Rolling Average MySQL manque de fonctions de fenêtre, mais nous pouvons faire un calcul similaire à l'aide d'auto-jointures. Pour chaque rangée de notre table de dénombrement, nous joignons chaque ligne qui était dans les sept derniers jours et prendre la moyenne. Cette requête gère automatiquement les intervalles de date, car nous examinons les lignes dans une période plutôt que les N lignes précédentes. SQL Server Rolling SQL Server moyenne a des fonctions de fenêtre, de sorte que le calcul de la moyenne mobile peut être fait dans le style Postgres ou MySQL style. Pour simplifier, utilisaient la version MySQL avec une jointure automatique. C'est conceptuellement le même que dans MySQL. Les seules traductions sont la fonction dateadd et explicitement nommées groupe par colonnes. Autres moyennes Nous nous sommes concentrés sur la moyenne à la baisse de sept jours dans ce poste. Si nous voulions regarder la moyenne de 7 jours, c'est aussi simple que de trier les dates dans l'autre sens. Si on voulait regarder une moyenne centrée, wed use: Postgres: lignes entre 3 précédent et 3 suivant MySql: entre signups. date - 3 et signups. date 3 dans MySQL SQL Server: entre dateadd (jour, -3, signups. Date) et dateadd (jour, 3, signups. date)
No comments:
Post a Comment