Funciones analíticas

Las funciones analíticas calculan un valor agregado basado en un grupo de filas. Se diferencian de las funciones de agregado en que devuelven varias filas para cada grupo. El grupo de filas se denomina ventana y está definido por el analytic_clause. Para cada fila, se define una ventana deslizante de filas. La 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 en un intervalo lógico, como el tiempo.

Las funciones analíticas son el último conjunto de operaciones realizadas en una consulta, excepto la cláusula final ORDERBY. Todas las combinaciones y de todos los WHEREGROUPBY y HAVING cláusulas sean completados antes de las funciones analíticas son procesados. Por lo tanto, las funciones analíticas solo pueden aparecer en la lista de selección o en la cláusula ORDERBY.

Las funciones analíticas se utilizan comúnmente para calcular agregados acumulativos, móviles, centrados y de informes.

analytic_function::=

Descripción de la ilustración » función analítica.gif»

analytic_clause::=

Descripción de la ilustración » analytic_clause.gif»

query_partition_clause::=

Descripción de la ilustración » query_partition_clause.gif»

order_by_clause::=

Descripción de la ilustración » order_by_clause.gif»

windowing_clause ::=

Descripción de la ilustración » windowing_clause.gif «

La semántica de esta sintaxis se discute en las secciones que siguen.

función analítica

Especifique el nombre de una función analítica (consulte la lista de funciones analíticas después de esta discusión de semántica).

argumentos

Las funciones analíticas toman de 0 a 3 argumentos. Los argumentos pueden ser cualquier tipo de datos numéricos o cualquier tipo de datos no numéricos que se pueda convertir implícitamente a un tipo de datos numéricos. Oracle determina el argumento con la precedencia numérica más alta y convierte implícitamente los argumentos restantes a ese tipo de datos. El tipo devuelto también es ese tipo de datos, a menos que se indique lo contrario para una función individual.

analytic_clause

Use OVERanalytic_clause to indicate that the function operates on a query result set. This clause is computed after the FROMWHEREGROUPBY, and HAVING clauses. Puede especificar funciones analíticas con esta cláusula en la lista de selección o ORDERBY cláusula. Para filtrar los resultados de una consulta basada en una función analítica, anide estas funciones dentro de la consulta principal y, a continuación, filtre los resultados de la subconsulta anidada.

Notas sobre la cláusula analítica: Las siguientes notas se aplican a analytic_clause:

• No puede anidar funciones analíticas especificando ninguna función analítica en ninguna parte de analytic_clause. Sin embargo, puede especificar una función analítica en una subconsulta y calcular otra función analítica sobre ella.

• Puede especificar OVERanalytic_clause con funciones analíticas definidas por el usuario, así como funciones analíticas integradas. Consulte CREAR FUNCIÓN.

query_partition_clause

Use la cláusulaPARTITIONBY para dividir el conjunto de resultados de la consulta en grupos basados en uno o másvalue_expr. Si omite esta cláusula, la función tratará todas las filas del conjunto de resultados de la consulta como un solo grupo.

Para usar el query_partition_clause en una función analítica, utilice la rama superior de la sintaxis (sin paréntesis). Para usar esta cláusula en una consulta de modelo (en el model_column_clauses) o en una combinación externa con particiones (en el outer_join_clause), utilice la rama inferior de la sintaxis (con paréntesis).

Puede especificar varias funciones analíticas en la misma consulta, cada una con las teclas iguales o diferentes PARTITIONBY.

Si los objetos consultados tienen el atributo parallel, y si especifica una función analítica con el query_partition_clause, los cálculos de la función también se paralelizan.

Los valores válidos de value_expr son constantes, columnas, funciones no analíticas, expresiones de funciones o expresiones que involucran cualquiera de estas.

order_by_clause

Use order_by_clause para especificar cómo se ordenan los datos dentro de una partición. Para todas las funciones analíticas, puede ordenar los valores de una partición en varias teclas, cada una definida por un value_expr y cada una calificada por una secuencia de órdenes.

Dentro de cada función, puede especificar varias expresiones de orden. Hacerlo es especialmente útil cuando se utilizan funciones que clasifican valores, porque la segunda expresión puede resolver vínculos entre valores idénticos para la primera expresión.

Cuandoorder_by_clause da como resultado valores idénticos para varias filas, la función se comporta de la siguiente manera:

• CUME_DISTDENSE_RANKNTILEPERCENT_RANK y RANK devuelven el mismo resultado para cada una de las filas.

• ROW_NUMBER asigna a cada fila un valor distinto, incluso si hay un lazo basado en el order_by_clause. El valor se basa en el orden en el que se procesa la fila, que puede no ser determinado si ORDERBY no garantiza un pedido total.

• Para todas las demás funciones analíticas, el resultado depende de la especificación de la ventana. Si especifica una ventana lógica con la palabra clave RANGE, la función devuelve el mismo resultado para cada una de las filas. Si especifica una ventana física con la palabra clave ROWS, el resultado no es determinista.

Restricciones en la cláusula ORDER BY Las siguientes restricciones se aplican a la cláusula ORDERBY :

• Cuando se utiliza en una función analítica, el order_by_clause debe tomar una expresión (expr). La palabra clave SIBLINGS no es válida (solo es relevante en consultas jerárquicas). Posición (position) y los alias de columna (c_alias) también son válidas. De lo contrario, este order_by_clause es el mismo que se usa para ordenar la consulta o subconsulta general.

