Moving Average Sql Server 2012

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 LindI he leído la discusión que usted mencionó. Es aplicable a PostgreSQL ya que se le permite crear una función agregada definida por el usuario utilizando SQL en PostgreSQL, pero no se permite en SQL Server. El uso de CTE recursivo es una forma factible en SQL Server, pero me doy cuenta de que CTE puede incurrir en más exploración de tabla que las funciones de ventana. Así que hago este post para preguntar si es posible calcular el promedio móvil exponencial utilizando la función de ventana de SQL Server 2012 como calcular el promedio móvil simple. Ndash xiagao1982 Apr 14 13 at 2:53 Primero, se calcula la EMA (SMA (x)) en lugar de la EMA (x). En segundo lugar, su quotsmoothing constantquot es en realidad el valor beta en mi fórmula, no el alfa. Con esos dos cambios, el SQLFiddle tiene este aspecto: sqlfiddle / 6/19192/1 Sin embargo, todavía hay una pequeña diferencia entre el resultado real y el resultado esperado. Volvería y vería si su definición EMA coincide con la que conozco. Ndash Sebastian Meine May 7 13 at 13:46 Acabo de mirar el formulario en la hoja de cálculo adjunta y está muy lejos de la definición estándar de EMA. Mi fórmula calcula el promedio móvil exponencial de las últimas diez filas. La hoja de cálculo calcula primero el promedio estándar en las últimas diez filas y, a continuación, la media móvil sin restricción ponderada exponencialmente sobre todos los promedios. Esto sigue el formulario aquí: en. wikipedia. org/wiki/EWMAchart ndash Sebastian Meine 7 de mayo 13 a las 13: 52AVG (Transact-SQL) ALL Aplica la función agregada a todos los valores. ALL es el valor predeterminado. DISTINCT Especifica que AVG se realizará sólo en cada instancia única de un valor, independientemente de cuántas veces se produzca el valor. Expresión Es una expresión de la categoría de tipo de datos numéricos o aproximados numéricos, excepto para el tipo de datos de bit. Las funciones agregadas y las subconsultas no están permitidas. OVER (partitionbyclause orderbyclause) partitionbyclause divide el conjunto de resultados producido por la cláusula FROM en particiones a las que se aplica la función. Si no se especifica, la función trata todas las filas del conjunto de resultados de la consulta como un solo grupo. Orderbyclause determina el orden lógico en el que se realiza la operación. Ordenar porcláusula es requerida. Para obtener más información, vea Cláusula OVER (Transact-SQL). El tipo de retorno se determina por el tipo de resultado de expresión evaluado. Categoría decimal (p, s) Si el tipo de datos de la expresión es un tipo de datos de alias, el tipo de retorno también es del tipo de datos de alias. Sin embargo, si se promueve el tipo de datos base del tipo de datos alias, por ejemplo de tinyint a int. El valor de retorno es del tipo de datos promovido y no del tipo de datos alias. AVG () calcula el promedio de un conjunto de valores dividiendo la suma de esos valores por el recuento de valores nonnull. Si la suma supera el valor máximo para el tipo de datos del valor devuelto, se devolverá un error. AVG es una función determinista cuando se usa sin las cláusulas OVER y ORDER BY. No es determinista cuando se especifica con las cláusulas OVER y ORDER BY. Para obtener más información, consulte Funciones deterministas y no determinísticas. A. Uso de las funciones SUM y AVG para los cálculos En el ejemplo siguiente se calculan las horas de vacaciones promedio y la suma de horas de licencia por enfermedad que han utilizado los vicepresidentes de los ciclos de trabajo de aventura. Cada una de estas funciones agregadas produce un único valor de resumen para todas las filas recuperadas. El ejemplo utiliza la base de datos AdventureWorks2012. Cómo calcular un promedio móvil 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. Hola a todos, He estado preguntándome cómo hacerlo, tengo una consulta que ejecuto donde quiero ejecutar una media móvil de 10 días en la SUM (a. GBPRevenue ), Logré hacer una suma acumulativa (usando Archivo b) pero la media móvil es difícil :( SELECT COUNT (DISTINCT a. OrderNumber) AS siteCount, COUNT (DISTINCT dbo. NewOrders. OrderNumber) AS TotalOrderAmountNewOrders, SUM (a. (Fecha, 19000101, aBatchCompleted), 19000101) AS fecha, (SELECT SUM (b. GBPRevenue) FROM dbo. Archive b WHERE DATEADD (día, DATEDIFF (día, 19000101, b) ) Y (SiteID o ID de cliente) y (sitio del sitio O sitio 0)) AS cumulativeGBPRevenue GRUPO POR DATEADD (día, DATEDIFF), DATEADD (día, DATEDIFF (día, 19000101, a. BatchCompleted), 19000101) (Día, 19000101, a. BatchCompleted), 19000101) Lunes 19 de Marzo de 2012 12:18 PM Respuestas Utilice CROSS APPLY con un TOP (10) dentro de N 560439.26quot E 125505.63quot Marcado como respuesta por Kalman Toth Moderador Sábado, Octubre 06, 2012 8:27 AM He intentado la primera aproximación como esto: pero las filas adicionales volvieron encima de mis columnas de la suma SUM (a. GBPRevenue) COMO GBPRevenue, AVG (t2.GBPRevenue) AS GBPRevenueMovingAverage de JOIN dbo. Archive t2 ON DATEDIFF ( Día, a. BatchCompletedNoTime, t2.BatchCompletedNoTime) ENTRE 0 Y 10 declare t tabla (date datetime no nulo, float de tarifa no nulo) insertar t seleccionar getdate () - 10, 1 unión todos select getdate () - 9, 2 union all Seleccione getdate () - 8, 4 union all seleccione getdate () - 7, 4 union all seleccione getdate () - 6, 5 union all seleccione getdate () - 5, 6 union all seleccione getdate () - 4, 6 union all Seleccionar getdate () - 3, 8 unir todos seleccionar getdate () - 2, 9 unir todos seleccionar getdate () - 1, 10 unir todos seleccionar getdate () - 0, 11 unir todos seleccionar getdate () 431, 9 seleccionar t. Fecha, avg (tt. rate) como simpleMovingAvg de t AS t join t tt en DATEDIFF (día, tt. date, t. date) entre 0 y 2 grupo por t. date ordenar por t. date Lunes 19 de Marzo de 2012 3:05 PM declarar t tabla (date datetime no nulo, tasa flotante no nulo) insertar t seleccionar getdate () - 10, 1 unión todos seleccionar getdate () - 9, 2 unir todos seleccionar getdate () - 8, 4 unir todos Seleccione getdate () - 7, 4 union all seleccione getdate () - 6, 5 union all seleccione getdate () - 5, 6 union all seleccione getdate () - 4, 6 union all seleccione getdate () - 3, 8 union all Seleccione getdate () - 2, 9 union all seleccione getdate () - 1, 10 union all seleccione getdate () - 0, 11 union all seleccione getdate () 431, 9 seleccione t. date, avg (tt. rate) como simpleMovingAvg De t AS t join t tt en DATEDIFF (día, tt. date, t. date) entre 0 Y 2 grupo por t. date orden por t. date - SwePeso SELECT d. Date, AVG (t. Rate) FROM t UNT ALL SELECT 0, DATEADD (DAY, 1, t. Date) UNION TODO SELECCIONA 0, DATEADD (DAY, 2, t. Date) AS AST CROSS APPLY (SELECT 1, DATEADD D (Mantener, Fecha) GRUPO POR d. Date HAVING MAX (d. Keep) 1 N 560439.26quot E 125505.63quot Lunes, Marzo 19, 2012 3:24 PM este anidado select hecho: muy simple en realidad (SELECT AVG (GBPRevenue) FROM dbo. Archive b WHERE (DATEDIFF (día, a. BatchCompletedNoTime, b. BatchCompletedNoTime) ENTRE 0 Y 10 Y (clientID 0 OR b. clientID clienteID) Y (sitio del sitio O sitio 0))) AS GBPRevenueMovingAverage Editado por Quantum Information Monday , March 19, 2012 3:27 PM Marcado como respuesta por Quantum Information Lunes 19 de Marzo de 2012 3:27 PM Sin marcado como respuesta por Quantum Information Lunes 19 de Marzo de 2012 17:27 Lunes 19 de Marzo de 2012 3:27 PM No es eso que va a darle un promedio móvil de 11 días (Entre 0 y 10) Lunes, Marzo 19, 2012 3:28 PM Las tablas tienen claves ¿es esto lo que querías decir? ¿Por qué te importa que los ingresos estén en GBP? Totalmente diferente tipo de entidad de otros ingresos CREATE TABLE OrderArchives (ordertimestamp DATETIME2 (3) NOT NULL PRIMARY KEY, ordernbr INTEGER NOT NULL, revenueamt DECIMAL (18,2) NOT NULL) INSERT INTO OrderArchives VALUES (2010-06-23 08:32 : 33.670, 606, 2252.68), (2010-06-23 09: 32: 31.453, 607, 2350.75), (2010-06-23 16: 05: 59.053, 008, 2535.49), (2010-06-27 04: 06: 46.103, 609, 274.54), (2010-06-29 12:25: 50.483, 610, 2254.20), (2010-07-04 13: 49: 10.460, 601, 255.23), (2010-07-05 18 : 40: 03.083, 614, 1000.09) El primer problema es que sus especificaciones no son claras. Antes de diez días calendarios Antes de diez días laborales Antes de diez días archivados El siguiente problema es que tiene una marca de tiempo (DATETIME en dialecto T-SQL) y desea datos por día. Hagamos eso en un CTE y obtengamos totales diarios. Entonces la cláusula de la ventana 2012 hace el resto fácil. CON (ordenar fecha, dailyrevenuetot) AS (SELECT orderdate, SUM (revenueamt) DE (SELECT CAST (ordertimestamp AS DATE) AS fecha de pedido, revenueamt FROM OrderArchives) ASX GROUP BY orderdate) AVG (revenueamt) OVER (ORDENAR POR orderdate ROWS ENTRE 10 FILOS PREDETERMINADOS Y ACTUALES) AS dailyrevenueamtrunningavg FROM DailyOrderArchives --CELKO-- Libros en la Serie Celko para Morgan-Kaufmann Publishing: Analytics y OLAP en SQL / Data and Databases: / Mediciones y Estándares en SQL SQL para Smarties / SQL Programación Estilo / SQL Puzzles y Respuestas / Pensando en Conjuntos / Árboles y Jerarquías en SQL Lunes 19 de Marzo de 2012 3:37 PM esto es básicamente hacer lo que quiero, Ive split up the Consulta en 2 partes, lo único es que generalmente está produciendo valores más altos de lo que debería para los promedios móviles: DECLARE tbl TABLE (dailyGBPRevenue NUMERIC (18, 4), TotalOrderCountProcessedOrders INT, fecha DATETIME) INSERT INTO tbl SELECT SUM (a. GBPRevenue ) AS GBPRevenue, COUNT (DISTINCT a. OrderNumber), a. BatchCompletedNoTime FROM Archivo a GROUP BY a. BatchCompletedNoTime SELECT a. date, a. dailyGBPRevenue, AVG (aa. dailyGBPRevenue) AS dailyGBPRevenueMvgAvg, a. TotalOrderCountProcessedOrders, AVG (aa. TotalOrderCountProcessedOrders ) AS TotalOrderCountProcessedOrdersMvgAvg FROM tbl a JOIN tbl aa ON DATEDIFF (día, aa. date, a. date) ENTRE 0 Y 10 DONDE a. date ENTRE fromDate AND toDate GROUP BY a. date, a. dailyGBPRevenue, a. TotalOrderCountProcessedOrders ORDER BY date Martes 20 de Marzo de 2012 4:18 PM hay una manera fácil de cambiar esto para que vea las últimas 10 filas en lugar del intervalo de fechas JOIN tbl aa EN DATEDIFF (day, aa. date, a. date) ENTRE 0 Y 10 Miércoles, Marzo 21, 2012 1:10 PM Use CROSS APPLY con un TOP (10) dentro. N 560439.26quot E 125505.63quot Marcado como respuesta por Kalman Toth Moderador sábado, 06 de octubre de 2012 8:27 Miércoles, 21 de marzo de 2012 13:22 Microsoft está realizando una encuesta en línea para entender su opinión sobre el sitio web de Msdn. Si decide participar, se le presentará la encuesta en línea cuando abandone el sitio Web de Msdn. ¿Quieres participar? Ayúdanos a mejorar MSDN. Visita nuestra página UserVoice para enviar y votar ideas