Objetivos:
NOTA: Usamos como ejemplo la Base de Datos: ORCL, la cual viene por defecto en cualquier versión de ORACLE.
____________________________________________________________________________________
Las funciones analíticas calculan un valor agregado basado en un grupo de filas. Una función analítica trabaja sobre varias filas y devuelve el resultado en la fila actual. Estas se diferencian de las funciones de grupo/agregadas en que las analíticas devuelven múltiples filas para cada grupo mientras que las grupales o agregadas retornan un valor por grupo.[1]
En Síntesis:
Una función agregada o grupal, agrega datos de varias filas en una sola, por lo cual, reduce el número de filas devueltas por la consulta. Por su parte las funciones analíticas funcionan en subconjuntos de filas, pero no reducen el número de filas devueltas por la consulta.
Las funciones analíticas son el último conjunto de operaciones realizadas en una consulta, a excepción de la cláusula ORDER BY, la cual va al final. Todas las combinaciones y todas las cláusulas WHERE, GROUP BY y HAVING se completan antes de procesar las funciones analíticas. Por lo tanto, las funciones analíticas sólo pueden aparecer en la lista de selección o en la cláusula ORDER BY.
Una función agregada o grupal, agrega datos de varias filas en una sola, por lo cual, reduce el número de filas devueltas por la consulta. Por su parte las funciones analíticas funcionan en subconjuntos de filas, pero no reducen el número de filas devueltas por la consulta.
Las funciones analíticas son el último conjunto de operaciones realizadas en una consulta, a excepción de la cláusula ORDER BY, la cual va al final. Todas las combinaciones y todas las cláusulas WHERE, GROUP BY y HAVING se completan antes de procesar las funciones analíticas. Por lo tanto, las funciones analíticas sólo pueden aparecer en la lista de selección o en la cláusula ORDER BY.
Una función analítica podría ser a su vez una función grupal, como es el caso de COUNT y RANK o una función analítica pura como NTILE.
Nota: No es posible anidar funciones analíticas especificando alguna en la cláusula analytic_clause. Sin embargo, puede especificar una función analítica en una subconsulta y usar otra función analítica sobre ella.
Sintaxis analytic_function:
analytic_function([ arguments ])
OVER (analytic_clause)
---
Sintaxis analytic_clause:
[ query_partition_clause ]
[ order_by_clause [ windowing_clause ] ]
---
Sintaxis query_partition_clause:
PARTITION BY
{ value_expr[, value_expr ]...
| ( value_expr[, value_expr ]... )
}
---
Sintaxis order_by_clause:
ORDER [ SIBLINGS ] BY
{ expr | position | c_alias }
[ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
[, { expr | position | c_alias }
[ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
]...
---
Sintaxis windowing_clause:
{ ROWS | RANGE }
{ BETWEEN
{ UNBOUNDED PRECEDING
| CURRENT ROW
| value_expr { PRECEDING | FOLLOWING }
}
AND
{ UNBOUNDED FOLLOWING
| CURRENT ROW
| value_expr { PRECEDING | FOLLOWING }
}
| { UNBOUNDED PRECEDING
| CURRENT ROW
| value_expr PRECEDING
}
}
---
Cláusula OVER:
Sintaxis analytic_function:
analytic_function([ arguments ])
OVER (analytic_clause)
---
Sintaxis analytic_clause:
[ query_partition_clause ]
[ order_by_clause [ windowing_clause ] ]
---
Sintaxis query_partition_clause:
PARTITION BY
{ value_expr[, value_expr ]...
| ( value_expr[, value_expr ]... )
}
---
Sintaxis order_by_clause:
ORDER [ SIBLINGS ] BY
{ expr | position | c_alias }
[ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
[, { expr | position | c_alias }
[ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
]...
---
Sintaxis windowing_clause:
{ ROWS | RANGE }
{ BETWEEN
{ UNBOUNDED PRECEDING
| CURRENT ROW
| value_expr { PRECEDING | FOLLOWING }
}
AND
{ UNBOUNDED FOLLOWING
| CURRENT ROW
| value_expr { PRECEDING | FOLLOWING }
}
| { UNBOUNDED PRECEDING
| CURRENT ROW
| value_expr PRECEDING
}
}
---
Cláusula OVER:
El ámbito de una función analítica se define en una cláusula OVER. OVER es una palabra clave obligatoria para todas las funciones analíticas. Dando () como parámetro, OVER indica el mayor alcance posible.
En términos simples, la cláusula OVER en Oracle especifica la partición o el orden en el que funcionará una función analítica.[2]
A continuación algunas Funciones Analíticas:
FIRST_VALUE: retorna el primer valor en un conjunto ordenado de valores. Si el primer valor del conjunto es nulo, la función devuelve NULL a menos que especifique IGNORE NULLS. Esta configuración es útil para la densificación de datos. Si especifica IGNORE NULLS, FIRST_VALUE devuelve el primer valor no nulo en el conjunto, o NULL si todos los valores son nulos.
LAST_VALUE: Esta función funciona de la misma manera que FIRST_VALUE pero con el último valor de un conjunto ordenado de valores.
RANK: Como función analítica, calcula el rango de cada fila devuelta de una consulta con respecto a las otras filas retornadas.
ROW_NUMBER: Asigna un número único a cada fila a la que se aplica (ya sea cada fila de la partición o cada fila devuelta por la consulta), en la secuencia ordenada de filas especificada en el order_by_clause, comenzando por 1.
NTILE: Divide un conjunto de datos ordenados en la cantidad de cubos(buckets) indicados por el parámetro: expr y asigna el número de cubos apropiado a cada fila. Los cubos están numerados del 1 al valor expr. El valor expr debe ser a una constante positiva para cada partición. Oracle espera un número entero, y si expr es una constante NONINTEGER(no entera), Oracle trunca el valor a un entero. El valor de retorno es NUMBER.
SELECT
department_id,
COUNT(*)
OVER (PARTITION BY department_id) Total_Dept,
job_id,
COUNT(*)
OVER (PARTITION BY job_id) Total_Job
FROM hr.employees;
/*Este ejemplo muestra como usar la función analítica COUNT; En el OUTPUT: cada resultado en la columna total_dept representa la cantidad de registros con el department_id en cuestión, mientras que la columna total_job es la cantidad de registros con la columna job_id en cuestión.*/
---OUTPUT:
last_name,
salary,
commission_pct,
RANK()
OVER (
PARTITION BY department_id
ORDER BY salary DESC, commission_pct DESC
) "Rank"
FROM employees
WHERE department_id = 80;
/*El ejemplo muestra como usar la función analítica RANK; Notar como la columna Rank asigna un rango o nivel a cada registro retornado, dándole prioridad a los registros con los salarios mas altos, notar que si los salarios son iguales se toma en cuenta la columna de la comisión y si esta es también igual, el rango seria el mismo.*/
---OUTPUT:
last_name,
department_id,
salary,
FIRST_VALUE(salary) IGNORE NULLS
OVER (
PARTITION BY department_id ORDER BY salary ASC
) AS lowest_in_dept
FROM hr.employees;
/*Este ejemplo muestra como usar la función analítica FIRST_VALUE; Notar como la columna lowest_in_dept siempre muestra el primer salario por cada departamento de la lista, como la función esta ordenada por el salario de forma ascendente, el primer salario sera el mas bajo por cada departamento.*/
---OUTPUT:
last_name,
department_id,
salary,
LAST_VALUE(salary) IGNORE NULLS
OVER (
PARTITION BY department_id ORDER BY salary ASC
) AS highest_in_dept
FROM hr.employees;
/*Contrario a la función FIRST_VALUE, LAST_VALUE muestra el último valor encontrado de la lista en cuestión, por esta razón el ejemplo se presta para mostrar el salario mas alto por cada departamento de la lista, ya que el mismo equivale al último valor de la lista ordenada por el salario en forma ascendente.*/
---OUTPUT:
department_id,
last_name,
employee_id,
ROW_NUMBER()
OVER (
PARTITION BY department_id ORDER BY employee_id ASC
) AS emp_id
FROM hr.employees;
/*El ejemplo muestra como usar la función analítica ROW_NUMBER; Notar que la columna emp_id muestra el orden o posición que representa el empleado en su departamento (ordenado por numero de empleado ascendente).*/
---OUTPUT:
last_name,
salary,
NTILE(4)
OVER (ORDER BY salary DESC) AS quartile
FROM employees
WHERE department_id = 100;
/*El ejemplo muestra como usar la función analítica NTILE; La columna quartile equivale a la cantidad de cubos(buckets) asignada a cada registro ordenado por el salario en forma descendente dividido por 4.*/
---OUTPUT:
