Funciones Analíticas en Oracle

Por:
Ing.Daniel Mauricio Díaz Forero

Oracle ha mejorado las capacidades de procesamiento analítico introduciendo una nueva familia de funciones SQL analíticas. Estas funciones analíticas le permites a usted calcular:

•  Funciones de Rangos y Percentíles

•  Movimiento de ventanas de cálculos

•  Análisis lag/lead

•  Análisis Primero/Ultimo

•  Estadísticas y Regresiones Lineales

Funciones Anal íticas y Uso :

Orden de Procesamiento

El procesamiento de las consultas utilizando funciones anal íticas se lleva a cabo en tres partes :

•  Todos los JOINS, WHERE, GROUP BY y HAVING son realizadas.

•  El resultado es hecho disponible a las funciones analíticas y es donde todos los cálculos toman lugar.

•  Si la consulta tiene una cláusula ORDER BY, el ordenamiento es ejecutado para permitir el ordenamiento de los resultados.

Particiones del conjunto de resultados

Las funciones analíticas permiten a los usuarios dividir los conjuntos de resultados de una consulta en grupos de datos llamados particiones. El termino partición utilizado en las consultas analíticas no tiene nada que ver con la característica del particionamiento de tablas en Oracle. Las particiones son creadas después de que se definan los grupos con la cláusula GROUP BY, así que estos están disponibles para cualquier resultado agregado como Sumas, y Promedios. Las divisiones de las particiones pueden estar basadas sobre las columnas o expresiones deseadas. El resultado de una consulta puede ser particionado en solo una partición manteniendo todas las filas.

Ventana

Para cada fila en una partición, usted puede definir una ventana deslizante de datos. La ventana determina el rango de filas utilizado para realizar los cálculos para la fila actual. Los tama ños de las ventanas pueden estar basados en cualquier grupo físico de números, o cualquier intervalo lógico como el tiempo. La ventana tiene una fila de partida y una fila de fin dependiendo de esta definición la ventana puede tener uno o varios finales.

Fila actual

Cada cálculo realizado con una función analítica esta basado en la fila actual de una partición.

Funciones de Rango

Una función de rango calcula el rango de un registro comparado a otros registros en un conjunto de datos sobre los valores de un conjunto de medidas. Los tipos de funciones de ranking son:

•  Funciones de Rango y Rango Denso

•  Funciones (Cume – Dist)

•  Funciones de Rango de Porcentaje

•  Funciones de NTILE

•  Funciones del numero de filas (Row Number)

Funciones de Rango y Rango Denso

Las funciones de rango y rango denso le permiten a usted clasificar ítems dentro de un grupo, por ejemplo, encontrando los primeros tres productos vendidos en un área durante el último a ño. Existen dos funciones que realizan el ranking como se muestra en la siguiente sintaxis :

•  RANK () OVER ([ query_partition_clause] ORDER BY clause])

•  DENSE_RANK () OVER ([query_partition_clause] ORDER BY clause])

La diferencia entre RANK y DENSE RANK es que en el DENSE RANK no deja brechas entre el rango de secuencias cuando hay empates. Es decir, si usted esta clasificando un conjunto de datos usando DENSE_RANK y tiene tres elementos empatados en segundo lugar, usted podría decir que los tres estuvieron en segundo lugar y que la siguiente persona estará en el tercer lugar. La función RANK también puede permitir a tres personas en segundo lugar pero la siguiente persona que llegue estará en el quinto.

Puntos clave sobre los rangos son:

•  El orden ascendente es el ordenamiento por defecto

•  Las expresiones en la cl á usula PARTITION BY dividen el conjunto de resultados en grupos sobre los cuales opera la función RANK

•  Si la cláusula PARTITION BY no esta definida en la sentencia los rangos son computados sobre el conjunto entero de datos (el conjunto entero de datos se toma como un único rango).

•  La cláusula ORDER BY especifica las medidas sobre las cuales se esta realizando la clasificación, y el orden en que deben ordenarse las columnas en cada grupo.

•  La cláusula NULLS FIRST | NULLS LAST indica la posición de los valores nulos dentro del rango de datos comparados con los valores no nulos.

EJEMPLO:

Listado de todos los empleados realizando una clasificaci ón según el salario y el departamento en el que labora cada empleado.

SELECT first_name , last_name , salary , department_id , DENSE_RANK () OVER (PARTITION BY department_id ORDER BY salary DESC) "Ranking"

