Lorsque j'ai eu un problème similaire, j'ai fini par utiliser des tables temporaires pour une variété de raisons, mais cela a rendu cela beaucoup plus facile. Ce que j'ai fait ressemble beaucoup à ce que vous faites, en ce qui concerne le schéma. Rendre le schéma quelque chose comme ID identité, startdate, enddate, valeur. Lorsque vous sélectionnez, effectuez une sous-sélection avg des 20 précédentes en fonction de l'ID d'identité. Ne le faites que si vous vous trouvez déjà en utilisant des tables temporaires pour d'autres raisons (j'ai touché les mêmes lignes plusieurs fois pour des métriques différentes, il était donc utile d'avoir le petit jeu de données). D'après mon expérience, Mysql à partir de 5.5.x tend à ne pas utiliser d'index sur des sélections dépendantes, que ce soit une sous-requête ou une jointure. Cela peut avoir un impact très important sur les performances où les critères de sélection dépendants changent à chaque rangée. La moyenne mobile est un exemple de requête qui fait partie de cette catégorie. Le temps d'exécution peut augmenter avec le carré des lignes. Pour éviter cela, choisissez un moteur de base de données qui peut effectuer des recherches indexées sur des sélections dépendantes. Je trouve que postgres fonctionne efficacement pour ce problème. Maven: Calculer une moyenne mobile dans PostgreSQL Dans notre série Metrics Maven, Composes data scientist partage des fonctionnalités de base de données, des astuces, des astuces et du code que vous pouvez utiliser pour obtenir les métriques que vous Besoin de vos données. Dans cet article, regardez bien comment calculer une moyenne mobile dans PostgreSQL. Cet article s'appuie sur nos deux précédents articles sur les fonctions de fenêtre et les cadres de fenêtre dans PostgreSQL. Eh bien profiter des fonctionnalités de fenêtrage weve discuté précédemment pour calculer une moyenne mobile et bien aussi regarder une méthode alternative. Qu'est-ce qu'une moyenne mobile Une moyenne mobile est exactement ce qu'elle ressemble - une moyenne qui se déplace continuellement en fonction de l'évolution des données. Par exemple, vous pouvez prendre la moyenne d'une certaine valeur pour les 100 premières entrées ou pour les 30 jours précédents. Parce que vous obtiendrez de nouvelles entrées dans votre base de données ou parce que chaque nouveau jour est une autre date, la moyenne va changer. Le terme moyenne mobile est également synonyme de moyenne mobile ou moyenne courante, mais il existe quelques types différents de moyennes mobiles. Dans cet article allaient se concentrer sur la moyenne mobile simple pour obtenir nos pieds mouillés et bien aussi brièvement examiner la moyenne mobile cumulative à la fin de l'article. Un futur article couvrira les moyennes mobiles pondérées et exponentielles. La raison pour laquelle vous utilisez une moyenne mobile pour vos statistiques est de faciliter la détection des tendances. C'est une technique couramment utilisée dans la finance et l'analyse d'affaires pour lisser les creux et les pointes qui peuvent se produire dans les données afin que les vraies tendances peuvent être identifiés au cours de la série changeante. Déterminer comment effectuer le calcul que les changements de données peut être un peu intimidant, cependant, si youve jamais fait. Une fois que vous apprenez une méthode que vous aimez, cependant, (bien couvrir deux) son facile à faire et youll trouver de nombreux usages pour elle dans votre suivi et des rapports. Let's get à elle. Premières choses en premier: bien besoin d'une table qui contient les valeurs que nous voulons en moyenne. En pratique chez Compose, nous constatons souvent que les données de base dont nous avons besoin ne sont pas déjà clairement définies dans une table. Pour cette raison, nous disposons de quelques tableaux regroupant les données dont nous avons besoin ensemble. Ce sont les tables de base auxquelles nous appliquerons des calculs plus avancés, comme une moyenne mobile. Dans certains cas, il s'agit de tables dérivées qui existent temporairement pour l'exécution de la requête principale. Dans d'autres cas, nous pourrions utiliser une vue ou une vue matérialisée. Donc, mais vous y arriver, vous aurez besoin d'une table contenant les valeurs que vous voulez à la moyenne et quelle dimension (s) vous voulez commander les données par. Pour notre exemple, disons que nous avons été invités à créer une moyenne mobile de 30 jours pour les téléchargements de l'application de l'exemple Co. Les données de téléchargement de l'application sont quotidiennement remplies à une table appelée appdownloadsbydate et la partie la plus récente ressemble à ceci: La commande par date sera importante car nous voulons calculer une moyenne mobile de 30 jours sur la série précédente de dates. Pour cette raison, il est important que nous avons une ligne pour chaque date. Dans notre cas, nous le faisons, mais si vous avez des lacunes dans vos données où il n'y a pas de valeurs pour certaines dates, vous pouvez utiliser generateseries lors de la construction de votre table de base pour vous assurer que vous avez obtenu toutes les lignes dont vous avez besoin. Notez comment cette plage de dates contient des téléchargements d'applications fluctuantes de 35 à 7. Il est très difficile de voir une tendance à partir de ces données: Entrez la moyenne mobile. Utilisation de cadres de fenêtre pour une moyenne mobile simple Si vous vous souvenez de notre article précédent dans cette série. Les cadres de fenêtre sont utilisés pour indiquer le nombre de lignes autour de la ligne courante que la fonction de fenêtre doit inclure. Ils créent un sous-ensemble de données pour que la fonction de fenêtre fonctionne. En fonction de vos données et de vos besoins, votre calcul de la moyenne mobile peut inclure des lignes précédant et suivant la ligne en cours, mais pour nos besoins, notre moyenne mobile utilisera les lignes précédentes et la ligne courante car nous voulons générer une nouvelle valeur de la moyenne mobile Chaque nouvelle date. Notre requête ressemble à ceci: Nous utilisions ORDER BY sur notre champ date pour nous assurer que nos données seront dans l'ordre que nous attendons et nous avons spécifié ROWS ENTRE 29 ROUTE PRÉCÉDENTE ET COURANTE pour définir le cadre de fenêtre pour le calcul AVG. Comme le cadre de fenêtre avance pour chaque date, seules les 29 lignes précédentes et l'actuelle (30 jours au total) sont utilisées pour le calcul: Comme nous ne vous montrons pas de dates dans notre table de base avant le 26 mai pour cet exemple, Des résultats sur les dates où nous vous avons montré les 29 lignes précédentes. Prenons le 30 juin, par exemple. Notre cadre de fenêtre concentre notre agrégation AVG sur les téléchargements de l'application de juin seulement, cette partie de notre table de base: Donc, maintenant, si nous traçons la moyenne mobile calculée, nous pouvons voir que les données sont lissées et il ya une tendance à la hausse Pendant la première semaine de juin, puis une tendance à la baisse plus volatile après cela: Puisqu'il s'agit de montrer un mois seulement de données n'est pas très révélateur pour un rapport analytique, mais nous l'espérons, vous aidera à comprendre comment calculer une moyenne mobile peut être utile pour les entreprises une analyse. Un conseil sur ne pas inclure la ligne actuelle Si pour une raison quelconque vous ne voulez pas inclure la ligne courante pour votre fonction de fenêtre et vous utilisez uniquement PRECEDING ou seulement SUIVANT paramètres pour votre cadre de fenêtre, une façon facile de faire est d'utiliser x PRECEDING ou Y SUIVANT deux fois dans vos ROWS ENTRE. clause. Par exemple, disons que nous voulions utiliser 30 lignes précédant notre ligne courante, mais pas la ligne courante dans le cadre de la fenêtre. Nous pourrions écrire cette clause comme ceci: ROWS ENTRE 30 PRÉCÉDENTS ET PRÉCÉDENTS. De même, nous pouvons exclure la ligne en cours, mais faire 30 lignes suivantes comme ceci: ROWS ENTRE 1 SUIVANT ET 30 SUIVANT. Une méthode alternative pour une moyenne mobile simple Avant PostgreSQL 9.0, nous n'avions pas les options de cadre de fenêtre x PRECEDING ou y FOLLOWING à notre disposition. Pour calculer une moyenne mobile sans utiliser de cadre de fenêtre, nous pouvons utiliser deux alias de table de notre table de base. Eh bien utiliser un alias pour fonctionner sur l'autre en utilisant un intervalle de date. Vérifiez-le: En utilisant cette méthode, nous pouvons obtenir les mêmes résultats que décrit ci-dessus avec le cadre de fenêtre. Si vous opérez sur de grandes quantités de données, l'option cadre de fenêtre va être plus efficace, mais cette alternative existe si vous voulez l'utiliser. Calcul d'une moyenne mobile cumulative Maintenant que weve a examiné quelques méthodes pour calculer une moyenne mobile simple, bien changer notre exemple de cadre de fenêtre pour montrer comment vous pouvez également faire une moyenne mobile cumulative. Les mêmes principes s'appliquent, mais plutôt que d'avoir un cadre de fenêtre continuellement décalage pour un intervalle, le cadre de fenêtre se prolonge simplement. Par exemple, au lieu de faire une moyenne mobile de 30 jours, allaient calculer une moyenne mobile de l'année à ce jour. Pour chaque nouvelle date, sa valeur est simplement incluse dans le calcul moyen de toutes les dates précédentes. Jetons un coup d'oeil à cet exemple: Parce que notre table de base commence à Janvier 1st pour l'année en cours, utilisaient UNBOUNDED PRECEDING pour définir notre cadre de fenêtre. Les résultats que nous obtenons pour ce calcul cumulatif ressemblent à ceci: si nous traçons ces résultats, vous pouvez voir que l'avantage de la moyenne mobile cumulative est un lissage supplémentaire des données de sorte que seules les modifications significatives des données apparaissent comme des tendances. Maintenant que vous connaissez un couple de différents types de moyennes mobiles que vous pouvez utiliser et un couple de méthodes différentes pour les calculer, vous pouvez effectuer une analyse plus perspicace et créer plus Des rapports efficaces. Dans notre prochain article Metrics Maven, regardez bien quelques options pour la façon de rendre les données assez pour qu'au lieu de valeurs comme 20.4184782608695652, bien voir 20.42. Rendez-vous à la prochaine fois 2017 ComposePreviously nous avons discuté de la façon d'écrire des moyennes roulantes 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. 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