Cálculo de promedios móviles con T-SQL
Este artículo le mostrará cómo calcular promedios móviles con datos del mercado financiero a través del tiempo utilizando T-SQL. Hay dos promedios móviles utilizados con análisis técnico en los mercados financieros - Simple y exponencial. El objetivo aquí es proporcionarle una solución que le permitirá hacer los promedios móviles simples (SMA) de manera eficiente, y construir sobre eso para hacer EMA.
Promedio móvil simple (SMA)
La diferencia entre el promedio normal que usamos (es decir, a través de la función AVG ()) y una media móvil simple es que las medias móviles usan sólo un subconjunto de los datos completos con respecto a la fecha en la fila actual. Por ejemplo, en el mercado de valores, muchos de los sitios de finanzas y herramientas de gráficos cuentan con una superposición de 20 días SMA (media móvil simple) encima del precio de las acciones. La imagen de abajo muestra el precio de las acciones de Google en azul oscuro y su SMA de 20 días en azul claro.
El punto de datos a 30/12/09 para los 20 días de SMA es de alrededor de $ 600. Este promedio sólo incluye los 20 días de negociación anteriores. Como puede ver, es útil también para determinar los patrones de tendencia generales sin ser susceptible a la volatilidad a corto plazo. Sin embargo, es la caída es que es un indicador de retraso, y por lo tanto no es un buen indicador de lo que el futuro inmediato podría celebrar. Independientemente de ello, esta métrica puede ser útil con cualquier conjunto de datos históricos para gráficos de resumen de tendencias y instantáneas, ya sea para las existencias, los ingresos mensuales o el consumo de energía.
Implementación
En este punto, estoy adivinando la mayoría de los lectores de este artículo ya tienen los engranajes agitando sobre cómo resolver este problema. Así que vamos a pasar y entretener algunas estrategias sobre cómo lograr esto. Este es el código de configuración de este artículo que incluye los datos históricos de Google para Google:
Puesto que haremos todo nuestro filtrado en la columna de la fecha, es la opción obvia para nuestro índice agrupado.
Estrategia # 1 - Únete a Grupo Por
Esta estrategia es probablemente la solución que la mayoría de la gente va a subir con el primero. Se trata de unir la tabla sobre sí mismo, con condiciones que filtran la fecha en la segunda tabla como así:
Ahora en cuanto a rendimiento, esto no es una mala consulta - podríamos haber hecho mucho peor. Para mi máquina, esto resulta en aproximadamente 125 lecturas lógicas. Sin embargo, esta no es nuestra solución ideal como 1) podemos proporcionar un mejor rendimiento, y 2) no cumple con las especificaciones. A pesar de que se llama un promedio de 20 días, se refiere a los últimos 20 días de negociación, que son (para el mercado de valores) M-F, excepto los días festivos. Así que aunque hemos filtrado nuestros subconjuntos a 20 días, esa consulta volverá no más de 16 días de negociación debido a fines de semana y días festivos. Con el fin de cumplir con las especificaciones de nuestra solución - tenemos que agregar una columna de número que se ordena por quote_date y utilizar que para filtrar por. Aquí está el código para crear nuestra tabla modificada:
Ahora podemos modificar la consulta para filtrar por el campo numero de fila n, en lugar de por fecha. Además, con la consulta anterior, si había menos de 20 registros antes de la fila que se estaba procesando, no había nada en su lugar para hacer cumplir que el promedio mantuvo su tamaño de muestra fijo. Como resultado, la primera fila sólo promedió 1 fila, la segunda fila sólo promedió 2 filas, la tercera sólo 3, y así sucesivamente. En esta consulta, agregaremos una instrucción case a la función AVG () para asegurar que tiene 20 filas para promediar (de lo contrario devuelve null):
Ahora esta consulta cumple con nuestros requisitos. Sin embargo, todavía estamos flotando alrededor de las 125 lecturas lógicas cuando se miran las estadísticas de IO. Exploremos más profundamente para encontrar una solución mejor.
Estrategia # 2 - Uso de una pequeña matemática / w Totales corrientes
Con el fin de reducir aún más el rendimiento de esta consulta, revisemos cómo el SMA funciona matemáticamente. Nuestro SMA = Sum (20 últimos precios) / 20, y nuestra anterior solución nos obligó a recuperar las 20 filas anteriores para calcular la suma. Sin embargo, una vez que hemos calculado la primera suma (en la fila 20), en realidad ya no necesitamos seguir tomando la suma de los últimos 20 precios de cierre. Para aquellos que han hecho la división de ventana deslizante, este concepto proporcionará cierta familiaridad. Si se puede visualizar la ventana de movimiento de la suma total, dos cosas suceden a medida que avanzamos: 1) el precio de cierre de la nueva fila se agrega a la suma, y 2) el precio de cierre de la fila 20 anterior se resta. La fórmula de iteración sería:
Cantidad actual = Suma anterior + Actual Cerrar precio - Cerrar precio Hace 20 días
Si no entiende completamente este concepto, es más fácil de entender visualmente en una hoja de cálculo:
Figura 1 - Configuración de la base
Esto muestra el caso base en el que tenemos que hacer una suma inicial del precio de cierre en las primeras 20 filas. Recuerde que para esta solución, se centran en la suma, no en el promedio. La suma de 20 días para el 11/20/2009 es 11154.43. Ahora pasemos al siguiente día de negociación - 23/11/09:
Figura 2 - Primera iteración
Para calcular la suma de esta vez, ya no necesitamos 20 puntos de datos - sólo necesitamos tres: la suma del día anterior (C21), el precio de cierre actual (B22) y el precio de cierre del 26/10/2009 (B2 ). Conectándolo a nuestra fórmula, obtenemos:
= Suma anterior (11154.43) + Precio actual Cerrar (582.35) - Cerrar precio de hace 20 días (554.21)
Todo lo que tenemos que hacer es dividir este número por 20, y obtenemos nuestro promedio de 20 días para el 11/23/09 ($ 559.12). Avancemos una vez más.
Figura 3 - Segunda iteración
En este punto, debe empezar a ver la ventana deslizante & quot; Como iterar a través de las filas. Siéntase libre de probar esta iteración con la fórmula que utilizamos antes y los datos de la figura 3. Esperemos que el patrón se ha hecho evidente ahora que seguimos iterando a través de las filas. Tomando esto en consideración, volvamos al SQL y modificamos nuestra tabla #mod_goog_data con un campo adicional que contiene el precio de cierre de 20 días hábiles antes:
Con esta nueva tabla #mod_goog_data, ahora tenemos acceso a 2 de los 3 puntos de datos en cada fila; Sin embargo todavía necesitamos datos de la fila anterior. Afortunadamente, podemos tomar la información útil proporcionada por Jeff Moden en su artículo sobre Totales corrientes (asegúrese de leer todas las reglas antes de intentar esto) y configurar una declaración de actualización que nos permita calcular el 20 Día SMA en un pase (Después de recoger la suma inicial de 20 días):
Aunque estamos ejecutando 3 instrucciones separadas para esta solución, cada una realiza solamente 2 lecturas lógicas. Además, nuestra actualización principal sólo requiere un solo pase para calcular el 20-Día-SMA. Creo que podemos estar de acuerdo en que no se puede obtener un rendimiento mucho mejor que esto. Una vez más asegúrese de leer el artículo de Jeff antes de proceder. Si usted se siente incómodo con la solución de totales corrientes de Jeff, recomendaría que se adhiera a la solución en la estrategia # 1. Teóricamente Recursive CTE debe funcionar bien con este tipo de problema, pero todas mis pruebas ha proporcionado un rendimiento más débil - se tarda más tiempo, y hace mucho más lógico que la solución lógica en la Estrategia # 1.
Promedios móviles exponenciales
Sobre la base de nuestra solución para SMA, podemos utilizar la misma estrategia para calcular EMA. Los promedios exponenciales son ligeramente diferentes en que pone más peso en los datos más recientes, y menos en los datos más antiguos. En el cálculo de la EMA, a diferencia de la SMA (que los algoritmos se basa en los últimos 20 días), la EMA en realidad sólo necesita la fila anterior EMA, y el precio de cierre actual. Aquí está la fórmula:
EMA = (Close Price * K) + (EMA anterior) * (1 - K)
K = 2 / (1 + N)
N = Número de períodos
El EMA es más comúnmente usado para calcular un indicador técnico llamado Convergencia / Divergencia de Media Móvil (MACD). Al igual que el SMA, es también un indicador de tendencia siguiente y se utiliza para determinar el impulso del mercado (aquí hay más información de Wikipedia sobre MACD). La fórmula para el MACD es simplemente:
MACD = EMA de 12 días - EMA de 26 días
En nuestro ejemplo calcularemos tanto el EMA de 12 días como el de 26 días para proporcionar un ejemplo práctico. Dado que el cálculo de la EMA requiere el valor EMA del día anterior, debemos configurar un caso base para generar el primer valor EMA. Esto se puede hacer simplemente calculando un promedio regular de todos los días antes de nuestro primer cálculo EMA. Si todo esto es mucho para entender, no te preocupes es realmente muy simple. Una vez más, aquí hay un ejemplo con una hoja de cálculo para la EMA de 12 días:
En la 11ª fila de datos (D12), el promedio inicial se calcula calculando los primeros 11 precios de cierre y dividiendo por 11 (aunque en la hoja de cálculo aparece en la columna EMA de 12 días, en realidad no queremos que ese valor aparezca El usuario, ya que no es una EMA verdadera). Una vez que llegamos a la 12ª fila de datos (D13), vemos la primera iteración de la fórmula EMA que se está utilizando (K ha sido reemplazado con .153 por la fórmula en la celda D6). A partir de este momento, la fórmula EMA se utiliza hasta llegar al final del conjunto de datos. Un método similar se utiliza para calcular la EMA de 26 días; La única diferencia es que comenzaría en la 25ª fila de datos para encontrar el promedio de 25 días, y que K se calcularía usando N = 26.
Primero vamos a recrear nuestra tabla #mod_goog_data para usarla para calcular las dos métricas EMA:
Una vez más MACD es una simple sustracción de EMA de 12 días menos EMA de 26 días, podemos calcular el MACD sobre la marcha cuando seleccionamos los valores de la tabla. Ahora para comenzar a calcular la EMA, primero calculamos los promedios iniciales (promedio de 11 días y promedio de 25 días). Una vez que tengamos eso, podemos hacer la actualización de la transferencia como lo hicimos para la SMA (actualización de 2 campos esta vez en lugar de uno).
Al igual que con la solución SMA, tenemos un número insignificante de lecturas lógicas - una muy rápida & amp; Eficiente. Por supuesto, cualquiera que esté familiarizado con el MACD sabe que por lo general se acompaña con un EMA de 9 días del valor MACD con el fin de medir la divergencia y los cambios de momento puntual. Utilizando las herramientas proporcionadas en este artículo, encontrará que es muy fácil de calcular.
Una vez más, si se siente incómodo con este método de Totales corrientes, desafortunadamente esta vez no puede usar una combinación cruzada para calcular la EMA. Recuerde que la fórmula requiere el EMA del día anterior, por lo que la solución debe tener un medio para transferir los valores de la fila anterior. Desafortunadamente, eso deja usando un CTE Recursivo o un Cursor; Con esas opciones, probablemente volvería a calcular el EMA en el lado del cliente.
Gracias a este autor compartiendo:
9 SQL para informes y análisis
En esta sección se describe cómo generar informes empresariales efectivos derivados de consultas empresariales e incluye las siguientes secciones:
Uso de capacidades analíticas de SQL para responder a consultas de negocios
Oracle Database ha mejorado las capacidades de procesamiento analítico de SQL introduciendo una familia de funciones analíticas y agregadas de SQL. Estas funciones le permiten calcular rangos, percentiles y promedios móviles, y le permiten responder preguntas como las siguientes:
¿Cuáles son los 10 productos más vendidos por país?
¿Cuál es el promedio móvil semanal para los productos en stock?
¿Qué porcentaje de ventas totales ocurre durante el cuarto trimestre?
¿Cuánto más alto es el descuento promedio en el cuarto trimestre que el descuento para el promedio anual?
¿Cuál sería el ranking de rentabilidad de las refinerías de petróleo existentes si el 20 por ciento de las refinerías de un país estuviera cerrado?
Las funciones de agregación son una parte fundamental del almacenamiento de datos, ya que permiten derivar diferentes tipos de totales y, a continuación, utilizar estos totales para cálculos adicionales. Para mejorar el rendimiento agregado en su almacén de datos, Oracle Database proporciona varias extensiones a la cláusula GROUP BY. El cubo. ENROLLAR. AGRUPAMIENTO. Y las funciones GROUPING SETS hacen que la consulta y la generación de informes sean más fáciles y rápidas. La función ROLLUP calcula agregaciones como SUM. CONTAR. MAX. MIN. Y AVG en niveles crecientes de agregación, desde el nivel individual más detallado hasta un gran resumen total. La función CUBE es una extensión similar a ROLLUP. Permitiendo que una única sentencia calcule todas las combinaciones posibles de agregaciones.
Las funciones analíticas calculan un valor agregado basado en un grupo de filas. Estas funciones difieren de las funciones agregadas en que devuelven varias filas para cada grupo. Este grupo de filas se denomina ventana. Esta ventana permite cálculos como el promedio móvil o el total acumulativo. Para cada fila, se define una ventana de filas. Esta ventana determina el rango de filas utilizado para realizar los cálculos de la fila actual. Los tamaños de ventana se pueden basar en un intervalo lógico tal como tiempo, o un número físico de filas. Algunas funciones se utilizan sólo con ventanas y se denominan a menudo funciones de ventana.
Para mejorar el rendimiento, las funciones agregadas y analíticas pueden actuar en paralelo: varios procesos pueden ejecutar simultáneamente todas estas funciones. Estas capacidades hacen que los cálculos, los análisis y los informes sean más fáciles y eficientes, mejorando así el rendimiento, la escalabilidad y la simplicidad del almacén de datos.
Puede aprovechar las avanzadas capacidades de SQL y PL / SQL que Oracle Database ofrece para traducir consultas empresariales en SQL. Esta sección describe estas capacidades avanzadas e incluye los siguientes temas:
Promedios de rotación en SQL Server
Las funciones de agregado son convenientes & # 8211; Resuelven una necesidad empresarial y facilitan el desarrollo. Lamentablemente, no todos los requisitos de negocio son tan fáciles de resolver. Veamos un ejemplo: promedios de rodadura.
El promedio móvil
Una media móvil es un concepto simple; Un promedio se calcula sobre un subconjunto fijo de datos. Los cálculos del promedio móvil se utilizan con mayor frecuencia con datos de series de tiempo y ayudan a eliminar las fluctuaciones a corto plazo, al tiempo que destacan las tendencias a largo plazo. Las facturas de servicios públicos a menudo cuentan con una media móvil de consumo para ayudar al cliente a entender su uso. Los consumidores no están preocupados por el uso de la electricidad siendo alto en un día de agosto cuando hubo una ola de calor; Quieren ver cómo su consumo está cambiando con el tiempo.
Promedios móviles con expresiones comunes de la tabla: 2005 & # 8211; 2008R2
Comenzando con SQL Server 2005, se hizo fácil escribir un promedio móvil en una sola sentencia T-SQL usando una expresión de tabla común. Los CTE rápidamente se hicieron populares; Una cantidad increíble de la funcionalidad prevously difícil fue hecha posible con CTEs incluyendo consultas recursive y promedios rodantes. Eche un vistazo a este ejemplo en la base de datos de ejemplo AdventureWorks2012:
Aunque no es el método más sencillo para construir un promedio móvil, el CTE logra realizar el trabajo. En esta consulta, estamos utilizando el CTE para crear una tabla de trabajo y luego realizar una auto-join. Este mismo tipo de cosa es posible usando una tabla temporal o variable de tabla, pero el CTE lo logra en una declaración y es, discutible, más fácil de leer.
Las expresiones comunes de la tabla también ocultan un secreto oscuro & # 8211; SQL Server ejecuta el cuerpo CTE cada vez que se hace referencia a la expresión CTE, cte en este ejemplo. Cuanto más compleja sea la expresión de tabla común, más trabajo tendrá que realizarse. Al ejecutar este promedio móvil con STATISTICS IO activado, es fácil ver las múltiples ejecuciones en forma de dos escaneos en cada tabla:
En una base de datos esta pequeña, esto no plantea grandes problemas de rendimiento, pero esto causará grandes problemas para una base de datos de tamaño moderado.
Promedios móviles con funciones de ventana: 2012 y más allá
SQL Server 2012 proporcionó un mejor soporte para las funciones de ventanas. Aunque la compatibilidad con OVER () ya estaba disponible en SQL Server 2005, SQL Server 2012 aporta considerablemente más funcionalidad a la tabla. Mediante el uso de la cláusula ROW o RANGE de la función windowing, es posible simplificar la consulta y mejorar el rendimiento. Echar un vistazo:
Aunque las dos preguntas son notablemente diferentes, la mayor diferencia es la introducción de ROWS 12 PRECEDING. Esto toma el lugar de la unión automática en el ejemplo anterior. En lugar de escribir unirse a nosotros mismos, simplemente le decimos a SQL Server que nos gustaría un promedio de st en las últimas 12 filas ordenadas por año y mes. ¿Qué tipo de efecto tiene esto en el trabajo que realiza SQL Server?
111 lecturas en lugar de 369 lecturas. Claramente este cambio hace una mejora sustancial de rendimiento para SQL Server. Hemos reducido el número de lecturas, hemos eliminado la complejidad de las consultas y hemos hecho que sea algo obvio para los futuros desarrolladores cómo podrían modificarlo o aprovecharlo. Cambiar la consulta nueva a una media móvil por día en lugar de por mes es simple y requiere incluso menos líneas de código:
Esto realiza el número de nombre de lecturas lógicas y físicas como el promedio mensual de balanceo usando una función de ventana.
Resumiendo
Ahí lo tienes & # 8211; Dos maneras diferentes de realizar una media móvil en SQL Server. Un método es claramente mucho más fácil que el otro. Existen varias optimizaciones en SQL Server 2012 para facilitar la construcción de esta funcionalidad y mejorar el rendimiento de SQL Server al mismo tiempo.
Promedio móvil exponencial en T-SQL
Los promedios móviles exponenciales son similares a los promedios móviles ponderados en que asignan menos peso a los cambios hace mucho tiempo, y más peso a los cambios recientes. Las medias móviles ponderadas son lineales, pero las medias móviles exponenciales son exponenciales. Es decir, el peso puede expresarse como una curva:
Pesos en EMA
Hay una gran manera de calcular promedios móviles exponenciales en T-SQL utilizando una característica indocumentada sobre variables y totales en ejecución en SQL Server. En este post de blog voy a mostrar cómo usar ese método para calcular el promedio móvil exponencial en T-SQL, pero también voy a presentar un método que está utilizando las características estándar de SQL Server. Desafortunadamente, eso significa usar un bucle.
En los ejemplos calcularé una media móvil exponencial de 9 días. Los ejemplos utilizan la base de datos TAdb. Un script para crear TAdb se puede encontrar aquí.
Promedio móvil exponencial (EMA): método de totales corrientes
La teoría detrás de las características totales corrientes en las actualizaciones es descrita en detalle por Jeff Moden en su artículo Resolviendo los Problemas de Total de Riesgo y Ordinal Rank.
Otros recursos que describen el uso de este método para calcular EMA son el blog Calculando promedios móviles con T-SQL por Gabriel Priester y el foro post Exponential Moving Average Challenge. Tanto en SQL Server Central.
Básicamente, en T-SQL puede actualizar variables así como columnas en una instrucción de actualización. Las actualizaciones se realizan fila por fila internamente por SQL Server. Este comportamiento fila por fila es lo que hace posible calcular un total en ejecución.
En este ejemplo se muestra cómo funciona:
Tenga en cuenta que & # 8220; ColumnRunningTotal & # 8221; Es un total acumulado de & # 8220; ColumnToSum & # 8221 ;.
Usando este método podemos calcular EMA9 con este T-SQL:
Resultados EMA9
El cálculo de EMA es bastante simple. Utilizamos la fila actual y la anterior, pero con más peso a la fila actual. El peso se calcula mediante la fórmula 2 / (1 + 9), donde \ Es el parámetro para la longitud de la EMA. Para calcular EMA9 para la fila 10 anterior, el cálculo es:
En este caso, la fila actual obtiene el 20% del peso (2 / (1 + 9) = 0,2) y la fila anterior obtiene el 80% del peso (1-2 / (1 + 9) = 0,8).
Encontrará este cálculo en la instrucción anterior en la instrucción CASE:
Promedio móvil exponencial (EMA): Método de bucle
Por lo que sé, excepto para el método de totales corrientes descrito anteriormente, no hay forma de calcular EMA utilizando una sentencia SQL basada en conjuntos. Por lo tanto, el T-SQL a continuación está utilizando un bucle while para calcular EMA9:
Los resultados son los mismos que en el ejemplo de totales en ejecución anterior.
Actuación
Como era de esperar, la versión de totales de ejecución basada en los totales es mucho más rápida que la versión de bucle. En mi máquina la solución basada del sistema era cerca de 300 ms, comparada a cerca de 1200 con la versión del lazo. Sin embargo, la versión de bucle se ajusta más a los estándares SQL. Así que la elección entre los métodos depende de lo que es más importante para usted, el rendimiento o los estándares.
El promedio móvil exponencial se puede utilizar en el análisis de tendencias, al igual que con los otros tipos de promedios móviles, Promedio móvil simple (SMA) y Promedio móvil ponderado (WMA).
Cálculo de 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. ¿Cuál es el más rápido y eficiente? Intentaremos 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 que encontrará adjunto.
Configuración de los datos y estado del problema empresarial
Muchas veces te encontrarás con muchas transacciones dentro de un mes, pero en nuestro caso asumiremos que ya has agrupado tus transacciones para cada mes. Asignaremos nuestra TECLA PRIMARIA a un tipo de datos DATE e incluiremos algunos valores sobre los cuales queremos acumular totales de doce meses.
CREATE TABLE #RollingTotalsExample
INSERT INTO #RollingTotalsExample
SELECT '2011-01-01'. 626
UNION ALL SELECT '2011-02-01'. 231 UNION ALL SELECT '2011-03-01'. 572
UNION ALL SELECT '2011-04-01'. 775 UNION ALL SELECT '2011-05-01'. 660
UNION ALL SELECT '2011-06-01'. 662 UNION ALL SELECT '2011-07-01'. 541
UNION ALL SELECT '2011-08-01'. 849 UNION ALL SELECT '2011-09-01'. 632
UNION ALL SELECT '2011-10-01'. 906 UNION ALL SELECT '2011-11-01'. 961
UNION ALL SELECT '2012-04-01'. 605 UNION ALL SELECT '2012-05-01'. 83
UNION ALL SELECT '2012-06-01'. 44 UNION ALL SELECT '2012-07-01'. 382
Dado que un total de doce meses no puede producirse hasta que tenga al menos doce meses de datos en su conjunto, buscamos generar un valor NULL para nuestra columna total de balanceo para las primeras 11 filas de los resultados devueltos. Sólo en el 12º mes de 2011 tendremos doce meses de datos para calcular el total de balance. Con nuestros datos de muestra, podemos calcular ese total como 7776 (o podemos ejecutar la consulta a continuación si los cálculos manuales no son lo tuyo).
WHERE [Fecha] & lt; = '2011-12-01';
Cálculo de un balance de doce meses total es similar a calcular un total de todas las filas anteriores, con sólo unos pocos trucos de cálculo que mostraremos un poco más adelante.
Soluciones que funcionan en SQL Server 2005 en adelante
Solución # 1: Uso de una tabla de clasificación
UNION ALL SELECT 4 UNIÓN TODO SELECCION 5 UNION TODO SELECCIONAR 6 UNION TODO SELECCIONAR 7
. Valor = MAX (CASO n CUANDO 0 ENTONCES a. Valor END)
. Rolling12Months = SUM (Valor)
FROM #RollingTotalsExemplo a
Aplicación cruzada Tally b
GRUPO POR DATEADD (mes [Fecha])
ORDEN POR DATEADD (mes [Fecha]);
Cuando examinamos los resultados de esta consulta, encontramos que hay un par de cuestiones:
Las primeras 11 filas no muestran NULL como nos gustaría.
Las últimas 11 filas representan fechas que están fuera de nuestro rango de datos, es decir, son posteriores a 2012-12-01.
La primera cuestión puede ser atendida con un ROW_NUMBER (). Mientras que el segundo problema se resuelve con una cláusula HAVING. Puesto que debemos hacer referencia a nuestra columna GroupingDate en varios lugares, vamos a poner ese cálculo en una aplicación cruzada, por lo que sólo tenemos que hacerlo una vez.
- Balancear los totales de doce meses usando una tabla de Tally
UNION ALL SELECT 4 UNIÓN TODO SELECCION 5 UNION TODO SELECCIONAR 6 UNION TODO SELECCIONAR 7
UNIÓN TODO SELECCIONAR 8 UNIÓN TODO SELECCIONAR 9 UNIÓN TODO SELECCIONAR 10 UNIÓN TODO SELECCIONAR 11
AVG (Transact-SQL)
ESTE TEMA SE APLICA A: SQL Server Base de datos Azure SQL Base de datos Azure SQL Data Warehouse Parallel Data Warehouse
Aplica la función agregada a todos los valores. ALL es el valor predeterminado.
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érico o aproximado numérico aproximado, excepto para el tipo de datos de bit. Las funciones agregadas y las subconsultas no están permitidas.
Partition_by_clause 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. Order_by_clause determina el orden lógico en el que se realiza la operación. Order_by_clause es necesario. Para obtener más información, vea Cláusula OVER (Transact-SQL).
Promedios móviles en MDX
Los promedios móviles son una técnica estadística importante para analizar datos de series temporales. A menudo se utiliza para realizar estimaciones de predicción o tendencia. Y es especialmente popular para analizar los datos financieros. Hay muchos tipos diferentes de promedios móviles, de simple a ponderado a exponencial, etc "Moving average" artículo en Wikipedia es un buen punto de partida para aprender sobre ellos. En este artículo discutiremos la implementación de las medias móviles en MDX.
Promedio móvil simple
La forma clásica de calcular el promedio móvil se explica en mi libro "Fast Track to MDX", capítulo 8. Traducido al ejemplo de Adventure Works, la media móvil de los últimos 12 meses para el [importe de ventas por Internet] se puede escribir como sigue:
Aquí usamos la función Lag para ir 11 meses atrás, luego poner un rango de ese mes hace 11 meses a la actual, y calcular el promedio sobre ella. Para tener una idea de cómo se comporta el promedio móvil cuando miramos los últimos 12, 6 o 3 meses, podemos ejecutar la siguiente consulta:
Y su resultado puede verse en el siguiente gráfico:
Las fórmulas anteriores para el promedio móvil son sencillas. De hecho, el BIDS en la pestaña Cálculo, en el lado izquierdo de la pantalla tiene una pestaña Plantillas, que contiene plantillas para cálculos comunes. El promedio móvil está entre ellos, y el código MDX en la plantilla es el mismo que el anterior:
El uso de la función MDX Avg aquí está justificado, porque estamos hablando de la media móvil. Sin embargo, debemos observar cómo la función Avg trata a las celdas con valor NULL. Para la compatibilidad con Excel, cuando la celda no contiene ningún valor, es decir, contiene NULL, Avg lo trata como si la celda no existiera. Es decir. No cuenta esta celda en el número total de celdas que se utilizarán en el denominador. Si tal comportamiento no es deseable, en lugar de Avg (set, exp) se podría escribir Sum (set, exp) / Count (set). Nuestro ejemplo entonces se convertirá en
O bien, si está bien ignorar los valores cercanos al inicio de la dimensión Tiempo, entonces en lugar de Conde () podemos escribir la constante 12 en el denominador
Si la diferencia en la semántica w. r.t. El tratamiento NULL entre Avg y Sum no es importante (por ejemplo, se sabe que NULL no ocurrirá de todos modos), entonces Sum es siempre preferible en Analysis Services 2005, ya que puede tener un mejor rendimiento.
Hablando de rendimiento, veamos qué tan bien se comporta esta expresión. Teo Lachev ha proporcionado la siguiente consulta como ejemplo compatible con Adventure Works que muestra un rendimiento problemático:
Esta consulta se ejecuta durante unos 13 segundos en mi computadora portátil. Esto es definitivamente demasiado largo - hay alrededor de 18,484 clientes en Adventure Works, y desde que estamos recibiendo datos durante 12 meses - que todavía sería alrededor de 17.000 células por segundo, que es sospechosamente bajo. Lo que es aún más interesante, es si comparamos esto con el cálculo de la suma de ejecución de mi entrada de blog. Obtendremos las siguientes consultas para comparar:
De inmediato resulta claro que la única diferencia entre las dos consultas es. Item (0) en lugar de Lag (11), es decir, ejecutar la suma de la suma resume todos los meses desde el principio de la hora, mientras que la primera consulta solo mira Últimos 12 meses. El sentido común nos diría que la primera consulta debería ejecutarse más rápido, ya que observa menos datos (12 meses frente a 38 meses). Sin embargo, la primera consulta toma 13 segundos, y la segunda toma sólo 1,3 segundos - completamente en contra del sentido común.
Para entender lo que está pasando aquí, echemos un vistazo a los contadores de rendimiento. El contador interesante aquí para mirar es MSAS 2005: cubiertas MDX \ Total de cálculo. Cuando ejecutamos la primera consulta, va a 18487. Pero para la segunda consulta se mantiene baja en 4. El número alto de cubiertas de cálculo es una clara indicación de que algo falló en el plan de ejecución. Como sabemos, el optimizador de consultas AS2005 tiene dos modos principales en los que evalúa los subespacios - el que yo solía llamar "modo de evaluación masiva" (los materiales de marketing de Katmai este modo recibió un nombre oficial - "cálculo de bloques") y el modo célula por célula . (Si está interesado en explicaciones detalladas sobre estos modos y otros aspectos internos de los planes de ejecución, podría valer la pena asistir a mi sesión de preconferencia "Deep Dive en MDX" en la cumbre de PASS 2007 en dos semanas). Pero incluso en el modo célula por célula, el número de cubiertas de cálculo debería haber sido menor. El número 18487 está sospechosamente cerca del número 18484, que es el número de clientes, lo que nos lleva a suponer que había cobertura de cálculo creada para cada cliente. La ejecución de la traza de consultas con "Query Subcube Verbose" habilitada confirma esta suposición - vemos SE subcube consulta generada para cada cliente. La conclusión es que el uso de la función de agregación como Aggregate o Sum en un conjunto de rangos que utiliza la función Lag produce un plan de ejecución horrible. Retraso por sí mismo no es una mala función, pero en esta combinación, AS2005 falla buen plan de consulta. Este escenario en particular, así como muchos otros se fija en Katmai, pero ¿qué podemos hacer en el ínterin. Podríamos trabajar alrededor del uso de la función Lag y usar otra función MDX que nos posicionará en el miembro deseado. La función más obvia para hacer eso es ParallelPeriod.
La consulta reescrito para usar ParallelPeriod se verá de la siguiente manera:
Y se ejecuta en aproximadamente 1 segundo.
Promedio móvil ponderado
El promedio móvil ponderado (WMA) es el promedio móvil donde los valores de los períodos de tiempo anteriores se multiplican por el peso antes de ser resumidos. Por lo general, los períodos de tiempo más recientes aumentan de peso. Una distribución muy común de pesos es cuando disminuyen aritméticamente, es decir, WMA se calcula mediante la siguiente fórmula:
El denominador es una progresión aritmética simple que se puede calcular usando la siguiente fórmula:
¿Cómo podemos implementar esta fórmula en MDX. Hay varias soluciones posibles. El más directo es durante la iteración sobre el conjunto para comprobar la posición dentro del conjunto y multiplicar por el peso apropiado. AS2005 agregó una nueva función MDX para obtener el índice de tupla durante la iteración sobre el conjunto - CurrentOrdinal. Para el WMA de 6 meses el MDX se verá como sigue:
Aquí n = 6. Por lo tanto n * (n + 1) / 2 = 21. Sin embargo, esto da resultados inexactos al principio del tiempo, durante los primeros 5 meses, ya que el conjunto contiene en realidad menos de 6 miembros. Si esto es importante, la fórmula puede ser reescrita para ser más precisa:
Podemos mejorar esto un poco mediante la caché explícita de la subexpresión común por lo que se calculará sólo una vez (más sobre las técnicas de almacenamiento en caché durante la sesión de preconferencia "Deep dive to MDX" PASS):
Ahora, prestemos una mayor atención a los resultados de esta consulta. Si observamos los números, notaremos que parecen ser demasiado grandes. In fact they are exactly 10,000 times bigger than what they are supposed to be. This is a known bug in AS2005 with respect to the mathematical operations with Currency data type. Until this bug is fixed, we will have to compensate for it by dividing the result by 10,000:
Exponential Moving Average
Exponential moving average (EMA) is a special case of WMA, where the weights decrease exponentially. There are few variations on the exact formula, below is one common definition:
The parameter "alpha" is called smoothing factor, and it relates to the number of periods to go back in the moving average computation as
Since the above definition is a recursive one, we can write this in MDX using recursion as well:
Recursive calculations are not efficient in AS2005, since query optimizer doesn't know how to optimize them. In order to improve performance, the EMA can be rewritten using non-recursive approach. The math works out to the following formula:
I leave the translation of this formula into MDX as an exercise for the reader.
The chart below shows comparison between SMA, WMA and EMA in a graphical way.
Thread: Creating a Simple Moving Average with mySQL
Creating a Simple Moving Average with mySQL
Good day people,
I am interested in creating a Simple Moving Average (SMA) using only SQL. An SMA is rather easy to calculate and is being used for smoothing numerical data. Example: the SMA of 5 days for today would be the average of the last 5 days. The SMA of tomorrow is the average of the 5 days before it. For more information on SMA see SAM .
Now - sure I could use PHP+mySQL to pruduce such SMA values but I think using only SQL for this task will be faster (am I right??) and for sure more elegant but I am rather out of ideas how to do this since the SMA, as its name indicates, moves from value to value and calculates the SMA according to its preceding consecutive values.
The algorithm works as follows:
1. if you, for example, use the factor of 5 days for smoothing, start with the date that is 5 days away from the oldest date in the table.
2. create the average of those last 5 days.
3. store the calculated value.
4. move to the next day.
5. go back to point number 2 or stop if reached end of table.
Could you people give me a hint on this. I would be very happy to hear your thoughts on this.
Take care and keep up the good work!
Moving average and Weighted Moving Average
In my previous blog post. I wrote about how to calculate median value and weighted median value in a secure and fast approach.
In this blog post I am going to describe how you can calculate a fast moving average and also calculate a fast weighted moving average.
This is the sample data we should work with during the whole exercise. I also display both the normal moving average and the weighted moving average at the same time.
DECLARE @Sample TABLE
dt SMALLDATETIME ,
Rate SMALLMONEY
INSERT @Sample
VALUES ( DATEADD ( DAY. DATEDIFF ( DAY. 10. GETDATE ()), 0 ), 2 ),
( DATEADD ( DAY. DATEDIFF ( DAY. 9. GETDATE ()), 0 ), 2 ),
( DATEADD ( DAY. DATEDIFF ( DAY. 8. GETDATE ()), 0 ), 4 ),
( DATEADD ( DAY. DATEDIFF ( DAY. 7. GETDATE ()), 0 ), 4 ),
( DATEADD ( DAY. DATEDIFF ( DAY. 6. GETDATE ()), 0 ), 5 ),
ma SMALLMONEY ,
wma SMALLMONEY
First of all, I am going to show you an approach made by a cursor. This is actually one of the fastest way to accomplish this task!
I'm trying to create a moving average in my model. Looking for some assistance.
I tried following the details in Alberto Ferrari's blog here. But I could not get the DayNumber measure working, the syntax did not seem correct and I could not correct it.
My model has a Fact table containing a list of cases, joining to a Date table via Created Date. I have a second relationship (Inactive) to the Date table on the ClosedDate column. I have a measure: Case Closed Count:=CALCULATE(COUNTROWS('Case'), USERELATIONSHIP('Case'[ClosedDateKey], 'Date'[DateKey])).
I would like a measure getting the sum of Case Closed Count for the last three days of the current context. I then plan to divide that number by 3 to get the moving 3-day-average.
One other piece of logic I would like to consider - if the last day is TODAY, then the previous 3 days are used - the data is updated every 15 minutes, so doing this at 09:00 in the morning would skew the average, as it's not a fully completed day.
Any assistance is appreciated.
Sunday, February 17, 2013 5:25 PM
No comments:
Post a Comment