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%')

Trabalhando com Updatable Views no SQL Server - 2 de 2

Continuando post anterior (http://sqlbrasil.blogspot.com/2010/05/trabalhando-com-updated-views-no-sql.html)  vou demonstrar agora como criar Updatable Views no SQL Server:

Vamos criar um exemplo baseado em uma tabela de pedidos bem simplificada conforme instrução de create abaixo:

Create Table Pedidos

(DataPedido DateTime Not Null,
NroOrdem Int Not Null,
CodCliente Int,
ValorTotal Decimal(11,2),
Desconto Decimal(11,2),
DataEntrega DateTime,
Observacoes Varchar(80),
Primary Key (DataPedido, NroOrdem));

Vamos considerar "quebrar" esta tabela em três novas conforme a data do pedido

• Pedidos até 01/01/2009
• Pedido de 01/01/2009 até 31/12/2009
• Pedidos posteriores a 31/12/2009

Na verdade para garantir que os dados respeitarão estas"regras devemos acrescentar uma CHECK CONSTRAINT, abaixo o início da criação das três novas tabelas, os outros campos ficam iguais a tabela original.

Create Table PedidosAte2008

(DataPedido DateTime Not Null CHECK (DataPedido < '01/01/2009'),
...
Create Table Pedidos2009

(DataPedido DateTime Not Null CHECK (DataPedido between '01/01/2009' and '31/12/2009'),
...
Create Table PedidosApos2009

(DataPedido DateTime Not Null CHECK (DataPedido > '31/12/2009'),

A seguir insira os dados nas tabelas "quebradas" conforme a check constraint:


Insert into PedidosAte2008
   Select * From Pedidos
   Where DataPedido < '01/01/2009';

Insert into Pedidos2009
Se fores utilizar a view com o mesmo nome da tabela original, renomeie ou exclua a tabela pedidos.
Agora basta criar a view "juntando" os dados das três tabelas:

Create View Pedidos
    With SchemaBinding
as
   Select DataPedido, NroOrdem, CodCliente,ValorTotal,Desconto,DataEntrega,Observacoes
    from dbo.PedidosAte2008
Union All
  Select DataPedido, NroOrdem, CodCliente,ValorTotal,Desconto,DataEntrega,Observacoes
   from dbo.Pedidos2009
Union All
   Select DataPedido, NroOrdem, CodCliente,ValorTotal,Desconto,DataEntrega,Observacoes
    from dbo.PedidosApos2009;

Com a view criada é possível utilizar Insert, Update e Delete nela que o SQL Server irá se basear na check constraint para identificar qual das três tabelas que deverá ser alterada.

Como no exemplo utilizamos o mesmo nome da tabela original, os programas que utilizavam a tabela Pedidos não precisarão ser alterados.