artigo SQL Magazine 9 - Uso de Funções Analíticas no Oracle – Parte 2

Uso de Funções Analíticas no Oracle – Parte 2

Introdução

Na primeira parte deste artigo, publicado na edição anterior, as funções analíticas, utilizadas para facilitar as análises de negócios (Business Inteligence) e dar suporte a tarefas analíticas para apoio à decisão, tiveram seus conceitos gerais apresentados.

As funções referentes à classificação e cruzamento de informações serão apresentadas com detalhes nesta edição, merecendo atenção especial.

Funções Analíticas de Classificação

As funções analíticas de classificação são uma forma fácil de calcular a posição de cada registro em relação aos demais. Os registros podem ser classificados com base no valor (crescente ou decrescente) de uma determinada coluna ou com base em distribuição e percentis, como veremos no decorrer das funções apresentadas nesta seção.

RANK e DENSE_RANK

As funções RANK e DENSE_RANK classificam resultados de uma seleção com base em uma das colunas selecionadas (em ordem crescente (ASC) ou decrescente (DESC)) na cláusula ORDER BY, que é obrigatória. Os valores classificados ainda podem estar particionados em grupos através da cláusula PARTITION BY. Neste caso, a classificação recomeçará do valor 1 cada vez que o valor do conjunto de colunas da cláusula PARTITION BY se alterar. A Listagem 1 apresenta a definição dos comandos de classificação.

RANK ou DENSE_RANK ( )

OVER ([PARTITION BY lista de itens para partição]

             [ORDER BY  lista de itens que ordenam a classificação] [ASC ou DESC]

             [NULLS FIRST ou NULLS LAST])

Listagem 1 – Sintaxe dos comandos de classificação.

O exemplo da Listagem 2 classifica as cidades por quantidade de clientes aos finais de semana usando a função analítica de classificação RANK não particionada.

select RPAD(s.city,20) "Cidade",

       SUM(f.customer_count) "Cliente",

       RANK() OVER(ORDER BY SUM(f.customer_count) DESC) "Colocação"

from   times t, store s, sales_fact f

where  t.time_key = f.time_key and 

s.store_key = f.store_key and

t.day_of_week in ('Sunday', 'Saturday')

GROUP  BY s.city

ORDER  BY 3;

 Cidade      Cliente    Colocação

 ----------- ---------- --------------

 New York         22722              1

 Cincinnati       11443              2

 Louisville       10731              3

 ...

 Dallas            2276             20

Listagem 2 – Utilização de Função analítica RANK não particionada

O exemplo da Listagem 3 classifica as cidades por quantidade de clientes aos Sábados e Domingos. Para isto, faz uso da função analítica de classificação RANK particionada, onde a partição acontece por dia da semana, gerando assim uma classificação para o Sábado e outra para o Domingo.

select t.day_of_week "Dia Semana",

       RPAD(s.city,20) "Cidade",

       SUM(f.customer_count) "Cliente",

       RANK() OVER(PARTITION BY t.day_of_week ORDER BY SUM(f.customer_count) DESC) Colocação"

from   times t, store s, sales_fact f

where  t.time_key = f.time_key and

         s.store_key = f.store_key and

       t.day_of_week in (‘Sunday’, ‘Saturday’)

GROUP  BY t.day_of_week, s.city

ORDER  BY 1, 4;

Dia Semana  Cidade     Cliente Colocação

----------- ---------- ------- ---------

Saturday    New York     10756         1

Saturday    Cincinnati    5540         2

...

Saturday    Dallas        1084        20

Sunday      New York     11966         1

Sunday      Cincinnati    5903         2

...

Sunday      Dallas        1192        20

Listagem 3 – Utilização de Função analítica RANK particionada

A diferença no uso das funções RANK e DENSE_RANK está  no seu comportamento frente a classificações repetidas. Na função RANK, valores iguais recebem o mesmo número da linha do menor e a próxima classificação recebe o valor referente a sua posição no conjunto. Já com a função DENSE_RANK, a próxima linha receberá o seqüencial da classificação.