• An analytic function that uses the RANGE keyword can use multiple sort keys in its ORDERBY clause if it specifies any of the following windows:

  • RANGEBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW. The short form of this is RANGEUNBOUNDEDPRECEDING.

  • RANGEBETWEENCURRENTROWANDUNBOUNDEDFOLLOWING

  • RANGEBETWEENCURRENTROWANDCURRENTROW

  • RANGEBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING

  Los límites de ventana que no sean estos cuatro pueden tener solo una clave de clasificación en la cláusula ORDERBY de la función analítica. Esta restricción no se aplica a los límites de ventana especificados por la palabra clave ROW.

ASC / DESC Especifique la secuencia de orden (ascendente o descendente). ASC es el valor predeterminado.

NULLS FIRST / NULLS LAST Especifica si las filas devueltas que contienen nulls deben aparecer primero o último en la secuencia de orden.

NULLSLAST es el valor predeterminado para el orden ascendente, y NULLSFIRST es el valor predeterminado para el orden descendente.

Las funciones analíticas siempre operan en filas en el orden especificado en el order_by_clause de la función. Sin embargo, el order_by_clause de la función no garantiza el orden del resultado. Utilice el order_by_clause de la consulta para garantizar el orden del resultado final.

windowing_clause

Algunas funciones analíticas permiten el windowing_clause. En la lista de funciones analíticas al final de esta sección, las funciones que permiten el windowing_clause van seguidas de un asterisco (*).

FILAS / RANGO Estas palabras clave definen para cada fila una ventana (un conjunto físico o lógico de filas) que se utiliza para calcular el resultado de la función. La función se aplica a todas las filas de la ventana. La ventana se mueve a través del conjunto de resultados de la consulta o partición de arriba a abajo.

• ROWS especifica la ventana en unidades físicas (filas).

• RANGE especifica la ventana como una lógica de desplazamiento.

Usted puede especificar esta cláusula a menos que haya especificado el order_by_clause. Algunos límites de ventana definidos por la cláusula RANGE le permiten especificar solo una expresión en order_by_clause. Consulte «Restricciones a la cláusula ORDER BY».

El valor devuelto por una función analítica con un desplazamiento lógico es siempre determinista. Sin embargo, el valor devuelto por una función analítica con un desplazamiento físico puede producir resultados no deterministas a menos que la expresión de orden resulte en un orden único. Es posible que tenga que especificar varias columnas en order_by_clause para lograr este orden único.

ENTRE … Y utilice el BETWEEN… cláusula AND para especificar un punto de inicio y un punto final para la ventana. La primera expresión (antes de AND) define el punto de inicio y la segunda expresión (después de AND) define el punto final.

Si omite BETWEEN y especifica solo un punto final, Oracle lo considera el punto de inicio y el punto final por defecto es la fila actual.

PRECEDENTE ILIMITADO Especifique UNBOUNDEDPRECEDING para indicar que la ventana comienza en la primera fila de la partición. Esta es la especificación del punto de inicio y no se puede usar como especificación del punto final.

sin límites SIGUIENTES Especificar UNBOUNDEDFOLLOWING para indicar que la ventana termina en la última fila de la partición. Esta es la especificación del punto final y no se puede usar como especificación del punto de inicio.

FILA ACTUAL Como un punto de inicio CURRENTROW especifica que la ventana comienza en la fila o el valor actual (dependiendo de si se ha especificado ROW o RANGE, respectivamente). En este caso, el punto final no puede ser value_exprPRECEDING.

Como un punto final CURRENTROW especifica que la ventana termina en la fila o el valor actual (dependiendo de si se ha especificado ROW o RANGE, respectivamente). En este caso el punto de partida no puede ser value_exprFOLLOWING.

value_expr ANTERIOR o value_expr SIGUIENTE De RANGE o ROW:

• Si value_exprFOLLOWING es el punto de inicio y el punto final debe ser value_exprFOLLOWING.

• Si value_exprPRECEDING es el punto final, entonces el punto de inicio debe ser value_exprPRECEDING.

Si está definiendo una ventana lógica definida por un intervalo de tiempo en formato numérico, es posible que deba usar funciones de conversión.

Si especificó ROWS:

• value_expr es un desplazamiento físico. Debe ser una constante o expresión y debe evaluarse a un valor numérico positivo.

• Si value_expr es parte del punto de inicio, entonces debe evaluarse a una fila antes del punto final.

Si usted especifica RANGE:

• value_expr es una lógica de desplazamiento. Debe ser una constante o expresión que se evalúa como un valor numérico positivo o un literal de intervalo. Consulte «Literales» para obtener información sobre los literales de intervalo.

• sólo Se puede especificar una expresión en el order_by_clause.

• Si value_expr evalúa a un valor numérico, el ORDERBYexpr debe ser un valor numérico o DATE tipo de datos.

• If value_expr evaluates to an interval value, then the ORDERBYexpr must be a DATE data type.

If you omit the windowing_clause entirely, then the default is RANGEBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW.

Analytic functions are commonly used in data warehousing environments. En la lista de funciones analíticas que sigue, las funciones seguidas de un asterisco ( * ) permiten la sintaxis completa, incluido el windowing_clause.

AVG *
CORR *
COUNT *
COVAR_POP *
COVAR_SAMP *
CUME_DIST
DENSE_RANK
FIRST
FIRST_VALUE *
LAG
LAST
LAST_VALUE *
LEAD
LISTAGG
MAX *
MEDIAN
MIN *
NTH_VALUE *
NTILE
PERCENT_RANK
PERCENTIL_CONT
PERCENTIL_DISC
RANK
RATIO_TO_REPORT
Funciones REGR_ (Regresión lineal) *
NÚMERO DE FILA
STDDEV *
STDDEV_POP *
STDDEV_SAMP *
SUMA *
VAR_POP *
VAR_SAMP *
VARIANZA *