Calcular La Media Móvil En Sql Server

Estoy trabajando con SQL Server 2008 R2, tratando de calcular una media móvil. Para cada registro, en mi opinión, me gustaría recoger los valores de los 250 registros anteriores, y luego calcular la media para esta selección. Mi punto de vista columnas son las siguientes: TransactionID es único. Para cada TransactionID. Me gustaría calcular el promedio de valor de la columna, más de 250 registros anteriores. Así que para TransactionID 300, recoger todos los valores de 250 filas anteriores (ver descendente está ordenada por TransactionID) y luego en la columna MovAvg escribir el resultado de la media de estos valores. Busco para recoger datos dentro de un rango de registros. preguntó 28 de Oct 14 a las 20: 58How para calcular una media móvil de SQL sin una actualización del cursor: Si está trabajando con las nuevas versiones de SQL Server, puede utilizar al sistema de ventanas para lograr la misma cosa. He publicado el código actualizado al final del post. Para este video, todavía me gusta el proceso de pensamiento de anclaje a una fecha. Vídeo: 3 días de media móvil en SQL Una forma eficiente para calcular un promedio móvil en SQL utilizando algunos trucos para fijar los anclajes de la fecha. Hay debates sobre la mejor manera de hacer un SQL media móvil en SQL Server. Algunas personas piensan que hay momentos en que un cursor es más eficiente. Otros piensan que puede hacerlo todo de una manera basada en el conjunto sin el cursor. El otro día iba a calcular una media móvil y mi primer pensamiento fue utilizar un cursor. Hice una cierta investigación rápida y encontré esta pregunta foro: Media móvil en TSQL Hay un post que muestra una subconsulta con una fecha de anclaje para ayudar a encontrar compensar el 1 y 2 días. Aquí está la secuencia de comandos que puede utilizar para probar el SQL 3 días Moving resultado final media. Ésta es la consulta final. Ésta es la consulta que usaría con SQL Server 2012. Compartir esto: AVG (Transact-SQL) todo aplica la función agregada de todos los valores. ALL es el valor predeterminado. Especifica distintas que AVG pueden realizar sólo en cada instancia única de un valor, independientemente del número de veces que ocurre el valor. la expresión es una expresión de la categoría de tipo de datos numérico exacto o numérico aproximado, excepto por el tipo de datos bit. No se permiten funciones de agregación y subconsultas. 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 resultado de la consulta establecido como un solo grupo. orderbyclause determina el orden lógico en el que se realiza la operación. Se requiere orderbyclause. Para obtener más información, vea OVER (cláusula de Transact-SQL). El tipo de retorno está determinada por el tipo del resultado evaluado de expresión. categoría decimal (p, s) Si el tipo de datos de 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 el tipo de datos base del tipo de datos de alias se promueve, por ejemplo, de tinyint a int. el valor de retorno es del tipo de datos promovido y no el tipo de datos de alias. AVG () calcula el promedio de un conjunto de valores dividiendo la suma de esos valores a la cuenta de valores no nulos. Si la suma excede 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 la una y cláusulas ORDER BY. Es determinista cuando se especifica con la una y cláusulas ORDER BY. Para obtener más información, vea Funciones deterministas y no deterministas. A. Uso de la SUM y AVG funciones para el cálculo el siguiente ejemplo se calcula el promedio de horas de vacaciones y la suma de horas de licencia por enfermedad que los vicepresidentes de Adventure Works Cycles han utilizado. Cada una de estas funciones de agregación produce un solo valor de resumen para todas las filas recuperadas. El ejemplo utiliza la AdventureWorks2012 database. Sign Iniciar sesión en el menú Inicio de la nube SQL Sysadmin de opinión Libros Blogs Iniciar sesión Registrarse resultados 17 de octubre de 2013 Cálculo de valores dentro de una ventana del balanceo en Transact SQL Antes de que el SQL se llevaron a cabo las funciones de ventana, que fue difícil de calcular la rodadura totales o promedios móviles de manera eficiente en SQL Server. En la actualidad hay una serie de técnicas, pero que tiene el mejor rendimiento Dwain campamentos se sale el cronómetro metafórica. Dwain campamentos cálculo de los valores dentro de una ventana deslizante en SQL En cualquier momento en que es necesario combinar los valores sobre varias filas en SQL, el problema puede ser un reto, especialmente cuando se trata de rendimiento. Nos centraremos en el balanceo de doce meses totales problema, pero nuestros métodos se pueden aplicar a cualquier ventana de tiempo (por ejemplo, 3 meses) o para las medias y otras agregaciones a través de esas ventanas de tiempo también. Un total de laminación durante un mes es el total para ese mes, más los meses anteriores dentro de la ventana de tiempo, o NULL si don8217t tiene los valores para todos los meses anteriores dentro de la ventana de tiempo. En versiones anteriores de SQL Server, que tuvo que saltar a través de algunos aros para llegar a un método que funciona bien, pero SQL 2012 ofrece algunas nuevas características que hacen que sea más sencillo. En cualquiera de los casos, hay varias soluciones válidas. ¿Qué es más rápido y eficiente We8217ll tratar de responder a esta pregunta en este artículo. Vamos a trabajar en SQL 2012. Si desea seguir a lo largo, se puede utilizar el recurso Queries. sql Muestra you8217ll remite adjunto. Configuración de datos y planteamiento del problema de negocios Muchas veces you8217ll se encuentra con muchas de las transacciones dentro de un mes, pero en nuestro caso we8217ll asumen you8217ve ya se agrupan sus transacciones por cada mes. We8217ll asignar nuestra clave principal para un tipo de datos DATE, e incluyen algunos valores sobre los que queremos acumular rodando doce meses totales. Esto también produce un ligero plan de consulta diferente, así we8217ll estar interesado en ver cómo sus resultados de rendimiento en comparación con otras soluciones propuestas hasta ahora. Esto en cuanto a las soluciones tradicionales, y mis disculpas si me pasó a pasar por alto uno de sus favoritos, pero no dude en codificarlo y añadirlo a la herramienta de prueba de rendimiento we8217ll presentar más tarde para ver cómo le va. Solución 5: Uso de una actualización de Peculiar Si you8217ve nunca oído hablar de la peculiar Update (QU) y cómo se puede aplicar a problemas tales como los totales acumulados, recomiendo encarecidamente que tiene una lectura de este artículo ha destacado por SQL MVP Jeff Moden. titulado Resolver el total acumulado y ordinal clasifican los problemas. Antes de continuar, debemos señalar que hay los que insisten en que el método QU representa un comportamiento no documentado de SQL Server y por lo tanto no es de fiar. Podemos decir que la sintaxis está claramente descrito por la entrada MS Books on line para la instrucción UPDATE para las versiones de SQL 2005, 2008 y 2012. De hecho, se remonta más lejos que eso. He utilizado con éxito en SQL Server 2000 pero fue heredado de Sybase y estaba en la primera versión de SQL Server se han publicado. Para los detractores I8217ll decir que el comportamiento 8220undocumented8221 es al menos consistente a través de todas las versiones y es probable que haya pocas razones para sospechar que se dejará de utilizar o cambiar en futuras versiones de MS SQL. Considérate advertido Si alguna vez considerar el uso de un QU para solucionar cualquier problema, es necesario tomar buena nota de las muchas reglas que se aplican (también incluidos en el artículo referido por Jeff). Los principales, los cuales I8217ve manipula en esta consulta, se pueden resumir en: La tabla debe tener un índice agrupado que indica el orden de las filas de origen por el periodo como desea que se va a desplazar. La tabla debe tener una columna en la que se puede colocar el total de ejecución agregada. Al realizar la actualización, es necesario bloquear la tabla utilizando la sugerencia de consulta TABLOCKX para asegurarse de que nadie más se mete en cualquier INSERT s, s DELETE o UPDATE s antes you8217re través. Debe evitar SQL de intentar poner en paralelo la consulta utilizando la opción (MAXDOP 1) sugerencia. Desde un período de doce meses continúa promedio es simplemente un total acumulado en el encubrimiento, podemos añadir una columna a nuestra mesa y aplicamos una consulta QU hacer nuestro cálculo. Debo confesar que este tiene un aspecto un poco desordenado, con todas las variables que tienen que declarar. Básicamente lo que estamos haciendo es hacer un seguimiento de los valores últimos doce (retraso), con el fin de eliminar el 12 (donde se asigna la columna de la Rolling12Months) de lo que es un total acumulado QU como se describe en el artículo Jeff8217s. Tenemos grandes esperanzas en su velocidad ya que se sabe que es el método más rápido para resolver el problema de totales acumulados. Una vez más, usted debe convencerse de que los resultados son consistentes con las soluciones anteriores, y sí esta solución todavía se comporta igual en SQL 2012. Si you8217re conmigo hasta ahora, también puede preguntarse 8220what sucede si necesito para calcular múltiples en ejecución doce meses totales a través de diferentes partitions8221 Esto es relativamente sencillo para todas las otras soluciones que se presentan, pero sí propone un poco de un desafío con el QU. La respuesta a esto se puede encontrar en el archivo de recursos adjunta: Peculiar actualización Partitioned. sql. SQL 2012 soluciones hasta ahora, todo lo que hemos hecho trabajarán en SQL 2008. Lo único we8217ve hecho de que no se admite en SQL 2005 es de las inicializaciones de las variables declaramos d en el enfoque QU. Ahora let8217s ver qué SQL 2012 de las nuevas características ha que se pueden aplicar a este problema. Solución 6: El uso de un marco de ventana Nuestra primera solución SQL 2012 (6) muestra cómo utilizar un marco de ventana que se inicia 11 filas antes de la fila actual, a través de la fila actual para resumir los resultados deseados. Una vez más, los resultados devueltos son los mismos, pero el plan de consulta es bastante diferente a la de la solución antes de SQL 2012 sin embargo, no we8217re particularmente optimistas de que este enfoque dará lugar a una alternativa razonablemente rendimiento debido a la cantidad de 8220look-backs8221 necesaria para hacer que funcione . Comparación de rendimiento de los Métodos La prueba de fuego para ver cómo las soluciones de múltiples realizar es comprobar los tiempos reales de ejecución en un servidor de reposo utilizando un instrumento de prueba con muchas filas. Se muestra nuestro instrumento de prueba, junto con la forma en la Solución 1 y 2 se han modificado (consulte los comentarios en el código) para: insertar los resultados en una tabla temporal, para evitar el impacto de tiempo transcurrido de la devolución de las filas a los resultados Studio8217s administración de SQL Server cuadrícula. Retire la aritmética FECHA, porque cuando la generación de varios millones de arneses de prueba de fila es difícil generar que muchos meses únicas, así columna de la tabla de la fecha ha sido revisado para ser un tipo de datos BIGINT. Para las soluciones restantes (2) 6 8211, hemos graficado la CPU y los resultados de tiempo transcurrido desde 1M aunque filas 4M. Interpretación de los resultados transcurrido y los tiempos de CPU parecen ser consistentes a través de los diferentes métodos con respecto a su pedido. Todos aparecen a escala de forma lineal. La peculiar actualización, suponiendo que lo puede entender y todas sus reglas asociadas, parece ser la solución más rápida disponible para la solución de este problema, incluso teniendo en cuenta las nuevas características disponibles en SQL 2012. En SQL 2012, el enfoque de marco de la ventana es sin duda limpio, compacto y elegante, pero un poco peculiar senderos la solución de actualización a través de las filas que probamos. Estos resultados parecen ajustarse a una prueba anterior sobre Totales acumulados en SQL 8220Denali8221 CTP3 por Microsoft Certified Master Wayne Sheffield en su blog. Si you8217re pegado con una versión anterior de SQL (2005 o 2008), y por alguna razón que can8217t Abide utilizando un peculiar de actualización (por ejemplo, si don8217t confiar en este comportamiento no documentado), las soluciones más rápidas disponibles para usted son ya sea la CRUZ APLICAN TOP o utilizando una sub-consulta correlacionada, ya que ambos de los que parecía estar en una estrecha vinculación a través del tablero. Parece que el 8220traditional8221 combinación interna es algo que debe evitarse. Es probable que sólo empeorará si lo que necesita hacer cálculos con fechas dentro de los JOIN8217s cláusula ON. Del mismo modo, con ayuda de una tabla de conteo o múltiple GAL s (SQL 2012) ciertamente no era el camino a seguir. No exploramos soluciones CURSOR - basado, pero se puede volver pista a la que se hace referencia en el artículo totales acumulados para tener una idea de cómo podrían actuar en este caso. I8217ve se ve también algunas soluciones que emplean una expresión de tabla común recursiva (RCTE), pero sin duda la apuesta wouldn8217t en su rendimiento en comparación con las soluciones de marcos de ventanas o QU. Hay muchas maneras de calcular los valores dentro de una ventana de rodar en SQL y hay algunos ganadores de rendimiento claras entre ellos. Esperamos que haya encontrado esta guía de los métodos disponibles interesante e informativo. Descargas totales: 30 Promedio: 4.6 / 5 Dwain Camps ha sido un jefe de proyecto durante muchos años. Dado que el rendimiento de las aplicaciones puede ser un factor crítico de éxito para los proyectos, que ha estado evangelizando en la necesidad de desarrollar con mayor rendimiento de SQL. Por tutoría y de creación de artículos en SQL, que espera formar a una generación futura de los ingenieros de software en la formas correctas e incorrectas para entregar el código SQL. También tiene un interés especial en el desarrollo de soluciones a problemas complejos, uso intensivo de datos utilizando SQL de alto rendimiento, ya que la naturaleza declarativa de SQL permite el desarrollo de soluciones mediante algoritmos únicos que las lenguas de procedimiento pueden no ser capaces de hacer. Sigue Dwain sobre los artículos Twitter Relacionados También en SQL Con el auge de las bases de datos NoSQL que están explotando los aspectos de SQL para realizar consultas, y están adoptando transaccionalidad completa, ¿existe el peligro de los modelos de datos documento naturaleza jerárquica causando un conflicto fundamental con la teoría relacional Nosotros pedido a nuestro experto relacional, Hugh Bin-Haad para exponer un área difícil para la base de datos de theorists. hellip Leer más también en SQL Server Cada programador de bases de datos de SQL Server tiene que estar familiarizado con las funciones del sistema. Estos van desde lo sublime (como recuento de filas o identidad) a lo ridículo (IsNumeric ()) Robert Sheldon proporciona una visión general de los más utilizados de them. hellip Leer más También en programación T-SQL para poder hacer un uso completo de el catálogo del sistema para obtener más información acerca de una base de datos, es necesario estar familiarizado con las funciones de metadatos. Ahorran una gran cantidad de tiempo y de escribir cuando se consultan los metadatos. Una vez que el cuelgue de estas funciones, el catálogo del sistema de repente parece sencillo de utilizar, como Robert Sheldon demuestra en este article. hellip Leer más También en T-SQL Programación La forma en que formato de código T-SQL puede afectar la productividad de la personas que tienen que mantener su trabajo posteriormente. Nunca es una buena experiencia para ver código SQL, gritar ¿Quién diablos escribió el código, y luego darse cuenta de que era usted. Subvención da algunos ejemplos de mala formato y explica por qué nunca debe check-in SQL formateado mal-code. hellip Leer más Muy bueno Gran artículo Me sorprendió que LAG () lo hizo tan mal. Creo que cada invocación se hace por separado en lugar de factorizar y optimizado como una ventana. Gran explicación Estoy de acuerdo, esto es una gran explicación de las diferentes formas de calcular los valores dentro de una ventana de rodadura. Si prueba estos ejemplos en SQL 2012 tiene que cambiar MiTabla con RollingTotalsExample. Muchas gracias, señor Camps método Tally Hola Dwain, me di cuenta de que su consulta de tabla de Tally estaba causando un operador Table Spool y pensé que podría considerar la posibilidad de la parte Tally de una tabla de fechas como esta: SELECT GroupingDate, ValueMAX (CASO CUANDO Fecha GroupingDate ENTONCES FIN a. value), Rolling12MonthsCASE CUANDO ROWNUMBER () OVER (ORDER BY GroupingDate) LT 12 SUMA ELSE (Valor) ENTONCES FIN dE nulo en ResultsSoln2 RollingTotalsExample una CROSS APPLY (mdash Eliminar los valores de fecha aritmética (Fecha), (Fecha1), (Fecha2), (fecha3), (Date4), (Date5), (Date6), (Date7), (Date8), (Date9), (Fecha10), (Date11)) c (GroupingDate) GROUP BY GroupingDate QUE TIENE lt GroupingDate MAX (Fecha) ORDER BY GroupingDate (Disculpas si el formato es malo ndash ninguna vista previa) Este cambio todavía wouldnrsquot lo convierten en un candidato, pero hace una enorme mejora para que los queryhellip Gracias por los comentarios Gracias Joe y Nic. Irsquom alegra que hayas encontrado el interesante artículo. Joe: Yo también estaba un poco sorprendido por los resultados de los GAL y eso me hace pensar lo que sería el punto de equilibrio. Tal vez 3 meses podría no ser tan malo, pero todavía es difícil de creer que podría ser más rápido que el QU. Las tablas de conteo MM: Por alguna razón, no tengo una preferencia personal para mesas de escrutinio en línea, pero sus resultados son interesantes aunque sólo sea para tener en cuenta para otros casos. Asistencia con el movimiento anual total Mi primer mensaje. Necesito calcular el total anual en movimiento para el Valor por encima de los 12 meses anteriores, con este mes siendo el mes 12. entonces necesito para obtener el total anual en movimiento durante los 12 meses anteriores a esta. Con la idea de comparar MAT para este mes con el mismo mes del año pasado, y por cada mes que precede. Mi intento me dio esto: Con CTE como (SELECCIONAR rnum ROWNUMBER () sobre (por fecha) Fecha Valor Rolling12MonthsCASE CUANDO ROWNUMBER () OVER (por fecha) gt 11 ENTONCES SUM (Valor) OVER (ORDER BY Fecha filas entre.. 11 precedente y CURRENT ROW) FIN dE RollingTotalsExample) Seleccione de la CTE, (Seleccionar MRNUM max (rnum) del CTE) DEMAX Dónde rnum entre MRNUM ndash 23 y MRNUM con la capacidad de cambiar la declaración, fueron para reflejar si quiero este año o el año anterior. Mi datos reales tiene la fecha que en Entero 201 409, que creo que va a hacer la vida más fácil para mí, ya que puede restar 100 para obtener el año anterior. Excelente artículo y cualquier ayuda sería apreciada. Esta es mi solución de trabajo (con un poco de ruido) mdash móvil de 12 meses totales utilizando SQL 2012 y un marco de ventana SI OBJECTID (lsquotempdb..PreviousYearrsquo) NO ES NULO DROP TABLE previousYear Con CTE como (SELECT rnum ROWNUMBER () sobre (la orden por fecha ). Fecha. Valor Rolling12MonthsCASE CUANDO ROWNUMBER () OVER (por fecha) gt 11 ENTONCES SUM (Valor) OVER (ORDER BY Fecha filas entre 11 precedente y CURRENT ROW) fIN dE RollingTotalsExample) Seleccionar pyRowNum ROWNUMBER () sobre (la orden por MRNUM ). . sStart MRNUM ndash 24. Eend MRNUM ndash 12 en previousYear De CTE, (Seleccionar MRNUM max (rnum) del CTE) DEMAX Dónde rnum entre MRNUM ndash 23 y 12 MRNUM ndash mdash móvil de 12 meses totales utilizando SQL 2012 y un marco de ventana SI OBJECTID (lsquotempdb..ThisYearrsquo) NO ES NULO DROP TABLE thisyear Con CTE como (SELECCIONAR rnum ROWNUMBER () sobre (por fecha). Fecha. Valor. Rolling12MonthsCASE CUANDO ROWNUMBER () OVER (por fecha) gt 11 ENTONCES SUM (Valor) OVER (ORDER BY Fecha FILAS Entre el 11 y precedente CURRENT ROW) fIN dE RollingTotalsExample) Seleccionar tyRowNum ROWNUMBER () sobre (la orden por MRNUM). . sStart MRNUM ndash 11. Eend MRNUM en thisyear De CTE, (Seleccionar MRNUM max (rnum) del CTE) DEMAX Dónde rnum entre MRNUM ndash 11 y MRNUM Seleccionar de thisyear ty combinación izquierda previousYear py en ty. tyRowNum py. pyRowNum Estos pueden trabajar Irsquom no cerca de un borrador con el acceso SQL en este momento, así que canrsquot probarlo (puede haber algunos errores tipográficos / errores de sintaxis). T. DateKey SELECT, AVG (T. ValueField) OVER (Oder POR T. DateKey ASC ENTRE 365 Y precedente y CURRENT ROW) AS DE YMAValueField DataTable COMO D ORDER BY ASC T. DateKey En caso de AVG es una de las funciones de agregado no es compatible con ENTRE gama (sé SUMA es compatible). T. DateKey SELECT, SUM (T. ValueField) OVER (ASC ODER POR T. DateKey ENTRE 365 Y precedente y CURRENT ROW) / CASO CUANDO DATEDIFF (DIA, StartDate, T. DateKey) 365 lt ENTONCES DATEDIFF (DIA, StartDate, T. DateKey) ELSE 365 FIN dE AS YMAValueField DataTable COMO D ORDER BY ASC T. DateKey Ingreso Talk simple Únete a más de 200.000 profesionales de Microsoft, y conseguir, libre acceso completo a los artículos técnicos, dos veces al mes a nuestro boletín de conversación simple, y las herramientas de SQL libres. Visita nuestros patrones y prácticas de la biblioteca para aprender más acerca de la gestión del ciclo de vida de base de datos. Encontrar la manera de automatizar el proceso de construcción, pruebas y despliegue de los cambios de base de datos para reducir el riesgo y hacer posible liberaciones rápidas. Artículos recientes en T-SQL Código ProgrammingSQL servidor de T-SQL de mayor clasificación para calcular una media móvil Por: Dallas Snider Leer Comentarios Consejos relacionados: Más Funciones - Definido por el usuario UDF Problema ¿Cómo puedo suavizar los datos en una columna con una media móvil de T - SQL ¿puede usted por favor caminar a través de un ejemplo de SQL Server con el código T-SQL ¿Cómo podemos validar los datos de series de tiempo de los resultados de soluciones pueden ser inherentemente ruidosas y una buena manera de suavizar los datos para calcular una media móvil. Hay un número de maneras de calcular una media móvil de T-SQL, pero en este consejo que se verá en una forma de calcular un promedio móvil que establece el promediado de x número de filas detrás y x número de filas por delante de la corriente fila de datos. La ventaja de esto es que no hay retraso en el valor medio y el valor devuelto de media móvil está en la misma fila con su valor actual. Vamos a empezar por la creación de una mesa y cargar algunos datos usando el T-SQL a continuación. Tenemos 361 puntos de datos que crean una onda sinusoidal ruidoso. Después de cargar los datos, vamos a ejecutar el siguiente código T-SQL para seleccionar todas las columnas junto con el valor promedio móvil. En el siguiente código, el tamaño de la ventana de media móvil es de 15 (7 filas que preceden a la fila actual, además de la fila actual, además de los siguientes 7 filas). El promedio móvil de la columna de DataValue se devuelve como la columna de la MovingAverageWindowSize15. La cláusula ORDER BY es extremadamente importante para mantener los datos en el orden de clasificación adecuada. Podemos copiar y pegar los resultados en Excel para validar el cálculo es correcto. En la imagen de abajo, la ventana se inicia en la celda C3 y termina en C17. La media móvil calculada por el T-SQL en este consejo aparece en la celda D10. El promedio calculado por Excel es en la parte inferior y es igual al valor en D10. En la siguiente figura, podemos ver los valores de los datos originales representados en azul con la media móvil representa en rojo. Los próximos pasos ajustar el tamaño de la ventana de media móvil para ver cómo cambia la trama. Además, asegúrese de revisar estos otros consejos acerca de T-SQL desde mssqltips: Última actualización: 08/03/2016