Funções Analíticas em Oracle – parte 2

Na primeira parte deste artigo, resolvemos um problema usual que exigiu a utilização da cláusula OVER para podermos calcular uma função de grupo que extraiu informação em um agrupamento de dados.

Neste artigo vamos explorar um pouco mais o que pode ser feito com esta partícula. A primeira coisa é que pode-se extrair mais particionamentos em um único comando SELECT. Para isto basta utilizar quantas cláusulas OVER forem necessárias. Veja um exemplo:

Seu diretor adorou a qualidade a solução que você apresentou, mas, como ele notou que pode extrair mais informações do banco de dados, ele também gostaria de ver o maior valor de prestação da empresa. Desta forma, além de você mostrar o maior valor de prestação a cada mês e o respectivo contrato, ele também quer saber qual o maior valor de prestação de toda empresa.

Para resolver este problema, você terá que repetir o comando anterior e acrescentar uma nova coluna com o novo particionamento da busca. Neste caso, você ainda utilizar a função de grupo MAX, mas utilizará a cláusula OVER atuando na coluna CDEMPRESA. Veja como fica o comando:

SELECT dtvenc, cdempresa, cdcontr, vlprest, max_empresa

  FROM (

   SELECT to_char( dtvenc, 'MM-YYYY') dtvenc,

          cdcontr, vlprest,

          MAX( vlprest ) OVER

           (PARTITION BY to_char(dtvenc,'YYYYMM')) max_prest,

          MAX( vlprest ) OVER

           (PARTITION BY cdempresa ) max_empresa

     FROM vRECEBER

    WHERE dtvenc > sysdate ) WHERE vlprest = max_prest;

Funções Analíticas em Oracle – DENSE_RANK e LAG/LEAD

Vamos explorar algumas novas funções de grupo que podem ser utilizadas em conjunto com as funções analíticas em Oracle.

DENSE_RANK

Esta função de grupo permite que se extraia a posição (ranking) de valores de uma lista em função dos demais. Para isto basta informar a ordem através do qual o valor está ordenado. Esta situação é muito comum para, por exemplo, determinar o raniking das maiores prestações que iremos receber em um determinado mês. Veja o exemplo:

SELECT to_char( dtvenc, 'MM-YYYY') dtvenc, cdcontr, vlprest,

       DENSE_RANK() OVER

        (PARTITION BY to_char(dtvenc,'YYYYMM')

         ORDER BY vlprest ) dense_rank

  FROM vRECEBER

 WHERE dtvenc > sysdate;

Funções Analíticas em Oracle - Windowing

Neste artigo vamos explorar um pouco mais as funções analíticas no Oracle. Desta vez vamos explorar um pouco o recurso de windowing. Na realidade toda utilização de funções analíticas está baseada neste conceito. Uma cláusula analítica define o acesso a um grupo de linhas e a este grupo de linhas dá-se o nome de janela (window). Esta janela será utilizada para realizar os cálculos com base na linha especificada. Desta forma, nota-se que é possível especificar uma janela para cada linha de pesquisa.

Algumas das funções analíticas podem ser utilizadas para estabelecer uma janela de cálculo no Oracle. São elas: AVG, CORR, COVAR_POP, COVAR_SAMP, COUNT, FIRST_VALUE, LAST_VALUE, MAX, MIN, STDDEV, STDDEV_POP, STDDEV_SAMP, SUM, VAR_POP, VAR_SAMP e VARIANCE. Funções de regressão linear também podem ser utilizadas.

Estrutura

A cláusula de windowing é colocada após o OVER, depois de se especificar o particionamento e a ordem. Note que é necessário, quando se utiliza o windowing especificar uma ordem para que se possa estabelecer o intervalo de linhas que serão utilizadas como base para extrair os dados da janela.

Pode-se optar por utilizar RANGE (quando for um padrão lógico) ou ROWS (quando for físico). Estas cláusulas determinarão a forma e abrangência para cálculo do resultado. A grande vantagem está em se determinar “regiões” de busca. Desta forma, deve-se utilizar a cláusula BETWEEN para determinar o intervalo de ação do cálculo. Define-se o limite inferior e superior para se realizar o cálculo e pode-se utilizar expressões como:

- UNBOUNDED PRECEDING: desde o início da partição

- UNBOUNDED FOLLOWING: até o final da partição

- CURRENT ROW: linha atual

A combinação destas expressões com o BETWEEN determinarão a janela de consulta.

Quando trabalhamos com ROWS não há preocupação sobre o tipo de dados que se utiliza para a cláusula ORDER BY. O cálculo será realizado no grupo de linhas físico retornado na consulta.

