Evento Discovery Informix Brasil - Agosto/2010

Semana que vem ocorrerá o Discovery Informix Brasil, evento que apresentará os recursos e as novidades deste banco de dados, e que contará com as presenças de:


Jerry Keesee - Director of Informix Database Development - ("o cara que faz o informix")
Stuart Litel - International Informix Users Group – President
Miguel Carbone - International Informix Users Group – Board of Directors

Agenda:

Dia 26/08/2010 - São Paulo
Dia 27/08/2010 - Rio de Janeiro
Dia 31/08/2010 - Belo Horizonte

Maiores detalhes em:

http://www.imartins.com.br/informix/artigos/evento-discovery-informix-sao-paulo-rio-janeio-belo-horizonte
http://informixbr.blogspot.com/2010/08/evento-informix-em-2608-nao-perca.html

Concatenar strings em registros diferentes - WM_Concat

Na sua empresa há uma tabela de Funcionários e outra de dependentes, conjuge, filhos, etc...) de cada funcionário. Há, portanto, duas tabelas: Funcionario( Func_ID, Nome, Dt_Nasc) e Dependente(Dep_ID, Func_ID, Nome, Relacao). É um tradicional relacionamento 1-N.

Como exemplo:

TABELA FUNCIONARIO
ID	NOME	DT_NASC
1	João	25/12/1980
2	Maria	04/04/1987
3	Paulo	29/09/1984

TABELA DEPENDENTE
DEP_ID	FUNC_ID	NOME	RELACAO
1	1	Ana Maria	Conjuge
2	1	Ana Paula	Filho(a)
3	1	João Jr	Filho(a)
4	2	Carlos	Conjuge
5	3	Pedro	Filho(a)
6	3	Julia	Filho(a)

Então, preparando uma festa de dia das crianças para os filhos do funcionário, alguém solicita um relatório com o nome do funcionário e o nome de todos seus filhos. Detalhe: todos em uma mesma coluna. Portanto, para atender à solicitação, teríamos:

RELATÓRIO DOS FILHOS
NOME DO FUNCIONÁRIO	LISTA DE FILHOS(AS)
João	Ana Paula, João Jr
Maria
Paulo	Pedro, Julia

As tabelas de Funcionários e Dependentes podem ser criadas como views temporárias, apenas para uso nas queries de teste, pelo comando:

with funcionario as

(select 1 FUNC_ID, 'João' Nome, to_date('25/12/1980', 'dd/mm/yyyy') Dt_Nasc from dual union all
select 2 FUNC_ID, 'Maria' Nome, to_date('04/04/1987', 'dd/mm/yyyy') Dt_Nasc from dual union all
select 3 FUNC_ID, 'Paulo' Nome, to_date('29/09/1984', 'dd/mm/yyyy') Dt_Nasc from dual),
dependente as
(select 1 DEP_ID, 1 FUNC_ID, 'Ana Maria' Nome, 'Conjuge' Relacao from dual union all
select 2 DEP_ID, 1 FUNC_ID, 'Ana Paula' Nome, 'Filho(a)' Relacao from dual union all
select 3 DEP_ID, 1 FUNC_ID, 'João Jr' Nome, 'Filho(a)' Relacao from dual union all
select 4 DEP_ID, 2 FUNC_ID, 'Carlos' Nome, 'Conjuge' Relacao from dual union all
select 5 DEP_ID, 3 FUNC_ID, 'Pedro' Nome, 'Filho(a)' Relacao from dual union all
select 6 DEP_ID, 3 FUNC_ID, 'Julia' Nome, 'Filho(a)' Relacao from dual )


No Oracle há uma função não documentada, WM_CONCAT, que pode ser utilizada como uma função agregadora, da mesma forma que as habituais MAX, MIN, SUM e AVG. A query seria:

select f.nome, WM_CONCAT(d.nome)
  from funcionario f,
       dependente d
 where f.func_id = d.func_id
   and d.relacao = 'Filho(a)'
 group by f.nome;

Os valores são separados por vírgula. Caso seja necessário, a função REPLACE pode substituir a vírgula. eu usei para acrescentar um espaço após a vírgula, deste modo:

