As funções analíticas

as funções analíticas calculam um valor agregado baseado num grupo de linhas. Eles diferem das funções agregadas em que eles retornam várias linhas para cada grupo. O grupo de linhas é chamado de janela e é definido pelo analytic_clause. Para cada linha, é definida uma janela deslizante de linhas. A janela determina o intervalo de linhas utilizadas para efectuar os cálculos para a linha actual. Os tamanhos de janelas podem ser baseados em um número físico de linhas ou um intervalo lógico, como o tempo.

funções Analíticas são o último conjunto de operações executadas em uma consulta, exceto para o final ORDERBY cláusula. Todas as associações e todos os WHEREGROUPBY e HAVING cláusulas são concluídas antes de as funções analíticas são processados. Portanto, as funções analíticas podem aparecer apenas na lista selecionada ou ORDERBY cláusula.

as funções analíticas são normalmente utilizadas para calcular agregados cumulativos, móveis, centralizados e de reporte.

analytic_function::=

Descrição da ilustração “analytic_function.gif”

analytic_clause::=

Descrição da ilustração “analytic_clause.gif”

query_partition_clause::=

Descrição da ilustração “query_partition_clause.gif”

order_by_clause::=

Descrição da ilustração “order_by_clause.gif”

windowing_clause ::=

Descrição da ilustração “windowing_clause.gif “

The semantics of this syntax are discussed in the sections that follow.

função analítica

especifique o nome de uma função analítica (veja a lista de funções analíticas após esta discussão de semântica).

argumentos

funções analíticas levam 0 a 3 argumentos. Os argumentos podem ser qualquer tipo de dados numéricos ou qualquer tipo de dados não-americanos que podem ser implicitamente convertidos para um tipo de dados numéricos. Oracle determina o argumento com a maior precedência numérica e implicitamente converte os argumentos restantes para esse tipo de dados. O tipo return é também esse tipo de dados, salvo indicação em contrário para uma função 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. Pode indicar funções analíticas com esta cláusula na lista de selecção ou ORDERBY. Para filtrar os resultados de uma consulta com base em uma função analítica, anote essas funções dentro da consulta Pai, e então filtrar os resultados da subquery aninhada.

Notas sobre o analytic_clause: As observações a seguir se aplicam analytic_clause:

• Você pode aninhar funções analíticas especificando qualquer função analítica em qualquer parte do analytic_clause. No entanto, você pode especificar uma função analítica em um subquery e computar outra função analítica sobre ele.

• pode especificar OVERanalytic_clause com funções analíticas definidas pelo Utilizador, bem como funções analíticas incorporadas. Veja Criar função.

query_partition_clause

Use PARTITIONBY cláusula para particionar o conjunto de resultados de consulta em grupos, com base em um ou mais value_expr. Se você omitir esta cláusula, então a função trata todas as linhas do conjunto de resultados da consulta como um único grupo.

para usar o query_partition_clause numa função analítica, use o ramo superior da sintaxe (sem parêntesis). Para usar esta cláusula em uma consulta modelo (no model_column_clauses) ou uma junção externa particionada (no outer_join_clause), use o ramo inferior da sintaxe (com parênteses).

pode especificar várias funções analíticas na mesma consulta, cada uma com a mesma ou diferente PARTITIONBY keys.

If the objects being queried have the parallel attribute, and if you specify an analytic function with the query_partition_clause, then the function computations are parallelized as well.

valores válidos de value_expr são constantes, colunas, funções não-analíticas, expressões de funções ou expressões envolvendo qualquer uma delas.

order_by_clause

Use o order_by_clause para especificar como os dados são ordenados dentro de uma partição. Para todas as funções analíticas, pode ordenar os valores numa partição em várias teclas, cada uma definida por um value_expr e cada uma qualificada por uma sequência de ordenação.

dentro de cada função, poderá indicar várias expressões de ordenação. Fazê-lo é especialmente útil ao usar funções que classificam valores, porque a segunda expressão pode resolver laços entre valores idênticos para a primeira expressão.

sempre que o order_by_clause resulta em valores idênticos para linhas múltiplas, a função comporta-se da seguinte forma::

• CUME_DISTDENSE_RANKNTILEPERCENT_RANK e RANK retornar o mesmo resultado para cada uma das linhas.

• ROW_NUMBER atribui a cada linha um valor distinto, mesmo se houver um empate, com base no order_by_clause. O valor é baseado na ordem em que a linha é processada, o que pode ser não determinístico se o ORDERBY não garante uma total do pedido.

• para todas as outras funções analíticas, o resultado depende da especificação da janela. Se indicar uma janela lógica com oRANGE palavra-chave, então a função devolve o mesmo resultado para cada uma das linhas. Se você especificar uma janela física com oROWS palavra-chave, então o resultado é não-determinístico.

restrições à ordem por Cláusula as seguintes restrições aplicam-se àORDERBY cláusula:

• Quando usado numa função analítica, o order_by_clause deve tomar uma expressão (expr). A palavra-chaveSIBLINGS não é válida (é relevante apenas nas consultas hierárquicas). A posição (position) e os pseudónimos de coluna (c_alias) também são inválidos. Caso contrário, este order_by_clause é o mesmo que o usado para ordenar a consulta global ou subquery.