SELECT

         RPAD(ename,20)                "Nome do Funcionário",

         sal                                  "Salário",

         RANK() OVER(ORDER BY sal) "Classificação",

         DENSE RANK() OVER(ORDER BY sal) "Classificação Densa",

FROM emp

ORDER BY 4;

Nome do Funcionário      Salário          Classificação       Classificação Densa

-----------------------  -----------  ------------------  -------------------------

SMITH                                 800                    1                               1

JAMES                            950                    2                         2

ADAMS                           1100                    3                         3

WARD                            1250                    4                         4

MARTIN                          1250                    4                         4

MILLER                          1300                    6                         5

TURNER                          1500                    7                         6

...      

Listagem 4 – Comparativo entre a utilização das funções RANK e DENSE_RANK

CUME_DIST e PERCENT_RANK

As funções CUME_DIST e PERCENT_RANK classificam os resultados de uma seleção com base em distribuição cumulativa e percentis. Um percentil é uma medida da posição relativa de uma observação quando comparada com os outros valores do conjunto onde a observação está inserida. Por exemplo, se um estudante acerta 75% de uma avaliação e sua nota é o 40º percentil, significa que somente 40% da turma tiveram nota pior que este estudante e 60% saíram-se melhor do que ele. Existem diversas maneiras de se calcular percentis, entre elas a distribuição cumulativa e as fórmulas utilizadas para gerar os valores referentes aos percentis pelas funções CUME_DIST e PERCENT_RANK apresentadas na Listagem 5.

CUME_DIST = nº de valores no conjunto vindos antes do valor / tamanho do conjunto

PERCENT_RANK = posição da linha na partição – 1 / nº de linhas da partição - 1

Listagem 5 – Fórmulas utilizadas para geração dos valores pelas funções CUME_DIST e PERCENT_RANK

O exemplo da Listagem 6 apresenta uma consulta que retorna o nome do funcionário, salário e os valores de posição de acordo com as quatro funções de classificação apresentadas até este momento. Note que a ordem dos elementos na classificação é a mesma, o que varia é o valor atribuído à posição ocupada pelo elemento.

SELECT  ename  "Funcionário", sal "Salário",

        RANK()               OVER(ORDER BY sal DESC)        "Rank",

        DENSE_RANK()          OVER(ORDER BY sal DESC)            "Dense Rank",

        CUME_DIST()            OVER(ORDER BY sal DESC)            "Cume Dist",

        PERCENT_RANK()                OVER(ORDER BY sal DESC)            "Percent Rank"

FROM    Emp

ORDER   BY 2 DESC; 

Funcionário Salário   Rank   Dense Rank  Cume Dist    Percent Rank

----------- --------- ------ ----------- ------------ --------------

KING             5000      1           1   ,071428571              0

SCOTT            3000      2           2   ,214285714     ,076923077

FORD             3000      2           2   ,214285714     ,076923077

JONES            2975      4           3   ,285714286     ,230769231

BLAKE            2850      5           4   ,357142857     ,307692308

CLARK            2450      6           5   ,428571429     ,384615385

ALLEN            1600      7           6           ,5     ,461538462

TURNER           1500      8           7   ,571428571     ,538461538

MILLER           1300      9           8   ,642857143     ,615384615

WARD             1250     10           9   ,785714286     ,692307692

MARTIN           1250     10           9   ,785714286     ,692307692

ADAMS            1100     12          10   ,857142857     ,846153846

JAMES             950     13          11   ,928571429     ,923076923

SMITH             800     14          12            1              1

Listagem 6 – Exemplo comparativo de aplicação das funções RANK, DENSE_RANK, CUME_DIST e PERCENT_RANK

NTILE

