Calcular El Promedio Móvil En Sql

SQL Server T-SQL Código para calcular un promedio móvil ¿Cómo puedo suavizar los datos en una columna con una media móvil en T-SQL ¿Puede usted por favor caminar a través de un Ejemplo en SQL Server con código T-SQL ¿Cómo podemos validar los resultados Solución Los datos de la serie temporal pueden ser intrínsecamente ruidosos y una buena manera de suavizar los datos es calcular un promedio móvil. Hay un número de maneras de calcular una media móvil en T-SQL, pero en esta punta vamos a mirar una manera de calcular un promedio móvil que establece la ventana de promedio x número de filas detrás y x número de filas por delante de la actual Fila de datos. La ventaja de esto es que no hay retraso en el valor promedio devuelto y el valor de la media móvil está en la misma fila con su valor actual. Comencemos creando una tabla y cargando algunos datos usando el T-SQL a continuación. Tenemos 361 puntos de datos que crean una onda senoidal ruidosa. Después de cargar los datos, ejecutaremos el siguiente código T-SQL para seleccionar todas las columnas junto con el valor del promedio móvil. En el siguiente código, el tamaño de la ventana del promedio móvil es 15 (7 filas que preceden a la fila actual, más la fila actual, más las 7 filas siguientes). El promedio móvil de la columna DataValue se devuelve como la columna MovingAverageWindowSize15. La cláusula ORDER BY es extremadamente importante para mantener los datos en el orden ordenado adecuado. Podemos copiar y pegar los resultados en Excel para validar que el cálculo es correcto. En la imagen de abajo, la ventana comienza en la celda C3 y termina en C17. El promedio móvil calculado por el T-SQL en esta punta aparece en la celda D10. El promedio calculado por Excel está en la parte inferior y es igual al valor en D10. En la siguiente figura, podemos ver los valores de los datos originales trazados en azul con el promedio móvil representado en rojo. Pasos siguientes Ajuste el tamaño de la ventana del promedio móvil para ver cómo cambia la gráfica. Además, asegúrese de consultar estos otros consejos sobre T-SQL de mssqltips: Última actualización: 3/8 / 2016Anteriormente discutimos cómo escribir promedios de rodadura en Postgres. Por demanda popular estaban mostrando cómo hacer lo mismo en MySQL y SQL Server. Bueno cubrir cómo anotar gráficos ruidosos como este: Con una línea promedio de 7 días anteriores como este: La gran idea Nuestro primer gráfico de arriba es bastante ruidoso y difícil de obtener información útil. Podemos suavizarlo trazando un promedio de 7 días sobre los datos subyacentes. Esto se puede hacer con funciones de ventana, auto-uniones, o subconsultas correlacionadas - y cubrir las dos primeras. Bueno empezar con un promedio anterior, lo que significa que el punto promedio en el 7 del mes es el promedio de los primeros siete días. Visualmente esto cambia los picos en el gráfico a la derecha, ya que un pico grande se promedia en los siguientes siete días. En primer lugar, crear una tabla de conteo intermedio Queremos calcular un promedio sobre el total de inscripciones para cada día. Suponiendo que tengamos una tabla típica de usuarios con una fila por usuario nuevo y una marca de tiempo createdat, podemos crear nuestra tabla de agregados agregados como así: En Postgres y SQL Server puedes usar esto como un CTE. En MySQL puede guardarlo como una tabla temporal. Postgres Rolling Media Afortunadamente Postgres tiene funciones de ventana que son la forma más sencilla de calcular un promedio de ejecución. Esta consulta asume que las fechas no tienen espacios. La consulta está promediando en las últimas siete filas, no en las últimas siete fechas. Si sus datos tienen espacios vacíos, llénelos con generateseries o ensamblando contra una tabla con filas de fecha densas. MySQL Rolling Average MySQL carece de funciones de ventana, pero podemos hacer una computación similar usando auto-uniones. Para cada fila en nuestra tabla de conteo, nos unimos a cada fila que estaba dentro de los últimos siete días y tomar el promedio. Esta consulta gestiona automáticamente los intervalos de fechas, ya que estamos viendo las filas dentro de un intervalo de fechas en lugar de las N filas anteriores. SQL Server Rolling Media SQL Server tiene funciones de ventana, por lo que calcular el promedio de balanceo se puede hacer en el estilo Postgres o estilo MySQL. Por simplicidad, estaban usando la versión de MySQL con una autojunción. Esto es conceptualmente lo mismo que en MySQL. Las únicas traducciones son la función dateadd y se denominan explícitamente grupo por columnas. Otros promedios Nos enfocamos en el promedio de 7 días en este post. Si queremos ver el promedio de 7 días, es tan simple como clasificar las fechas en la otra dirección. Si quisiéramos mirar un promedio centrado, usamos: Postgres: filas entre 3 anteriores y 3 siguientes MySql: entre signups. date - 3 y signups. date 3 en MySQL SQL Server: entre dateadd (día, -3, signups. Fecha) y dateadd (día, 3, signups. date) Promedio móvil en T-SQL Un cálculo común en el análisis de tendencias es el promedio móvil (o móvil). Un promedio móvil es el promedio de, por ejemplo, las últimas 10 filas. El promedio móvil muestra una curva más suave que los valores reales, más aún con un período más largo para el promedio móvil, lo que lo convierte en una buena herramienta para el análisis de tendencias. Esta publicación del blog mostrará cómo calcular el promedio móvil en T-SQL. Se utilizarán diferentes métodos dependiendo de la versión de SQL Server. El siguiente gráfico muestra el efecto de suavizado (línea roja) con un promedio móvil de 200 días. Las cotizaciones de acciones son la línea azul. La tendencia a largo plazo es claramente visible. T-SQL Moving Avergage 200 días La demostración a continuación requiere la base de datos TAdb que se puede crear con el script que se encuentra aquí. En el próximo ejemplo calcularemos un promedio móvil de los últimos 20 días. Dependiendo de la versión de SQL Server, habrá un método diferente para hacer el cálculo. Y, como veremos más adelante, las nuevas versiones de SQL Server tienen funciones que permiten un cálculo mucho más efectivo. SQL Server 2012 y posterior Moving Average Esta versión hace uso de una función de ventana agregada. Lo nuevo en SQL 2012 es la posibilidad de restringir el tamaño de la ventana especificando cuántas filas que preceden a la ventana deben contener: Las filas precedentes son 19, porque incluiremos la fila actual también en el cálculo. Como puede ver, el cálculo del promedio móvil en SQL Server 2012 es bastante simple. La siguiente figura muestra el principio de ventana. La fila actual está marcada con amarillo. La ventana está marcada con un fondo azul. El promedio móvil es simplemente el promedio de QuoteClose en las líneas azules: T-SQL Moving average window. Los resultados de los cálculos en versiones anteriores de SQL Server son iguales, por lo que no se mostrarán de nuevo. SQL Server 2005 8211 2008R2 Moving Average Esta versión hace uso de una expresión de tabla común. El CTE es auto referenciado para obtener las últimas 20 filas para cada fila: Promedio móvil antes de SQL Server 2005 La versión anterior a 2005 utilizará una combinación externa izquierda en la misma tabla para obtener las últimas 20 filas. Comparación de rendimiento Si ejecutamos los tres métodos diferentes simultáneamente y comprobamos el plan de ejecución resultante, hay una diferencia dramática en el rendimiento entre los métodos: Comparación de tres Diferentes métodos para calcular el promedio móvil Como puede ver, las mejoras de la función de ventana en SQL 2012 hacen una gran diferencia en el rendimiento. Uso Como se mencionó en el comienzo de este post, las medias móviles se utilizan como una herramienta para ilustrar las tendencias. Un enfoque común es combinar promedios móviles de diferentes longitudes, con el fin de detectar cambios en las tendencias a corto, mediano y largo plazo, respectivamente. De particular interés son el cruce de líneas de tendencia. Por ejemplo, cuando la tendencia corta se mueve sobre la tendencia larga o media, esto puede interpretarse como una señal de compra en el análisis técnico. Y cuando la tendencia corta se mueve bajo una línea de tendencia más larga, esto puede interpretarse como una señal de venta. El siguiente gráfico muestra Cotizaciones, Ma20, Ma50 y Ma200. T-SQL Ma20, Ma50, Ma200 comprar y vender señales. Esta entrada del blog forma parte de una serie sobre análisis técnico, TA, en SQL Server. Vea los otros mensajes aquí. Publicado por Tomas Lind