Tengo una mesa, Medidas. Con columnas: timestamp (Unix timestamp), etiqueta. valor. Para obtener una media móvil simple de decir, 15 días, puedo hacer algo como: Ahora quiero obtener el promedio móvil de las últimas 150 filas, pero no estoy seguro de cómo consultarlo. Esto es obviamente incorrecto, pero he estado pensando en ello y no puedo entenderlo. Cualquier idea Mi tren de pensamiento es que necesito emparejar cada entrada con las 150 entradas debajo de él y computar el valor promedio para esas 150 entradas. Im también bastante seguro que theres probablemente una mejor manera sin CROSS JOIN. Ya que sería muy lento. 19 de noviembre de 2010 a las 9: 1821 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. En primer lugar, todas las combinaciones, WHERE. 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) (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 denominado 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 anteriormente dependían de operaciones con elementos frecuentes ahora se benefician de un rendimiento significativamente mejorado, así como 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 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 centran 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 usar con LASTVALUE y FIRSTVALUE. Vemos la salida deseada en la columna La siguiente salida: Computación de valores de datos para rellenar espacios Los ejemplos en 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 de tiempo. 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 función 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 Crear la vista de cubo jerárquico La vista materializada que se muestra a continuación puede ya existir en su sistema si no, crear 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. The outer WHERE clause specifies time at three levels: the days of August 2001, the entire month, and the entire third quarter of 2001. Note that the last two rows of the results contain the month level and quarter level aggregations. Note that to make the results easier to read if you are using SQLPlus, the column headings should be adjusted with the following commands. 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. Oracle Database 11g Jobs Oracle Database 11g Jobs Demand Trend The demand trend of job ads citing Oracle Database 11g as a proportion of all IT jobs with a match in the Database amp Business Intelligence category. Oracle Database 11g Salary Trend This chart provides the 3-month moving average for salaries quoted in permanent IT jobs citing Oracle Database 11g within the UK. Oracle Database 11g Salary Histogram This chart provides a salary histogram for IT jobs citing Oracle Database 11g over the 3 months to 7 October 2017 within the UK. Oracle Database 11g Top 30 Job Locations The table below looks at the demand and provides a guide to the median salaries quoted in IT jobs citing Oracle Database 11g within the UK over the 3 months to 7 October 2017. The Rank Change column provides an indication of the change in demand within each location based on the same 3 month period last year. Location (Click see detailed statistics and trends) Rank Change on Same Period Last Year Matching Permanent IT Job Ads Median Salary Last 3 MonthsHello, Database Version: Oracle 11g I have a cube having 4 dimensions-- 1 Time dimension and 3 User dimensions namely Channel, Mode and Type. I want to find the average of sales over a period of 3 months based on different Channel, Mode and type. For Eg. i want to find average sales done by Channel A AND Mode B AND Type C between 1-JAN-2008 and 31-MAR-2008. How can i find this. Using the views created by AWM while creating the cube, i can get the cummulative sales for these dimensions, but not average. I tried using Calculated measures, but whenever i select the required Channel, Mode and Type, the selected Channel, Mode and Type is not added in the expression created. And again i get the cummulative value irrespective of Channel, Mode and Type. Please help me find a solution for this. Or atleast a query for finding the average. . : 20 There is an AVERAGE function in OLAP DML. If you create a custom measure you can assign an OLAP DML function such as AVERAGE and return the data through the SQL View. Please refer to the OLAP DML Manual, there is a link on the OLAP OTN Home Page and this will explain how to use the AVERAGE function. Keith Laker Oracle EMEA Consulting OLAP Blog: oracleOLAP. blogspot/ OLAP Wiki: wiki. oracle/page/OracleOLAPOption DM Blog: oracledmt. blogspot/ OWB Blog. blogs. oracle/warehousebuilder/ OWB Wiki. wiki. oracle/page/OracleWarehouseBuilder DW on OTN. www. oracle/technology/products/bi/db/11g/index How do i do it. What option should i select for creating this function in calculated measure..Can u just explain me the steps for doing it..I refered the DML Manual regarding the Average Function. And found a sample query, LIMIT product TO SPORTSWEAR REPORT W 14 HEADING Average Sales AVERAGE(sales district) When i execute this query against the GLOBAL Schema, i get an error saying ORA-33852: You provided extra input starting at REPORT. I am really stuck up in this..I have googled max, but not finding any solution..Please do help me. I checked this today and the Moving Average calculation is a supported template in 11g. Within your existing cube, there should be a node that says Calculated Measure. If you right click on that node it will allow to create a new measure using a calculation template. There is a pulldown menu Choose a Calculation Type and in the category of Moving Aggregates there is the option to create a moving average. In this case you probably want to use a 3 month moving average. The template will guide you to provide the inputs for the parameters, such as the source measure. The resulting measure will have the same dimensionality as the source measure. Keith Laker Oracle EMEA Consulting OLAP Blog: oracleOLAP. blogspot/ OLAP Wiki: wiki. oracle/page/OracleOLAPOption DM Blog: oracledmt. blogspot/ OWB Blog. blogs. oracle/warehousebuilder/ OWB Wiki. wiki. oracle/page/OracleWarehouseBuilder DW on OTN. www. oracle/technology/products/bi/db/11g/index Ya i did try with that and i was able to get the 3 months moving average, but i need 6 months average. Can u just explain me how i can get 6 months average. This is the expression generated for moving average. AVG( DEMOCUBE. AMT ) OVER HIERARCHY (TIME. TIMEHIER BETWEEN 1 PRECEDING AND 1 FOLLOWING WITHIN LEVEL ) What does the underlined values 1 refer to..When i click on 1 . it gives me option to change the value..What value should i change to get the moving average of 6 months. Please help me. Sorry forgot to mention about the time dimension. My time dimension is this way, TotalTime--gtYear--gtQuarter--gtMonth--gtDay So what should i select in the LEVEL also for getting the six months moving average.. Awaiting for reply..Please do help. Trying to create this type of calculation should be quick and simple, instead with 11g we seem to created a complete an utter nightmare. The documentation in the help is of little use in trying to understand what you are supposed to do. Anyway, after a lot of experimenting with Excel and AWM I finally managed to arrive at the answer. To create a 6 month moving average for a measure, in my case SALES, the equation looks like this: AVG( UNITSCUBE. SALES ) OVER HIERARCHY ( TIME. CALENDAR BETWEEN 5 PRECEDING AND CURRENT MEMBER WITHIN LEVEL ) For you the inputs in blue would be (from left to right) Cube name - which in my case is sales Hierarchy name - which in my case is TIME. CALENDAR Include X preceding - in this case 5 (because you want to step back 5 periods from the current period. If you include the current period this equals your six months for the average ) and X following - in this 0 which translates to CURRENT MEMBER in the above equation. As we only want to go back 6 months and not include any future periods this input becomes 0. Within Level - This is actually quite powerful and quite confusing as well. For this simple expression we can just set the value to Level. But this will compute a 6 period moving average across all levels. At the quarter level you will get an average of the last 6 quarters and at the year level an average of the last 6 years. Unfortunately, I cant see how to compute the average sales only at the month level. If you use any of the other settings for the last parameter the information that comes back is not as expected and the help is not very clear about these various level settings. But the above equation does seem to work, just gives you a little more data than you probably need. Hope this helps Keith Laker Oracle EMEA Consulting OLAP Blog: oracleOLAP. blogspot/ OLAP Wiki: wiki. oracle/page/OracleOLAPOption DM Blog: oracledmt. blogspot/ OWB Blog. blogs. oracle/warehousebuilder/ OWB Wiki. wiki. oracle/page/OracleWarehouseBuilder DW on OTN. www. oracle/technology/products/bi/db/11g/index Thank You Mr. Laker..Ur inputs were a great help to me in solving the problem.. I am a beginner in Oracle OLAP. I faced some problems while creating View for the Cube in Oracle 10g, so i switched to Oracle 11g where views are created automatically as and when Dimensions or Cubes are created. But i did not find much examples for creating calculated measures and also the help file for Oracle OLAP 11g was not elaborate..So i found difficulty in creating the calculated measure..What do you suggest which of the Oracle versions is more stable and has more efficiency.. There are 5149 rows in the fact table. But it takes long time to return results of the query. I am querying the views to get the results of cube..Why is it so slow. And do i need to index the tables for the queries to execute faster. I would look on the Oracle OLAP Blog where I published a series of workshops on how create an OLAP data model. This covers the main features that can be used to optimise the data loading and aggregation process. OLAP Workshop 1. Basic OLAP Concepts OLAP Workshop 2. Understanding OLAP Technology OLAP Workshop 3. Building an Analytic Workspace OLAP Workshop 4. Managing Different Types of Hierarchies OLAP Workshop 5. Building Cubes OLAP Workshop 6. Advanced Cube Design OLAP Workshop 7. Creating Calculated Measures OLAP Workshop 8. Creating A Calculated Measure Cube - IOGALFF This should help you resolve the issues you are having regarding slow query performance. Keith Laker Oracle EMEA Consulting OLAP Blog: oracleOLAP. blogspot/ OLAP Wiki: wiki. oracle/page/OracleOLAPOption DM Blog: oracledmt. blogspot/ OWB Blog. blogs. oracle/warehousebuilder/ OWB Wiki. wiki. oracle/page/OracleWarehouseBuilder DW on OTN. www. oracle/technology/products/bi/db/11g/index I saw your last comment posted on the OLAP Blog. I have not published your comments as the question is very technical and specific to your application. Could you post it to the forum so the rest of the OLAP community can help find a solution. I think I know the answer so once you post the details to the forum I will try to give you a reply. Keith Laker Oracle EMEA Consulting OLAP Blog: oracleOLAP. blogspot/ OLAP Wiki: wiki. oracle/page/OracleOLAPOption DM Blog: oracledmt. blogspot/ OWB Blog. blogs. oracle/warehousebuilder/ OWB Wiki. wiki. oracle/page/OracleWarehouseBuilder DW on OTN. www. oracle/technology/products/bi/db/11g/index I was reading the post regaring creating calculated measure in 11g, so i posted my problem there..Anyways i am posting it here again.. Thanks for the explanation of creating calculated measure in Oracle 11g. I am facing a problem in finding average. I created a calculated measure to find the average of ratio of SALES of Product A to Sales of Product B over a period of six months. I used the option of Moving Average calculation type. What problem i am facing is if there are no sales of either of the product in a month, then the ratio will be null and the average is calculated over the period of the remaining months where we get ratio. For Eg., The sales of Product A in six months are as follows, Jan - 50 Feb - 67 Mar - 0 Apr - 13 May - 24 Jun - 0 The sales of Product B in six months are as follows, Jan - 15 Feb - 10 Mar - 25 Apr - 50 May - 18 Jun - 7 So now the ratio of Sales of Product A to Product B will be Jan - 3.333333333 Feb - 6.7 Mar - 0 Apr - 0.26 May - 1.333333333 Jun - 0 And the average ratio over the period of 6 months will be, 1.937777778 which is the expected result, but i am getting it as 2.906666667 which is the average excluding the zero ratios. How can i write the expression to include even the zeroes. What i am doing is, first i am creating a calculated measure to find the ratio. And then i am creating another calculated measure to find the average over the ratios. RatioofAtoB: CUBEFORDEMO. SALESPRODUCTTYPE PRODUCTTYPE2 / CUBEFORDEMO. SALESPRODUCTTYPE PRODUCTTYPE3 AvgRatioofAToB: AVG(CUBEFORDEMO. RatioofAtoB) OVER HIERARCHY (TIME. TIMEHIER BETWEEN 5 PRECEDING AND CURRENT MEMBER WITHIN LEVEL) Is it right what i am doing..Can u please help me in finding solution for this. And also one more problem is, If i want to find ratio of Sales of Product A againts all other products, the expression i want to use is RatioofAtoOthers: CUBEFORDEMO. SALESPRODUCTTYPE PRODUCTTYPE2 / CUBEFORDEMO. SALESPRODUCTTYPE PRODUCTTYPE2 But i am not able to create the expression, it is not allowing or either ltgt in the expression for not equal to . Right now i am having only three members in Product dimension, so i am calculating the ratio as RatioofAtoOthers: (CUBEFORDEMO. SALESPRODUCTTYPE PRODUCTTYPE2) / (CUBEFORDEMO. SALESPRODUCTTYPE PRODUCTTYPE3 CUBEFORDEMO. SALESPRODUCTTYPE PRODUCTTYPE4 ).. But in future if i increase the number of members in Product dimension, then i have to keep changing the expression..So how can i form the expression to avoid changing the expression. Oracle Database 11g Jobs in Reading Oracle Database 11g Jobs Demand Trend in Reading The demand trend of job ads across the Reading region citing Oracle Database 11g as a proportion of all IT jobs with a match in the Database amp Business Intelligence category. Oracle Database 11g Salary Trend in Reading This chart provides the 3-month moving average for salaries quoted in permanent IT jobs citing Oracle Database 11g across the Reading region. Oracle Database 11g Top 30 Related IT Skills in Reading For the 6 months to 7 October 2017, IT jobs citing Oracle Database 11g also mentioned the following skills in order of popularity. The figures indicate the number co-occurrences and its proportion to all job ads across the Reading region with a requirement for Oracle Database 11g.
No comments:
Post a Comment