Funções analíticas no Oracle Database
Introdução
No artigo de hoje vou comentar sobre Funções analíticas e darei um exemplo de como elas podem ser úteis para resolver problemas comuns e otimizar a performance das instruções SQL
As funções analíticas foram introduzidas no Oracle 8i e normalmente são utilizadas para substituir tarefas que antes eram realizadas por stored procedures ou funções (criadas em PL/SQL), para resolver problemas comuns, como por exemplo, transformar em colunas o resultado de múltiplas linhas (exemplo que será demonstrado mais adiante) ou classificar os valores de uma determinada coluna dentro de cada grupo de um conjunto de linhas (ver exemplos dos links das referências ao final do artigo).
As funções analíticas muitas vezes são confundidas com funções de agregação, portanto, é importante entender que a diferença principal entre elas é que as primeiras retornam múltiplas linhas para cada grupo dentro de uma instrução SQL, enquanto que, as segundas, retornam apenas uma linha para o grupo todo. A grande maioria das funções analíticas podem ser usadas como funções agregadas, e vice-versa. Uma restrição, é que as funções analíticas podem ser usadas somente na lista de colunas ou na cláusula ORDER BY de instruções SELECT.
Apesar de muitas vezes as funções analíticas não serem muito fáceis de usar, normalmente elas são mais performáticas do que stored procedures ou funções que criamos para resolver o mesmo problema. Além disso, é mais fácil usar algo que já está pronto e é bom, do que tentar reinventar a roda e correr o risco dessa roda sair meio quadrada!
Alguns exemplos de funções analíticas: AVG, FIRST, LAST, LISTAGG, MAX, MIN, PERCENT_RANK e RANK. Para ver outras funções, consulte as referências.
Utilizando funções analíticas
Segue abaixo um exemplo de instrução SQL (que demonstro nos treinamentos de SQL Tuning) para apresentar uma lista de pedidos e itens de pedidos, que deverá conter a data, código do pedido e código de cada produto (item) do pedido:
SELECT P.DT_PEDIDO,
P.CD_PEDIDO,
I.CD_PRODUTO
FROM ECOMMERCE.PEDIDO P
INNER JOIN ECOMMERCE.ITEM_PEDIDO I
ON P.CD_PEDIDO = I.CD_PEDIDO
WHERE P.CD_PEDIDO between 1900 and 1960
ORDER BY 1;Podemos observar que a instrução SQL acima é bem simples e que o seu resultado também é (ver abaixo o resultado parcial, contendo as 10 primeiras linhas, de um total de 60 que foram retornadas):
DT_PEDIDO CD_PEDIDO CD_PRODUTO
------------------------- ---------------- ------------------
19/09/2008 19:44:38 1956 1
23/09/2008 19:44:38 1919 1
23/09/2008 19:44:38 1919 2
23/09/2008 19:44:38 1919 3
24/09/2008 19:44:38 1915 3
24/09/2008 19:44:38 1915 4
27/09/2008 19:44:38 1960 1
27/09/2008 19:44:38 1960 2
27/09/2008 19:44:38 1960 3
27/09/2008 19:44:38 1960 4 No resultado parcial acima podemos observar que a maior parte dos pedidos possui mais de um produto. O pedido de número 1919 possui os produtos de códigos 1, 2 e 3. O pedido de número 1915 possui os produtos de códigos 3 e 4. O produto 1960 possui os produtos de códigos 1, 2, 3 e 4. Na minha opinião, a visualização deste resultado não está muito boa. Não seria mais fácil de ler e entender este resultado se todos os produtos do mesmo pedido retornassem na mesma linha? Eu acredito que sim, portanto, vou transformar o resultado, agrupando os produtos de cada pedido (3º coluna).
Essa transformação será feita inicialmente sem utilizar uma função analítica. Criaremos uma função chamada concatenate_list para agrupar os valores de cada pedido e veremos em seguida o tempo de execução de uma instrução SQL que irá chamá-la. Segue abaixo o código de criação da função concatenate_list e a instrução SQL que executaremos para chamá-la:
create or replace FUNCTION ECOMMERCE.concatenate_list
(p_cursor IN SYS_REFCURSOR)
RETURN VARCHAR2
IS
l_return VARCHAR2(32767);
l_temp VARCHAR2(32767);
BEGIN
LOOP
FETCH p_cursor
INTO l_temp;
EXIT WHEN p_cursor%NOTFOUND;
l_return := l_return || ',' || l_temp;
END LOOP;
RETURN LTRIM(l_return, ',');
END;
SELECT P.DT_PEDIDO,
P.CD_PEDIDO,
I.PRODUTOS
FROM ECOMMERCE.PEDIDO P
INNER JOIN (SELECT I1.CD_PEDIDO,
ECOMMERCE.concatenate_list(
CURSOR(SELECT I2.CD_PRODUTO
FROM ECOMMERCE.ITEM_PEDIDO I2
WHERE I2.CD_PEDIDO = I1.CD_PEDIDO
ORDER BY 1)) AS PRODUTOS
FROM ECOMMERCE.ITEM_PEDIDO I1
GROUP BY I1.CD_PEDIDO) I
ON P.CD_PEDIDO = I.CD_PEDIDO
WHERE P.CD_PEDIDO between 1900 and 1960
ORDER BY 1;
Após executar o SQL acima, veremos o resultado parcial abaixo:
DT_PEDIDO CD_PEDIDO PRODUTOS
------------------------ ------------ --------------
19/09/2008 19:44:38 1956 1
23/09/2008 19:44:38 1919 1,2,3
24/09/2008 19:44:38 1915 3,4
27/09/2008 19:44:38 1960 1,2,3,4O que você achou? Não ficou melhor? Agora temos como resultado 1 linha por pedido e na coluna PRODUTOS temos a relação de todos os produtos do pedido relacionado, separados pelo caractere vírgula. O tempo médio de execução deste SQL (executado 4 vezes) foi de 0,065s. Agora que tal tentarmos melhorar este tempo usando a função analítica LISTAGG?
A função LISTAGG foi criada no Oracle 11G, portanto, em versões anteriores você deverá ainda deverá utilizar a opção anterior (com a função concatenate_list ou coisa similar) ou usar uma função não documentada e sem suporte, chamada WM_CONCAT. Segue abaixo o código da instrução SQL anterior, substituindo o uso da função customizada concatenate_list pela função analítica LISTAGG:
SELECT P.DT_PEDIDO,
P.CD_PEDIDO,
LISTAGG(I.CD_PRODUTO, ',')
within group (ORDER BY 1) PRODUTOS
FROM ECOMMERCE.ITEM_PEDIDO I
INNER JOIN ECOMMERCE.PEDIDO P
ON I.CD_PEDIDO = P.CD_PEDIDO
WHERE P.CD_PEDIDO = 1960
GROUP BY P.DT_PEDIDO, P.CD_PEDIDO
ORDER BY 1;Ao executar a instrução SQL acima, o resultado foi igual ao do SQL anterior, porém existem 2 vantagens em utilizá-la. A 1ª é que a gente não precisou criar nenhuma função extra (função concatenate_list). A 2ª e principal vantagem foi o ganho no tempo de execução, que caiu para 0.043s (média de 4 execuções). Resumindo, conseguimos obter o mesmo resultado em um tempo 33,84% mais rápido.
Conclusão
Se você ainda não conhecia uma função analítica, minha dica é que você pesquise mais sobre o assunto e passe a utilizá-las sempre que possível! Se você conhecia e já está convencido de que em geral elas são mais performáticas, não há mais desculpas para deixar de usá-las!
Artigo original: http://www.fabioprado.net/2013/12/funcoes-analiticas-no-oracle-database.html
