Olá,
Neste artigo irei abordar de forma simples e direta exemplos de uso de algumas funções analíticas que podemos utilizar nas instruções SQL com o Oracle. Ás vezes muitas dessas funções são ignoradas por quem está construindo uma instrução SQL. O uso de funções analíticas podem ajudar muito a tornar uma instrução SQL que até então é complexa em uma versão muito mais simples. Dentre os exemplos de funções analíticas que irei abordar estão RANK, DENSE_RANK, ROW_NUMBER, FIRST_VALUE, LAST_VALUE, LAG e LEAD.
Todos os exemplos das funções analíticas terão como base a tabela T1 abaixo na qual existem 3 grupos de ID (10, 20 e 30) com seus respectivos valores na coluna VALUE. Vale a pena salientar que a tabela possui uma linha duplicada intencionalmente (ID: 20 e VALUE: 202).
SQL> select * from t1 order by 1,2;
ID VALUE
---------- ----------
10 101
10 102
10 103
20 201
20 202
20 202
20 203
30 301
30 302
30 303
10 linhas selecionadas.
RANK
A função analítica RANK tem como objetivo retornar a classificação de cada linha de um conjunto de resultados. Por exemplo, abaixo irei classificar ou criar um rank para as linhas da tabela T1 de acordo com os valores da coluna VALUE ordenados de forma ascendente. Vale a pena salientar que valores repetidos terão o mesmo rank conforme observado nas linhas de ID: 20 e VALUE: 202 o que irá gerar uma quebra na sequência do rank, ou seja, a sequencia de número 6 foi perdida.
SQL> select id,
2 value,
3 rank() over (order by VALUE) rank
4 from t1;
ID VALUE RANK
---------- ---------- ----------
10 101 1
10 102 2
10 103 3
20 201 4
20 202 5
20 202 5
20 203 7
30 301 8
30 302 9
30 303 10
10 linhas selecionadas.
Caso a intenção seja o de gerar a mesma classificação de acordo com os valores da coluna VALUE ordenados de forma ascendente, mas agora agrupando por ID, bastará apenas utilizar a palavra chave PARTITION BY. Como dito anteriormente, valores repetidos terão o mesmo rank conforme observado nas linhas de ID: 20 e VALUE: 202 o que irá gerar uma quebra na sequência do rank, ou seja, a sequencia de número 3 foi perdida.
SQL> select id,
2 value,
3 rank() over (partition by ID order by VALUE) rank
4 from t1;
ID VALUE RANK
---------- ---------- ----------
10 101 1
10 102 2
10 103 3
20 201 1
20 202 2
20 202 2
20 203 4
30 301 1
30 302 2
30 303 3
10 linhas selecionadas.
DENSE_RANK
A função analítica DENSE_RANK age da mesma forma que a função RANK, porém com a diferença nos valores de classificação do rank. Os valores gerados serão consecutivos, mas os valores duplicados ainda continuarão com rank repetidos.
SQL> select id,
2 value,
3 dense_rank() over (order by VALUE) rank
4 from t1;
ID VALUE RANK
---------- ---------- ----------
10 101 1
10 102 2
10 103 3
20 201 4
20 202 5
20 202 5
20 203 6
30 301 7
30 302 8
30 303 9
10 linhas selecionadas.
SQL> select id,
2 value,
3 dense_rank() over (partition by ID order by VALUE) rank
4 from t1;
ID VALUE RANK
---------- ---------- ----------
10 101 1
10 102 2
10 103 3
20 201 1
20 202 2
20 202 2
20 203 3
30 301 1
30 302 2
30 303 3
10 linhas selecionadas.
ROW_NUMBER
A função analítica ROW_NUMBER tem como objetivo gerar um valor único para a linha retornada da mesma forma que a pseudo coluna ROWNUM faz. Neste caso os valores da classificação serão sempre consecutivos.
SQL> select rownum from dual connect by level <=10;
ROWNUM
----------
1
2
3
4
5
6
7
8
9
10
10 linhas selecionadas.
SQL> select id,
2 value,
3 row_number() over (order by VALUE) rank
4 from t1;
ID VALUE RANK
---------- ---------- ----------
10 101 1
10 102 2
10 103 3
20 201 4
20 202 5
20 202 6
20 203 7
30 301 8
30 302 9
30 303 10
10 linhas selecionadas.
SQL> select id,
2 value,
3 row_number() over (partition by ID order by VALUE) rank
4 from t1;
ID VALUE RANK
---------- ---------- ----------
10 101 1
10 102 2
10 103 3
20 201 1
20 202 2
20 202 3
20 203 4
30 301 1
30 302 2
30 303 3
10 linhas selecionadas.
Um uso muito comum para uso da função analítica ROW_NUMBER é utilizá-la em instruções SQL que precisam obter valores de um conjunto de dados no qual precisam ser retornados os N maiores ou menores valores de cada grupo. Por exemplo, o SQL abaixo irá retornar as duas linhas com os maiores valores (VALUE) de cada grupo (ID).
SQL> select *
2 from (select id,
3 value,
4 row_number () over (partition by ID order by VALUE) rank
5 from t1)
6 where rank <= 2;
ID VALUE RANK
---------- ---------- ----------
10 101 1
10 102 2
20 201 1
20 202 2
30 301 1
30 302 2
6 linhas selecionadas.
FIRST_VALUE
A função analítica FIRST_VALUE irá retornar o primeiro valor de um conjunto de dados ordenado. Por exemplo, abaixo irei mostrar na coluna RANK o primeiro valor de VALUE retornado de cada grupo ID. Como a ordenação é ascendente pela coluna VALUE (order by VALUE), então o menor valor (primeiro) de cada grupo de ID será retornado para cada linha.
SQL> select id,
2 value,
3 first_value(value) over (partition by ID order by VALUE range
4 between unbounded preceding and unbounded following) rank
5 from t1;
ID VALUE RANK
---------- ---------- ----------
10 101 101
10 102 101
10 103 101
20 201 201
20 202 201
20 202 201
20 203 201
30 301 301
30 302 301
30 303 301
10 linhas selecionadas.
LAST_VALUE
A função analítica LAST_VALUE irá retornar o último valor de um conjunto de dados ordenado. Por exemplo, abaixo irei mostrar na coluna RANK o último valor de VALUE retornado de cada grupo ID. Como a ordenação é ascendente pela coluna VALUE (order by VALUE), então o maior valor (último) de cada grupo de ID será retornado para cada linha.
SQL> select id,
2 value,
3 last_value(value) over (partition by ID order by VALUE range
4 between unbounded preceding and unbounded following) rank
5 from t1;
ID VALUE RANK
---------- ---------- ----------
10 101 103
10 102 103
10 103 103
20 201 203
20 202 203
20 202 203
20 203 203
30 301 303
30 302 303
30 303 303
10 linhas selecionadas.
LAG
A função analítica LAG tem como objetivo acessar os dados de uma linha anterior a partir da linha atual retornada. No exemplo abaixo irei retornar os valores das linhas anteriores da coluna VALUE (1º, 3º e 9º). Por exemplo, no resultado abaixo a linha com VALUE 303 mostrou na coluna VALUE_PREVIOUS_1 o valor 302 que é exatamente o valor da linha anterior ao valor 303. Já a coluna VALUE_PREVIOUS_3 mostrou o valor 203 que é exatamente o valor das 3 linhas anteriores ao valor 303. Já a coluna VALUE_PREVIOUS_9 mostrou o valor 101 que é exatamente o valor das 9 linhas anteriores ao valor 303.
SQL> select id,
2 value,
3 lag(value,1,0) over (order by value) AS value_previous_1,
4 lag(value,3,0) over (order by value) AS value_previous_3,
5 lag(value,9,0) over (order by value) AS value_previous_9
6 from t1;
ID VALUE VALUE_PREVIOUS_1 VALUE_PREVIOUS_3 VALUE_PREVIOUS_9
---------- ---------- ---------------- ---------------- ----------------
10 101 0 0 0
10 102 101 0 0
10 103 102 0 0
20 201 103 101 0
20 202 201 102 0
20 202 202 103 0
20 203 202 201 0
30 301 203 202 0
30 302 301 202 0
30 303 302 203 101
10 linhas selecionadas.
LEAD
A função analítica LEAD tem como objetivo acessar os dados de uma linha posterior a partir da linha atual retornada. No exemplo abaixo irei retornar os valores das linhas posteriores da coluna VALUE (1º, 3º e 9º). Por exemplo, no resultado abaixo a linha com VALUE 101 mostrou na coluna VALUE_NEXT_1 o valor 102 que é exatamente o valor da linha posterior ao valor 101. Já a coluna VALUE_NEXT_3 mostrou o valor 201 que é exatamente o valor das 3 linhas posteriores ao 101. Já a coluna VALUE_NEXT_9 mostrou o valor 303 que é exatamente o valor das 9 linhas posteriores ao valor 101.
SQL> select id,
2 value,
3 lead(value,1,0) over (order by value) AS value_next_1,
4 lead(value,3,0) over (order by value) AS value_next_3,
5 lead(value,9,0) over (order by value) AS value_next_9
6 from t1;
ID VALUE VALUE_NEXT_1 VALUE_NEXT_3 VALUE_NEXT_9
---------- ---------- ------------ ------------ ------------
10 101 102 201 303
10 102 103 202 0
10 103 201 202 0
20 201 202 203 0
20 202 202 301 0
20 202 203 302 0
20 203 301 303 0
30 301 302 0 0
30 302 303 0 0
30 303 0 0 0
10 linhas selecionadas.
6 comentários:
Muito bom, ajudou muito.......Obrigado
MUITO OBRIGADO!!! DEMAIS !!
Parabéns pelo post, excelente!
Top, muito obrigado.
Seria legal explicar todos os comandos usados, como o "range between unbounded preceding and unbounded following"
Cara, muito bem explicado e de maneira simples o conteúdo. Foi a melhor explicação que encontrei até hoje sobre ranking de dados.
Muito bom, ajudou muito.
Excelente texto!
Obrigada!
Postar um comentário