T-Sql Moving Average Calculation

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 LindTempo promedio ponderado en T-SQL En un post anterior he demostrado el cálculo de promedios móviles en T-SQL. Sin embargo, hay un inconveniente importante con promedios móviles simples. Los cambios de precios en el inicio del período tienen la misma importancia que los cambios de precios más recientes. De alguna manera le gustaría asignar pesos diferentes a los cambios de precio, de modo que los cambios más recientes obtienen el mayor peso. Para este propósito, se puede calcular un promedio móvil ponderado (WMA). En esta entrada de blog mostraré dos métodos diferentes para calcular WMA, uno que se puede usar en SQL Server 2005 y posterior, y la otra versión es para versiones de SQL Server anteriores a 2005. Para calcular el peso relativo de cada cambio de precio, Necesidad de conocer la posición de cada cambio de precio en relación con el día calculado. Debido a esto, no se puede utilizar una función de ventana. No es posible obtener información de las filas individuales en la ventana. En el ejemplo siguiente calcularemos el promedio móvil ponderado de 9 días (WMA9). El ejemplo utiliza el TAdb. Un script para crear TAdb se puede encontrar aquí. Independientemente de la versión de SQL Server, para cada fila necesitamos acceder a las 8 filas anteriores, 9 con la fila actual incluida. Esas 9 filas serán la ventana que contiene nuestros cambios de precio. A cada fila de esa ventana se le asignará un peso lineal que aumenta con la misma cantidad para cada fila hasta la fila actual. El peso para cada fila se calculará utilizando la posición de la fila de la ventana relativa a la fila actual. Let8217s dicen que queremos calcular WMA para la 9ª línea (cotizaciones 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 suma de lo anterior es 1355,52. Esto se divide entonces por la suma de los pesos, es decir 123456789 45. El WMA9 para la fila 9 es 1355,52 / 45 30,12. Si desea calcular WMA que no sea durante 9 días, utilice el siguiente T-SQL (obtenga la función GetNums2 aquí) para obtener el divisor para la longitud del período (por ejemplo, 45 para 9 días WMA): Divisor de media móvil ponderada (WMA) Promedio móvil ponderado SQL Server 2005 y versiones posteriores Esta versión utiliza un CTE para calcular el WMA: Resultado durante 9 días Promedio móvil ponderado (WMA9) En los resultados anteriores, puede ver el WMA9 para la fila 9 es 30,12, como se calculó anteriormente. Promedio móvil ponderado antes de SQL Server 2005 La única diferencia entre la versión de SQL Server 2005 y ésta es el uso de una expresión de tabla común. La versión anterior a 2005 utiliza tablas reales en lugar de CTE8217s: Rendimiento Cuando se calcula una media móvil simple y se usa SQL Server 2012 o posterior, se puede observar un gran aumento en el rendimiento cuando se usan funciones de ventana en comparación con los metohods alternativos utilizados en las versiones anteriores de SQL Server . Sin embargo, los cálculos de las medias móviles ponderadas pueden utilizar las funciones de ventana de la misma manera. Una comparación entre la versión de WMA de SQL Server 2005 muestra una pequeña mejora con respecto a las versiones utilizadas en las versiones anteriores de SQL Server: T-SQL WMA SQL Server 2005 comparado con la versión anterior a 2005. Debido a los cálculos costosos involucrados en WMA, podría ser una buena idea persistir los resultados. Uso WMA se utilizan de la misma manera que SMA, en el análisis de tendencias. WMA tiene más peso en los cambios de precios recientes sin embargo. 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 Tomas Lind - Servicios de consultoría como SQL Server DBA y Desarrollador de bases de datos en High Coast Database Solutions AB. Quiero incorporar dos cálculos promedio para un montón de columnas de valor en mi sentencia select. Vea este enlace para mi estructura de tabla simplificada incluyendo el cálculo de salida deseado: Pastebin Month1 valor de la columna value1 para ese mes, Month2 si suma 0 y luego escribe 0, else avg (Month1 y Month2) y así sucesivamente. Así que para cada producto, quiero el promedio móvil para cada mes dentro de un año. Tengo esta disposición en mi Excel pero no puedo transferir la expresión a sql. Para cada producto, calcule el promedio de todos los años y duplique el valor calculado en todas las filas de ese producto. Espero que me puedas ayudar con esto. Parece que necesito un procedimiento, pero tal vez es sólo una declaración simple. Estoy trabajando con SQL Server 2008 R2, tratando de calcular una media móvil. Para cada registro en mi vista, me gustaría recopilar los valores de los 250 registros anteriores, y luego calcular el promedio para esta selección. Mis columnas de vista son las siguientes: TransactionID es único. Para cada TransactionID. Me gustaría calcular el promedio para el valor de la columna, más de 250 registros anteriores. Así para TransactionID 300, recopile todos los valores de 250 filas anteriores (la vista se clasifica descendiendo por TransactionID) y luego en la columna MovAvg escriba el resultado del promedio de estos valores. Estoy buscando para recopilar datos dentro de un rango de registros. Cómo se calcula un promedio móvil de SQL sin una actualización de cursor: Si está trabajando con las versiones más recientes de SQL Server, puede utilizar las funciones de ventana para lograr lo mismo. He publicado el código actualizado al final de la publicación. Para este video, todavía me gusta el proceso de pensamiento de anclaje a una fecha. Video: Promedio móvil de 3 días en SQL Una manera eficiente de calcular una media móvil en SQL usando algunos trucos para establecer anclas de fecha. Hay debates sobre la mejor manera de hacer un SQL Moving Average en SQL Server. Algunas personas piensan que hay momentos en que un cursor es más eficiente. Otros piensan que puedes hacerlo todo de una manera basada en un set sin el cursor. El otro día iba a calcular una media móvil y mi primer pensamiento fue utilizar un cursor. Hice algunas investigaciones rápidas y encontré esta pregunta del foro: Promedio móvil en TSQL Hay una publicación que muestra una subconsulta con una fecha de anclaje para ayudar a encontrar el desplazamiento de 1 y 2 días. Aquí está el script que puede usar para probar el resultado final de SQL Moving Average de 3 días. Aquí está la consulta final. Aquí está la consulta que utilizaría con SQL Server 2012. Compartir esto: