Para melhor visualização, recomendo resolução de no mínimo 1280 x 800 e navegador Mozilla Firefox


sexta-feira, 13 de janeiro de 2017

Funções analíticas no Oracle: RANK, DENSE_RANK, ROW_NUMBER, FIRST_VALUE, LAST_VALUE, LAG e LEAD

Por Eduardo Legatti

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:

Anônimo disse...

Muito bom, ajudou muito.......Obrigado

Anônimo disse...

MUITO OBRIGADO!!! DEMAIS !!

Unknown disse...

Parabéns pelo post, excelente!

Anônimo disse...

Top, muito obrigado.

Seria legal explicar todos os comandos usados, como o "range between unbounded preceding and unbounded following"

Unknown disse...

Cara, muito bem explicado e de maneira simples o conteúdo. Foi a melhor explicação que encontrei até hoje sobre ranking de dados.

Nathania Heimoski disse...

Muito bom, ajudou muito.
Excelente texto!
Obrigada!

Postagens populares