Existem três categorias de funções testadas pelo exame: funções escalares, analíticas e agregadas. Funções escalares retornam uma resposta para cada linha processada. As funções agregadas retornam uma resposta para um conjunto de zero a várias linhas. As funções analíticas caem em algum lugar entre elas. Eles têm a capacidade de retornar várias linhas de dentro de um grupo de linhas. Como uma determinada instrução SELECT processa dados linha por linha, uma função analítica pode operar em uma janela de linhas. Essa janela pode ser definida por números de linhas ou por lógica, como um intervalo de tempo. À medida que o processamento se move de uma linha para outra no grupo de linhas da instrução SELECT, a janela pode deslizar, dependendo de como a função analítica especifica a janela. As funções analíticas são o último conjunto de operações executadas em uma consulta anterior à cláusula ORDER BY, que é a etapa final do processamento. Por esse motivo, você não pode incluir funções analíticas em nenhum lugar que não seja a lista SELECT ou a cláusula ORDER BY. Em outras palavras, a análise é proibida nas cláusulas WHERE, HAVING e GROUP BY. Vejamos algumas palavras-chave comumente usadas com funções analíticas.
OVER, PARTITION BY, and ORDER BY
Para ver como OVER, PARTITION BY e ORDER BY são usados com análises, vejamos um exemplo. Primeiro, considere o seguinte conjunto de dados de amostra:
Podemos usar a função SUM como uma função agregada para obter a soma total de todos os valores de SQ_FT para todo o conjunto de dados, assim:
Como alternativa, podemos usar SUM como uma função analítica e combiná-la com OVER para exibir um total em execução para cada linha, assim (números de linha adicionados)
A coluna “Running Total” exibe exatamente o que é indicado (um total em execução de valores SQ_FT) construído dentro de sua própria janela, que neste caso corresponde ao conjunto de linhas para o SELECT. A coluna “Subset” também executa a operação SUM, mas em uma janela deslizante definida como a linha atual, a anterior e a seguinte.
Por exemplo, o valor do “Subset”da linha 13 (quarto número 104) é a combinação dos valores de SQ_FT exibidos nas linhas 13, 14 e 15. Por quê? Devido ao segundo uso da função analítica SUM, detalhada nas linhas 3, 4 e 5. Vamos adicionar a opção PARTITION BY para executar nossa SUM sobre conjuntos de linhas por valores para a coluna WINDOW.
A inclusão de PARTITION BY alterou a maneira como são calculados o Running Total e o Subset. Por exemplo, observe os valores na linha 16. A coluna Running Total é reiniciada com um valor de 533, pois particionamos os dados pelo WINDOW e o valor para WINDOW agora é None (o primeiro exemplo desse valor e, portanto, o primeiro linha em uma nova partição). Portanto, o total atual é o valor atual de SQ_FT e nada mais: 533. Veja a linha 18. O valor Total total é 2422, que é a soma da última linha do Ocean (linha 15, onde o valor era 898) e o valor total valor atual na linha 18 (que é 1524). Juntos, 898 mais 1524 são 2422, o valor atual para o total em execução de SQ_FT para a partição definida pelas linhas com o valor Ocean. Vamos alterar ORDER BY para o SELECT, mas deixamos as funções analíticas inalteradas.
Observe que a instrução SELECT reorganiza as linhas, mas os valores para cada linha permanecem inalterados. Por exemplo, na linha 13, o Número do quarto 102 ainda mostra um valor de Subset de 2262, exatamente como nos resultados da consulta anterior. Como você já viu, o valor da coluna Subset é determinado pela partição e pela ordem das linhas, mas essa ordem não foi alterada. a função de análise nas linhas 4 a 7 permanece inalterada. Tudo o que mudamos foi a ordem de classificação da instrução SELECT.
LAG, LEAD
As funções LAG e LEAD são semelhantes entre si. Para uma determinada linha dentro de uma janela, o LAG mostra o valor de uma coluna na linha anterior e LEAD mostra o próximo valor. Por exemplo, considere os seguintes dados na tabela SHIP_CABINS:
Vamos usar LAG e LEAD em uma instrução SELECT para retornar cada linha que mostra o valor dessa linha para SQ_FT, o SQ_FT da linha anterior (Lag) e o SQ_FT da linha subsequente (lead):
Neste exemplo, as funções LAG (linha 2) e LEAD (linha 3) são especificadas para operar sobre uma janela classificada por WINDOW e SQ_FT. A instrução SELECT usa a mesma lógica ORDER BY (linha 5). Observe a linha 9, onde a primeira linha da saída mostra um NULL para o valor de Lag. Isso ocorre porque a lógica na linha 2 especifica a lógica resultante nesta linha (linha 9) como a primeira linha na janela (especificada na linha 2). Portanto, não há linha anterior.
Veja a Figura 1 para uma ilustração desses conceitos. Primeiro, PARTITION BY separa as linhas em conjuntos ou grupos, conforme especificado. Em nosso exemplo, especificamos PARTION BY WINDOW e nossos dados de amostra têm dois valores para WINDOW; portanto, separamos nossas linhas em dois grupos – um para o valor WINDOW de ‘Ocean’ e outro para o valor WINDOW de ‘None’. Segundo, para cada grupo, definimos uma janela para cada linha de destino. Em nosso exemplo, a janela consiste nas linhas que se estendem de uma linha antes da linha de destino até uma linha após a linha de destino. Isso significa que, à medida que avançamos em cada linha, nossa janela deslizará.
Terceiro, conforme avançamos em cada linha, queremos o SQ_FT; o LAG (SQ_FT), que é da linha anterior; e o LEAD (SQ_FT), que é da linha seguinte. Finalmente, retornamos a saída e vemos nossos resultados. Vamos alterar a lógica ORDER BY e ver se isso altera o valor do Lag.
A lógica ORDER BY agora classifica a saída por ROOM_NUMBER. Mas observe a saída na linha 11: é o mesmo valor ROW_NUMBER de 104 e é o mesmo valor NULL para Lag. Isso demonstra como a janela do LAG é definida pelo próprio LAG, independentemente da cláusula ORDER BY. Até agora, vimos as funções LAG e LEAD que retornam dados especificamente de uma linha imediatamente antes de (LAG) ou após (LEAD) a linha atual. A diferença de linha é especificada pelo deslocamento, cujo padrão é 1, como vimos até agora, mas pode ser especificado como outra coisa. Se o deslocamento especificar uma linha inexistente, a função LAG ou LEAD retornará um valor NULL. Vamos alterar o deslocamento da nossa função LAG para 2 e ver o que acontece.
Nesta lista de amostra, consulte a linha 2 para a função LAG e a especificação de deslocamento de 2, imediatamente após a referência SQ_FT. O resultado aparece nas linhas retornadas. Observe que as linhas 9 e 10 mostram valores NULL para LAG. Isso ocorre porque não há linhas no deslocamento para essas linhas. Para outras linhas, como a linha 13, você vê o SQ_FT de 533 e o valor Lag SQ_FT é obtido do deslocamento da linha por dois anteriores, que nesse caso é a linha mostrada na linha 11.
VARIANCE e STDDEV
A função STDDEV retorna o desvio padrão da amostra de um conjunto de valores numéricos. O desvio padrão é um valor matemático que representa o grau de distribuição de um determinado valor numérico dentro de um intervalo maior de valores. O desvio padrão para um determinado número é a raiz quadrada de sua variação. A Oracle tem funções para ambos, portanto, o valor do STDDEV de um determinado número é a raiz quadrada de sua VARIANCE, uma função agregada. Usando STDDEV, você pode analisar um conjunto de números e determinar a dispersão de cada valor a partir da média. STDDEV é útil ao analisar um conjunto de números procurando correlações entre esses números – aqueles cujos valores de STDDEV são baixos estão mais próximos da média do que aqueles com valores relativamente mais altos de STDDEV. As fórmulas matemáticas para calcular o desvio padrão e a variação são simples, mas um pouco envolvidas. Por exemplo, considere o seguinte conjunto de valores:
| # | 1 | 2 | 3 | 4 |
| Valores | 1 | 2 | 4 | 13 |
Os valores são 1, 2, 4 e 13, para um total de quatro valores. Para calcular a variação e o desvio padrão do nosso exemplo, faça o seguinte:
- Calcule a média:
A soma dos valores 1 + 2 + 4 + 13 = 20
Em seguida, divida a soma pelo número total de valores: 20/4 = 5. Essa é a média. - Para cada número, determine a diferença entre o número e a média e depois ache o quadrado do resultado:
| # | 1 | 2 | 3 | 4 |
| Valores | 1 | 2 | 4 | 13 |
| Média (20/4=5) | 5 | 5 | 5 | 5 |
| Diferença: | -4 | -3 | -1 | 8 |
| Diferença ao quadrado | 16 | 9 | 1 | 64 |
- Some as diferenças ao quadrado. Pegue o resultado e divida pela contagem total de valores menos um:
16 + 9 + 1 + 64 = 90
90 / (4 – 1) = 90/3 = 30. Esta é a fórmula VARIANCE - Calcule a raiz quadrada da variação:
SQRT (30) = 5,477. Este é o desvio padrão.
As funções VARIANCE e STDDEV executam esses cálculos para você. Nesse contexto, a função se comportará como uma função agregada. Por exemplo, considere o seguinte conjunto de dados:
Agora vamos usar o AVG, MEDIAN, VARIANCE e STDDEV como funções agregadas no conjunto completo de linhas.
Este é um exemplo do uso de STDDEV como uma função agregada. Em outras palavras, o conjunto completo de linhas especificado pela instrução SELECT é considerado e um único desvio padrão é calculado e retornado. No entanto, o STDDEV também pode ser usado como uma função de linha única em combinação com uma janela deslizante de linhas. O código a seguir calcula a VARIANCE cumulativa e o STDDEV cumulativo na mesma janela:
Em nossa saída, o VARIANCE e o STDDEV são calculados cumulativamente, e você pode ver os valores finais que correspondem aos resultados agregados calculados no exemplo anterior.
PERCENTILE_CONT
A função PERCENTILE_CONT usa interpolação linear entre o teto e o piso do conjunto de linhas de uma determinada linha para calcular o valor do percentil para essa linha. A função PERCENTILE_CONT usa como entrada uma porcentagem que você especifica, digamos, .4 (para 40%) e analisa um grupo de linhas que você especifica; então determina o valor numérico equivalente igual à porcentagem do todo, usando extrapolação linear. Por exemplo, vamos exibir linhas de SHIP_CABINS que mostram a metragem quadrada de cada cabine, mas para as cabines com o mesmo tipo de janela, considere a maior metragem quadrada, calcule 60% dessa metragem quadrada e exiba esse valor
Aqui está a fórmula usada para calcular o PERCENTILE_CONT. Primeiro, calculamos a porcentagem particionando as linhas pelo WINDOW. Nossos dados de amostra têm dois valores para Window: Ocean e None. Então, nós temos dois grupos. Para cada grupo, PERCENTILE_CONT considerará a porcentagem especificada (P), que no nosso caso é 0,60 e, em seguida, contará o número de linhas (RN). Para Ocean, existem quatro linhas, mostradas no exemplo anterior nas linhas 10, 11, 12 e 15. Usando essas informações, calculamos uma linha de destino (TR) usando a seguinte fórmula:
TR = (1 + (P * (RN − 1)))
Usando nossos números, aqui está o cálculo:2.8 = (1 + (.6 * (4 – 1)))
Nosso número de linha de destino é 2,8. Como temos quatro linhas para Ocean, nossa linha de destino está teoricamente localizada entre a segunda e a terceira linhas. Nosso objetivo é determinar o valor SQ_FT que existiria nesse número de linha teórico 2.8. Para calcular esse valor, precisamos saber o seguinte: CEIL e FLOOR do número da linha de destino e os valores nessas linhas. Para o nosso exemplo, essas informações são as seguintes:
- O CEIL para 2,8 é 3 e o valor para a terceira linha do Ocean é 533
- O FLOOR para 2,8 é 2 e o valor na segunda linha do Ocean é 205.
A fórmula usada por PERCENTILE_CONT é a seguinte:
(CEIL – TR) * (Valor de FLOOR) + (TR – FLOOR) * (Valor de CEIL)
Em outras palavras, aqui está o cálculo:
(((3 – 2.8) * 205) + ((2.8 – 2) * 533)) = 467.4
O resultado é o que você vê em cada linha do oceano em nossa produção. A intenção de PERCENTILE_CONT é calcular para um grupo de linhas o valor interpolado na porcentagem especificada (no nosso caso, 60%) do todo para esse grupo.
As funções que analisamos são chamadas de funções de linha única. O termo linha única significa que cada função retorna um valor para cada linha que encontra. Outro termo para a função de linha única é a função escalar.
O foco deste tópico é a categoria denominada funções de grupo. Uma função de grupo retorna um valor para cada conjunto de zero ou mais linhas que encontra. Existem dois tipos de funções de grupo: funções agregadas e analíticas. As funções agregadas também são conhecidas como funções com múltiplas linhas. As funções agregadas geralmente são usadas com uma instrução SELECT que seleciona muitas linhas, em que a função agregada varre um conjunto de linhas e retorna uma resposta única para todas elas.
As funções analíticas podem considerar um conjunto de linhas e processá-las como um único conjunto ou como subconjuntos variáveis, incluindo subconjuntos sobrepostos. Em outras palavras, uma única linha de origem pode ser representada em mais de uma linha de subconjunto da saída de uma função analítica. Você já viu a sintaxe das funções analíticas no tópico anterior quando analisamos STDDEV, PERCENTILE_CONT, LAG e LEAD. Veremos essa sintaxe novamente na próxima seção sobre as funções dos grupos RANK e DENSE_RANK.
Muitas das funções de grupo podem ser usadas como uma função agregada e também como uma função analítica, com base na sintaxe da instrução específica na qual é invocada. Há poucas exceções. A Tabela 1 resume algumas das funções de grupo mais usadas disponíveis no SQL. Observe que a maioria das funções listadas pode funcionar como uma função agregada ou analítica; alguns são limitados a apenas um tipo.
Como funções escalares, as funções de grupo aceitam entrada, processam essa entrada e retornam saída. Também como funções escalares, as funções de grupo aceitam entrada de tipos de dados específicos e retornam saídas de tipos de dados específicos, e os tipos de dados de entrada e saída não são necessariamente os mesmos, como ocorre com as funções escalares. Enquanto as funções de grupo numérico (que processam e retornam dados numéricos) são as mais comuns, muitas funções de grupo processam dados com tipos de dados de caracteres e datas. As funções de grupo devem ser tratadas separadamente das funções escalares porque se comportam de maneira diferente. Uma instrução SELECT típica não pode chamar funções de grupo e funções escalares no mesmo nível de agregação. Por exemplo, considere uma tabela SHIP_SHOP com três linhas de dados. Se executarmos uma instrução SELECT simples na tabela com uma função escalar como ROUND, obteremos uma linha retornada para cada linha processada.
Se executarmos uma instrução SELECT simples no mesmo conjunto de três linhas usando uma função de grupo como AVG (para média), obteremos um resultado em uma linha mostrando o valor médio para todas as três linhas.
O problema aqui é que estamos começando com três linhas de entrada e tentando combinar resultados de uma função escalar e de grupo no mesmo nível de agregação. O escalar (ROUND) deseja retornar três linhas, mas a função de grupo (AVG) deseja retornar uma linha. Isso não é permitido junto na mesma saída SELECT e aciona a mensagem de erro. No entanto, podemos usar os dois tipos de funções em diferentes níveis de agregação na mesma instrução SELECT. Por exemplo, podemos primeiro executar a média (a função de grupo) e depois arredondar os resultados (a função escalar).
De qualquer maneira, o ponto é que não podemos aplicar as funções escalar e de grupo juntas em linhas no mesmo nível de agregação. No entanto, podemos optar por aninhar as chamadas de função em uma única instrução SQL para processar cada conjunto de linhas uma por vez, passando os resultados de uma para outra, combinados em uma única instrução SELECT, aninhando as funções. Vamos analisar a funcionalidade detalhada de algumas das funções de grupo mais usadas.
COUNT
Sintaxe: COUNT (e1) Parâmetros: e1 é uma expressão. e1 pode ser qualquer tipo de dados. A função agregada COUNT determina o número de ocorrências de valores não NULL. Ele considera o valor de uma expressão e determina se esse valor NÃO é NULL para cada linha que encontra. Por exemplo, considere a tabela VENDORS, mostrada em Figura 2:
Observe que COUNT ignora todos e quaisquer valores que sejam NULL. Por fim, podemos simplesmente contar o número de linhas em toda a tabela.
COUNT retornará apenas o número, ou quantidade, de valores não NULL nas colunas; ou, quando usado para contar linhas, retorna o número de linhas. Lembre-se de que a tabela SELECT * FROM é a maneira abreviada de solicitar a seleção de todas as colunas em uma determinada tabela. A função COUNT é frequentemente usada com o asterisco dessa maneira para obter uma contagem rápida de todas as linhas em uma determinada tabela usando COUNT (*). Poderíamos ter misturado essas funções em várias combinações
Observe que uma COUNT do asterisco está solicitando uma contagem de todas as linhas. Na rara situação em que uma linha contém nada além de valores NULL em todas as suas colunas, COUNT (*) ainda contará essa linha.
Os operadores DISTINCT e ALL podem ser usados com funções agregadas. DISTINCT retorna apenas valores exclusivos. ALL é o oposto de DISTINCT e é o valor padrão. Se você omitir DISTINCT, o ALL será assumido. Em outras palavras, se você nunca usou o DISTINCT, sempre implicou ALL até agora e consultou todos os resultados relevantes. Portanto, se você tiver três linhas de valores PRICE de 2,99, 3,99 e 3,99, DISTINCT retornará apenas 2,99 e 3,99, onde ALL retornará todos os três valores, incluindo os valores duplicados. Aqui está um exemplo mostrando DISTINCT e ALL usados em uma função COUNT:
Este exemplo nos diz que a tabela EMPLOYEES possui sete linhas com valores para LAST_NAME, das quais cinco são valores exclusivos para LAST_NAME, portanto, duas são duplicadas. Lembre-se também de que DISTINCT e ALL não podem ser usados com o asterisco.
SUM
Sintaxe: SUM (e1) Parâmetros: e1 é uma expressão cujo tipo de dados é numérico. A função SUM adiciona valores numéricos em uma determinada coluna. Leva apenas dados numéricos como entrada. SUM adiciona todos os valores em todas as linhas e retorna uma única resposta. Aqui está um exemplo:
select sum(subtotal) from orders;
Essa consulta adicionará todos os valores para SUBTOTAL na tabela ORDERS e produzirá um único resultado. Aqui está outro exemplo:
select sum(subtotal) from orders where to_char(order_date, 'MON-RR') = 'APR-17';
Esta consulta encontrará todas e todas as linhas para as quais o pedido tem um valor ORDER_DATE em abril de 2017 e, em seguida, adicionará todos os valores na coluna SUBTOTAL e produzirá uma única resposta.
MIN, MAX
Sintaxe: MIN (e1); Parâmetros MAX (e1): e1 é uma expressão com um tipo de dado de caractere, data ou número. Para um determinado conjunto de linhas identificado por uma instrução SELECT, MIN retorna o valor mínimo único e MAX retorna o valor máximo único. MIN e MAX podem trabalhar com dados numéricos, de data e de caractere e usam a mesma lógica básica que ORDER BY usa para os diferentes tipos de dados, especificamente:
Numeric numéricos baixos são MIN; números altos são MAX
Date As datas anteriores são MIN; datas posteriores são MAX. As datas anteriores são inferiores às datas posteriores.
Character A é menor que Z; Z é menor que a. O valor da string 2 é maior que o valor da string 100. O caractere 1 é menor que os caracteres 10.
Os valores NULL são ignorados, a menos que todos os valores sejam NULL; nesse caso, MIN ou MAX retornará NULL. Por exemplo, considere a seguinte lista de dados da tabela EMPLOYEES:
Agora vamos identificar os valores MIN e MAX
Observe que Hoddlestein é o primeiro valor alfabético da lista de valores LAST_NAME. Mesmo que os dados retornados por MIN e MAX representem os dados encontrados em uma única linha da lista, não se deixe enganar por pensar que isso representa uma resposta de linha única – não. O SQL vê cada resposta de MIN e MAX como uma resposta agregada, significando que o valor individual é a resposta que representa o conjunto completo de linhas.
AVG
Sintaxe: AVG (e1) Parâmetros: e1 é uma expressão com um tipo de dados numérico. A função AVG calcula o valor médio para um conjunto de linhas. O AVG funciona apenas com dados numéricos. Ele ignora valores NULL. Por exemplo, vamos olhar para a tabela PAY_HISTORY; depois disso, solicitaremos o valor médio de todos os valores na coluna SALARY.
Podemos aninhar os resultados dessa consulta na função escalar ROUND, da seguinte forma:
Se preferir podemos formatar os dados usando a função TO_CHAR e um modelo de formato.
Nestes últimos exemplos de instruções SELECT, aninhamos uma única função agregada em duas funções escalares ou de linha única. Você pode incorporar uma única função agregada em quantas funções escalares aninhadas desejar. A função agregada não precisa ser a função mais interna; você pode incluir uma função agregada com qualquer número de funções escalares em uma combinação aninhada, desde que todos os tipos de dados de parâmetro sejam respeitados. Mas se você deseja incluir duas funções agregadas em uma combinação aninhada, tenha cuidado – há limitações no uso de funções agregadas aninhadas. Abordaremos essa questão mais adiante neste capítulo. É mais complexo do que parece. DISTINCT e ALL estão disponíveis para uso com o AVG. Caso a listagem de dados de uma tabela inclua alguns valores repetidos, o uso de DISTINCT transformará os resultados, de modo que a média seja calculada apenas nas ocorrências exclusivas de cada valor.
MEDIAN
Sintaxe: MEDIAN (e1) Parâmetros: e1 é uma expressão com um tipo de dados numérico ou de data. O MEDIAN pode operar com tipos de dados numéricos ou de data. Ele ignora valores NULL. A função MEDIAN está um pouco relacionada ao AVG. MEDIAN tem o desempenho esperado: a partir de um conjunto de dados, MEDIAN retorna o valor do meio ou, se não for facilmente identificado, um valor interpolado de dentro do meio. Em outras palavras, MEDIAN classificará os valores e, se houver um número ímpar de valores, ele identificará o valor no meio da lista; caso contrário, se houver um número par de valores, ele localizará os dois valores no meio da lista e executará interpolação linear entre eles para localizar um resultado. Aqui está um exemplo:
Se você executasse as instruções SQL anteriores, o valor retornado pela instrução SELECT seria 3. Veja outro exemplo:
select id from tabela_teste;
| ID |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
select median(id) from tabela_teste;
| MEDIAN(ID) |
|---|
| 2.5 |
Resultado do MEDIAN é a soma das linhas dois e três (2+3) =5 daí pega 5 / 2 = 2.5 que é o valor resultante.
RANK
RANK tem a ver com a classificação numérica (1, 2, 3 e assim por diante) de um valor dentro de um grande grupo de valores. Quando um conjunto de valores vinculados entre linhas é encontrado, o RANK atribui o mesmo número de classificação às linhas vinculadas, mas mantém a contagem de cada linha e continua a contar as linhas subseqüentes com a posição numerada corretamente. A versão analítica de RANK retorna as classificações de um conjunto de linhas. A versão agregada possui um parâmetro de uma expressão pertencente a uma linha e retorna a classificação para esse valor. Ambas as versões de RANK são descritas nas seções a seguir.
RANK: Analytic
Sintaxe: RANK() OVER (PARTITION BY p1 ORDER BY ob1)
Parâmetros: p1 é uma partição. ob1 é uma expressão. O uso de PARTITION BY é opcional. Todos os outros elementos são necessários. A função RANK calcula a classificação de um valor dentro de um grupo de valores. As classificações podem não ser números consecutivos, pois o SQL conta as linhas vinculadas individualmente; portanto, se três linhas forem vinculadas pela primeira vez, cada uma será classificada como 1, 1 e 1 e a próxima linha será classificada como 4. Por exemplo, observe o uso de RANK () na seguinte linha 2 (números de linha adicionados):
No exemplo, os valores retornados por RANK estão na coluna SQ_FT_RK. As linhas são particionadas por ROOM_STYLE; portanto, essas são classificadas primeiro; em uma determinada partição, as linhas são classificadas por SQ_FT. Observe a linha 14, que é o início de um novo ROOM_STYLE, e o valor RANK é reiniciado em 1. Observe também que a próxima linha, linha 15, tem o mesmo valor para SQ_FT, portanto, o ranking está empatado em 1. Em seguida, linha 16 é classificado como 3, pois esta é a terceira linha do ‘Suite’ ROOM_STYLES e o SQ_FT de 586 não está vinculado a nenhuma outra linha.
RANK: Agregado
Sintaxe: RANK(c1) WITHIN GROUP (ORDER BY e1)
Parâmetros: c1 e e1 são expressões. Nesse formato, os parâmetros podem ser repetidos de forma que, para cada c1, você possa ter um e1 correspondente; para cada c2 (se incluído), deve haver um e2 correspondente; e assim por diante. Cada parâmetro sucessivo é separado do parâmetro anterior por vírgula, como em:
rank(c1,c2,c3) within group (order by e1,e2,e3)
Além disso, o tipo de dados de c1 deve corresponder ao tipo de dados de e1, o tipo de dados de c2 (se incluído) deve corresponder ao tipo de dados de e2 e assim por diante. Aqui está um exemplo:
O exemplo mostra que, para um valor SQ_FT de 533, a classificação é 4
DENSE_RANK
DENSE_RANK é semelhante a RANK. DENSE_RANK retorna a classificação numérica (1, 2, 3 e assim por diante) de um valor dentro de um grande grupo de valores. Quando um empate é encontrado, DENSE_RANK atribui o mesmo número a cada valor equivalente. Mas quando a contagem é retomada após um empate, é aí que DENSE_RANK difere de RANK. A função DENSE_RANK não pulará nenhum número e atribuirá o próximo número seqüencial, independentemente de quantos valores empatados (equivalentes) ele acabou de numerar.
DENSE_RANK: Analytic
Syntax: DENSE_RANK() OVER (PARTITION BY p1 ORDER BY ob1)
Parâmetros: p1 é uma partição. ob1 é uma expressão. No caso de DENSE_RANK, se três linhas estiverem ligadas pela primeira vez, cada uma será classificada como 1, 1 e 1 e a próxima linha será classificada como 2. Por exemplo, observe o uso de DENSE_RANK () na linha 2 a seguir. :
Observe as linhas 16 e 17. Quando usamos RANK, as classificações aqui eram 3 e 4, respectivamente. Usando DENSE_RANK, as classificações são 2 e 3. Depois que as duas linhas vinculadas nas linhas 14 e 15 são retornadas, DENSE_RANK retoma a classificação das linhas subseqüentes com o próximo número sequencial, independentemente de quantas linhas vinculadas tenham precedido.
DENSE_RANK: Aggregate
Syntax: DENSE_RANK(c1) WITHIN GROUP (ORDER BY e1)
Parâmetros: c1 é uma constante; e1 é uma expressão com um tipo de dados que corresponde ao tipo de dados c1 correspondente. Pares numéricos e de caracteres são permitidos. As regras para o formulário agregado de DENSE_RANK são as mesmas do formulário agregado de RANK. A diferença é que DENSE_RANK retornará um valor consistente com a lógica DENSE_RANK, conforme explicado na seção anterior; ou seja, se uma série de linhas vinculadas for encontrada, classifique os números das linhas subsequentes será atribuído com o próximo número sequencial, independentemente de quantos linhas precedidas. Portanto, se quatro linhas estiverem empatadas por segundo, elas serão classificadas como 2, 2, 2 e 2, e a próxima linha será classificada como 3.
FIRST, LAST
Syntax:
Aggregate_function KEEP (DENSE_RANK FIRST ORDER BY e1)
Aggregate_function KEEP (DENSE_RANK LAST ORDER BY e1)
Parâmetros: e1 é uma expressão com um tipo de dados numérico ou de caractere. As funções FIRST e LAST são semelhantes. Ambos são considerados funções agregadas, bem como funções analíticas. Para um determinado intervalo de valores classificados, cada um retorna o primeiro valor (FIRST) ou o último valor (LAST) da população de linhas que definem e1, na ordem classificada. Aqui está um exemplo:
Neste exemplo, estamos fazendo o seguinte:
- Primeiro, classificamos todas as linhas da tabela SHIP_CABINS de acordo com o valor na coluna GUESTS e identificamos o PRIMEIRO valor nessa ordem de classificação, que é uma maneira complexa de dizer que estamos identificando o valor mais baixo para a coluna GUEST
- Para todas as linhas com um valor GUEST que corresponde ao valor mais baixo que acabamos de encontrar, determine o valor MAX para SQ_FT.
- Quer dizer que estamos exibindo o maior número de “metros quadrados” para toda e qualquer cabine que acomode o menor número de convidados, de acordo com a coluna GUEST.
Exemplo Agregado
SELECT department_id,
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct) "Worst",
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct) "Best"
FROM employees
GROUP BY department_id;
DEPARTMENT_ID Worst Best
------------- ---------- ----------
10 4400 4400
20 6000 13000
30 2500 11000
40 6500 6500
50 2100 8200
60 4200 9000
70 10000 10000
80 6100 14000
90 17000 24000
100 6900 12000
110 8300 12000
7000 7000
Exemplo Analítico
SELECT last_name, department_id, salary,
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct)
OVER (PARTITION BY department_id) "Worst",
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct)
OVER (PARTITION BY department_id) "Best"
FROM employees
ORDER BY department_id, salary;
LAST_NAME DEPARTMENT_ID SALARY Worst Best
------------------- ------------- ---------- ---------- ----------
Whalen 10 4400 4400 4400
Fay 20 6000 6000 13000
Hartstein 20 13000 6000 13000
. . .
Gietz 110 8300 8300 12000
Higgins 110 12000 8300 12000
Grant 7000 7000 7000
desenvoracle.home.blog 