T-Sql Moving Average

Estoy trabajando con SQL Server 2008 R2, tratando de calcular un promedio 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. Pidió Oct 28 14 a las 20: 58Esta es una pregunta de Evergreen Joe Celko. Ignoro qué plataforma de DBMS se utiliza. Pero en cualquier caso Joe fue capaz de responder hace más de 10 años con SQL estándar. Joe Celko SQL Puzzles y Respuestas citation: Ese último intento de actualización sugiere que podríamos usar el predicado para construir una consulta que nos daría una media móvil: ¿Es la columna extra o el enfoque de consulta mejor? La consulta es técnicamente mejor porque el enfoque UPDATE Desnormalizar la base de datos. Sin embargo, si los datos históricos que se están registrando no van a cambiar y el cálculo de la media móvil es caro, podría considerar el uso de la columna. SQL consulta de rompecabezas: por todos los medios uniforme. Usted acaba de tirar al cubo de peso apropiado dependiendo de la distancia desde el punto de tiempo actual. Por ejemplo quottake weight1 para datapoints dentro de 24hrs de datapoint actual weight0.5 para datapoints dentro de 48hrsquot. Ese caso importa cuántos puntos de datos consecutivos (como 6:12 am y 11:48 pm) están distantes entre sí. Un caso de uso que puedo pensar sería un intento de suavizar el histograma dondequiera que los puntos de datos no sean lo suficientemente densos. 22:22 No estoy seguro de que su resultado esperado (salida) muestra clásico simple móvil (rolling) promedio de 3 días. Porque, por ejemplo, el primer triple de números por definición da: pero esperas 4.360 y su confusión. Sin embargo, sugiero la siguiente solución, que utiliza la función de ventana AVG. Este enfoque es mucho más eficiente (claro y menos uso de recursos) que SELF-JOIN introducido en otras respuestas (y estoy sorprendido de que nadie ha dado una mejor solución). Verá que AVG está envuelto con el caso cuando rownum gt p. days entonces para forzar NULL s en las primeras filas, donde el promedio móvil de 3 días no tiene sentido. Respondió 23 de febrero a las 13:12 Podemos aplicar Joe Celkos sucia izquierda método de unión externa (como citado por Diego Scaravaggi) para responder a la pregunta como se le preguntó. Genera la salida solicitada: respondió 9 de enero a 0:33 Su respuesta 2016 Stack Exchange, IncCómo calcular una media 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 la misma cosa. 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. Esta es la consulta que usaría con SQL Server 2012. Compartir esto: Calcular valores dentro de una ventana de desplazamiento en Transact SQL Dwain Camps Calcular valores dentro de una ventana de desplazamiento en SQL Cada vez que necesite combinar valores en varias filas en SQL, el problema Puede ser un reto, sobre todo cuando se trata de rendimiento. Nos centraremos en el problema de los totales de doce meses, pero nuestros métodos se pueden aplicar a cualquier ventana de tiempo (por ejemplo, 3 meses) oa promedios y otras agregaciones a través de esas ventanas de tiempo también. Un total acumulado de un mes es el total para ese mes más los meses anteriores dentro de la ventana de tiempo, o NULL si no tiene los valores para todos los meses anteriores dentro de la ventana de tiempo. En versiones anteriores de SQL Server, tuvo que saltar a través de unos pocos aros para llegar a un método que funciona bien, pero SQL 2012 ofrece algunas características nuevas que lo hacen más simple. En cualquier caso, hay varias soluciones válidas. Lo que es más rápido y más eficiente We8217ll tratar de responder a esta pregunta en este artículo. Estaremos trabajando en SQL 2012. Si desea seguir adelante, puede utilizar el recurso de ejemplo consultas. sql you8217ll encontrar adjunto. Configuración de los datos y declaración del problema de negocios Muchas veces usted se encuentra con muchas transacciones dentro de un mes, pero en nuestro caso suponemos que ya ha agrupado sus transacciones para cada mes. We8217ll asignará nuestra CLAVE PRIMARIA a un tipo de datos DATE e incluirá algunos valores sobre los cuales queremos acumular totales de doce meses. Esto también produce un plan de consulta ligeramente diferente, por lo que estaremos interesados ​​en ver cómo sus resultados de rendimiento se comparan con otras soluciones propuestas hasta ahora. Tanto para las soluciones tradicionales, y mis disculpas si he pasado a pasar por alto uno de sus favoritos, pero no dude en codificar y agregar a la prueba de rendimiento arnés we8217ll presente más tarde para ver cómo se cotiza. Solución 5: Utilizar una Actualización Quirky Si nunca has oído hablar de Quirky Update (QU) y cómo se puede aplicar a problemas como la ejecución de totales, te recomiendo que tengas una lectura de este destacado artículo de MVP de SQL Jeff Moden. Titulado Solución de los problemas de rangos totales y ordinales. Antes de continuar, debemos tener en cuenta que hay aquellos que insisten en que el método QU representa un comportamiento indocumentado de SQL Server y por lo tanto no es de confianza. Podemos decir que la sintaxis está claramente descrita por la entrada de MS Books On Line para la instrucción UPDATE para las versiones SQL 2005, 2008 y 2012. De hecho, se remonta más allá de eso. Lo he utilizado con éxito en SQL Server 2000 pero fue heredado de Sybase y estaba en la primera versión de SQL Server lanzada nunca. Para los opositores I8217ll decir que el 8220indocumented8221 comportamiento es al menos coherente en todas las versiones y es probable que haya pocas razones para sospechar que se depreciará o cambiar en futuras versiones de MS SQL. Considérese advertido Si alguna vez considera usar un QU para resolver cualquier problema, debe tomar nota de las muchas reglas que se aplican (también incluidas en el artículo referenciado por Jeff). Las principales, que se han manejado en esta consulta, se pueden resumir en: La tabla debe tener un índice agrupado que indique el orden de las filas de origen según el período que desee que se atraviese. La tabla debe tener una columna en la que puede colocar el total acumulado. Cuando realice la actualización, deberá bloquear la tabla mediante la sugerencia de consulta TABLOCKX para asegurarse de que nadie más ingrese en ninguna INSERT s, DELETE s o UPDATE antes de completar la actualización. Debe evitar que SQL intente paralelizar la consulta con la sugerencia OPTION (MAXDOP 1). Dado que un promedio de doce meses es simplemente un total de ejecución en disfraz, podemos agregar una columna a nuestra tabla y aplicar una consulta QU para hacer nuestro cálculo. Debo confesar que esto se ve un poco desordenado, con todas las variables que necesita para DECLARAR. Básicamente lo que estamos haciendo es hacer un seguimiento de los últimos doce (rezagados) valores, con el fin de eliminar la 12a (donde está asignada la columna Rolling12Months) de lo que de lo contrario es un QU ejecutando total como se describe en Jeff8217s artículo. Tenemos grandes esperanzas para su velocidad dado que se sabe que es el método más rápido para resolver el problema de totales corrientes. 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 lo mismo en SQL 2012. Si usted está conmigo hasta ahora, también puede estar preguntando 8220what pasa si necesito calcular múltiples ejecución Totales de doce meses a través de particiones diferentes8221 Esto es relativamente simple para todas las otras soluciones presentadas pero propone un poco de desafío usando el QU. La respuesta a esto se puede encontrar en el archivo de recursos adjunto: Quirky Update Partitioned. sql. Soluciones SQL 2012 Hasta ahora, todo lo que hemos hecho funcionará en SQL 2008. Lo único que we8217ve hecho que no está soportado en SQL 2005 es las inicializaciones de las variables que DECLARE d en el enfoque QU. Ahora vamos a ver qué nuevas características tiene SQL 2012 que se pueden aplicar a este problema. Solución 6: Uso de un marco de ventana Nuestra primera solución de SQL 2012 (6) muestra cómo utilizar un marco de ventana que comienza 11 filas antes de la fila actual, hasta la fila actual para SUM nuestros resultados deseados. Una vez más, los resultados devueltos son los mismos, pero el plan de consulta es bastante diferente que para la anterior solución SQL 2012, sin embargo, no estamos particularmente optimistas de que este enfoque dará lugar a una alternativa razonablemente eficaz debido al número de 8220look-backs8221 necesarios para que funcione . Comparación de rendimiento de los métodos La prueba real para ver cómo funcionan las múltiples soluciones es comprobar los tiempos reales de ejecución en un servidor inactivo utilizando un arnés de prueba con muchas filas. Nuestro algoritmo de prueba se muestra, junto con la forma en que 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 tiempo transcurrido impacto de devolver las filas a SQL Server Management Studio8217 resultados cuadrícula. Elimine la aritmética DATE, ya que al generar arneses de prueba de varios millones de filas es difícil generar muchos meses únicos, por lo que la columna de tabla de fechas se ha revisado para que sea un tipo de datos BIGINT. Para las soluciones restantes (2 8211 6), hemos representado graficamente la CPU y los resultados de tiempo transcurrido de 1M a través de 4M filas. Interpretando los resultados transcurridos y los tiempos de CPU parecen ser consistentes a través de los diferentes métodos con respecto a su orden. Todos parecen escalar de manera lineal. La Quirky Update, suponiendo que pueda entenderlo y todas sus reglas asociadas, parece ser la solución más rápida disponible para resolver este problema, incluso teniendo en cuenta las nuevas características disponibles en SQL 2012. En SQL 2012, el enfoque de marco de ventana es, sin duda, Compacto y elegante, pero un poco arrastra la solución Quirky Update a través de las filas que hemos probado. Estos resultados de la prueba parecen conformarse a una prueba anterior en los totales corrientes en SQL 8220Denali8221 CTP3 por el amo certificado Microsoft Wayne Sheffield en su blog. Si usted está atascado con una versión anterior de SQL (2005 o 2008), y por alguna razón usted no puede acostumbrarse usando una Actualización Quirky (por ejemplo, si usted no confía en este comportamiento indocumentado), las soluciones más rápidas disponibles para usted son CROSS APPLY TOP o Utilizando una correlativa sub-consulta, ya que ambos parecían estar en un estrecho vínculo a través del tablero. Parece que el 8220traditional8221 INNER JOIN es algo que hay que evitar. Probablemente sólo empeorará si necesita hacer aritmética de fecha dentro de la cláusula JOIN8217s ON. Del mismo modo, el uso de una Tally Table o múltiples LAG s (SQL 2012) ciertamente no era el camino a seguir. No exploramos las soluciones basadas en CURSOR, pero puede retroceder la pista del artículo al que se hace referencia en los totales en ejecución para obtener una idea de cómo podrían funcionar en este caso. También he visto algunas soluciones que emplean una expresión de tabla común recursiva (rCTE), pero ciertamente no apostaría a su rendimiento en comparación con las soluciones QU o marco de ventana. Hay muchas maneras de calcular los valores dentro de una ventana de balanceo en SQL y hay algunos ganadores de rendimiento claros entre ellos. Esperamos que esta guía de los métodos disponibles sea interesante e informativa. Descargas Total: 30 Media: 4.6 / 5 Dwain Camps ha sido un director de proyectos durante muchos años. Debido a que el rendimiento de las aplicaciones puede ser un factor crítico para el éxito de los proyectos, ha estado evangelizando sobre la necesidad de desarrollar SQL de alto rendimiento. Mediante la tutoría y la creación de artículos sobre SQL, espera formar a una futura generación de ingenieros de software de la manera correcta y equivocada para entregar código SQL. También tiene un interés especial en el desarrollo de soluciones a problemas complejos e intensivos en datos utilizando SQL de alto rendimiento porque la naturaleza declarativa de SQL permite el desarrollo de soluciones algorítmicamente únicas que los lenguajes procedimentales pueden no ser capaces de realizar. Sigue a Dwain en Twitter Artículos relacionados También en SQL Con el aumento de las bases de datos NoSQL que están explotando aspectos de SQL para la consulta, y están abrazando la transaccionalidad completa, existe el peligro de los modelos de documento-naturaleza jerárquica causando un conflicto fundamental con la teoría relacional Pidió a nuestro experto relacional, Hugh Bin-Haad para exponer un área difícil para los teóricos de la base de datos. hellip Leer más También en SQL Server Cada programador de base de datos SQL Server debe estar familiarizado con las funciones del sistema. Estos van desde lo sublime (como rowcount o la identidad) hasta el ridículo (IsNumeric ()) Robert Sheldon ofrece una visión general de los más utilizados de them. hellip Leer más También en T-SQL Programación Para poder hacer pleno uso de Para obtener más información sobre una base de datos, debe familiarizarse con las funciones de metadatos. Guardan una gran cantidad de tiempo y escriben al consultar los metadatos. Una vez que usted consigue la caída de estas funciones, el catálogo del sistema de repente parece simple de usar, como Robert Sheldon demuestra en este article. hellip Leer más También en la programación T-SQL La forma en que el formato de código T-SQL puede afectar a la productividad de la Personas que posteriormente tienen que mantener su trabajo. Nunca es una buena experiencia ver código SQL, grito que el diablo escribió este código, y luego darse cuenta de que era usted. Grant da algunos ejemplos de mal formato y explica por qué nunca debe registrarse en mal formato SQL code. hellip Leer más Muy bueno Gran artículo Me sorprendió que LAG () hizo tan mal. Supongo que cada invocación se realiza por separado en lugar de factorizado y optimizado como una ventana. Gran explicación Estoy de acuerdo, esta es una gran explicación de diferentes maneras de calcular los valores dentro de una ventana de balanceo. Si prueba estos ejemplos en SQL 2012, tiene que cambiar MyTable con RollingTotalsExample. Muchas gracias, Sr. Camps Tally método Hola Dwain, me di cuenta de que su consulta Tally tabla estaba causando un operador Table Spool y pensé que podría considerar hacer la Tally parte de una tabla de fechas como este: SELECT GroupingDate, ValueMAX (CASE GroupingDate WHEN Fecha THEN aValue END), Rolling12MonthsCASE WHEN ROWNUMBER () OVER (ORDER BY GroupingDate) lt 12 THEN NULL ELSE SUM (Valor) END INTO ResultsSoln2 DE RollingTotalsExample a CROSS APPLY (mdash Quitar los valores aritméticos DATE (Date) (Fecha2), (Fecha3), (Fecha4), (Fecha5), (Fecha6), (Fecha7), (Fecha8), (Fecha9), (Fecha10), (Fecha11)) c (Fecha de agrupación) GRUPO POR AgruparDate TENIENDO AgruparDate lt MAX (Fecha) ORDER BY GroupingDate (Disculpas si el formato es mala ndash sin vista previa) Este cambio todavía wouldnrsquot lo convierten en un contendiente, pero hace una mejora masiva que queryhellip Gracias por los comentarios Gracias Joe y Nic. Irsquom contento de haber encontrado el artículo interesante. Joe: Yo también estaba un poco sorprendido por los resultados del LAG y me hace preguntarme cuál sería el punto de equilibrio. Tal vez 3 meses podría no ser tan malo, pero todavía es difícil creer que podría ser más rápido que el QU. Tally Tables MM: Por alguna razón, tengo una preferencia personal por las tablas de Tally en línea, pero sus resultados son interesantes si sólo para considerar en otros casos. Asistencia con Moving Annual Total Mi primer puesto. Necesito calcular el total anual móvil para el valor anterior para los 12 meses precedentes, con este mes el mes 12. Entonces necesito conseguir el total anual móvil para los 12 meses antes de esto. Con la idea de comparar MAT para este mes con el mes correspondiente del año pasado, y para cada mes anterior. Mi intento me dio esto: Con cte como (SELECT rNum ROWNUMBER () Sobre (order by Date) Fecha. Valor Rolling12MonthsCASE WHEN ROWNUMBER () OVER (ORDEN POR Fecha) gt 11 THEN SUM (Value) OVER (ORDER BY Fecha ROWS ENTRE DeMax Donde rNum entre mRNum ndash 23 y mRNum Con la posibilidad de cambiar la declaración Were para reflejar si quiero este año o el Año anterior. Mis datos reales tienen la fecha como en entero 201409 que creo que hará la vida más fácil para mí como puedo 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 Rolling totales de 12 meses utilizando SQL 2012 y un marco de ventana SI OBJECTID (lsquotempdb..PreviousYearrsquo) IS NOT NULL DROP TABLE PreviousYear Con cte como (SELECT rNum ROWNUMBER () Over (order by Date ) Fecha Valor Rolling12MonthsCASE WHEN ROWNUMBER () OVER (ORDEN POR Fecha) gt 11 THEN SUM (Valor) OVER (ORDER BY Fecha ROWS ENTRE 11 FILAS PREVIAS Y ACTUALES) END FROM RollingTotalsExample) Seleccionar pyRowNum ROWNUMBER () Over (order by mRNum ). . SStart mRNum ndash 24. EEnd mRNum ndash 12 en PreviousYear De cte, (Seleccionar mRNum max (rNum) de cte) deMax Donde rNum entre mRNum ndash 23 y mRNum ndash 12 mdash Rolling 12 meses totales usando SQL 2012 y un marco de ventana SI OBJECTID (lsquotempdb..ThisYearrsquo) IS NOT NULL DROP TABLE Este año con cte como (SELECT rNum ROWNUMBER () Sobre (ordenar por fecha) Fecha. Valor. Rolling12MonthsCASE WHEN ROWNUMBER () OVER (ORDEN POR Fecha) gt 11 THEN SUM (Valor) OVER (ORDEN POR Fecha ROWS ENTRE 11 FILAS PREVIAS Y ACTUALES) FIN DE RollingTotalsExample) Seleccionar tyRowNum ROWNUMBER () Over (ordenar por mRNum). . SStart mRNum ndash 11. EEnd mRNum en ThisYear De cte, (Seleccionar mRNum max (rNum) de cte) deMax Donde rNum entre mRNum ndash 11 y mRNum Seleccione desde ThisYear ty Izquierda Join PreviousYear py en ty. tyRowNum py. pyRowNum Estos pueden trabajar Irsquom no cerca de una comp Sql acceso ahora mismo así que canrsquot probarlo (puede haber algunos errores tipográficos / sintaxis). SELECT T. DateKey, AVG (T. ValueField) OVER (ODER BY T. DateKey ASC ENTRE 365 PREDETERMINADOS Y CORRIENTE ROW) AS YMAValueField FROM DataTable AS T ORDER BY T. DateKey ASC En caso de que AVG sea una de las funciones agregadas no soportadas Con el rango BETWEEN (sé SUM es compatible). SELECT T. DateKey, SUM (T. ValueField) OVER (ODER BY T. DateKey ASC ENTRE 365 ANTECEDENTES Y CORRIENTE ROW) / CASO CUANDO DATEDIFF (DAY, StartDate, T. DateKey) lt 365 LUE DATEDIFF (DAY, StartDate, T Únase a más de 200.000 profesionales de Microsoft y obtenga acceso completo y gratuito a artículos técnicos, nuestro boletín de noticias Simple Talk de dos veces al mes y herramientas gratuitas de SQL. . Visite nuestra biblioteca de patrones y prácticas para obtener más información sobre la gestión del ciclo de vida de la base de datos. Descubra cómo automatizar el proceso de creación, prueba e implementación de los cambios en la base de datos para reducir el riesgo y hacer posibles versiones rápidas. Los artículos más calificados recientes en T-SQL ProgrammingSQL Server Código T-SQL para calcular una media móvil Por: Dallas Snider Leer comentarios Consejos relacionados: Más funciones - UDF definido por el usuario Problema ¿Cómo puedo suavizar los datos en una columna con una media móvil en T - SQL ¿Puede por favor recorrer un ejemplo en SQL Server con código T-SQL? ¿Cómo podemos validar los resultados? Solución Los datos de series temporales pueden ser intrínsecamente ruidosos y una buena manera de suavizar los datos es calcular una media 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/2016