La parte más problemática de su tarea es el cálculo del producto agregado en SQL puro (como FYI: hay muchas funciones de agregado estándar, como Sum (), Min (), Max (), etc., pero sin producto P ()). Una posible solución descrita en: La función de producto agregado extiende SQL (www. codeproject / Tips / 137564 / Aggregate-Product-function-extends-SQL) se basa en la ecuación matemática: traducida a la siguiente sentencia SQL: Puede aplicar esta solución a su problema. Con el fin de calcular el promedio de ejecución (en movimiento) puede construir la auto-unión de la tabla de datos y aplicar el cálculo para todas las filas anteriores a la actual. La implementación real dependerá de la base de datos que está utilizando. Espero que esto pueda ayudar. Esta es una pregunta de Evergreen Joe Celko. Ignoro qué plataforma de DBMS se utiliza. Pero en cualquier caso Joe fue capaz de responder hace más de 10 años con SQL estándar. Joe Celko SQL Puzzles y Respuestas citation: Ese último intento de actualización sugiere que podríamos usar el predicado para construir una consulta que nos daría una media móvil: ¿Es la columna extra o el enfoque de consulta mejor? La consulta es técnicamente mejor porque el enfoque UPDATE Desnormalizar la base de datos. Sin embargo, si los datos históricos que se están registrando no van a cambiar y el cálculo de la media móvil es caro, podría considerar el uso de la columna. SQL consulta de rompecabezas: por todos los medios uniforme. Usted acaba de tirar al cubo de peso apropiado dependiendo de la distancia desde el punto de tiempo actual. Por ejemplo quottake weight1 para datapoints dentro de 24hrs de datapoint actual weight0.5 para datapoints dentro de 48hrsquot. Ese caso importa cuántos puntos de datos consecutivos (como 6:12 am y 11:48 pm) están distantes entre sí. Un caso de uso que puedo pensar sería un intento de suavizar el histograma dondequiera que los puntos de datos no sean lo suficientemente densos. 22:22 No estoy seguro de que su resultado esperado (salida) muestra clásico simple móvil (rolling) promedio de 3 días. Porque, por ejemplo, el primer triple de números por definición da: pero esperas 4.360 y su confusión. Sin embargo, sugiero la siguiente solución, que utiliza la función de ventana AVG. Este enfoque es mucho más eficiente (claro y con menos recursos) que SELF-JOIN introducido en otras respuestas (y estoy sorprendido de que nadie haya dado una solución mejor). Verá que AVG está envuelto con el caso cuando rownum gt p. days entonces para forzar NULL s en las primeras filas, donde el promedio móvil de 3 días no tiene sentido. Respondió 23 de febrero a las 13:12 Podemos aplicar Joe Celkos sucia izquierda método de unión externa (como citado por Diego Scaravaggi) para responder a la pregunta como se le preguntó. La media móvil exponencial en T-SQL Las medias móviles exponenciales son similares a las medias móviles ponderadas en que asignan menos peso a los cambios hace mucho tiempo, y más peso a los recientes Cambios. Las medias móviles ponderadas son lineales, pero las medias móviles exponenciales son exponenciales. Es decir, el peso puede expresarse como una curva: hay una gran manera de calcular promedios móviles exponenciales en T-SQL utilizando una característica indocumentada sobre variables y totales en ejecución en SQL Server. En este post de blog voy a mostrar cómo usar ese método para calcular el promedio móvil exponencial en T-SQL, pero también voy a presentar un método que está utilizando las características estándar de SQL Server. Desafortunadamente, eso significa usar un bucle. En los ejemplos calcularé una media móvil exponencial de 9 días. Los ejemplos usan la base de datos TAdb. Un script para crear TAdb se puede encontrar aquí. Media móvil exponencial (EMA): Método de los totales corrientes La teoría detrás de las características totales corrientes en las actualizaciones es descrita en detalle por Jeff Moden en su artículo Resolviendo los Problemas de Total de Riesgo y Orden Ordinario. Otros recursos que describen el uso de este método para calcular EMA son el blog Calculando promedios móviles con T-SQL por Gabriel Priester y el foro post Exponential Moving Average Challenge. Tanto en SQL Server Central. Básicamente, en T-SQL puede actualizar variables así como columnas en una instrucción de actualización. Las actualizaciones se realizan fila por fila internamente por SQL Server. Este comportamiento fila por fila es lo que hace posible calcular un total en ejecución. Este ejemplo muestra cómo funciona: Tenga en cuenta que 8220ColumnRunningTotal8221 es un total de 8220ColumnToSum8221. Usando este método podemos calcular EMA9 con este T-SQL: El cálculo de EMA es bastante simple. Utilizamos la fila actual y la anterior, pero con más peso a la fila actual. El peso se calcula mediante la fórmula 2 / (19), donde 822098221 es el parámetro para la longitud de la EMA. Para calcular EMA9 para la fila 10, el cálculo es: En este caso, la fila actual obtiene 20 del peso (2 / (19) 0,2) y la fila anterior obtiene 80 del peso (1-2 / (19) 0.8) . Encontrará este cálculo en la instrucción anterior en la instrucción CASE: EMM (Exponential Moving Average): Método de bucle En la medida en que yo sepa, excepto para el método de totales de ejecución descrito anteriormente, no hay forma de calcular EMA utilizando una sentencia SQL basada en conjuntos . Por lo tanto, el T-SQL a continuación utiliza un bucle while para calcular EMA9: Los resultados son los mismos que en el ejemplo de totales en ejecución anterior. Rendimiento Como era de esperar, la versión basada en conjuntos de ejecución de totales es mucho más rápida que la versión de bucle. En mi máquina la solución basada del sistema era cerca de 300 ms, comparada a cerca de 1200 con la versión del lazo. Sin embargo, la versión de bucle se ajusta más a los estándares SQL. Así que la elección entre los métodos depende de lo que es más importante para usted, el rendimiento o los estándares. Uso El promedio móvil exponencial se puede utilizar en el análisis de tendencias, al igual que con los otros tipos de promedios móviles, Promedio móvil simple (SMA) y Promedio móvil ponderado (WMA). También hay otros cálculos en el análisis técnico que utiliza la EMA, MACD por ejemplo. Esta entrada del blog forma parte de una serie sobre análisis técnico, TA, en SQL Server. Vea las otras publicaciones aquí. Publicado por Tomas Lind Tomas Lind - Servicios de consultoría como SQL Server DBA y desarrollador de bases de datos en High Coast Database Solutions AB. AVG (Transact-SQL) ALL Aplica la función agregada a todos los valores. ALL es el valor predeterminado. DISTINCT Especifica que AVG se realizará sólo en cada instancia única de un valor, independientemente de cuántas veces se produzca el valor. Expresión Es una expresión de la categoría de tipo de datos numéricos o aproximados numéricos, excepto para el tipo de datos de bit. Las funciones agregadas y las subconsultas no están permitidas. OVER (partitionbyclause orderbyclause) partitionbyclause divide el conjunto de resultados producido por la cláusula FROM en particiones a las que se aplica la función. Si no se especifica, la función trata todas las filas del conjunto de resultados de la consulta como un solo grupo. Orderbyclause determina el orden lógico en el que se realiza la operación. Ordenar porcláusula es requerida. Para obtener más información, vea Cláusula OVER (Transact-SQL). El tipo de retorno se determina por el tipo de resultado de expresión evaluado. Categoría decimal (p, s) Si el tipo de datos de la expresión es un tipo de datos de alias, el tipo de retorno también es del tipo de datos de alias. Sin embargo, si se promueve el tipo de datos base del tipo de datos alias, por ejemplo de tinyint a int. El valor de retorno es del tipo de datos promovido y no del tipo de datos alias. AVG () calcula el promedio de un conjunto de valores dividiendo la suma de esos valores por el recuento de valores nonnull. Si la suma supera el valor máximo para el tipo de datos del valor devuelto, se devolverá un error. AVG es una función determinista cuando se usa sin las cláusulas OVER y ORDER BY. No es determinista cuando se especifica con las cláusulas OVER y ORDER BY. Para obtener más información, consulte Funciones deterministas y no determinísticas. A. Uso de las funciones SUM y AVG para los cálculos En el ejemplo siguiente se calculan las horas de vacaciones promedio y la suma de horas de 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.21 SQL para análisis e informes Tratamiento de NULLs como entrada a funciones de ventana Funciones de ventana La semántica NULL coincide con la semántica NULL para las funciones de agregación de SQL. Otras semánticas se pueden obtener mediante funciones definidas por el usuario, o mediante el uso de DECODE o una expresión CASE dentro de la función de ventana. Funciones de ventanilla con desplazamiento lógico Un desplazamiento lógico se puede especificar con constantes como RANGE 10 PRECEDING. O una expresión que evalúa a una constante, o por una especificación de intervalos como RANGE INTERVAL N DAY / MONTH / YEAR PRECEDING o una expresión que se evalúa a un intervalo. Con el desplazamiento lógico, sólo puede haber una expresión en la lista ORDER BY en la función, con el tipo compatible con NUMERIC si el desplazamiento es numérico o DATE si se especifica un intervalo. Ejemplo 21-7 Función de agregación acumulativa A continuación se muestra un ejemplo de las cantidades acumuladas por ID de cliente por trimestre en 1999: En este ejemplo, la función analítica SUM define, para cada fila, una ventana que comienza al principio de la partición (UNBOUNDED PRECEDING ) Y termina, de forma predeterminada, en la fila actual. Las s SUM anidadas son necesarias en este ejemplo ya que estamos realizando una SUM sobre un valor que es en sí mismo una SUMA. Las agregaciones anidadas se usan muy a menudo en funciones agregadas analíticas. Ejemplo 21-8 Movimiento de la función agregada Este ejemplo de una ventana basada en el tiempo muestra, para un cliente, el promedio móvil de ventas para el mes actual y dos meses anteriores: Tenga en cuenta que las dos primeras filas para el cálculo del promedio móvil de tres meses en el Los datos de salida se basan en un tamaño de intervalo más pequeño que el especificado porque el cálculo de la ventana no puede alcanzar más allá de los datos recuperados por la consulta. Es necesario considerar los diferentes tamaños de ventana que se encuentran en los bordes de los conjuntos de resultados. En otras palabras, puede que tenga que modificar la consulta para incluir exactamente lo que desea. Función de agregación centralizada El cálculo de las funciones de agregación de ventanas centradas alrededor de la fila actual es sencilla. Este ejemplo calcula para todos los clientes un promedio móvil de ventas centrado durante una semana a fines de diciembre de 1999. Encuentra un promedio del total de ventas para el día anterior a la fila actual y un día después de la fila actual incluyendo la fila actual. Ejemplo 21-9 Agregado centrado Las filas inicial y final de cada cálculo del promedio móvil centrado en productos en los datos de salida se basan en dos días, ya que el cálculo de la ventana no puede superar los datos recuperados por la consulta. Los usuarios deben considerar los diferentes tamaños de ventana que se encuentran en los bordes de los conjuntos de resultados: es posible que sea necesario ajustar la consulta. Funciones agregadas de ventanas en presencia de duplicados El siguiente ejemplo ilustra cómo las funciones de agregación de ventanas calculan valores cuando hay duplicados, es decir, cuando se devuelven varias filas para un único valor de ordenación. La consulta recupera la cantidad vendida a varios clientes durante un intervalo de tiempo especificado. La consulta define una ventana en movimiento que se ejecuta desde la fecha de la fila actual hasta 10 días antes. Tenga en cuenta que la palabra clave RANGE (RANGO) Se utiliza para definir la cláusula windowing de este ejemplo. Esto significa que la ventana puede contener potencialmente muchas filas para cada valor en el rango. En este caso, hay tres pares de filas con valores de fecha duplicados. Ejemplo 21-10 Funciones de agregación de ventanas con desplazamientos lógicos En la salida de este ejemplo, todas las fechas excepto 6 de mayo y 12 de mayo devuelven dos filas con fechas duplicadas. Examine los números comentados a la derecha de la salida para ver cómo se calculan los valores. Tenga en cuenta que cada grupo entre paréntesis representa los valores devueltos para un solo día. Tenga en cuenta que este ejemplo sólo se aplica cuando utiliza la palabra clave RANGE en lugar de la palabra ROWS. También es importante recordar que con RANGE. Sólo puede utilizar 1 ORDER BY expresión en las funciones analíticas ORDER BY cláusula. Con la palabra clave ROWS, puede utilizar múltiples órdenes por expresiones en la cláusula ORDER BY de las funciones analíticas. Tamaño de ventana variable para cada fila Hay situaciones en las que es útil variar el tamaño de una ventana para cada fila, en función de una condición especificada. Por ejemplo, es posible que desee hacer que la ventana sea más grande para ciertas fechas y más pequeña para otras. Suponga que desea calcular el promedio móvil del precio de las acciones durante tres días hábiles. Si tiene un número igual de filas por día para todos los días laborables y no se almacenan días no laborables, puede utilizar una función de ventana física. Sin embargo, si las condiciones no se cumplen, puede calcular una media móvil utilizando una expresión en los parámetros de tamaño de ventana. Las expresiones en una especificación de tamaño de ventana se pueden hacer en varias fuentes diferentes. La expresión podría ser una referencia a una columna de una tabla, como una tabla de tiempo. También podría ser una función que devuelve el límite adecuado para la ventana en función de los valores de la fila actual. La siguiente sentencia para una hipotética base de datos de precios utiliza una función definida por el usuario en su cláusula RANGE para establecer el tamaño de la ventana: En esta sentencia, ttimekey es un campo de fecha. Aquí, fn podría ser una función PL / SQL con la siguiente especificación: 4 si ttimekey es Lunes, Martes Si alguno de los días anteriores son días festivos, ajusta el conteo apropiadamente. Tenga en cuenta que cuando se especifica la ventana utilizando un número en una función de ventana con ORDER BY en una columna de fecha, se convierte en el número de días. También podría haber utilizado la función de conversión de intervalo literal, como NUMTODSINTERVAL (fn (ttimekey), DAY) en lugar de sólo fn (ttimekey) para significar lo mismo. También puede escribir una función PL / SQL que devuelve un valor de tipo de datos INTERVAL. Funciones de agregación de ventanas con desplazamientos físicos Para las ventanas expresadas en filas, las expresiones de ordenación deben ser únicas para producir resultados determinísticos. Por ejemplo, la consulta siguiente no es determinista porque timeid no es único en este conjunto de resultados. Ejemplo 21-11 Funciones de agregación de ventanas con desplazamientos físicos Una manera de manejar este problema sería agregar la columna prodid al conjunto de resultados y al orden en timeid y prodid. Funciones FIRSTVALUE y LASTVALUE Las funciones FIRSTVALUE y LASTVALUE permiten seleccionar la primera y la última fila desde una ventana. Estas filas son especialmente valiosas porque a menudo se utilizan como líneas de base en los cálculos. Por ejemplo, con una partición que contenga datos de ventas ordenados por día, puede preguntar cuánto fue cada día de ventas en comparación con el primer día de ventas (FIRSTVALUE) del período O quizá desee saber, para un conjunto de filas en el aumento de la orden de venta , ¿Cuál fue el porcentaje de cada venta en la región en comparación con la mayor venta (LASTVALUE) en la región? Si la opción IGNORE NULLS se utiliza con FIRSTVALUE. Devolverá el primer valor no nulo en el conjunto o NULL si todos los valores son NULL. Si IGNORE NULLS se utiliza con LASTVALUE. Devolverá el último valor no nulo en el conjunto o NULL si todos los valores son NULL. La opción IGNORE NULLS es particularmente útil para llenar correctamente una tabla de inventario. Funciones de agregación de informes Después de procesar una consulta, los valores agregados como el número de filas resultantes o el valor promedio de una columna se pueden calcular fácilmente dentro de una partición y ponerse a disposición de otras funciones de generación de informes. Las funciones agregadas de informes devuelven el mismo valor agregado para cada fila de una partición. Su comportamiento con respecto a NULL es el mismo que las funciones de agregado de SQL. La sintaxis es: Además, se aplican las siguientes condiciones: Un asterisco () sólo está permitido en COUNT () DISTINCT sólo se admite si las funciones de agregación correspondientes le permiten valorar expresión1 y valor expresión2 puede ser cualquier expresión válida que incluya referencias de columna o agregados. La cláusula PARTITION BY define los grupos en los que se calcularían las funciones de ventana. Si la cláusula PARTITION BY está ausente, entonces la función se calcula sobre todo el conjunto de resultados de la consulta. Las funciones de informes sólo pueden aparecer en la cláusula SELECT o en la cláusula ORDER BY. El principal beneficio de las funciones de informes es su capacidad para realizar múltiples pasadas de datos en un solo bloque de consulta y acelerar el rendimiento de la consulta. Consultas tales como Contar el número de vendedores con ventas de más de 10 de las ventas de la ciudad no requieren uniones entre bloques de consulta separados. Por ejemplo, considere la pregunta Para cada categoría de producto, busque la región en la que tuvo ventas máximas. La consulta SQL equivalente que utiliza la función de agregación de informes MAX sería: La consulta interna con la función de agregación de informes MAX (SUM (montos)) devuelve: Los resultados completos de la consulta son: Ejemplo 21-12 Reporting Aggregate Example Reporting aggregates combinado con consultas anidadas enable Responder a consultas complejas de manera eficiente. Por ejemplo, ¿qué sucede si desea conocer los productos más vendidos en las subcategorías de productos más significativos? A continuación se presenta una consulta que encuentra los 5 productos más vendidos para cada subcategoría de productos que contribuyen a más de 20 de las ventas dentro de su categoría de producto: RATIOTOREPORT Función La función RATIOTOREPORT calcula la relación entre un valor y la suma de un conjunto de valores. Si la expresión de valor de expresión se evalúa como NULL. RATIOTOREPORT también se evalúa como NULL. Pero se trata como cero para calcular la suma de valores para el denominador. Su sintaxis es: En esto, se aplica lo siguiente: expr puede ser cualquier expresión válida que incluya referencias de columna o agregados. La cláusula PARTITION BY define los grupos en los que se va a calcular la función RATIOTOREPORT. Si la cláusula PARTITION BY está ausente, entonces la función se calcula sobre todo el conjunto de resultados de la consulta. Para calcular RATIOTOREPORT de las ventas de cada canal, puede utilizar la siguiente sintaxis: Funciones LAG / LEAD Las funciones LAG y LEAD son útiles para comparar valores cuando las posiciones relativas de las filas se pueden conocer de forma fiable. Funcionan especificando el recuento de filas que separan la fila de destino de la fila actual. Debido a que las funciones proporcionan acceso a más de una fila de una tabla al mismo tiempo sin unirse automáticamente, pueden mejorar la velocidad de procesamiento. La función LAG proporciona acceso a una fila con un desfase dado antes de la posición actual y la función LEAD proporciona acceso a una fila con un desplazamiento dado después de la posición actual. Sintaxis de LAG / LEAD Estas funciones tienen la siguiente sintaxis: offset es un parámetro opcional y el valor por defecto es 1. default es un parámetro opcional y es el valor devuelto si el offset se sitúa fuera de los límites de la tabla o partición. Consulte Densificación de datos para informes para obtener información que muestre cómo utilizar las funciones LAG / LEAD para realizar consultas de comparación de período a período en datos escasos. Funciones PRIMERA / ÚLTIMA Las funciones de agregado FIRST / LAST le permiten clasificar un conjunto de datos y trabajar con sus filas de primer orden o de rango inferior. Después de encontrar las filas superiores o inferiores, se aplica una función agregada a cualquier columna deseada. Es decir, FIRST / LAST le permite clasificar en la columna A, pero devuelve el resultado de un agregado aplicado en las filas de primer orden o de última fila de la columna B. Esto es valioso porque evita la necesidad de una autocombinación o subconsulta, Mejorando así el rendimiento. La sintaxis de estas funciones comienza con una función de agregado regular (MIN MAX, SUM, AVG, COUNT, VARIANCE, STDDEV) que produce un único valor de retorno por grupo. Para especificar la clasificación utilizada, las funciones FIRST / LAST añaden una nueva cláusula que comienza con la palabra KEEP. FIRST / LAST Sintaxis Estas funciones tienen la siguiente sintaxis: Tenga en cuenta que la cláusula ORDER BY puede tener varias expresiones. PRIMERO / ÚLTIMO Como agregados regulares Puede utilizar la familia FIRST / LAST de agregados como funciones agregadas regulares. Ejemplo 21-15 PRIMER / ÚLTIMO Ejemplo 1 La siguiente consulta nos permite comparar el precio mínimo y el precio de lista de nuestros productos. Para cada subcategoría de productos dentro de la categoría de ropa para hombre, devuelve lo siguiente: Precio de lista del producto con el precio mínimo más bajo Precio mínimo más bajo Precio de lista del producto con el precio mínimo más alto Precio más alto PRIMERO / ÚLTIMO Como agregados de informes También puede Utilice la familia FIRST / LAST de agregados como funciones de agregación de informes. Un ejemplo es el cálculo de los meses que tuvieron el mayor y menor aumento en el número de cabezas a lo largo del año. La sintaxis de estas funciones es similar a la sintaxis para cualquier otro agregado de informes. Considere el ejemplo en el Ejemplo 21-15 para FIRST / LAST. ¿Qué pasa si queremos encontrar los precios de lista de los productos individuales y compararlos con los precios de lista de los productos en su subcategoría que tenía los precios mínimos más altos y más bajos? La siguiente consulta nos permite encontrar esa información para la subcategoría de la documentación con FIRST / LAST Como agregados de reporte. Ejemplo 21-16 PRIMERO / ÚLTIMO Ejemplo 2 El uso de las funciones FIRST y LAST como agregados de reporte facilita la inclusión de los resultados en cálculos como Salario como un porcentaje del salario más alto. Funciones de percentil inverso Usando la función CUMEDIST, puede encontrar la distribución acumulativa (percentil) de un conjunto de valores. Sin embargo, la operación inversa (encontrar qué valor calcula a cierto percentil) no es ni fácil de hacer ni calculada eficientemente. Para superar esta dificultad, se introdujeron las funciones PERCENTILECONT y PERCENTILEDISC. Estos pueden usarse tanto como funciones de generación de informes de ventanas como también para funciones agregadas normales. Estas funciones necesitan una especificación de clasificación y un parámetro que toma un valor percentil entre 0 y 1. La especificación de orden se maneja mediante una cláusula ORDER BY con una expresión. Cuando se utiliza como una función agregada normal, devuelve un valor único para cada conjunto ordenado. PERCENTILECONT. Que es una función continua calculada por interpolación, y PERCENTILEDISC. Que es una función escalonada que asume valores discretos. Al igual que otros agregados, PERCENTILECONT y PERCENTILEDISC operan en un grupo de filas en una consulta agrupada, pero con las siguientes diferencias: Requieren un parámetro entre 0 y 1 (inclusive). Un parámetro especificado fuera de este rango resultará en error. Este parámetro debe especificarse como una expresión que se evalúa como una constante. Requieren una especificación de clasificación. Esta especificación de ordenación es una cláusula ORDER BY con una sola expresión. No se permiten expresiones múltiples. Sintaxis de agregación normal Ejemplo de percentil inverso Utilizamos la siguiente consulta para devolver las 17 filas de datos utilizadas en los ejemplos de esta sección: PERCENTILEDISC (x) se calcula escaneando los valores de CUMEDIST en cada grupo hasta que encuentre el primero mayor que O igual a x. Donde x es el valor percentil especificado. Para el ejemplo de consulta donde PERCENTILEDISC (0.5), el resultado es 5.000, como se muestra a continuación: El resultado de PERCENTILECONT se calcula mediante interpolación lineal entre filas después de ordenarlas. Para calcular PERCENTILECONT (x). Primero calculamos el número de fila RN (1x (n-1)), donde n es el número de filas en el grupo y x es el valor percentil especificado. El resultado final de la función agregada se calcula mediante interpolación lineal entre los valores de las filas en los números de fila CRN CEIL (RN) y FRN FLOOR (RN). El resultado final será: PERCENTILECONT (X) if (CRN FRN RN), entonces (valor de la expresión de la fila en RN) else (CRN - RN) (valor de expresión para la fila en FRN) (RN - Expresión para fila en CRN). Considere la consulta de ejemplo anterior, donde calculamos PERCENTILECONT (0.5). Aquí n es 17. El número de fila RN (1 0,5 (n-1)) 9 para ambos grupos. Poniendo esto en la fórmula, (FRNCRN9), devolvemos el valor de la fila 9 como resultado. Otro ejemplo es, si desea calcular PERCENTILECONT (0.66). El número de fila calculado RN (1 0,66 (n -1)) (1 0,6616) 11,67. PERCENTILECONT (0,66) (12-11,67) (valor de la fila 11) (11,67-11) (valor de la fila 12). Estos resultados son: Las funciones de agregado percentil inverso pueden aparecer en la cláusula HAVING de una consulta como otras funciones agregadas existentes. Como agregados de informes También puede utilizar las funciones de agregado PERCENTILECONT. PERCENTILEDISC como funciones de agregación de informes. Cuando se utiliza como funciones de agregación de informes, la sintaxis es similar a la de otros agregados de informes. Esta consulta calcula lo mismo (límite de crédito medio para los clientes en este conjunto de resultados, pero informa el resultado de cada fila en el conjunto de resultados, como se muestra en la siguiente salida: Restricciones de percentiles inversos Para PERCENTILEDISC la expresión en la cláusula ORDER BY puede Ser de cualquier tipo de datos que puede ordenar (numérico, cadena, fecha etc.) Sin embargo, la expresión en la cláusula ORDER BY debe ser un tipo numérico o de fecha y hora (incluidos los intervalos) porque la interpolación lineal se utiliza para evaluar PERCENTILECONT. Si la expresión es del tipo DATE, el resultado interpolado se redondea a la unidad más pequeña para el tipo. Para un tipo DATE, el valor interpolado se redondeará al segundo más cercano, para los tipos de intervalo al segundo más cercano (INTERVAL DAY TO SECOND) O al mes (INTERVAL YEAR TO MONTH) Al igual que otros agregados, las funciones de percentil inverso ignoran NULLs en la evaluación del resultado. Por ejemplo, cuando desea encontrar el valor mediano en un conjunto, Oracle Database ignora los valores NULL y encuentra la mediana Entre los valores no nulos. Puede utilizar la opción NULLS FIRST / NULLS LAST en la cláusula ORDER BY, pero se ignorarán cuando se ignoren NULL. Funciones de rango y distribución hipotéticas Estas funciones proporcionan funcionalidad útil para el análisis de lo que pasa. Por ejemplo, cuál sería el rango de una fila, si la fila se insertó hipotéticamente en un conjunto de otras filas. Esta familia de agregados toma uno o más argumentos de una fila hipotética y un grupo ordenado de filas, devolviendo el RANGO. DENSERANK. PERCENTRANK o CUMEDIST de la fila como si se hubiera insertado hipotéticamente en el grupo. Sintaxis Hipotética de Rango y Distribución Aquí, expresión constante se refiere a una expresión que se evalúa a una constante, y puede haber más de una de esas expresiones que se pasan como argumentos a la función. La cláusula ORDER BY puede contener una o más expresiones que definen el orden de clasificación en el que se basará la clasificación. ASC. DESC. NULLS PRIMERO. NULLS LAST opciones estarán disponibles para cada expresión en el ORDER BY. Ejemplo 21-17 Clasificación y distribución hipotética Ejemplo 1 Usando los datos de precios de lista de la tabla de productos utilizados en esta sección, puede calcular el RANGO. PERCENTRANK y CUMEDIST para un suéter hipotético con un precio de 50 para la forma en que se ajusta dentro de cada una de las subcategorías suéter. La consulta y los resultados son: A diferencia de los agregados de percentil inverso, la cláusula ORDER BY en la especificación de orden para las funciones hipotéticas de rango y distribución puede tomar múltiples expresiones. El número de argumentos y las expresiones en la cláusula ORDER BY deben ser los mismos y los argumentos deben ser expresiones constantes del mismo tipo o compatibles con la expresión ORDER BY correspondiente. El siguiente es un ejemplo usando dos argumentos en varias hipotéticas funciones de clasificación. Ejemplo 21-18 Clasificación y distribución hipotética Ejemplo 2 Estas funciones pueden aparecer en la cláusula HAVING de una consulta como otras funciones agregadas. No pueden utilizarse como funciones de agregación de informes o funciones de agregación de ventanas. Funciones de regresión lineal Las funciones de regresión apoyan el ajuste de una línea de regresión de mínimos cuadrados ordinarios a un conjunto de pares de números. Puede utilizarlos como funciones agregadas o como funciones de creación de ventanas o de informes. Las funciones son las siguientes: Oracle aplica la función al conjunto de pares (e1. E2) después de eliminar todos los pares para los cuales cualquiera de e1 o e2 es nulo. E1 se interpreta como un valor de la variable dependiente (un valor y), y e2 se interpreta como un valor de la variable independiente (un valor x). Ambas expresiones deben ser números. Las funciones de regresión se calculan simultáneamente durante un solo paso a través de los datos. Con frecuencia se combinan con el COVARPOP. COVARSAMP. Y funciones CORR. REGRCOUNT La función REGRCOUNT devuelve el número de pares de números no nulos utilizados para ajustar la línea de regresión. Si se aplica a un conjunto vacío (o si no hay pares (e1, e2) donde ninguno de e1 o e2 es nulo), la función devuelve 0. REGRAVGY y REGRAVGX Funciones REGRAVGY y REGRAVGX calculan los promedios de la variable dependiente y los independientes Variable de la línea de regresión, respectivamente. REGRAVGY calcula el promedio de su primer argumento (e1) después de eliminar (e1. E2) pares donde cualquiera de e1 o e2 es nulo. Del mismo modo, REGRAVGX calcula el promedio de su segundo argumento (e2) después de la eliminación nula. Ambas funciones devuelven NULL si se aplican a un conjunto vacío. REGRSLOPE y REGRINTERCEPT Funciones La función REGRSLOPE calcula la pendiente de la línea de regresión ajustada a pares no nulos (e1. E2). La función REGRINTERCEPT calcula la intersección y de la línea de regresión. REGRINTERCEPT devuelve NULL cuando la pendiente o los promedios de regresión son NULL. Función REGRR2 La función REGRR2 calcula el coeficiente de determinación (generalmente llamado R-cuadrado o bondad de ajuste) para la línea de regresión. REGRR2 devuelve valores entre 0 y 1 cuando se define la línea de regresión (la pendiente de la línea no es nula), y devuelve NULL de otra manera. Cuanto más cercano es el valor 1, mejor la línea de regresión se ajusta a los datos. REGRSXX, REGRSYY y REGRSXY Funciones REGRSXX. REGRSYY y REGRSXY funciones se utilizan en el cálculo de diversas estadísticas de diagnóstico para el análisis de regresión. Después de eliminar (e1. E2) pares donde cualquiera de e1 o e2 es nulo, estas funciones hacen los siguientes cálculos: Regresión lineal Estadísticas Ejemplos Algunas estadísticas de diagnóstico comunes que acompañan al análisis de regresión lineal se dan en la Tabla 21-2, Expresiones. Tenga en cuenta que esto libera nuevas funciones le permiten calcular todas estas. Tabla 21-2 Estadísticas de diagnóstico comunes y sus expresiones Cálculo de regresión lineal de muestra 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. Conjuntos de artículos frecuentes En lugar de contar cuántas veces ocurre un evento determinado (por ejemplo, cuán a menudo alguien ha comprado leche en el supermercado), los conjuntos de artículos frecuentes proporcionan un mecanismo para contar la frecuencia con la que ocurren varios eventos juntos (por ejemplo, Y cereales juntos en el supermercado). The input to the frequent-itemsets operation is a set of data that represents collections of items (itemsets). Some examples of itemsets could be all of the products that a given customer purchased in a single trip to the grocery store (commonly called a market basket), the web-pages that a user accessed in a single session, or the financial services that a given customer utilizes. The notion of a frequent itemset is to find those itemsets that occur most often. If you apply the frequent-itemset operator to a grocery stores point-of-sale data, you might, for example, discover that milk and bananas are the most commonly bought pair of items. Frequent itemsets have thus been used in business intelligence environments for many years, with the most common one being for market basket analysis in the retail industry. Frequent itemsets are integrated with the database, operating on top of relational tables and accessed through SQL. This integration provides a couple of key benefits: Applications that previously relied on frequent itemset operations now benefit from significantly improved performance as well as simpler implementation. SQL-based applications that did not previously use frequent itemsets can now be easily extended to take advantage of this functionality. Frequent itemsets analysis is performed with the PL/SQL package DBMSFREQUENTITEMSETS. See PL/SQL Packages and Types Reference for more information. Other Statistical Functions Oracle introduces a set of SQL statistical functions and a statistics package, DBMSSTATFUNCS. This section lists some of the new functions along with basic syntax. See PL/SQL Packages and Types Reference for detailed information about the DBMSSTATFUNCS package and Oracle Database SQL Reference for syntax and semantics. Descriptive Statistics You can calculate the following descriptive statistics: Median of a Data Set Mode of a Data Set You can calculate the following parametric statistics: Spearmans rho Coefficient Kendalls tau-b Coefficient In addition to the functions, this release has a new PL/SQL package, DBMSSTATFUNCS. It contains the descriptive statistical function SUMMARY along with functions to support distribution fitting. The SUMMARY function summarizes a numerical column of a table with a variety of descriptive statistics. The five distribution fitting functions support normal, uniform, Weibull, Poisson, and exponential distributions. WIDTHBUCKET Function For a given expression, the WIDTHBUCKET function returns the bucket number that the result of this expression will be assigned after it is evaluated. You can generate equiwidth histograms with this function. Equiwidth histograms divide data sets into buckets whose interval size (highest value to lowest value) is equal. The number of rows held by each bucket will vary. A related function, NTILE. creates equiheight buckets. Equiwidth histograms can be generated only for numeric, date or datetime types. So the first three parameters should be all numeric expressions or all date expressions. Other types of expressions are not allowed. If the first parameter is NULL. the result is NULL. If the second or the third parameter is NULL. an error message is returned, as a NULL value cannot denote any end point (or any point) for a range in a date or numeric value dimension. The last parameter (number of buckets) should be a numeric expression that evaluates to a positive integer value 0, NULL. or a negative value will result in an error. Buckets are numbered from 0 to ( n 1). Bucket 0 holds the count of values less than the minimum. Bucket( n 1) holds the count of values greater than or equal to the maximum specified value. WIDTHBUCKET Syntax The WIDTHBUCKET takes four expressions as parameters. The first parameter is the expression that the equiwidth histogram is for. The second and third parameters are expressions that denote the end points of the acceptable range for the first parameter. The fourth parameter denotes the number of buckets. Consider the following data from table customers. that shows the credit limits of 17 customers. This data is gathered in the query shown in Example 21-19 . In the table customers. the column custcreditlimit contains values between 1500 and 15000, and we can assign the values to four equiwidth buckets, numbered from 1 to 4, by using WIDTHBUCKET (custcreditlimit, 0, 20000, 4). Ideally each bucket is a closed-open interval of the real number line, for example, bucket number 2 is assigned to scores between 5000.0000 and 9999.9999. sometimes denoted 5000, 10000) to indicate that 5,000 is included in the interval and 10,000 is excluded. To accommodate values outside the range 0, 20,000), values less than 0 are assigned to a designated underflow bucket which is numbered 0, and values greater than or equal to 20,000 are assigned to a designated overflow bucket which is numbered 5 (num buckets 1 in general). See Figure 21-3 for a graphical illustration of how the buckets are assigned. You can specify the bounds in the reverse order, for example, WIDTHBUCKET ( custcreditlimit. 20000. 0. 4 ). When the bounds are reversed, the buckets will be open-closed intervals. In this example, bucket number 1 is ( 15000,20000 , bucket number 2 is ( 10000,15000 , and bucket number 4, is ( 0 ,5000. The overflow bucket will be numbered 0 ( 20000. infinity ), and the underflow bucket will be numbered 5 (- infinity. 0 . It is an error if the bucket count parameter is 0 or negative. The followin g query shows the bucket numbers for the credit limits in the customers table for both cases where the boundaries are specified in regular or reverse order. We use a range of 0 to 20,000. User-Defined Aggregate Functions Oracle offers a facility for creating your own functions, called user-defined aggregate functions. These functions are written in programming languages such as PL/SQL, Java, and C, and can be used as analytic functions or aggregates in materialized views. See Oracle Data Cartridge Developers Guide for further information regarding syntax and restrictions. The advantages of these functions are: Highly complex functions can be programmed using a fully procedural language. Higher scalability than other techniques when user-defined functions are programmed for parallel processing. Object datatypes can be processed. As a simple example of a user-defined aggregate function, consider the skew statistic. This calculation measures if a data set has a lopsided distribution about its mean. It will tell you if one tail of the distribution is significantly larger than the other. If you created a user-defined aggregate called udskew and applied it to the credit limit data in the prior example, the SQL statement and results might look like this: Before building user-defined aggregate functions, you should consider if your needs can be met in regular SQL. Many complex calculations are possible directly in SQL, particularly by using the CASE expression. Staying with regular SQL will enable simpler development, and many query operations are already well-parallelized in SQL. Even the earlier example, the skew statistic, can be created using standard, albeit lengthy, SQL. CASE Expressions Oracle now supports simple and searched CASE statements. CASE statements are similar in purpose to the DECODE statement, but they offer more flexibility and logical power. They are also easier to read than traditional DECODE statements, and offer better performance as well. They are commonly used when breaking categories into buckets like age (for example, 20-29, 30-39, and so on). The syntax for simple statements is: The syntax for searched statements is: You can specify only 255 arguments and each WHEN. THEN pair counts as two arguments. For a workaround to this limit, see Oracle Database SQL Reference . Suppose you wanted to find the average salary of all employees in the company. If an employees salary is less than 2000, you want the query to use 2000 instead. Without a CASE statement, you would have to write this query as follows, In this, foo is a function that returns its input if the input is greater than 2000, and returns 2000 otherwise. The query has performance implications because it needs to invoke a function for each row. Writing custom functions can also add to the development load. Using CASE expressions in the database without PL/SQL, this query can be rewritten as: Using a CASE expression lets you avoid developing custom functions and can also perform faster. Creating Histograms With User-Defined Buckets You can use the CASE statement when you want to obtain histograms with user-defined buckets (both in number of buckets and width of each bucket). The following are two examples of histograms created with CASE statements. In the first example, the histogram totals are shown in multiple columns and a single row is returned. In the second example, the histogram is shown with a label column and a single column for totals, and multiple rows are returned. Example 21-21 Histogram Example 1 Example 21-22 Histogram Example 2 Data Densification for Reporting Data is normally stored in sparse form. That is, if no value exists for a given combination of dimension values, no row exists in the fact table. However, you may want to view the data in dense form, with rows for all combination of dimension values displayed even when no fact data exist for them. For example, if a product did not sell during a particular time period, you may still want to see the product for that time period with zero sales value next to it. Moreover, time series calculations can be performed most easily when data is dense along the time dimension. This is because dense data will fill a consistent number of rows for each period, which in turn makes it simple to use the analytic windowing functions with physical offsets. Data densification is the process of converting spare data into dense form. To overcome the problem of sparsity, you can use a partitioned outer join to fill the gaps in a time series or any other dimension. Such a join extends the conventional outer join syntax by applying the outer join to each logical partition defined in a query. Oracle logically partitions the rows in your query based on the expression you specify in the PARTITION BY clause. The result of a partitioned outer join is a UNION of the outer joins of each of the partitions in the logically partitioned table with the table on the other side of the join. Note that you can use this type of join to fill the gaps in any dimension, not just the time dimension. Most of the examples here focus on the time dimension because it is the dimension most frequently used as a basis for comparisons. Partition Join Syntax The syntax for partitioned outer join extends the ANSI SQL JOIN clause with the phrase PARTITION BY followed by an expression list. The expressions in the list specify the group to which the outer join is applied. The following are the two forms of syntax normally used for partitioned outer join: Note that FULL OUTER JOIN is not supported with a partitioned outer join. Sample of Sparse Data A typi cal situation with a sparse dimension is shown in the following example, which computes the weekly sales and year-to-date sales for the product Bounce for weeks 20-30 in 2000 and 2001: In this example, we would expect 22 rows of data (11 weeks each from 2 years) if the data were dense. However we get only 18 rows because weeks 25 and 26 are missing in 2000, and weeks 26 and 28 in 2001. Filling Gaps in Data We can take the sparse data of the preceding query and do a partitioned outer join with a dense set of time data. In the following query, we alias our original query as v and we select data from the times table, which we alias as t. Here we retrieve 22 rows because there are no gaps in the series. The four added rows each have 0 as their Sales value set to 0 by using the NVL function. Note that in this query, a WHERE condition was placed for weeks between 20 and 30 in the inline view for the time dimension. This was introduced to keep the result set small. Filling Gaps in Two Dimensions N-dimensional data is typically displayed as a dense 2-dimensional cross tab of (n - 2) page dimensions. This requires that all dimension values for the two dimensions appearing in the cross tab be filled in. The following is another example where the partitioned outer join capability can be used for filling the gaps on two dimensions: In this query, the WITH sub-query factoring clause v1. summarizes sales data at the product, country, and year level. This result is sparse but users may want to see all the country, year combinations for each product. To achieve this, we take each partition of v1 based on product values and outer join it on the country dimension first. This will give us all values of country for each product. We then take that result and partition it on product and country values and then outer join it on time dimension. This will give us all time values for each product and country combination. Filling Gaps in an Inventory Table An inventory table typically tracks quantity of units available for various products. This table is sparse: it only stores a row for a product when there is an event. For a sales table, the event is a sale, and for the inventory table, the event is a change in quantity available for a product. For example, consider the following inventory table: The inventory table now has the following rows: For reporting purposes, users may want to see this inventory data differently. For example, they may want to see all values of time for each product. This can be accomplished using partitioned outer join. In addition, for the newly inserted rows of missing time periods, users may want to see the values for quantity of units column to be carried over from the most recent existing time period. The latter can be accomplished using analytic window function LASTVALUE value. Here is the query and the desired output: The inner query computes a partitioned outer join on time within each product. The inner query densifies the data on the time dimension (meaning the time dimension will now have a row for each day of the week). However, the measure column quantity will have nulls for the newly added rows (see the output in the column quantity in the following results. The outer query uses the analytic function LASTVALUE. Applying this function partitions the data by product and orders the data on the time dimension column ( timeid ). For each row, the function finds the last non-null value in the window due to the option IGNORE NULLS. which you can use with both LASTVALUE and FIRSTVALUE. We see the desired output in the column repeatedquantity in the following output: Computing Data Values to Fill Gaps Examples in previous section illustrate how to use partitioned outer join to fill gaps in one or more dimensions. However, the result sets produced by partitioned outer join have null values for columns that are not included in the PARTITION BY list. Typically, these are measure columns. Users can make use of analytic SQL functions to replace those null values with a non-null value. For example, the following query computes monthly totals for products 64MB Memory card and DVD-R Discs (product IDs 122 and 136) for the year 2000. It uses partitioned outer join to densify data for all months. For the missing months, it then uses the analytic SQL function AVG to compute the sales and units to be the average of the months when the product was sold. If working in SQLPlus, the following two commands will wrap the column headings for greater readability of results: Time Series Calculations on Densified Data Densificatio n is not just for reporting purpose. It also enables certain types of calculations, especially, time series calculations. Time series calculations are easier when data is dense along the time dimension. Dense data has a consistent number of rows for each time periods which in turn make it simple to use analytic window functions with physical offsets. To illustrate, lets first take the example on Filling Gaps in Data. and lets add an analytic function to that query. In the following enhanced version, we calculate weekly year-to-date sales alongside the weekly sales. The NULL values that the partitioned outer join inserts in making the time series dense are handled in the usual way: the SUM function treats them as 0s. Period-to-Period Comparison for One Time Level: Example How do we use this feature to compare values across time periods Specifically, how do we calculate a year-over-year sales comparison at the week level The following query returns on the same row, for each product, the year-to-date sales for each week of 2001 with that of 2000. Note that in this example we start with a WITH clause. This improves readability of the query and lets us focus on the partitioned outer join. If working in SQLPlus, the following command will wrap the column headings for greater readability of results: In the FROM clause of the in-line view densesales. we use a partitioned outer join of aggregate view v and time view t to fill gaps in the sales data along the time dimension. The output of the partitioned outer join is then processed by the analytic function SUM. OVER to compute the weekly year-to-date sales (the weeklyytdsales column). Thus, the view densesales computes the year-to-date sales data for each week, including those missing in the aggregate view s. The in-line view yearoveryearsales then computes the year ago weekly year-to-date sales using the LAG function. The LAG function labeled weeklyytdsalesprioryear specifies a PARTITION BY clause that pairs rows for the same week of years 2000 and 2001 into a single partition. We then pass an offset of 1 to the LAG function to get the weekly year to date sales for the prior year. The outermost query block selects data from yearoveryearsales with the condition yr 2001, and thus the query returns, for each product, its weekly year-to-date sales in the specified weeks of years 2001 and 2000. Period-to-Period Comparison for Multiple Time Levels: Example While the prior example shows us a way to create comparisons for a single time level, it would be even more useful to handle multiple time levels in a single query. For example, we could compare sales versus the prior period at the year, quarter, month and day levels. How can we create a query which performs a year-over-year comparison of year-to-date sales for all levels of our time hierarchy We will take several steps to perform this task. The goal is a single query with comparisons at the day, week, month, quarter, and year level. The steps are as follows: We will create a view called cubeprodtime. which holds a hierarchical cube of sales aggregated across times and products . Then we will create a view of the time dimension to use as an edge of the cube. The time edge, which holds a complete set of dates, will be partitioned outer joined to the sparse data in the view cubeprodtime . Finally, for maximum performance, we will create a materialized view, mvprodtime. built using the same definition as cubeprodtime . For more information regarding hierarchical cubes, see Chapter 20, SQL for Aggregation in Data Warehouses. The materialized view is defined using the following statement: Step 1 Create the hierarchical cube view The materialized view shown in the following may already exist in your system if not, create it now. If you must generate it, please note that we limit the query to just two products to keep processing time short: Because this view is limited to two products, it returns just over 2200 rows. Note that the column HierarchicalTime contains string representations of time from all levels of the time hierarchy. The CASE expression used for the HierarchicalTime column appends a marker (0, 1. ) to each date string to denote the time level of the value. A 0 represents the year level, 1 is quarters, 2 is months, and 3 is day. Note that the GROUP BY clause is a concatenated ROLLUP which specifies the rollup hierarchy for the time and product dimensions. The GROUP BY clause is what determines the hierarchical cube contents. Step 2 Create the view edgetime, which is a complete set of date values edgetime is the source for filling time gaps in the hierarchical cube using a partitioned outer join. The column HierarchicalTime in edgetime will be used in a partitioned join with the HierarchicalTime column in the view cubeprodtime. The following statement defines edgetime : Step 3 Create the materialized view mvprodtime to support faster performance The materialized view definition is a duplicate of the view cubeprodtime defined earlier. Because it is a duplicate query, references to cubeprodtime will be rewritten to use the mvprodtime materialized view. The following materialized may already exist in your system if not, create it now. If you must generate it, please note that we limit the query to just two products to keep processing time short. Step 4 Create the comparison query We have now set the stage for our comparison query. We can obtain period-to-period comparison calculations at all time levels. It requires applying analytic functions to a hierarchical cube with dense data along the time dimension. Some of the calculations we can achieve for each time level are: Sum of sales for prior period at all levels of time. Variance in sales over prior period. Sum of sales in the same period a year ago at all levels of time. Variance in sales over the same period last year. The following example performs all four of these calculations. It uses a partitioned outer join of the views cubeprodtime and edgetime to create an in-line view of dense data called densecubeprodtime. The query then uses the LAG function in the same way as the prior single-level example. 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: 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.
No comments:
Post a Comment