T-Sql Moving Average Function

Necesito calcular medias móviles semanales y mensuales por sensor por día para un gran conjunto de datos de muestra sobre la base de algunos criterios de calidad. Tengo una solución de trabajo basada en sub-consultas correlacionadas (o autojuntos), pero me preguntaba si el uso de funciones analíticas es posible y resultaría en un mejor rendimiento Aquí es lo que tengo ahora (simplificado): He intentado reemplazar el sub - Consulta para el promedio semanal con el fragmento a continuación, pero obviamente no puede manejar días sin muestras correctamente. Pensé en usar expresiones de RANGE o PARTITION BY, pero no puedo averiguar cómo especificar el marco de ventana para seleccionar las muestras de, p. la semana pasada. Incluso consideré Quirky Update, pero además de ser desordenado, no creo que tenga sentido con estos muchos días siendo promediados. Preguntó 06 de mayo 15 a las 17:13 Necesito medias móviles para cada id y para cada día. Por ejemplo, en la fecha de hoy, cualquier Id dado daría lugar a una fila con el promedio semanal basado en muestras de 4/30 a 5/6 y el promedio mensual basado en muestras de 4/7 a 5/6. Así que los marcos de ventana son más grandes que los pasos o particiones. Ndash hjort 6 de mayo a las 21:07 Realmente consideré las funciones analíticas LEAD / LAG combinadas con PARTITION BY SensorId, pero por lo que he podido decir, se basan en el recuento de filas y por lo tanto no manejan los días sin muestras. La cláusula mencionada en mi pregunta original. ¿Puede usted proporcionarme un ejemplo concreto para ayudarme a entender mejor cómo estas funciones pueden ayudarme a resolver el problema? 6 de mayo a las 22:35 Eso depende de si usted quiere que los días que faltan se cuenten como días con valor 0, o que no se cuenten en todas. Para el valor 0, puede crear una tabla de quotdaysquot y unirla, por lo tanto quotfillingquot brechas para el desplazamiento con 0. Para la segunda opción, puede crear de nuevo una tabla quotdaysquot y unirse a ella con el CTE en el ID y el CTE. Fecha entre el Days. date-X y Days. Date. A continuación, puede calcular el recuento y el promedio de cada fecha. Ndash Amir Pelled May 7 15 at 7:46 Mi código compilado más rápido, tenía menos escanea y menos lógico lee con mi pequeño conjunto de datos aleatorios. Su tipo de difícil de contar con un conjunto de datos tan pequeño y no tengo sus índices y lo demás. Así que probarlo por ti mismo. Si algo es más sencillo que su consulta. Ahora en mi consulta, el mes promedio era difícil. Si quieres puedes hacerlo como los otros que usan un cierto número de días. Así que usted podría hacer el promedio de los últimos 30 días. Lo que puse en era promedio para ese mes actual solamente. Tengo la sensación que usted podría poner su subconsulta adentro para eso, pero no intenté esa causa su tarde aquí. Nota . Las filas de fantasía anteriores asumen que su es una fila para cada día sin vacíos, o bien sus datos serán sesgados. Si usted tiene alguna pregunta o necesita algo más, hágamelo saber Gracias por el ejemplo concreto. Sin embargo, se parece mucho al fragmento de código que mencioné en la parte inferior de mi pregunta original, excepto que su propuesta no tiene un promedio ponderado adecuado para manejar correctamente los días con un número diferente de muestras. Y como usted señala usted mismo, ninguno de ellos puede manejar días sin muestras, que es un requisito. Ndash hjort May 7 15 at 8:21 Yeah that39s las limitaciones de las funciones de Windows. La única manera alrededor de eso, que wouldn39t ser bastante, sería crear la lista de cada datos de la fecha mínima a la fecha de finalización para cada SensorID entonces LEFT JOIN él a su tabla. Pero en ese momento estás haciendo mucho trabajo extra, es probablemente más eficiente para seguir con lo que tienes. Ndash Stephan May 7 15 at 12: 44 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. 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 usted utilizaría con SQL Server 2012. Compartir esto: Código de SQL Server T-SQL 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 un 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 desde mssqltips: Última actualización: 3/8 / 2016Cálculo de 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 difícil, particularmente 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 últimos artículos calificados en T-SQL Programming