SELECT to_char( dtvenc, 'MM-YYYY') dtvenc, cdcontr, vlprest,

       AVG( vlprest ) OVER

        (PARTITION BY to_char(dtvenc,'YYYYMM')

         ORDER BY to_char(dtvenc,'YYYYMM')

         ROWS BETWEEN

          UNBOUNDED PRECEDING AND CURRENT ROW ) avg_prest

  FROM vRECEBER

 WHERE dtvenc > sysdate;

Funções Analíticas em Oracle

Temos tratado nas últimas colunas sobre a extração de dados gerenciais no banco de dados Oracle. Uma boa forma de fazer esta extração é utilizar funções analíticas que já estão disponíveis há algumas versões do banco de dados.

Imagine que você tenha um sistema de cobrança na sua empresa. Seus recebimentos são baseados em contratos, ou seja, todos os meses você realiza vendas e, como as vendas são financiadas, você tem um fluxo freqüente de parcelas.

Você recebe a seguinte missão do diretor financeiro: “preciso saber quais são os contratos com os maiores valores de prestação a cada mês”. Seria relativamente simples oferecer esta resposta caso o diretor não tivesse incluído “contratos” na frase. Saber o valor das prestações a cada mês é muito simples.

Para resolver este problema, você precisará utilizar a partícula OVER do comando SELECT. Com esta opção é possível, além de extrair dados sumarizados, mostrar as linhas analíticas (detalhe) ao mesmo tempo.

Ao utilizar o OVER você poderá estabelecer uma “quebra” (ou particionamento) para sua análise. Veja no comando a seguir:

SELECT to_char( dtvenc, 'MM-YYYY') dtvenc, cdcontr,

       MAX( vlprest ) OVER (PARTITION BY to_char(dtvenc,'YYYYMM'))

       max_prest, vlprest

  FROM vRECEBER

 WHERE dtvenc > sysdate;

O destaque mostra a atuação da função d

Soluções de Business Intelligence com Oracle (2)

No artigo anterior, vimos como utilizar o pacote DBMS_PREDICTIVE_ANALYTICS para medir o grau de aderência de um conjunto de dados às previsões que se podem realizar com base nos dados de armazenado em um banco de dados Oracle.

Neste artigo, vamos ver como utilizar o Oracle Data Miner, uma ferramenta visual e gratuita da Oracle para extrair os mesmos dados, mas de uma maneira bem mais simpática.

O Oracle Data Miner pode ser obtido gratuitamente no Portal de desenvolvimento da Oracle: http://www.oracle.com/technology/products/bi/odm/odminer.html.A instalação é bastante simples, como tem sido com a maior parte das ferramentas da Oracle desenvolvidas em Java. Depois de criar um diretório (exemplo: c:\oracle\odm) e extrair os dados do arquivo ZIP, você deve clicar duas vezes em ODMINERW.EXE que se encontra no subdiretório BIN.

A primeira coisa a fazer é definir uma configuração de acesso ao banco de dados. Você encontrará a tela da figura 1:

Figura 1: Configuração da conexão com o banco de dados

Dê um nome à sua conexão, informe o usuário, senha, servidor, porta de comunicação e SID ou nome do serviço. Logo após clicar em OK, você terá a validação do seu ambiente. Em caso de falha, verifique os dados informados. Caso você tenha problemas, verifique com o administrador do seu ambiente se tudo está correto. Estes dados são os mesmos que você utiliza normalmente para se conectar ao banco de dados, portanto você não deverá encontrar dificuldades.

A figura 2 mostra a tela inicial do ODM.

Figura 2: Tela inicial do Oracle Data Miner

De todas as opções que aparecem, a primeira que nos interessa é “Data Sources”, pois aqui estarão os schemas que possuem as tabelas que serão analisadas.

Soluções de Business Intelligence com Oracle

As próximas colunas abordarão o conceito de Business Intelligence dentro do banco de dados Oracle.

Todos sabemos como é importante extrair informações gerenciais do banco de dados. Ter acesso aos dados transacionais (ou relacionais, como prefira) no banco de dados é uma realidade para praticamente todas as empresas.

Sistemas de gerenciamento empresarial (ERP) são responsáveis por armazenar e manter dados sob a rígida fiscalização e controle de processos. Isso faz com que as empresas consigam manter as operações básicas do dia-a-dia, como Folha de Pagamento, Recebíveis, Pagamentos e Controle Contábil. Porém, ter as operações controladas já não é suficiente em um ambiente de alta competitividade.

Para se tomar decisões, é necessário ter acesso ao maior volume de informações possível, dentro de um prazo razoável e de uma maneira permita analisar os dados com facilidade. Dentro desta perspectiva surgiu a necessidade de se adaptar os dados transacionais para que fosse possível extrair informações gerenciais.