select f.nome, replace(WM_CONCAT(d.nome), ',', ', ')
  from funcionario f,
       dependente d
 where f.func_id = d.func_id
   and d.relacao = 'Filho(a)'
 group by f.nome;

Por último, uma palavra de precaução: como esta função não é documentada, a Oracle tem liberdade para numa próxima versão alterar o funcionamento ou simplesmente removê-la. Sendo assim, utilizá-la numa query para atender um demanda momentânea é seguro. Já utilizá-la em uma solução definitiva ou que deva rodar em várias versões de Oracle, é menos recomendável.

Funções Agregadas em janelas no SQL Server

Depois dos posts do Miguel procurei no SQL Server um recurso similar ao explicado por ele das janelas para funções agregadas. No SQL Server existe o OVER, entretanto com opções mais limitadas, não existindo a cláusula RANGE.
Para conseguir o mesmo resultado tive que utilizar sub-queries, o que tira toda a facilidade da operação. Para exemplificar o terceiro exemplo do Oracle apresentado pelo Miguel que tinha o seguinte SQL (http://sqlbrasil.blogspot.com/2010/07/funcoes-agregadas-em-janelas-no-oracle.html):

Select as_of_date, pais, num_users,
     sum(num_users) over(partition by pais
     order by as_of_date
     range extract(day from as_of_date)-1 preceding ) acum_mes
from tst_janela3
order by 1,2;
 
tive que resolver da seguinte maneira no SQL Server:
 
Select as_of_Date, pais, num_users,
      (Select sum(num_users)
         from tst_Janela3 b
       where b.as_of_date >= a.as_of_date - DAY(a.as_of_date) + 1
          and b.as_of_date <= a.as_of_date
          and a.pais = b.pais) as acum_mes
from tst_janela3 a
order by 1, 2;

Funções agregadas em janelas no Oracle - parte III (final)

Para concluir esta pequena série de posts, vou utilizar uma terceira tabela de exemplo. Nela, há o número de acessos por usuário a cada dia, divididos por diferentes países (Brasil, EUA e Canadá):

create table tst_janela3 as

select 'BRASIL' pais,
        to_date('31-12-2009', 'dd-mm-yyyy')+level as_of_date,           
        trunc( dbms_random.VALUE(100, 200) ) num_users
from dual
connect by level <= 365
union all
select 'EUA' pais,
       to_date('31-12-2009', 'dd-mm-yyyy')+level as_of_date,        
       trunc( dbms_random.VALUE(100, 200) ) num_users
from dual
connect by level <= 365
union all
select 'CANADA' pais,
       to_date('31-12-2009', 'dd-mm-yyyy')+level as_of_date,
       trunc( dbms_random.VALUE(100, 200) ) num_users
from dual
connect by level <= 365;

O requisito é praticamente o mesmo: obter o total de acessos acumulados a cada dia, contando sempre do dia primeiro do mês até o dia em questão. Ou seja, em 14/Março, deve-se obter a soma de acessos do dia 01/Março até 14/Março. No dia 15/Março, do dia 1o. até 15.  Em 01/Abril, "zera" o acumulador e começa a somar novamente.

Considerando apenas um país, a consulta adequada está abaixo. A janela utilizada é o dia-1. O "-1" é necessário para não incluir no cálculo o último dia do mês anterior, afinal no dia 15/Março precisamos somar os 14 dias precedentes e o corrente.

select as_of_date,

       num_users,
       sum(num_users) over(order by as_of_date
                           range extract(day from as_of_date)-1 preceding ) acum_mes
from tst_janela3
where pais = 'BRASIL';

Muito bem, mas o requisito não inclui o filtro para BRASIL. É necessário o valor para cada dia, para cada país. Nesta caso, deve-se utilizar a claúsula PARTITION BY, para manter os valores separados por país:

select as_of_date,
       pais,
       num_users,
       sum(num_users) over(partition by pais
                           order by as_of_date
                           range extract(day from as_of_date)-1 preceding ) acum_mes
from tst_janela3
order by 1,2;

Como todas funções analíticas, as consultas em janelas podem ser especialmente úteis em queries de relatórios e queries para alimentar sistemas de Data Warehouse. Até onde sei, são um recurso exclusivo do Oracle, mas o César, meu colega de blog, vai tentar provar que é simples fazer com o Microsoft SQL Server.

Funções agregadas em janelas no Oracle - parte II

A janela pode ser especificada de diversas formas. Uma relação completa pode ser encontrada no manual da Oracle, a partir da versão. Aqui coloco uma lista parcial das alternativas:

BETWEEN x PRECEDING AND y FOLLOWING
BETWEEN x PRECEDING AND y PRECEDING
BETWEEN CURRENT ROW AND y FOLLOWING
BETWEEN x PRECEDING AND CURRENT ROW
BETWEEN x PRECEDING AND UNBOUNDED FOLLOWING
BETWEEN UNBOUNDED PRECEDING AND y FOLLOWING
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
 
Por default, a última alternativa (em verde) é utilizada. Um detalhe, no exemplo que estou utilizando, há apenas um registro para cada dia. Assim, '10' PRECEDING representa os últimos 10 dias. Se houvesse um número de registros variável para cada dia, a janela deve ser especificada como um intervalo.
 
INTERVAL 'nn' DAY PRECEDING
INTERVAL 'nn' SECONDS FOLLOWING
INTERVAL 'nn' MONTH PRECEDING
 
Para exemplificar utilizo outra tabela com duzentos registros distribuídos aleatoriamente no mês de Janeiro.
 
create table tst_janela2 as
select to_date('31-12-2009', 'dd-mm-yyyy')+trunc(dbms_random.value(1,31)) as_of_date, trunc( dbms_random.VALUE(100, 200) ) num_users
from dual
connect by level <= 200;
 
O requisito é obter a média do número de usuários nos últimos 7 dias (6 precedentes e o dia corrente). A consulta é:
 
select distinct as_of_date,

sum(num_users) over (order by as_of_date
range interval '6' day preceding) media_semanal
from tst_janela2
order by 1;

Um último detalhe: digamos que, nesta resposta, é necessário obter o valor apenas para os sábados. A consulta acima deve ser uma visão (subconsulta) da principal. Se acrescentar um filtro (apenas sábados)  na clausula WHERE da consulta interna, as linhas dos demais dias da semana são retiradas do cálculo e o resultado final não será o desejado. Portanto, o correto é:

select as_of_date, media_semanal

from(
select distinct as_of_date,
sum(num_users) over (order by as_of_date
range interval '6' day preceding) media_semanal
from tst_janela2)
where to_char(as_of_date, 'D') = 7
order by 1;

Funções agregadas em janelas no Oracle - parte I

É bastante conhecida a utilização de funções agregadas em SQL, como MIN, MAX, SUM e AVG, mas o Oracle oferece um recurso interessante e bastante poderoso: definir um intervalo (janela) para a função. Começo com um exemplo bem simples, baseado em uma tabela que contém um registro para cada dia do ano:

create table tst_janela as

select to_date('31-12-2009', 'dd-mm-yyyy')+level as_of_date, trunc( dbms_random.VALUE(100, 200) ) num_users
from dual
connect by level <= 365;

O objetivo é obter para cada dia, a média dos últimos 10 dias. Ou seja, em 01/Jan, o resultado é a média dos valores entre 1 e 10/Jan. Já em 25/Jan, a média entre valores de 16 a 25/Jan. A função a ser utilizada é AVG. O problema é como definir as linhas que devem ser consideradas no cálculo, já que elas variam a  cada dia. Esta é a "janela" (intervalo, conjunto de linhas) da função. A consulta abaixo resolve o problema:

select as_of_date,

sum(num_users) over (order by as_of_date
range between '10' preceding and current row) media_10dias
from tst_janela;

Fundamental é compreender bem como a segunda coluna é calculada. Sum(num_users) dispensa comentários, mas a claúsula seguinte é chave da solução. Order by as_of_date é o critério como as linhas devem ser ordenadas. Range between '10' preceding and current row estabelece a janela começando 10 linhas (dias) atrás e terminando na linha corrente.

Consulta Database que utiliza um arquivo no SQL Server

Para descobrir qual o banco de dados (database) que está utilizando um arquivo no SQL Server, utilize o seguinte SQL:


Select Name from sys.databases a
 Where Exists
        (Select 1 from sys.master_files b
         where a.database_id = b.database_id
            and b.physical_name like '%NomeArquivo%')