Tengo una tabla de datos de uso horario del producto (cuántas veces se utiliza el producto). Asimismo, tengo los datos de uso para 4 productos diferentes (ProductId de 1 a 4) almacenados por cada hora en la tabla de productos. Como usted puede imaginarse, está creciendo constantemente mientras que el proceso nocturno de ETL vierte los datos para el día anterior entero. Si un producto no se utiliza en ninguna hora del día, el registro para esa hora no aparecerá en esta tabla. Del mismo modo, si un producto no se utiliza para todo el día, no habrá ningún registro para ese día en la tabla. Necesito generar un informe que dé el uso diario y los últimos 7 días que balancean el promedio Y así sucesivamente. Estoy pensando en crear una vista indexada en el servidor SQL 2014. ¿Se puede pensar en una consulta SQL eficiente para hacer esto se preguntó Sep 18 14 a las 21:08 Esta es una pregunta 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). Usted ve que AVG se envuelve con el caso cuando rownum gt p. days entonces para forzar NULL s en 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 sucio método de unión externa izquierda (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. Aquí está la consulta que usaría con SQL Server 2012. Compartir esto: Anteriormente hablamos sobre cómo escribir promedios de rodadura en Postgres. Por demanda popular estaban mostrando cómo hacer lo mismo en MySQL y SQL Server. Bueno cubrir cómo anotar gráficos ruidosos como este: Con una línea promedio de 7 días anteriores como este: La gran idea Nuestro primer gráfico de arriba es bastante ruidoso y difícil de obtener información útil. Podemos suavizarlo trazando un promedio de 7 días sobre los datos subyacentes. Esto se puede hacer con funciones de ventana, auto-uniones, o subconsultas correlacionadas - y cubrir las dos primeras. Bueno empezar con un promedio anterior, lo que significa que el punto promedio en el 7 del mes es el promedio de los primeros siete días. Visualmente esto cambia los picos en el gráfico a la derecha, ya que un pico grande se promedia en los siguientes siete días. En primer lugar, crear una tabla de conteo intermedio Queremos calcular un promedio sobre el total de inscripciones para cada día. Suponiendo que tengamos una tabla típica de usuarios con una fila por usuario nuevo y una marca de tiempo createdat, podemos crear nuestra tabla de agregados agregados como así: En Postgres y SQL Server puedes usar esto como un CTE. En MySQL puede guardarlo como una tabla temporal. Postgres Rolling Media Afortunadamente Postgres tiene funciones de ventana que son la forma más sencilla de calcular un promedio de ejecución. Esta consulta asume que las fechas no tienen espacios. La consulta está promediando en las últimas siete filas, no en las últimas siete fechas. Si sus datos tienen espacios vacíos, llénelos con generateseries o ensamblando contra una tabla con filas de fecha densas. MySQL Rolling Average MySQL carece de funciones de ventana, pero podemos hacer una computación similar usando auto-uniones. Para cada fila en nuestra tabla de conteo, nos unimos a cada fila que estaba dentro de los últimos siete días y tomar el promedio. Esta consulta gestiona automáticamente los intervalos de fechas, ya que estamos viendo las filas dentro de un intervalo de fechas en lugar de las N filas anteriores. SQL Server Rolling Media SQL Server tiene funciones de ventana, por lo que calcular el promedio de balanceo se puede hacer en el estilo Postgres o estilo MySQL. Por simplicidad, estaban usando la versión de MySQL con una autojunción. Esto es conceptualmente lo mismo que en MySQL. Las únicas traducciones son la función dateadd y se denominan explícitamente grupo por columnas. Otros promedios Nos enfocamos en el promedio de 7 días en este post. Si queremos ver el promedio de 7 días, es tan simple como clasificar las fechas en la otra dirección. Si quisiéramos mirar un promedio centrado, usamos: Postgres: filas entre 3 anteriores y 3 siguientes MySql: entre signups. date - 3 y signups. date 3 en MySQL SQL Server: entre dateadd (día, -3, signups. Calculando valores dentro de una ventana de balanceo en Transact SQL Dwain Camps Calculando valores dentro de una ventana de balanceo en SQL Cada vez que necesite combinar valores entre varias filas en SQL, el problema puede ser Nos centraremos en el problema de los totales de doce meses, pero nuestros métodos pueden aplicarse 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 eficiente160 intentaremos responder a esta pregunta en este artículo. Estaremos trabajando en SQL 2012. 160Si desea seguir, puede utilizar el recurso de ejemplo consultas. sql que encontrará en el archivo 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 hemos agrupado sus transacciones por cada mes.160 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. Antes de continuar, debemos tener en cuenta que hay quienes insisten en que el método QU representa un comportamiento indocumentado de SQL Server y por lo tanto no es de confianza.160 Podemos decir que la sintaxis es Claramente descrito por la entrada de MS Books On Line para la instrucción UPDATE para las versiones de SQL 2005, 2008 y 2012.160 De hecho, se remonta más allá de eso. He utilizado con éxito en SQL Server 2000, pero fue heredado de Sybase y estaba en la primera versión de SQL Server jamás lanzado.160 Para los adversarios I8217ll decir que el 8220indocumented8221 comportamiento es al menos coherente en todas las versiones y probablemente no hay razón para Sospecha que se depreciará o cambiará en futuras versiones de MS SQL.160 Considérese advertido Si alguna vez considera utilizar un QU para resolver cualquier problema, debe tener en cuenta las muchas reglas que se aplican (también incluidas en el artículo referenciado Por Jeff) .160 Los principales, que se han manejado en esta consulta, se pueden resumir como: La tabla debe tener un índice agrupado que indique el orden de las filas de origen por el período como usted desea que sea atravesado. 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 usted necesita para DECLARAR .160 Básicamente lo que estamos haciendo es hacer un seguimiento de los últimos doce (rezagados) valores, con el fin de eliminar el 12 (donde el Rolling12Months se asigna) de lo que de otro modo es un total de ejecución QU como se describe en el artículo de Jeff8217s. Tenemos grandes esperanzas en su velocidad, dado que se sabe que es el método más rápido para resolver el problema de los totales corrientes. Una vez más, usted debe convencerse de que los resultados son consistentes con las soluciones anteriores, y esta solución sí se comporta de la misma manera en SQL 2012.160 Si usted está conmigo hasta ahora, también puede estar preguntándose qué pasa si necesito calcular varios corriendo doce Mes totales a través de particiones diferentes 8221160 Esto es relativamente simple para todas las otras soluciones presentadas, pero propone un poco de desafío usando el QU.160 La respuesta a esto se puede encontrar en el archivo de recursos adjunto: Quirky Update Partitioned. sql. SQL 2012 Soluciones Hasta ahora, todo lo que hemos hecho funcionará en SQL 2008.160 Lo único que we8217ve hecho que no está soportado en SQL 2005 es las inicializaciones de las variables que DECLARE d en el enfoque de QU.160 Ahora vamos a ver qué nuevas características de SQL 2012 Tiene que se puede 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á 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 se llevan a cabo múltiples soluciones es comprobar los tiempos reales de ejecución en un servidor inactivo utilizando un arnés de prueba con muchas filas.160 Se muestra nuestro arnés de prueba junto con la modificación de la solución 1 y 2 Consulte los comentarios en el código) para: Insertar los resultados en una tabla temporal, para evitar el efecto de tiempo transcurrido de devolver las filas a la cuadrícula de resultados de SQL Server Management Studio8217s. 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 la CPU parecen ser consistentes entre los diferentes métodos con respecto a su orden.160 Todos parecen escalar de una 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 ligeramente arrastra la solución Quirky Update a través de las filas que hemos probado.160 Estos resultados de prueba parecen conformarse a una prueba anterior en Running Totals en SQL 8220Denali8221 CTP3 de Microsoft Master Certified 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 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 una estrecha relación a través del tablero. Parece que el 8220traditional8221 INNER JOIN es algo que hay que evitar.160 Probablemente sólo empeorará si necesita hacer aritmética de fechas dentro de la cláusula ON de JOIN8217s.160 De la misma manera, utilizando una Tabla de Tareas o varios GAL (SQL 2012) ciertamente No era el camino a seguir. No exploramos las soluciones basadas en CURSOR, pero se puede retroceder al artículo referenciado en los totales corrientes para tener una idea de cómo podrían funcionar en este caso.160 También he visto algunas soluciones que emplean una expresión de tabla común (rCTE) recursiva, , Pero ciertamente wouldn8217t apuesta por su rendimiento en comparación con el QU o ventana marco soluciones. Hay muchas maneras de calcular valores dentro de una ventana de balanceo en SQL y hay algunos ganadores claros de rendimiento entre ellos.160 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 de éxito para 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 necesita 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. Ahorran mucho 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 Usted debe pegarse a usar tablas en SQL Server, en lugar de montones que tienen Sin índice agrupado, a menos que tenga razones bien consideradas para elegir montones. Sin embargo, hay usos para los montones en circunstancias especiales, y es útil saber cuáles son estos usos, y cuando usted debe evitar montones. Uwe Ricken explica, y demuestra por qué no sería prudente usar pilas en lugar de tablas cuando los datos son susceptibles de cambiar. 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 de tabla de Tally estaba causando un operador de tabla Spool y pensé que podría considerar hacer la parte quotTequal de una tabla quotDatesquot como esto: SELECT GroupingDate, ValueMAX (CASE GroupingDate WHEN Date 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 (8212 Eliminar los valores aritméticos DATE (Fecha), (Fecha1) (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 (Disculpe si el formato es malo 8211 sin vista previa) Este cambio todavía wouldn8217t lo convierten en un contendiente, pero hace una mejora masiva que query8230 Gracias por los comentarios Gracias Joe y Nic. Me alegra que hayas 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 (Seleccionar mRNum max (rNum) de cte) deMax Donde rNum entre mRNum 8211 23 y mRNum Con la posibilidad de cambiar la sentencia Were para reflejar si quiero este año o la 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 algo de ruido) 8212 Rolling totales de 12 meses usando SQL 2012 y un marco de ventana SI OBJECTID (8216tempdb..PreviousYear8217) NO ES TABLA NULL DROP 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 8211 24. EEnd mRNum 8211 12 in PreviousYear De cte, (Seleccionar mRNum max (rNum) de cte) deMax Donde rNum entre mRNum 8211 23 y mRNum 8211 12 8212 Rolling 12 meses totales usando SQL 2012 y un marco de ventana IF OBJECTID (8216tempdb..ThisYear8217) 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 8211 11. EEnd mRNum en ThisYear From cte, (Seleccionar mRNum max (rNum) de cte) deMax Donde rNum entre mRNum 8211 11 y mRNum Seleccione desde ThisYear ty Izquierda Join PreviousYear py en ty. tyRowNum py. pyRowNum Estos pueden trabajar I8217m no cerca de un comp Sql ahora mismo, así que puedo probarlo (puede haber errores tipográficos / de 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 obtendrá acceso completo y gratuito a artículos técnicos, nuestro boletín de noticias Simple Talk de dos veces al mes y herramientas SQL gratuitas. . 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
No comments:
Post a Comment