Media Móvil Exponencial En Sql

Promedio móvil en T-SQL Un cálculo común en el análisis de tendencias es el promedio móvil (o móvil). Un promedio móvil es el promedio de, por ejemplo, las últimas 10 filas. El promedio móvil muestra una curva más suave que los valores reales, más aún con un período más largo para el promedio móvil, lo que lo convierte en una buena herramienta para el análisis de tendencias. Esta publicación del blog mostrará cómo calcular el promedio móvil en T-SQL. Se utilizarán diferentes métodos dependiendo de la versión de SQL Server. El siguiente gráfico muestra el efecto de suavizado (línea roja) con un promedio móvil de 200 días. Las cotizaciones de acciones son la línea azul. La tendencia a largo plazo es claramente visible. T-SQL Moving Avergage 200 días La demostración a continuación requiere la base de datos TAdb que se puede crear con el script que se encuentra aquí. En el próximo ejemplo calcularemos un promedio móvil de los últimos 20 días. Dependiendo de la versión de SQL Server, habrá un método diferente para hacer el cálculo. Y, como veremos más adelante, las nuevas versiones de SQL Server tienen funciones que permiten un cálculo mucho más efectivo. SQL Server 2012 y posterior Moving Average Esta versión hace uso de una función de ventana agregada. Lo nuevo en SQL 2012 es la posibilidad de restringir el tamaño de la ventana especificando cuántas filas que preceden a la ventana deben contener: Las filas precedentes son 19, porque incluiremos la fila actual también en el cálculo. Como puede ver, el cálculo del promedio móvil en SQL Server 2012 es bastante simple. La siguiente figura muestra el principio de ventana. La fila actual está marcada con amarillo. La ventana está marcada con un fondo azul. El promedio móvil es simplemente el promedio de QuoteClose en las líneas azules: T-SQL Moving average window. Los resultados de los cálculos en versiones anteriores de SQL Server son iguales, por lo que no se mostrarán de nuevo. SQL Server 2005 8211 2008R2 Moving Average Esta versión hace uso de una expresión de tabla común. El CTE es auto referenciado para obtener las últimas 20 filas para cada fila: Promedio móvil antes de SQL Server 2005 La versión anterior a 2005 utilizará una combinación externa izquierda en la misma tabla para obtener las últimas 20 filas. Comparación de rendimiento Si ejecutamos los tres métodos diferentes simultáneamente y comprobamos el plan de ejecución resultante, hay una diferencia dramática en el rendimiento entre los métodos: Comparación de tres Diferentes métodos para calcular el promedio móvil Como puede ver, las mejoras de la función de ventana en SQL 2012 hacen una gran diferencia en el rendimiento. Uso Como se mencionó en el comienzo de este post, las medias móviles se utilizan como una herramienta para ilustrar las tendencias. Un enfoque común es combinar promedios móviles de diferentes longitudes, con el fin de detectar cambios en las tendencias a corto, mediano y largo plazo, respectivamente. De particular interés son el cruce de líneas de tendencia. Por ejemplo, cuando la tendencia corta se mueve sobre la tendencia larga o media, esto puede interpretarse como una señal de compra en el análisis técnico. Y cuando la tendencia corta se mueve bajo una línea de tendencia más larga, esto puede interpretarse como una señal de venta. El siguiente gráfico muestra Cotizaciones, Ma20, Ma50 y Ma200. T-SQL Ma20, Ma50, Ma200 comprar y vender señales. Esta entrada del blog forma parte de una serie sobre análisis técnico, TA, en SQL Server. Vea los otros mensajes aquí. Publicado por Tomas LindIm tratando de implementar una media móvil exponencial (EMA) en postgres, pero como comprobar la documentación y pensar en ello, cuanto más intento, más confundido estoy. La fórmula para EMA (x) es: Parece ser perfecto para un agregador, manteniendo el resultado del último elemento calculado es exactamente lo que hay que hacer aquí. Sin embargo, un agregador produce un solo resultado (como reducir, o doble) y aquí necesitamos una lista (una columna) de resultados (como mapa). He estado comprobando cómo funcionan los procedimientos y las funciones, pero AFAIK producen una sola salida, no una columna. He visto un montón de procedimientos y funciones, pero realmente no puedo entender cómo interactúa esto con el álgebra relacional, especialmente al hacer algo como esto, una EMA. No tuve suerte buscando las Internets hasta ahora. Pero la definición de EMA es muy simple, espero que sea posible traducir esta definición en algo que funcione en postgres y sea simple y eficiente, porque mover a NoSQL va a ser excesivo en mi contexto. Calcula la agregación que produce el resultado en cada fila para cada sublista de los datos de entrada? Dado que parece que está utilizando el agregador hasta la fila n, devolviendo el resultado y luego pasando a la fila 0 para calcular la agregación hasta la fila n1 de nuevo. Hay alguna manera de usar la acumulación o alguna variable estática (como en C) para que esto tenga que ser calculado una vez Gracias. Ndash Trylks Jan 20 12 at 11:59 No, usa el valor acumulado. Si ejecuta la consulta con el comando quotraise infoquot descomentado, podrá ver que la función sólo se llama una vez para cada salida de fila. Postgresql emite el valor de estado en cada fila (si hubo un finalfunc definido, que se llamaría para transformar el estado en un valor de salida). Ndash araqnid Jan 20 12 at 12:04 ErwinBrandstetter: He revertido la mayoría de los cambios - en el caso del formato de la primera parte (ancla) de la consulta, EMA (x1) puede ser claramente representado con una sola línea - esto Corespnds a la única línea que lo define en la pregunta. En el caso de la parte recursiva de la consulta, he utilizado mn - 1 en la condición de unión para indicar la equivalencia de la relación con EMA (xn - 1) en la pregunta, aunque esto será menos eficaz si el rendimiento es un , El OP puede cambiar la condición de unión para que sea como usted sugirió. Ndash Mark Bannister Jan 16 12 a las 9: 27Exponential Moving Average Hubo una buena pregunta en OTN hoy acerca de 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 se traduce directamente 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 doble 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 doble unión 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 ORDEN POR MONTH filas ENTRE fila sin precedentes y ACTUAL), 3) ema FROM t4 Caso especial K 0.5: con t1 como (seleccione 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.016094