• 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

  Janela de limites além desses quatro só pode ter uma chave de classificação no ORDERBY cláusula da função analítica. Esta restrição não se aplica aos limites da janela especificados pela palavra-chave ROW.

ASC / DESC especificar a sequência de ordenação (ascendente ou descendente). é o padrão.

NULLS primeiro / NULLS último indique se as linhas devolvidas que contêm nulls deverão aparecer primeiro ou último na sequência de ordenação.

NULLSLAST é o padrão para ordem crescente, e NULLSFIRST é o padrão por ordem decrescente.

as funções analíticas funcionam sempre em linhas na ordem especificada na order_by_clause da função. No entanto, o order_by_clause da função não garante a ordem do resultado. Use o order_by_clause da consulta para garantir a ordenação do resultado final.

windowing_clause

algumas funções analíticas permitem owindowing_clause. Na listagem das funções analíticas no final desta seção, as funções que permitem o windowing_clause são seguidas por um asterisco (*).

linhas / intervalo estas palavras-chave definem para cada linha uma janela (um conjunto físico ou lógico de linhas) usada para calcular o resultado da função. A função é então aplicada a todas as linhas da janela. A janela move-se através do conjunto de resultados da consulta ou partição de cima para baixo.

• ROWS especifica a janela em unidades físicas (linhas).

• RANGE especifica a janela como um deslocamento lógico.

Você não pode especificar esta cláusula a menos que tenha especificado o order_by_clause. Alguns limites de janelas definidos pela cláusula RANGE permitem-lhe indicar apenas uma expressão na cláusula order_by_clause. Refira-se a”restrições à encomenda por cláusula”.

o valor retornado por uma função analítica com um deslocamento lógico é sempre determinístico. No entanto, o valor retornado por uma função analítica com um deslocamento físico pode produzir resultados não determinísticos a menos que a expressão de ordenação resulte em uma ordenação única. Poderá ter de indicar várias colunas no order_by_clause para obter esta encomenda única.

entre … AND Use the BETWEENAND clause to specify a start point and end point for the window. A primeira expressão (antes de AND) define o ponto de início e a segunda expressão (depois de AND) define o ponto final.

se omitir BETWEEN e indicar apenas um ponto final, então a Oracle considera-o o ponto inicial, e o ponto final corresponde por omissão à linha actual.

UNBOUNDED PRECEDING Specify UNBOUNDEDPRECEDING to indicate that the window starts at the first row of the partition. Esta é a especificação do ponto de partida e não pode ser usada como uma especificação do ponto final.

UNBOUNDED SEGUINTE Especificar UNBOUNDEDFOLLOWING para indicar que a janela termina na última linha da partição. Esta é a especificação do ponto final e não pode ser usada como uma especificação do ponto de partida.

LINHA ATUAL Como um ponto de início, CURRENTROW especifica que a janela começa na linha atual ou valor (dependendo se você tiver especificado ROW ou RANGE, respectivamente). Neste caso, o ponto final não pode ser value_exprPRECEDING.

Como um ponto final, CURRENTROW especifica que a janela termina a linha atual ou valor (dependendo se você tiver especificado ROW ou RANGE, respectivamente). Neste caso, o ponto de partida não pode ser value_exprFOLLOWING.

value_expr ANTERIOR ou value_expr SEGUINTE Para RANGE ou ROW:

• Se value_exprFOLLOWING é o ponto de partida, o ponto final deve ser value_exprFOLLOWING.

• Se value_exprPRECEDING é o ponto final, então o ponto de partida deve ser value_exprPRECEDING.

Se estiver a definir uma janela lógica definida por um intervalo de tempo em formato numérico, então poderá ter de usar funções de conversão.

Se você especificou ROWS:

• value_expr é um deslocamento físico. Deve ser uma constante ou expressão e deve avaliar a um valor numérico positivo.

• Se value_expr faz parte do ponto inicial, então deve avaliar para uma linha antes do ponto final.

Se você especificou RANGE:

• value_expr é uma lógica de deslocamento. Deve ser uma constante ou expressão que avalia um valor numérico positivo ou um literal de intervalo. Consulte “Literals” para obter informações sobre literais de intervalo.

• pode indicar apenas uma expressão no order_by_clause.

• Se value_expr avalia para um valor numérico, o ORDERBYexpr deve ser um valor numérico ou DATE tipo de dados.

• 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. Na lista de funções analíticas que se segue, as funções seguidas por um asterisco (*) permitem a sintaxe completa, incluindo o windowing_clause.

AVG *
CORR *
CONTAGEM *
COVAR_POP *
COVAR_SAMP *
CUME_DIST
DENSE_RANK
PRIMEIRA
FIRST_VALUE *
GAL
SOBRENOME
LAST_VALUE *
CHUMBO
LISTAGG
MAX. *
MEDIANA
MIN *
NTH_VALUE *
NTILE
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
RANKING
RATIO_TO_REPORT
REGR_ (Regressão Linear) Funções *
ROW_NUMBER
STDDEV *
STDDEV_POP *
STDDEV_SAMP *
SOMA *
VAR_POP *
VAR_SAMP *
VARIÂNCIA *