A função NTILE possibilita a criação de tertis, quartis, decis e outros resumos estatísticos através da divisão dos elementos da população em faixas de acordo com o valor numérico atribuído como parâmetro para NTILE. Ou seja, caso o parâmetro atribuído para NTILE seja 4, ou NTILE(4), os elementos do conjunto de resposta da consulta serão divididos em 4 grupos ou faixas (tetris) e o retorno da função será em qual dos grupos ou faixas cada elemento se encontra. No exemplo apresentado na Listagem 7 os elementos serão divididos em 4 faixas (NTILE(4)) com base na ordem decrescente do valor de salário (ORDER BY sal DESC), fazendo com que os salários mais altos se localizem na primeira faixa de salários e os mais baixos na quarta faixa. Caso o NTILE recebesse o parâmetro 10 ao invés de 4, os elementos do conjunto resultante seriam distribuídos em 10 faixas (decis).

SELECT ename, sal, NTILE(4) OVER (ORDER BY sal DESC) Faixa

FROM    Emp

ORDER  BY sal DESC;

ENAME             SAL      FAIXA

---------- ---------- ----------

KING             5000          1

SCOTT            3000          1

…

ALLEN            1600          2

TURNER           1500          2

MILLER           1300          3

…

SMITH             800          4

Listagem 7 – Exemplo de utilização da função NTILE

ROW_NUMBER

A função ROW_NUMBER atribui um número de linha a cada linha da partição ou do conjunto de resultados. Esta seqüência é dada com base na coluna ou expressão do ORDER BY da cláusula OVER da função ROW_NUMBER. A Listagem 8 apresenta os nomes e salários dos funcionários em ordem dos maiores para os menores salários e o seqüencial da linha dentro do conjunto corresponde a esta ordenação. Caso houvesse a definição de partições, o seqüencial seria reiniciado a cada início de partição.

SELECT  ROW_NUMBER()

        OVER (ORDER BY sal DESC) Sequencia,

        ename, sal

FROM    Emp

ORDER   BY sal DESC;

   SEQUENCIA  ENAME       SAL 

   ---------- -------- ------

         1    KING       5000

         2    SCOTT      3000

         3    FORD       3000

   …

        14  SMITH         800

Listagem 8 – Exemplo de utilização da função ROW_NUMBER.

Tabulação Cruzada

Tabulação cruzada permite a criação de relatórios onde as informações são divididas por pontos de agregação e dimensões. Cada item constante da cláusula GROUP BY pode ter sua sumarização realizada gradativamente (do item mais à esquerda para o mais à direita na cláusula GROUP BY) através da função ROLLUP ou ter todos os seus elementos sumarizados de todas as maneiras possíveis (os totais de todas as combinações entre elementos da cláusula GROUP BY) através da função CUBE. Apresentaremos agora algumas funções analíticas relacionadas à tabulação cruzada.

ROLLUP

ROLLUP cria subtotais em todos os níveis de agregação para os elementos da cláusula GROUP BY. O primeiro nível de detalhamento equivale a uma cláusula GROUP BY padrão. A partir daí, os subtotais vão sendo gerados para os demais níveis e por fim um total geral é criado. Ou seja, no caso de 3 campos listados na cláusula GROUP BY (como no exemplo a seguir), 4 totalizações serão geradas (sempre número de colunas +1, onde mais 1 equivale ao total geral). O exemplo da Listagem 9 apresenta a quantidade de clientes e valor total de vendas nos três primeiros meses de 1998 e 1999, por região. As agregações e totalizações geradas estão em destaque no conjunto de resultados apresentado após o comando SELECT.

select    extract(year from t.transaction_date)                        "Ano",

          extract(month from t.transaction_date)              "Mês",

          RPAD(s.region,20)                                           "Região",

          SUM(f.customer_count)                                   "Qt. Clientes",

          SUM(f.sales)                                                  "Vendas"

from   times t, store s, sales_fact f

