Pl Sql Media Móvil

Promedio móvil exponencial Había una buena pregunta en OTN hoy sobre si hay una función estándar de Oracle para calcular la media móvil exponencial. La respuesta es que no hay tal función, pero con la cláusula modelo, se puede calcular muy fácil. Y es un gran ejemplo de lo que quiero decir con un número variable de cálculos basados ​​en valores calculados, escrito en mi tercera parte del tutorial de cláusula modelo. Antes de hoy, ni siquiera sabía lo que una media móvil exponencial era exactamente. Usted puede leer más sobre él aquí en Wikipedia o aquí con un ejemplo agradable. Desde el primer enlace: Un promedio móvil exponencial (EMA), aplica factores de ponderación que disminuyen exponencialmente. La ponderación de cada uno de los puntos de datos más antiguos disminuye exponencialmente, dando mucha más importancia a las observaciones recientes, mientras que todavía no descarta las observaciones más antiguas por completo. A partir del segundo enlace: La fórmula para calcular un promedio móvil exponencial (EMA) es: X EMA actual (es decir, EMA a calcular) C Valor actual de los datos originales K Constante de suavización P EMA anterior (El primer EMA del rango a calcular es Arbitrario y puede ser el valor de datos original correspondiente o, a menudo, un valor de Media Móvil Simple K Smoothing Constant 2 / (1 n) Y esta fórmula es seguida por un ejemplo que he extendido un poco, usando esta tabla: Los registros del producto A coincide con el ejemplo en el enlace. He inventado los números del producto B. Aquí está la consulta de la cláusula de modelo que implementa la fórmula. Nota cómo la fórmula directamente traducir a la única regla de la cláusula de modelo. La constante de suavizado K es Establecer a .5, sobre la base de una ventana de valores (n) igual a 3. Desafío: intente esto sin la cláusula de modelo y ver si se puede llegar a algo más integral 5 comentarios: 11.2 características en uso con dat as (seleccione 39A39 Fecha del producto 392009-01-0139, 10 cantidad de la unión dual todas seleccionan 39A39, fecha 392009-02-0139, 15 de la unión dual todas seleccionan 39A39, fecha 392009-03-0139, 17 de la unión dual todas seleccionan 39A39, fecha 392009-04-0139, 20 de unión dual todos seleccionan 39A39, fecha 392009-05-0139, 22 de unión dual seleccionan todos 39A39, fecha 392009-06-0139, 20 de unión dual seleccionan todos 39A39, fecha 392009-07-0139 , 25 de unión dual todos seleccionan 39A39, fecha 392009-08-0139, 27 de unión dual seleccionan todos 39A39, fecha 392009-09-0139, 30 de unión dual seleccionan todos 39A39, fecha 392009-10-0139, 35 de unión dual Todos seleccionan 39A39, fecha 392009-11-0139, 37 de la unión dual todos seleccionan 39A39, fecha 392009-12-0139, 40 de la unión dual todos seleccionan 39B39, fecha 392009-01-0139, 0 de la unión dual todo selecto 39B39, fecha 392009-02-0139, 50 de unión dual todos seleccionan 39B39, fecha 392009-03-0139, 10 de unión dual todos seleccionan 39B39, fecha 392009-04-0139, 40 de unión dual seleccionan todos 39B39, fecha 392009-05-0139 , 15 de doble unión todos seleccionan 39B39, fecha 392009-06-0139, 35 de doble unión todos seleccionan 39B39, fecha 392009-07-0139, 30 de doble unión todos seleccionan 39B39, fecha 392009-08-0139, 30 de doble unión Todos selecto 39B39, fecha 392009-09-0139, 20 de la unión dual todo selecto 39B39, fecha 392009-10-0139, 20 de la unión dual todos selecto 39B39, fecha 392009-11-0139, 20 de la unión dual todos selecto 39B39, fecha 392009-12-0139, 20 desde dual), rns as (select dat. . Rownumber () over (partición por pedido de producto por mes) rn -. 2 / (1count () sobre (partición por producto)) k. 0.5k de dat), res (producto, mes, cantidad, rn, x) como (seleccione r. product, r. month, r. amount, r. rn, r. amount x de rns r donde rn 1 union all select Ns. product, ns. month, ns. amount, ns. rn, ns. k (ns. amount - es. x) es. xx de rns ns, res es donde ns. rn es. rn 1 y ns. product es Producto, mes, cantidad, rn, ronda (x, 3) EMA de res orden por producto, mes después de computar la forma cerrada Vine con el siguiente código que si más como una ofuscación de algo completo. La idea es crear múltiplos en ejecución utilizando una concatenación de cadenas y la funcionalidad xml-eval. Las formas cerradas de los casos especiales sólo necesitan sumas corrientes. Hay un caso general y dos casos especiales que son mucho más fáciles: con t1 como (seleccionar el producto, el mes, el importe, el importe ci, el número rownumber () sobre (partición por pedido de producto por mes) rn, --2 / (1 rownumber () Ki 0.5 ki de las ventas), t2 como (seleccionar el producto, el mes, la cantidad, (caso cuando rn 1 entonces 1 else ki end ci) ai, caso cuando rn 1 entonces 1 else (1 (REPLACE (wmconcat (bi) over) (PARTITION BY product ORDER BY MONTH filas ENTRE la línea precedente y ACTUAL sin límite), 39,39, 3939) RETORNAR contenido).getnumberval () mi FROM t2), t4 como (seleccionar producto, mes, cantidad, mi, (ai / mi) xi de t3) SELECT producto, MONTH, amount, round (PARTITION BY product ORDER BY MONTH filas ENTRE fila sin precedentes y ACTUAL), 3) ema FROM t4 Caso especial K 0.5: con t1 como (seleccionar producto, mes, cantidad, rownumber () sobre (partición por pedido de producto por mes) rn (1), 1)) (1) () () () () () () () () () () (2, rn), 3) ema de t1 Caso especial K 2 / (1 i): con t1 como (seleccione el producto, el mes, la cantidad, el número de rownumber ) Sobre (partición por pedido de producto por mes) rn, cantidad rownumber () sobre (partición por pedido de producto por mes) ci de ventas) seleccionar producto, mes, cantidad, ronda Filas entre la fila precedente y actual sin límite) 2 / (rn (rn 1)), 3) ema de t1 I39ll publicar la prueba de la forma cerrada si alguien está interesado en ella. Este es un gran ejemplo de quotfun con SQLquot :-) Una combinación de XMLQuery, el wmconcat indocumentado, y funciones analíticas con la cláusula windowing. Me gusta. Aunque no es tan completo como la variante de cláusula modelo y el Rafu39s recursivo con uno, como usted mismo dijo. Y seguro, me gustaría ver la prueba de la forma cerrada. Abordé otra pregunta: cómo optimizar la constante de suavizado SELECT k - suavización constante. Mse - error medio cuadrado FROM (SELECT FROM ventas MODEL DIMENSION BY (producto. ROWNUMBER () OVER (PARTITION BY producto ORDEN POR mes ASC) rn) MEDIDAS (cantidad - cantidad de ventas mes - mes 0 ASC 0 AS P - 0 AS X. 0 AS SE - error cuadrado - - fila de trabajo y atributos - a) fila de trabajo es producto 39X39, rn 1 - b) atributos de trabajo son los siguientes:. 0 AS SSE - suma SE para todos los productos / meses. 0 AS MSE - significa SSE para todos los productos / meses. 0 AS k - para todos los productos / meses. 0 AS PreMSE - previo k39s MSE para todos los productos / meses. 0 AS diff - entre MSE actual y anterior. 0,1 AS delta - incremento inicial. 0 AS priorpt - punto de partida inicial -) REGLAS ITERATE (99) HASTA (abs (diff39A39,1) lt 0,00010) (Cany, rn amountcv (), cv () K39A39,1 priorpt39A39,1 delta39A39,1. , Rn ORDEN POR producto, rn ASC COALESCE (K39A39,1 Ccv (), cv () (1-K39A39,1) Xcv (), cv () - 1, Ccv (), cv ()) Pproduct, rn Xcv (), Cv () - 1. SEproduct, rn POWER (Ccv (), cv () - Xcv (), cv () - 1, 2) SSE39A39,1 SUM (SE) any, any MSE39A39,1 SUM (SE) any, any / 24. diff39A39,1 CASE iterationnumber CUANDO 0 entonces NULL ELSE preMSE39A39,1 - MSE39A39,1 END. PreMSE39A39,1 MSE39A39,1 delta39A39,1 CASO CUANDO diff39A39,1 lt 0 THEN - abs (delta39A39 , 1/2) ELSE abs (delta39A39,1) ENDpt 39A39,1 K39A39,1)) donde el producto 39A39 y rn 1 / K MSE ---------- --------- - .599999237 174.016094Esta 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ó Jan 9 at 0:33 Su respuesta 2016 Stack Exchange, IncAVG (Transact-SQL) ALL Aplica la función agregada a todos los valores. ALL es el valor predeterminado. DISTINCT Especifica que AVG se realizará sólo en cada instancia única de un valor, independientemente de cuántas veces se produzca el valor. Expresión Es una expresión de la categoría de tipo de datos numéricos o aproximados numéricos, excepto para el tipo de datos de bit. Las funciones agregadas y las subconsultas no están permitidas. OVER (partitionbyclause orderbyclause) partitionbyclause divide el conjunto de resultados producido por la cláusula FROM en particiones a las que se aplica la función. Si no se especifica, la función trata todas las filas del conjunto de resultados de la consulta como un solo grupo. Orderbyclause determina el orden lógico en el que se realiza la operación. Ordenar porcláusula es requerida. Para obtener más información, vea Cláusula OVER (Transact-SQL). El tipo de retorno se determina por el tipo de resultado de expresión evaluado. Categoría decimal (p, s) Si el tipo de datos de la expresión es un tipo de datos de alias, el tipo de retorno también es del tipo de datos de alias. Sin embargo, si se promueve el tipo de datos base del tipo de datos alias, por ejemplo de tinyint a int. El valor de retorno es del tipo de datos promovido y no del tipo de datos alias. AVG () calcula el promedio de un conjunto de valores dividiendo la suma de esos valores por el recuento de valores nonnull. Si la suma supera el valor máximo para el tipo de datos del valor devuelto, se devolverá un error. AVG es una función determinista cuando se usa sin las cláusulas OVER y ORDER BY. No es determinista cuando se especifica con las cláusulas OVER y ORDER BY. Para obtener más información, consulte Funciones deterministas y no determinísticas. A. Uso de las funciones SUM y AVG para los cálculos En el ejemplo siguiente se calculan las horas de vacaciones promedio y la suma de horas de baja por enfermedad que han utilizado los vicepresidentes de los ciclos de trabajo de aventura. Cada una de estas funciones agregadas produce un único valor de resumen para todas las filas recuperadas. El ejemplo utiliza la base de datos AdventureWorks2012.