FROM employees

Listado de los empleados del departamento 90 realizando una clasificación seg ún el salario y el departamento en el que labora cada empleado (Utilizando RANK).

SELECT first_name , last_name , salary , department_id , RANK () OVER (PARTITION BY department_id ORDER BY salary DESC) "Ranking"

FROM employees

WHERE department_id = 90 ;

Listado de los empleados del departamento 90 realizando una clasificación seg ún el salario y el departamento en el que labora cada empleado (Utilizando DENSE_RANK).

SELECT first_name , last_name , salary , department_id , DENSE_RANK () OVER (PARTITION BY department_id ORDER BY salary DESC) "Ranking"

FROM employees

WHERE department_id = 90 ;

Funciones (Cume-Dist)

La funci ó n CUME_DIST definida por algunos libros de estadística como el percentil inverso, calcula la posición de un valor específico en relación a un conjunto de valores. El orden puede ser ascendente o descendente. El ordenamiento por defecto es ascendente. El rango de valores para CUME_DIST va desde cero (0) a uno (1). Para calcular el percentil inverso (CUME_DIST) de un valor x en un conjunto S de tama ño N, se utiliza la fórmula :

CUME_DIST (x) = numero de valores en S antes de x (incluyendo a x y en el orden específico) / N

La sintaxis en Oracle es:

CUME_DIST () OVER ([Query partition_clause ] ORDER BY clause)

EJEMPLO:

C álculo del percentil inverso para cada todo el grupo de empleados

SELECT first_name , last_name , salary , department_id , CUME_DIST () OVER (ORDER BY salary DESC) "Rank"

FROM employees

Cálculo del percentil inverso de los empleados particionando por el departamento al que pertenece el empleado.

SELECT first_name , last_name , salary , department_id , CUME_DIST () OVER (PARTITION BY department_id ORDER BY salary DESC) "Ranking"

FROM employees

Funciones de rango de porcentaje (Percent-Rank)

PERCENT_RANK es similar a CUME_DIST, pero este usa rangos de valores a diferencia que el conteo de filas en este numerador. ???

Sin embargo, este retorna el rango de porcentaje de un valor relacionado a un grupo de valores. La función esta disponible en muchas hojas de c álculo populares. El valor PERCENT_RANK de una fila es calculado como :

Rango de la fila en la partici ó n – 1 / numero de filas en la partición – 1

PERCENT_RANK retorna valores en el rango de cero a uno. Las filas con un rango de 1 tendr án un rango de porcentaje de cero (0 ). Su sintaxis es:

PERCENT_RANK OVER ([query_partition_clause] ORDER BY clause)

EJEMPLO:

Calcular el rango de porcentaje de todo el grupo de empleados.

SELECT first_name , last_name , salary , department_id ,

PERCENT_RANK () OVER (ORDER BY salary ) "Percent Rank"

FROM employees ;

Calcular el rango de porcentaje de todo el grupo de empleados particionando por el departamento donde trabaja cada empleado para el departamento con el código 90.

SELECT first_name , last_name , salary , department_id ,

PERCENT_RANK () OVER (PARTITION BY department_id ORDER BY salary ) "Percent Rank"

FROM employees

WHERE department_id = 90 ;

Funciones NTILE (Percent-Rank)

NTILE permite el fácil cálculo de tertiles, cuadriles, deciles y otras estadísticas comunes. Esta función divide una partición ordenada en un número específico de grupos llamados buckets y asigna un número de bucket para cada fila en la partición. NTILE es un cálculo muy útil porque permite a los usuarios dividir un conjunto de datos en cuartos, terceros y otros agrupamientos.

Los buckets son calculados de forma que cada bucket tiene exactamente el mismo número de filas asignadas a este o por lo menos una fila más que los otros. Por ejemplo si usted tiene 100 registros en una partición y utiliza una función NTILE con cuatro buckets, a 25 filas se les asignara un valor de 1, 25 filas tendrán el valor 2, y así… Estos buckets son referidos como bucket equidimensional.

Si el número de filas en la partición no divide exactamente el número de buckets, entonces el número de filas asignado para cada bucket diferirá en por lo menos uno. La filas extras serán distribuidas una por bucket empezando desde el numero de bucket con el valor mas bajo. Por ejemplos si hay 103 filas en una partición la cual tiene una función NTILE(5), las primeras 21 filas estarán en el primer bucket, las siguientes 21 en el siguiente bucket, las siguientes 21 en el tercer bucket, las siguientes 20 en el cuarto bucket, y al final 20 en el quinto bucket.

La función NTILE tiene la siguiente sintaxis :

NTILE (expr) OVER ([partition_query_clause] ORDER BY clause)

En esta, la N en NTILE(N) puede ser una constante (por ejemplo 5) o una expresión.

Esta función como RANK, y CUME_DIST tiene una cláusula PARTITION BY por cada grupo calculado, una cláusula ORDER BY para especificar las medidas y su forma de ordenamiento, y las cláusulas NULLS FIRST | LAST FIRST para el tratamiento de valores nulos.

EJEMPLO:

Dividir el grupo de empleados en tertiles;

SELECT first_name , last_name , salary , department_id , NTILE ( 3 ) OVER (PARTITION BY department_id ORDER BY salary ) "Rank"

FROM employees

ORDER BY "Rank"

Dividir el grupo de empleados en cuadriles;

SELECT first_name , last_name , salary , department_id , NTILE ( 4 ) OVER (PARTITION BY department_id ORDER BY salary ) "Rank"

FROM employees

ORDER BY "Rank"

Dividir el grupo de empleados en deshiles;

SELECT first_name , last_name , salary , department_id , NTILE ( 10 ) OVER (PARTITION BY department_id ORDER BY salary ) "Rank"

FROM employees

ORDER BY "Rank"

Funciones de Numero de Filas (ROW_NUMBER)

Las funciones ROW_NUMBER asignan un numero único (secuencialmente, iniciando desde 1, como se defina en la cláusula ORDER BY) para cada fila dentro de la partición. Esta tiene la siguiente sentencia.

ROW_NUMBER ( ) OVER ( [query_partition_clause] order_by_clause )

EJEMPLO:

Calcular el ROW_NUMBER para todo el grupo de empleados particionando por el departamento donde labora cada empleado.

SELECT first_name , ROW_NUMBER () OVER (PARTITION BY department_id ORDER BY salary DESC)

FROM employees

Calcular el rango de porcentaje de todo el grupo de empleados particionando por el departamento donde laboran los empleados y para el departamento 90.

SELECT first_name , ROW_NUMBER () OVER (PARTITION BY department_id ORDER BY salary DESC)

FROM employees

WHERE department_id = 90 ;

Funciones de Ventana Agregada

Las funciones de ventanas pueden ser utilizadas para la realización de cálculos, movimiento y centrado de agregados. Ellas retornan un valor para cada fila en la tabla el cual depende de otras filas en la correspondiente ventana. Estas funciones incluyen sumas móviles, promedios móviles, mínimos y máximos móviles, sumas acumulativas y también funciones estadísticas. Estas pueden ser usadas únicamente en las cláusulas SELECT y ORDER BY de una consulta. Otras dos funciones también están disponibles FIRST_VALUE, el cual retorna el primer valor en la ventana, y LAST_VALUE el cual retorna el útimo valor en la ventana. Estas funciones facilitan el acceso a más de una fila en una tabla sin hacer auto-joins. Las funciones de ventana pueden ser de tipo ROW o tipo RANGE. La sintaxis es la siguiente:

ROW

Function( ) OVER (PARTITION BY <expr1> ORDER BY <expr2,..> ROWS BETWEEN <start_expr> AND <end_expr>)

ó

Function( ) OVER (PARTITION BY <expr1> ORDER BY <expr2,..> ROWS [<start_expr> PRECEDING or UNBOUNDED PRECEDING]

RANGE

Function( ) OVER (PARTITION BY <expr1> ORDER BY <expr2> RANGE BETWEEN <start_expr> AND <end_expr>)

Ó

Function( ) OVER (PARTITION BY <expr1> ORDER BY <expr2> RANGE [<start_expr> PRECEDING or UNBOUNDED PRECEDING]

EJEMPLO:

Contar los empleados que ganan m á s del salario promedio, y menos del salario promedio para cada deparamento

SELECT

COUNT(*) OVER (PARTITION BY department_id ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND AVG(salary) PRECEDING) Menos

COUNT(*) OVER (PARTITION BY department_id ORDER BY salary RANGE BETWEEN

AVG(salary) FOLLOWING AND UNBOUNDED FOLLOWING) Mas

FROM employees

ORDER BY department_id, salary;