where  t.time_key  = f.time_key and

       s.store_key = f.store_key and

       extract(month from t.transaction_date) <= 3 and

       extract(year    from t.transaction_date) < 2000

GROUP  BY ROLLUP (extract(year from t.transaction_date),

                 extract(month from t.transaction_date) , s.region)

       Ano        Mês Região               Qt. Clientes     Vendas

---------- ---------- -------------------- ------------ ----------

      1998          1 Central                      1148    4985,37

      1998          1 East                         1671    8011,71

      1998          1 West                          810    4065,44

      1998          1                              3629   17062,52

      1998          2 Central                      1268    5270,62

      1998          2 East                         1554    7322,22

      1998          2 West                          873    4191,16

      1998          2                              3695      16784

      1998          3 East                         2180   10848,89

      1998          3 West                         1095    3868,45

      1998          3                              4657   20530,15

      1998                                        11981   54376,67

      1999          1 Central                      1265    6769,04

      1999          1 West                          998    5377,39

      1999          1                              4105   21901,35

      1999          2 Central                       902    4370,93

      1999          2 East                         1655    6484,92

      1999          2 West                          813    3575,78

      1999          3 Central                      1332    6076,29

      1999          3 East                         1842    8903,79

      1999          3 West                         1162     5889,8

      1999          3                              4336   20869,88

      1999                                        11811   57202,86

                                                  23792  111579,53

Listagem 9 – Utilização de agrupamento com ROLLUP

CUBE

A função CUBE calcula todas as possíveis combinações de subtotais para um conjunto de colunas de um agrupamento (dimensões). Ele é utilizado para gerar relatórios tabulares de referência cruzada com um único comando SELECT. Além da criação dos subtotais nas diversas combinações, um total geral também é gerado. No caso de 3 campos listados na cláusula GROUP BY (como no exemplo a seguir), 7 tipos de totalizações (ou agrupamentos) serão geradas (sempre 2 vezes o número de colunas agrupadas + 1 do total geral). A Listagem 10 apresenta a mesma declaração do comando SELECT utilizado para exemplificar a função ROLLUP, apenas substituindo ROLLUP por CUBE. Pode-se perceber que o resultado apresentado em ROLLUP é parte do resultado apresentado em CUBE.

select    extract(year from t.transaction_date)                        "Ano",

          extract(month from t.transaction_date)              "Mês",

          RPAD(s.region,20)                                           "Região",

          SUM(f.customer_count)                                   "Qt. Clientes",

from      times t, store s, sales_fact f

where     t.time_key  = f.time_key and

          s.store_key = f.store_key and

          extract(month from t.transaction_date) <= 3 and

          extract(year    from t.transaction_date) < 2000

GROUP  BY CUBE (extract(year from t.transaction_date),

               extract(month from t.transaction_date) , s.region)

ORDER  BY 1, 2, 3;

       Ano        Mês Região               Qt. Clientes     Vendas

---------- ---------- -------------------- ------------ ----------

      1998          1 Central                      1148    4985,37

      1998          1 East                         1671    8011,71

      1998          1 West                          810    4065,44

      1998          1                              3629   17062,52

      1998          2 Central                      1268    5270,62

      1998          2 East                         1554    7322,22

      1998          2 West                          873    4191,16

      1998          2                              3695      16784

      1998          3 Central                      1382    5812,81

      1998          3 East                         2180   10848,89

      1998          3                              4657   20530,15

      1998            Central                      3798    16068,8

      1998            East                         5405   26182,82

      1998            West                         2778   12125,05

      1998                                        11981   54376,67

      1999          1 Central                      1265    6769,04

      1999          1 West                          998    5377,39

      1999          1                              4105   21901,35

      1999          2 East                         1655    6484,92

      1999          2 West                          813    3575,78

      1999          2                              3370   14431,63

      1999          3 Central                      1332    6076,29

      1999          3 East                         1842    8903,79

      1999          3 West                         1162     5889,8

      1999          3                              4336   20869,88

      1999            Central                      3499   17216,26

      1999            West                         2973   14842,97

      1999                                        11811   57202,86

                    1 Central                      2413   11754,41

                    1 East                         3513   17766,63

                    1                              7734   38963,87

                    2 Central                      2170    9641,55

                    2 East                         3209   13807,14

                    2 West                         1686    7766,94

                    2                              7065   31215,63

                    3 Central                      2714    11889,1

                    3 West                         2257    9758,25

                    3                              8993   41400,03

                      East                        10744   51326,45

                      West                         5751   26968,02

                                                  23792  111579,53

Listagem 10 – Utilização de agrupamento com CUBE

RATIO_TO_REPORT

Calcula a proporção de um valor em relação ao valor total indicado por um função de agregação. O resultado dessa função é essencial em trabalhos analíticos, como identificação de market share (participação no mercado por empresas concorrentes) e distribuição de custos. A Listagem 11 apresenta o cálculo do total de vendas por produto em relação ao total geral de vendas. A sintaxe geral do comando é:

       Observe que na Listagem 11 o OVER aparece sem parâmetros; Nesse caso, o percentual será calculado sobre o somatório geral da cláusula indicada em expr.

select extract(year from t.transaction_date)                    "Ano",

       SUM(f.sales)                                                 "Vendas",

       SUM(SUM(f.sales)) OVER()                                 "Total Geral",

       ROUND (RATIO_TO_REPORT(SUM(f.sales)) OVER (),2) * 100   "% Total"

from   times t, store s, sales_fact f

where  t.time_key  = f.time_key and

       s.store_key = f.store_key

GROUP  BY extract(year from t.transaction_date)

ORDER  BY 1;

Ano      Vendas Total Geral % Total

---- ---------- ----------- -------

1998  782025,21  2235280,12      35  <1998 representou 35% das vendas no período selecionado> 1999  877593,81  2235280,12      39

2000   575661,1  2235280,12      26

Listagem 11 – Exemplo de utilização da função RATIO_TO_REPORT.

LEAD e LAG

As funções LEAD e LAG permitem comparações entre duas linhas dentro de um mesmo conjunto de dados. Na prática, estas funções são utilizadas para comparação de valores de faturamento ou custo mensal ou anual e variações orçamentárias. Estas funções são baseadas em posição e não em valor.

LAG() provê acesso à linha anterior de um determinado conjunto de resultados e  LEAD() provê acesso à linha posterior à posição atual. A Listagem 12 mostra, para cada ano, o valor total de vendas e os valores de venda do ano anterior (LAG) e posterior (LEAD) ao apresentado na linha corrente.

select extract(year from t.transaction_date)                                  "Ano",

       SUM(f.sales)                                                                     "Vendas",

       LAG(SUM(f.sales))  OVER(ORDER BY extract(year from t.transaction_date)) "Ano Anterior",

       LEAD(SUM(f.sales)) OVER(ORDER BY extract(year from t.transaction_date)) "Ano Seguinte"

from   times t, store s, sales_fact f

where  t.time_key  = f.time_key and

       s.store_key = f.store_key

GROUP  BY extract(year from t.transaction_date)

ORDER  BY 1;

   Ano    Vendas Ano Anterior Ano Seguinte

------ --------- ------------ ------------

  1998 782025,21                 877593,81

  1999 877593,81    782025,21     575661,1

  2000  575661,1    877593,81

Listagem 12 – Exemplo de utilização das funções LAG e LEAD.

Caso se deseje buscar não o valor imediatamente anterior ou posterior, mas um intervalo maior para qualquer uma das direções, pode-se fazer uso da opção offset, que determina qual o intervalo a ser resgatado. Se ao invés de LAG(SUM(f.sales)), do exemplo da Listagem 12, usarmos LAG(SUM(f.sales), 2), o valor a ser retornado equivale ao valor vendido dois anos antes do ano corrente.

Conclusões