Saturday 11 November 2017

Movimiento Promedio De Sql Oracle


21 SQL para análisis e informes Para realizar estas operaciones, las funciones analíticas añaden varios elementos nuevos al procesamiento de SQL. Estos elementos se basan en el SQL existente para permitir expresiones de cálculo flexibles y poderosas. Con sólo unas pocas excepciones, las funciones analíticas tienen estos nuevos elementos. El flujo de procesamiento se representa en la Figura 21-1. Figura 21-1 Orden de procesamiento Los conceptos esenciales utilizados en las funciones analíticas son: El procesamiento de consultas utilizando funciones analíticas tiene lugar en tres etapas. Primero, todas las uniones, DÓNDE. Las cláusulas GROUP BY y HAVING se ejecutan. En segundo lugar, el conjunto de resultados se pone a disposición de las funciones analíticas, y todos sus cálculos tienen lugar. En tercer lugar, si la consulta tiene una cláusula ORDER BY al final, el ORDER BY se procesa para permitir un orden de salida preciso. El orden de procesamiento se muestra en la Figura 21-1. Particiones de conjuntos de resultados Las funciones analíticas permiten a los usuarios dividir los conjuntos de resultados de consulta en grupos de filas denominados particiones. Tenga en cuenta que el término particiones utilizadas con funciones analíticas no está relacionado con la función de particiones de tablas. A lo largo de este capítulo, el término particiones se refiere sólo al significado relacionado con las funciones analíticas. Las particiones se crean después de los grupos definidos con cláusulas GROUP BY, por lo que están disponibles para cualquier resultado agregado, como sumas y promedios. Las divisiones de partición pueden basarse en cualquier columna o expresión deseada. Un conjunto de resultados de consulta puede dividirse en una sola partición que contenga todas las filas, unas particiones grandes o muchas particiones pequeñas que contengan sólo unas pocas filas cada una. Para cada fila de una partición, puede definir una ventana deslizante de datos. 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 número físico de filas o un intervalo lógico tal como tiempo. La ventana tiene una fila inicial y una fila final. Dependiendo de su definición, la ventana puede moverse en uno o ambos extremos. Por ejemplo, una ventana definida para una función de suma acumulada tendría su fila inicial fija en la primera fila de su partición y su fila final se deslizaría desde el punto de inicio hasta la última fila de la partición. Por el contrario, una ventana definida para un promedio móvil tendría tanto sus puntos de inicio como de final de diapositivas para que mantengan un rango físico o lógico constante. Se puede establecer una ventana tan grande como todas las filas de una partición o simplemente una ventana deslizante de una fila dentro de una partición. Cuando una ventana está cerca de un borde, la función devuelve resultados para sólo las filas disponibles, en lugar de advertirle que los resultados no son lo que desea. Cuando se utilizan funciones de ventana, la fila actual se incluye durante los cálculos, por lo que sólo debe especificar (n -1) cuando se trata de n elementos. Cada cálculo realizado con una función analítica se basa en una fila actual dentro de una partición. La fila actual sirve como punto de referencia que determina el inicio y el final de la ventana. Por ejemplo, se podría definir un cálculo del promedio móvil centrado con una ventana que contenga la fila actual, las seis filas precedentes y las seis filas siguientes. Esto crearía una ventana deslizante de 13 filas, como se muestra en la Figura 21-2. Figura 21-2 Ventana deslizante Ejemplo Funciones de clasificación Una función de clasificación calcula el rango de un registro comparado con otros registros del conjunto de datos basado en los valores de un conjunto de medidas. Los tipos de función de clasificación son: Ejemplo de cálculo de regresión lineal En este ejemplo, calculamos una línea de regresión de mínimos cuadrados ordinarios que expresa la cantidad vendida de un producto como una función lineal del precio de lista de productos. Los cálculos se agrupan por canal de ventas. Los valores SLOPE. INTCPT. RSQR son la pendiente, el intercepto y el coeficiente de determinación de la línea de regresión, respectivamente. El valor (entero) COUNT es el número de productos en cada canal para los cuales se venden tanto la cantidad vendida como los datos de precio de lista. Álgebra lineal El álgebra lineal es una rama de las matemáticas con una amplia gama de aplicaciones prácticas. Muchas áreas tienen tareas que pueden ser expresadas usando álgebra lineal, y aquí hay algunos ejemplos de varios campos: estadísticas (análisis de regresión lineal múltiple y componentes principales), minería de datos (agrupación y clasificación), bioinformática (análisis de datos de microarrays), investigación de operaciones (Cadena de suministro y otros problemas de optimización), econometría (análisis de datos de demanda del consumidor) y finanzas (problemas de asignación de activos). Varias bibliotecas para álgebra lineal están disponibles libremente para que cualquiera pueda usarlas. El paquete Oracles UTLNLA expone los tipos de datos PL / SQL de matriz y los subprogramas PL / SQL wrapper para dos de las bibliotecas más populares y robustas de BLAS y LAPACK. El álgebra lineal depende de la manipulación de la matriz. Realizar la manipulación de la matriz en PL / SQL en el pasado requirió inventar una representación matricial basada en tipos de datos nativos de PL / SQLs y luego escribir rutinas de manipulación de matriz desde cero. Esto requirió un esfuerzo sustancial de programación y el desempeño de la implementación resultante fue limitado. Si los desarrolladores deciden enviar datos a paquetes externos para procesarlos en lugar de crear sus propias rutinas, la transferencia de datos de ida y vuelta podría tardar mucho tiempo. El uso del paquete UTLNLA permite que los datos permanezcan dentro de Oracle, elimina el esfuerzo de programación y ofrece una implementación rápida. Ejemplo 21-19 Álgebra lineal A continuación se muestra un ejemplo de cómo el soporte de álgebra lineal Oracles podría utilizarse para el análisis de negocios. Invoca una aplicación de regresión lineal múltiple construida utilizando el paquete UTLNLA. La aplicación de regresión múltiple se implementa en un objeto llamado OLSRegression. Tenga en cuenta que los archivos de ejemplo para el objeto OLS Regression se pueden encontrar en ORACLEHOME / plsql / demo. Considere el escenario de un minorista que analiza la eficacia de su programa de marketing. Cada una de sus tiendas asigna su presupuesto de marketing a los siguientes programas posibles: anuncios de medios (medios), promociones (promo), cupones de descuento (disct) y envíos directos (dmail). El análisis de regresión construye una relación lineal entre la cantidad de ventas que una tienda promedio tiene en un año dado (ventas) y el gasto en los cuatro componentes del programa de mercadeo. Supongamos que los datos de marketing se almacenan en la siguiente tabla: A continuación, puede construir el siguiente modelo de marketing de ventas utilizando coeficientes: Este modelo se puede implementar como la siguiente vista, que hace referencia al objeto de regresión OLS: El administrador del programa puede realizar un análisis como es este modelo de marketing de ventas razonable para los datos del año 2004 Es decir, es la correlación múltiple mayor que algún valor aceptable, por ejemplo, 0.9 El SQL para tal consulta podría ser el siguiente: Resolver preguntas tales como ¿Cuál es el ingreso esperado de ventas de línea base de una tienda sin programas de mercadeo en 2003 o ¿Cuál componente del programa de mercadeo fue el más efectivo en 2004? Es decir, un aumento en dólares en el cual el programa produjo el mayor incremento esperado en Ventas Véase Referencia de paquetes y tipos de PL / SQL de Oracle Database para obtener más información sobre el uso del paquete UTLNLA y el álgebra lineal. Conjuntos de artículos frecuentes En lugar de contar la frecuencia con la que ocurre un evento determinado (por ejemplo, con qué frecuencia alguien ha comprado leche en el supermercado), puede ser útil contar la frecuencia con la que ocurren varios eventos juntos (por ejemplo, Y cereales juntos en el supermercado). Puede contar estos múltiples eventos utilizando lo que se llama conjunto de elementos frecuente, que es, como su nombre indica, un conjunto de elementos. Algunos ejemplos de conjuntos de artículos podrían ser todos los productos que un cliente comprado en un solo viaje a la tienda de comestibles (comúnmente llamado cesta de mercado), las páginas web que un usuario accedió en una sola sesión, o los servicios financieros que un determinado Cliente utiliza. La motivación práctica para usar un conjunto de elementos frecuente es encontrar los conjuntos de elementos que se producen con mayor frecuencia. Si analizas los datos de punto de venta de una tienda de comestibles, podrías, por ejemplo, descubrir que la leche y los plátanos son el par de artículos más comúnmente comprados. Por lo tanto, se han utilizado conjuntos de artículos frecuentes en entornos de Business Intelligence durante muchos años, siendo el más común el análisis de cesta de mercado en la industria minorista. Los cálculos de conjuntos de elementos frecuentes se integran con la base de datos, funcionando encima de las tablas relacionales y se accede a través de SQL. Esta integración proporciona los siguientes beneficios clave: Las aplicaciones que antes se basaban en operaciones con elementos frecuentes ahora se benefician de un rendimiento significativamente mejorado, así como de una implementación más sencilla. Las aplicaciones basadas en SQL que no utilizaban conjuntos de elementos frecuentes ahora pueden ampliarse fácilmente para aprovechar esta funcionalidad. El análisis de conjuntos de elementos frecuentes se realiza con el paquete PL / SQL DBMSFREQUENTITEMSETS. Consulte Referencia de paquetes y tipos de paquetes PL / SQL de Oracle para obtener más información. Además, hay un ejemplo de uso frecuente de conjuntos de elementos en Conjuntos de elementos frecuentes. Otras funciones estadísticas Oracle introduce un conjunto de funciones estadísticas de SQL y un paquete de estadísticas, DBMSSTATFUNCS. Esta sección enumera algunas de las nuevas funciones junto con la sintaxis básica. Estadística descriptiva Puede calcular las siguientes estadísticas descriptivas: Mediana de un conjunto de datos Puede calcular las siguientes estadísticas paramétricas: Spearmans rho Coeficiente Kendalls tau-b Coeficiente Además de las funciones, este lanzamiento tiene un nuevo paquete PL / SQL, DBMSSTATFUNCS. Contiene la función estadística descriptiva RESUMEN junto con las funciones para apoyar la adaptación de la distribución. La función RESUMEN resume una columna numérica de una tabla con una variedad de estadísticas descriptivas. Las cinco funciones de ajuste de distribución soportan distribuciones normales, uniformes, Weibull, Poisson y exponenciales. WIDTHBUCKET Función Para una expresión dada, la función WIDTHBUCKET devuelve el número de cubo que el resultado de esta expresión se asignará después de que se evalúa. Con esta función puede generar histogramas de ancho de banda. Los histogramas de espacio dividen los conjuntos de datos en cubos cuyo tamaño de intervalo (el valor más alto al valor más bajo) es igual. El número de filas que contiene cada cubo variará. Una función relacionada, NTILE. Crea compartimientos equiheight. Los histogramas de espacio sólo se pueden generar para tipos numéricos, de fecha o de fecha y hora. Así que los primeros tres parámetros deben ser todas las expresiones numéricas o todas las expresiones de fecha. No se permiten otros tipos de expresiones. Si el primer parámetro es NULL. El resultado es NULL. Si el segundo o el tercer parámetro es NULL. Se devuelve un mensaje de error, ya que un valor NULL no puede indicar ningún punto final (o cualquier punto) de un rango en una dimensión de valor numérico o de fecha. El último parámetro (número de cubos) debe ser una expresión numérica que se evalúa a un valor entero positivo 0, NULL. O un valor negativo dará lugar a un error. Los cubos están numerados de 0 a (n 1). El cubo 0 mantiene el recuento de valores inferiores al mínimo. El cubo (n 1) contiene el recuento de valores mayores o iguales al valor máximo especificado. WIDTHBUCKET Sintaxis El WIDTHBUCKET toma cuatro expresiones como parámetros. El primer parámetro es la expresión de que el histograma de ancho de banda es para. El segundo y tercer parámetro son expresiones que indican los puntos finales del rango aceptable para el primer parámetro. El cuarto parámetro denota el número de cubetas. Considere los siguientes datos de los clientes de la tabla. Que muestra los límites de crédito de 17 clientes. Estos datos se recogen en la consulta mostrada en el Ejemplo 21-20. En la tabla de clientes. La columna custcreditlimit contiene valores entre 1500 y 15000, y podemos asignar los valores a cuatro cubos de equiwidth, numerados de 1 a 4, utilizando WIDTHBUCKET (custcreditlimit, 0, 20000, 4). Idealmente cada cubo es un intervalo cerrado-abierto de la línea numérica real, por ejemplo, el cubo número 2 se asigna a las puntuaciones entre 5000.0000 y 9999.9999. A veces denominado 5000, 10000) para indicar que se incluyen 5.000 en el intervalo y 10.000 se excluyen. Para asignar valores fuera del rango 0, 20.000), se asignan valores inferiores a 0 a un cubo de desbordamiento designado que está numerado 0 y se asignan valores mayores o iguales a 20.000 a un cubo de desbordamiento designado que está numerado como 5 (núm. en general). Vea la Figura 21-3 para una ilustración gráfica de cómo se asignan los cubos. Figura 21-3 Asignaciones de cubo Puede especificar los límites en el orden inverso, por ejemplo, WIDTHBUCKET (custcreditlimit 20000. 0. 4). Cuando los límites se invierten, los cubos serán intervalos abiertos-cerrados. En este ejemplo, el número de cubo 1 es (15000, 20000, el número de cubo 2 es (10000,15000 y el cubo número 4, es (0, 5000. El cubo de desbordamiento se numerará 0 (20000. infinito) y el cubo de desbordamiento Será numerado 5 (- infinito 0. Es un error si el parámetro de cuenta de cubo es 0 o negativo Ejemplo 21-20 WIDTHBUCKET La siguiente consulta muestra los números de cubo para los límites de crédito en la tabla de clientes para ambos casos donde Los límites se especifican en orden normal o inverso Utilizamos un rango de 0 a 20.000 Funciones agregadas definidas por el usuario Oracle ofrece una facilidad para crear sus propias funciones, denominadas funciones agregadas definidas por el usuario Estas funciones están escritas en lenguajes de programación tales como PL / SQL, Java y C, y se pueden utilizar como funciones o agregados analíticos en vistas materializadas. Consulte la Guía para desarrolladores de cartuchos de datos de Oracle Database para obtener más información sobre sintaxis y restricciones. Las ventajas de estas funciones son: Las funciones altamente complejas se pueden programar usando Un lenguaje completamente procesal. Mayor escalabilidad que otras técnicas cuando las funciones definidas por el usuario están programadas para el procesamiento paralelo. Los tipos de datos de objetos se pueden procesar. Como un simple ejemplo de una función agregada definida por el usuario, considere la estadística de sesgo. Este cálculo mide si un conjunto de datos tiene una distribución desequilibrada sobre su media. Le dirá si una cola de la distribución es significativamente mayor que la otra. Si creó un agregado definido por el usuario denominado udskew y lo aplicó a los datos de límite de crédito del ejemplo anterior, la sentencia y los resultados de SQL podrían tener este aspecto: Antes de crear funciones agregadas definidas por el usuario, debe considerar si se pueden satisfacer sus necesidades En SQL regular. Muchos cálculos complejos son posibles directamente en SQL, particularmente usando la expresión CASE. Permanecer con SQL regular permitirá un desarrollo más simple, y muchas operaciones de consulta ya están bien paralelizadas en SQL. Incluso el ejemplo anterior, la estadística de sesgo, se puede crear utilizando estándar, aunque largo, SQL. Expresiones CASE Oracle ahora soporta declaraciones CASE simples y buscadas. Las sentencias CASE son similares en propósito a la instrucción DECODE, pero ofrecen más flexibilidad y poder lógico. También son más fáciles de leer que las declaraciones tradicionales de DECODE, y ofrecen un mejor rendimiento también. Se utilizan comúnmente cuando se divide categorías en compartimientos como la edad (por ejemplo, 20-29, 30-39, etc.). La sintaxis de las sentencias CASE simples es: Prueba simple de CASE si el valor expr es igual a comparisonexpr. La sintaxis para las sentencias CASE buscadas es: Puede utilizar cualquier tipo de condición en una expresión CASE buscada, no sólo una prueba de igualdad. Puede especificar sólo 255 argumentos y cada WHEN. Entonces par cuenta como dos argumentos. Para evitar superar este límite, puede anidar expresiones CASE para que el returnexpr en sí es una expresión CASE. Ejemplo 21-21 CASO Suponga que quería encontrar el salario promedio de todos los empleados de la empresa. Si el salario de un empleado es inferior a 2000, desea que la consulta utilice 2000 en su lugar. Sin una sentencia CASE, puede optar por escribir esta consulta de la siguiente manera: Tenga en cuenta que esto se ejecuta en contra del esquema de ejemplo hr. En esto, foo es una función que devuelve su entrada si la entrada es mayor que 2000, y devuelve 2000 en caso contrario. La consulta tiene implicaciones de rendimiento porque necesita invocar una función para cada fila. Escribir funciones personalizadas también puede agregar a la carga de desarrollo. Usando expresiones CASE en la base de datos sin PL / SQL, esta consulta puede ser reescrita como: Usando una expresión CASE le permite evitar el desarrollo de funciones personalizadas y también puede realizar más rápido. Ejemplo 21-22 CASO para agrupar subconjuntos independientes El uso de CASE dentro de las funciones de agregado es una forma conveniente de realizar agregados en múltiples subconjuntos de datos cuando un GROUP BY simple no basta. Por ejemplo, el ejemplo anterior podría haber incluido varias columnas AVG en su lista SELECT, cada una con su propia expresión CASE. Podríamos haber tenido una consulta para encontrar el salario promedio de todos los empleados en los rangos salariales 0-2000 y 2000-5000. Parecería lo siguiente: Aunque esta consulta coloca los agregados de datos de subconjuntos independientes en columnas separadas, al agregar una expresión CASE a la cláusula GROUP BY, podemos mostrar los agregados como las filas de una sola columna. La siguiente sección muestra la flexibilidad de este enfoque con dos enfoques para crear histogramas con CASE. Creación de histogramas con cubos definidos por el usuario Puede utilizar la instrucción CASE cuando desee obtener histogramas con cubetas definidas por el usuario (tanto en número de cubos como en el ancho de cada cubo). Los siguientes son dos ejemplos de histogramas creados con instrucciones CASE. En el primer ejemplo, los totales del histograma se muestran en varias columnas y se devuelve una sola fila. En el segundo ejemplo, el histograma se muestra con una columna de etiqueta y una sola columna para los totales y se devuelven varias filas. Ejemplo 21-23 Histograma Ejemplo 1 Ejemplo 21-24 Histograma Ejemplo 2 Densificación de datos para el informe Los datos se almacenan normalmente en forma dispersa. Es decir, si no existe ningún valor para una combinación dada de valores de dimensión, no existe ninguna fila en la tabla de hechos. Sin embargo, es posible que desee ver los datos en forma densa, con filas para toda la combinación de valores de cota visualizados incluso cuando no existan datos de hecho para ellos. Por ejemplo, si un producto no se vende durante un período de tiempo determinado, es posible que todavía quiera ver el producto para ese período de tiempo con cero valor de venta junto a él. Además, los cálculos de series temporales se pueden realizar más fácilmente cuando los datos son densos a lo largo de la dimensión temporal. Esto se debe a que los datos densos llenarán un número consistente de filas para cada período, lo que a su vez hace que sea sencillo usar las funciones analíticas de ventana con desplazamientos físicos. La densificación de datos es el proceso de convertir datos escasos en forma densa. Para superar el problema de la escasez, puede utilizar una combinación externa dividida para rellenar los espacios en una serie temporal o cualquier otra dimensión. Dicha unión extiende la sintaxis de unión externa convencional aplicando la unión externa a cada partición lógica definida en una consulta. Oracle lógicamente particiona las filas de la consulta en función de la expresión que especifique en la cláusula PARTITION BY. El resultado de una unión externa particionada es una UNIÓN de las uniones externas de cada una de las particiones de la tabla con particiones lógicas con la tabla en el otro lado de la join. Note que puede utilizar este tipo de combinación para llenar los vacíos en cualquier Dimensión, no sólo la dimensión temporal. La mayoría de los ejemplos aquí se enfocan en la dimensión temporal porque es la dimensión más frecuentemente utilizada como base para las comparaciones. Sintaxis de combinación de particiones La sintaxis de una unión externa dividida extiende la cláusula ANSI SQL JOIN con la frase PARTITION BY seguida por una lista de expresiones. Las expresiones en la lista especifican el grupo al que se aplica la combinación externa. Las siguientes son las dos formas de sintaxis normalmente utilizadas para partición externa compartida: Tenga en cuenta que FULL OUTER JOIN no se admite con una combinación externa particionada. Ejemplo de datos dispersos En el siguiente ejemplo se muestra una situación típica con una dimensión escasa, que calcula las ventas semanales y las ventas acumuladas hasta el año del producto Bounce para las semanas 20-30 en 2000 y 2001: En este ejemplo, Esperaría 22 filas de datos (11 semanas cada una de 2 años) si los datos fueran densos. Sin embargo, obtendremos sólo 18 filas porque las semanas 25 y 26 faltan en el 2000 y las semanas 26 y 28 en 2001. Relleno de lagunas en los datos Podemos tomar los datos escasos de la consulta anterior y hacer una partición externa unida con un denso conjunto de tiempo datos. En la siguiente consulta, alias nuestra consulta original como v y seleccionamos datos de la tabla de tiempos, que alias como t. Aquí recuperamos 22 filas porque no hay lagunas en la serie. Las cuatro filas añadidas tienen cada 0 como su valor de Ventas establecido en 0 mediante la función NVL. Tenga en cuenta que en esta consulta, una condición WHERE se colocó durante semanas entre 20 y 30 en la vista en línea para la dimensión de tiempo. Esto se introdujo para mantener el conjunto de resultados pequeño. Rellenos de llenado en dos dimensiones Los datos N-dimensionales se muestran típicamente como una tabulación cruzada bidimensional densa de (n - 2) dimensiones de página. Esto requiere que se llenen todos los valores de dimensión para las dos dimensiones que aparecen en la ficha cruzada. A continuación se muestra otro ejemplo en el que la capacidad de unión externa particionada puede usarse para rellenar los espacios en dos dimensiones: En esta consulta, la cláusula WITH V1 resume los datos de ventas a nivel de producto, país y año. Este resultado es escaso pero los usuarios pueden querer ver todas las combinaciones de país, año para cada producto. Para lograr esto, tomamos cada partición de v1 en función de los valores del producto y lo unimos externamente en la dimensión del país primero. Esto nos dará todos los valores del país para cada producto. A continuación, tomar ese resultado y la partición de los valores de producto y país y, a continuación, unirse a ella exterior en la dimensión de tiempo. Esto nos dará todos los valores de tiempo para cada combinación de producto y país. Rellenos de llenado en una tabla de inventario Una tabla de inventario rastrea típicamente la cantidad de unidades disponibles para varios productos. Esta tabla es escasa: sólo almacena una fila para un producto cuando hay un evento. Para una tabla de ventas, el evento es una venta, y para la tabla de inventario, el evento es un cambio en la cantidad disponible para un producto. Por ejemplo, considere la siguiente tabla de inventario: La tabla de inventario ahora tiene las siguientes filas: Para los informes, los usuarios pueden querer ver estos datos de inventario de manera diferente. Por ejemplo, pueden querer ver todos los valores de tiempo para cada producto. Esto puede lograrse utilizando una unión externa particionada. Además, para las filas recién insertadas de períodos de tiempo que falten, los usuarios pueden querer ver los valores de la cantidad de unidades de columna que se trasladarán del período de tiempo existente más reciente. Este último se puede lograr utilizando la función de ventana analítica LASTVALUE valor. Aquí está la consulta y la salida deseada: La consulta interna calcula una unión externa particionada en el tiempo dentro de cada producto. La consulta interna densifica los datos en la dimensión de tiempo (lo que significa que la dimensión de tiempo tendrá ahora una fila para cada día de la semana). Sin embargo, la cantidad de la columna medida tendrá nulos para las filas recién agregadas (ver la salida en la cantidad de columna en los siguientes resultados.) La consulta externa utiliza la función analítica LASTVALUE. La aplicación de esta función divide los datos por producto y ordena los datos en la (Timeid) Para cada fila, la función encuentra el último valor no nulo en la ventana debido a la opción IGNORE NULLS, que se puede utilizar tanto con LASTVALUE como con FIRSTVALUE. Vemos la salida deseada en la columna La siguiente salida: Computación de valores de datos para rellenar errores Los ejemplos de la sección anterior ilustran cómo utilizar partición externa compartida para rellenar huecos en una o más dimensiones Sin embargo, los conjuntos de resultados producidos por una unión externa compartida tienen valores nulos para columnas que no se incluyen en La lista PARTITION BY. Por ejemplo, la consulta siguiente calcula los totales mensuales de los productos 64MB de tarjeta de memoria y DVD-R Discos (ID de producto 122 y 136) para el año 2000. Utiliza partición externa compartida para densificar datos para todos los meses. Para los meses que faltan, a continuación, utiliza la función SQL analítica AVG para calcular las ventas y las unidades a ser el promedio de los meses en que se vendió el producto. Si trabaja en SQLPlus, los dos comandos siguientes envolverán los encabezados de las columnas para una mayor legibilidad de los resultados: Los cálculos de series de tiempo en la densificación de datos densificados no son sólo para fines de informes. También permite ciertos tipos de cálculos, especialmente, cálculos de series temporales. Los cálculos de series temporales son más fáciles cuando los datos son densos a lo largo de la dimensión temporal. Los datos densos tienen un número consistente de filas para cada período de tiempo, lo que a su vez hace que sea sencillo utilizar funciones de ventanas analíticas con desplazamientos físicos. Para ilustrar, tomemos primero el ejemplo de rellenar los vacíos en los datos. Y permite agregar una función analítica a esa consulta. En la siguiente versión mejorada, calculamos las ventas semanales hasta la fecha junto con las ventas semanales. Los valores NULL que la partición externa compartida inserta al hacer la serie de tiempo denso se manejan de la manera habitual: la función SUMA los trata como 0s. Comparación de período a período para un nivel de tiempo: Ejemplo ¿Cómo utilizamos esta característica para comparar valores a través de períodos de tiempo Específicamente, cómo calculamos una comparación de ventas año a año en el nivel de semana La consulta siguiente devuelve en la misma fila , Para cada producto, las ventas del año hasta la fecha para cada semana de 2001 con la de 2000. Tenga en cuenta que en este ejemplo comenzamos con una cláusula WITH. Esto mejora la legibilidad de la consulta y nos permite centrarnos en la partición externa particionada. Si está trabajando en SQLPlus, el siguiente comando envolverá los encabezados de columna para una mayor legibilidad de los resultados: En la cláusula FROM de la vista en línea densesales. Utilizamos una unión externa particionada de la vista agregada v y la vista temporal t para rellenar huecos en los datos de ventas a lo largo de la dimensión de tiempo. La salida de la unión externa particionada es procesada por la función analítica SUM. OVER para calcular las ventas semanales hasta la fecha (la columna weeklyytdsales). Por lo tanto, la vista densesales calcula los datos de ventas del año hasta la fecha de cada semana, incluidos los que faltan en la vista agregada. La vista en línea yearoveryearsales luego calcula las ventas semanales del año hasta la fecha del año anterior usando la función LAG. La función LAG denominada weeklyytdsalesprioryear especifica una cláusula PARTITION BY que empareja filas para la misma semana de los años 2000 y 2001 en una sola partición. Luego pasamos un desplazamiento de 1 a la función LAG para obtener el año semanal hasta la fecha de ventas para el año anterior. El bloque de consulta más externo selecciona los datos de yearoveryearsales con la condición yr 2001 y así la consulta devuelve, para cada producto, Año a la fecha en las semanas especificadas de los años 2001 y 2000. Comparación período a período para varios niveles de tiempo: Ejemplo Mientras que el ejemplo anterior nos muestra una manera de crear comparaciones para un solo nivel de tiempo, sería aún más Útil para manejar múltiples niveles de tiempo en una sola consulta. Por ejemplo, podríamos comparar las ventas con el período anterior en los niveles de año, trimestre, mes y día. ¿Cómo podemos crear una consulta que realiza una comparación de año a año de las ventas del año hasta la fecha para todos los niveles de nuestra jerarquía de tiempo. Tomaremos varios pasos para realizar esta tarea. El objetivo es una sola consulta con comparaciones a nivel de día, semana, mes, trimestre y año. Los pasos son los siguientes: Crearemos una vista llamada cubeprodtime. Que contiene un cubo jerárquico de ventas agregadas a través de tiempos y productos. A continuación, crearemos una vista de la dimensión temporal que se utilizará como borde del cubo. El borde de tiempo, que contiene un conjunto completo de fechas, se particionará exterior unido a los datos escasos en la vista cubeprodtime. Finalmente, para obtener el máximo rendimiento, crearemos una vista materializada, mvprodtime. Construido con la misma definición que cubeprodtime. Para obtener más información sobre cubos jerárquicos, consulte el Capítulo 20, SQL para agregación en almacenes de datos. La vista materializada se define en el paso 1 de la siguiente sección. Paso 1 Cree la vista jerárquica de cubo La vista materializada que se muestra a continuación puede ya existir en su sistema si no, cree ahora. Si debe generarlo, tenga en cuenta que limitamos la consulta a sólo dos productos para mantener el tiempo de procesamiento corto: Debido a que esta vista está limitada a dos productos, devuelve un poco más de 2200 filas. Tenga en cuenta que la columna HierarchicalTime contiene representaciones de tiempo de cadena de todos los niveles de la jerarquía de tiempo. La expresión CASE utilizada para la columna HierarchicalTime agrega un marcador (0, 1.) a cada cadena de fecha para denotar el nivel de tiempo del valor. A 0 representa el nivel del año, 1 es cuartos, 2 es meses y 3 es día. Tenga en cuenta que la cláusula GROUP BY es una ROLLUP concatenada que especifica la jerarquía de rollup para las dimensiones de tiempo y producto. La cláusula GROUP BY es lo que determina el contenido del cubo jerárquico. Paso 2 Cree la vista edgetime, que es un conjunto completo de valores de fecha edgetime es la fuente para rellenar intervalos de tiempo en el cubo jerárquico usando una unión externa particionada. La columna HierarchicalTime en edgetime se utilizará en una combinación particionada con la columna HierarchicalTime en la vista cubeprodtime. La siguiente declaración define edgetime: Paso 3 Cree la vista materializada mvprodtime para soportar un rendimiento más rápido La definición de vista materializada es un duplicado de la vista cubeprodtime definida anteriormente. Dado que se trata de una consulta duplicada, las referencias a cubeprodtime se volverán a escribir para utilizar la vista materializada mvprodtime. Lo siguiente materializado puede ya existir en su sistema si no, crearlo ahora. Si debe generarlo, tenga en cuenta que limitamos la consulta a sólo dos productos para mantener el tiempo de procesamiento corto. Paso 4 Crear la consulta de comparación Ahora hemos establecido el escenario para nuestra consulta de comparación. Podemos obtener cálculos de comparación período-a-período en todos los niveles de tiempo. Requiere aplicar funciones analíticas a un cubo jerárquico con datos densos a lo largo de la dimensión temporal. Algunos de los cálculos que podemos lograr para cada nivel de tiempo son: Suma de ventas para el período anterior en todos los niveles de tiempo. Diferencia en las ventas respecto al período anterior. Suma de ventas en el mismo período hace un año en todos los niveles de tiempo. Variación en las ventas respecto al mismo período del año pasado. En el ejemplo siguiente se realizan los cuatro cálculos. Utiliza una combinación externa particionada de las vistas cubeprodtime y edgetime para crear una vista en línea de datos densos llamados densecubeprodtime. La consulta entonces utiliza la función LAG de la misma manera que el ejemplo previo de un solo nivel. La cláusula WHERE externa especifica el tiempo en tres niveles: los días de agosto de 2001, todo el mes y todo el tercer trimestre de 2001. Tenga en cuenta que las dos últimas filas de los resultados contienen las agregaciones de nivel de mes y trimestre. Los resultados más fáciles de leer si está utilizando SQLPlus, los encabezados de columna deben ajustarse con los siguientes comandos. The commands will fold the column headings to reduce line length: Here is the query comparing current sales to prior and year ago sales: The first LAG function ( salespriorperiod ) partitions the data on gidp. gato. subcat. prod. gidt and orders the rows on all the time dimension columns. It gets the sales value of the prior period by passing an offset of 1. The second LAG function ( salessameperiodprioryear ) partitions the data on additional columns qtrnum. monnum. and daynum and orders it on yr so that, with an offset of 1, it can compute the year ago sales for the same period. The outermost SELECT clause computes the variances. Creating a Custom Member in a Dimension: Example In many OLAP tasks, it is helpful to define custom members in a dimension. For instance, you might define a specialized time period for analyses. You can use a partitioned outer join to temporarily add a member to a dimension. Note that the new SQL MODEL clause is suitable for creating more complex scenarios involving new members in dimensions. See Chapter 22, SQL for Modeling for more information on this topic. As an example of a task, what if we want to define a new member for our time dimension We want to create a 13th member of the Month level in our time dimension. This 13th month is defined as the summation of the sales for each product in the first month of each quarter of year 2001. The solution has two steps. Note that we will build this solution using the views and tables created in the prior example. Two steps are required. First, create a view with the new member added to the appropriate dimension. The view uses a UNION ALL operation to add the new member. To query using the custom member, use a CASE expression and a partitioned outer join. Our new member for the time dimension is created with the following view: In this statement, the view timec is defined by performing a UNION ALL of the edgetime view (defined in the prior example) and the user-defined 13th month. The gidt value of 8 was chosen to differentiate the custom member from the standard members. The UNION ALL specifies the attributes for a 13th month member by doing a SELECT from the DUAL table. Note that the grouping id, column gidt. is set to 8, and the quarter number is set to 5. Then, the second step is to use an inline view of the query to perform a partitioned outer join of cubeprodtime with timec. This step creates sales data for the 13th month at each level of product aggregation. In the main query, the analytic function SUM is used with a CASE expression to compute the 13th month, which is defined as the summation of the first months sales of each quarter. The SUM function uses a CASE to limit the data to months 1, 4, 7, and 10 within each year. Due to the tiny data set, with just 2 products, the rollup values of the results are necessarily repetitions of lower level aggregations. For more realistic set of rollup values, you can include more products from the Game Console and Y Box Games subcategories in the underlying materialized view. Scripting on this page enhances content navigation, but does not change the content in any way. Moving average in T-SQL A common calculation in trend analysis is the moving (or rolling) average. 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 de 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 que es nuevo en SQL 2012 es la posibilidad de restringir el tamaño de la ventana especificando cuántas filas que preceden a la ventana debe 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í. Posted by Tomas LindAVG (Transact-SQL) ALL Applies the aggregate function to all values. 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 licencia 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.

No comments:

Post a Comment