Conceitualmente os primeiros são chamados de OLTP (Online Transaction Processing) e os segundos de DSS (Decision Support System).

Conceitos

Em uma rápida pesquisa na Internet, pode-se encontrar facilmente diversos conceitos relacionados ao Business Intelligence. Na Wikipedia encontra-se:

–        Data Warehouse (DW): sistema de computação utilizado para armazenar informação relativa às atividades de uma empresa em bancos de dados, de forma consolidada.

–        Data Mart: sub-conjunto de dados de um Data Warehouse. Geralmente são dados referentes a um assunto em especial ou diferentes níveis de sumarização.

–        Business Intelligence (BI): é um conjunto de metodologias de gestão implementadas através de ferramentas de software, cuja função é proporcionar ganhos nos processos decisórios gerenciais e da alta administração nas organizações.

Estas, dentre uma infinidade de outras definições, deixam claro o que se quer alcançar com estes três componentes básicos: a inteligência por trás dos dados. Em maior ou menor grau, com maior ou menor abrangência, ter acesso às informações armazenadas e extrair destes dados subsídios para tomada de decisão é fundamental para sobrevivência das empresas.

Necessidades

O mercado de BI é um dos que mais tem crescido ao longo dos últimos anos. Por este motivo os fornecedores de banco de dados têm procurado atender a esta demanda oferecendo produtos cada vez mais competitivos.

Por outro lado, o mercado de trabalho exige um profissional cada vez mais competente para lidar com esta demanda. Sem nos aprofundarmos neste aspecto, está claro que o profissional de BI deve aliar competências técnicas no banco de dados ao conhecimento do negócio da empresa.

Cada vez mais os produtos de BI deixam de ser uma “caixa preta” e passam a compor o negócio da empresa. Isso faz com que todos os profissionais envolvidos no processo assumam riscos e responsabilidades. No BI tradicional havia a preocupação em levar informações aos usuários. No atual, deve-se criar inteligência nos processos do negócio.

Processo

O processo básico que está por trás de um BI é identificar padrões nos dados armazenados. Com base nestes padrões é possível propor análises que possam prever o comportamento de um determinado perfil, seja cliente, produto, período, etc. Com isso é possível extrair informações importantes para a área financeira, marketing, segurança, etc.

Oracle 10g R 2

Nesta versão do Oracle foi disponibilizado o pacote DBMS_PREDICTIVE_ANALYTICS. Através deste pacote é possível medir o grau de aderência dos dados às previsões que se pode realizar.

A estrutura do pacote é:

PROCEDURE EXPLAIN

SQL e Programação de Banco de Dados

Apresentação

por Celso Henrique Poderoso de Oliveira

Caro amigo e leitor.

Quero me apresentar a você e iniciar um contato periódico para tratarmos assuntos relacionados à linguagem SQL e à programação de banco de dados. Trabalho com banco de dados há algum tempo e sei o quanto é importante trocar experiências com outros profissionais. Ninguém é capaz de extrair o máximo de um banco de dados, mas, juntos, podemos realizar mais e de uma maneira cada vez melhor.

Minha experiência maior tem sido desenvolvida junto ao banco de dados Oracle, mas a padronização do comando SQL e pelo fato de os conceitos de programação de um banco de dados serem facilmente aplicados a qualquer outro banco de dados, faz com que possamos tratar destes assuntos quase que universalmente.

SQL

A linguagem SQL (Structured Query Language) é a base para utilização de bancos de dados relacionais. Com a utilização dos comandos básicos (INSERT, DELETE, UPDATE e SELECT) pode-se resolver a maior parte dos problemas relacionados a manutenção e extração de dados no banco de dados. Com o SQL é possível criar as estruturas básicas de armazenamento, como tabelas e índices. Também há comandos específicos da linguagem para o controle e segurança relacionado a um banco de dados. Em princípio, os comandos SQL são divididos em:

·         DDL (Data Definition Language) ou Linguagem de definição de dados

·         DML (Data Manipulation Language) ou Linguagem de manipulação de dados

·         DQL (Data Query Language) ou Linguagem de recuperação de dados

·         DCL (Data Control Language) ou Linguagem de controle de dados

O SQL tem sido aprimorado ao longo do tempo. Duas entidades (ANSI – American National Standards Institute e ISO – International Standards Organization) vêm, desde 1986, publicando padrões de especificação da linguagem SQL.

Vamos discutir como abordar e tirar o máximo de proveito desta linguagem que é extremamente importante para todos os profissionais de banco de dados. Os principais tópicos