Existe alguma função para agrupar por intervalo de dias ?

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Responder
Avatar do usuário
tiago_pimenta
Rank: DBA Júnior
Rank: DBA Júnior
Mensagens: 213
Registrado em: Qua, 29 Jun 2011 9:49 am
Localização: Barretos / SP

Pessoal, boa tarde.

Gostaria de saber se existe alguma função (Provavelmente Analítica) que possa me ajudar no seguinte problema. Preciso gerar um relatório, onde o usuário irá informar 4 parâmetros (Data Inicial, Quantidade de Dias do Período, Quantidade de Dias entre os Períodos e a Data Final) e o relatório dará a quantidade de registros antes da data inicial e a quantidade de registros de cada período até a data final, exemplo:

Selecionar tudo

Data Inicial: 01/01/2015
Dias do Período: 5
Dias entre os períodos: 2
Data Final: 31/01/2015
Então nesse exemplo, eu precisaria retornar a quantidade de registros antes do dia 01/01/2015, a quantidade de registros de 01/01/2015 até 06/01/2015 (Dt Inicial + 5), a quantidade de registros de 08/01/2015 (6 + 2) até 13/01/2015 e assim até a data final.

Até agora, a única ideia que tivemos, foi de criar uma função que através de um loop, cria-se uma estrutura com os períodos (Semanas) para posteriormente fazer um join com a tabela, mas acredito eu, que se tiver uma função que faça isso, será mais performática do que a solução que pensamos.

Att.
DanielNN
Moderador
Moderador
Mensagens: 641
Registrado em: Seg, 03 Set 2007 3:26 pm
Localização: Fortaleza - CE
att,

Daniel N.N.

Por acaso esse "Dias do Período = 5" e "Dias entre os períodos= 2" são fixos?
Representam os "dias úteis" e os "finais de semana" respectivamente?
Se sim a consulta ficará bem mais fácil.
Avatar do usuário
tiago_pimenta
Rank: DBA Júnior
Rank: DBA Júnior
Mensagens: 213
Registrado em: Qua, 29 Jun 2011 9:49 am
Localização: Barretos / SP

Daniel,

Serão variáveis, mas a ideia é realmente de ser o período semanal. A planilha que ela nos enviou como exemplo, os períodos começam na segunda e terminam na sexta, mas a última semana, estava com menos dias e foi questionado para ela o porque, ai ela explicou que sempre faz essa planilha na segunda feira de manha, pegando o período da segunda anterior até a sexta, mas que naquela semana em questão, pediram para ela a planilha na sexta de manha, pois teriam uma reunião na sexta a tarde.

Mas eu gostaria de ler o que você pensou.

[ ]s
DanielNN
Moderador
Moderador
Mensagens: 641
Registrado em: Seg, 03 Set 2007 3:26 pm
Localização: Fortaleza - CE
att,

Daniel N.N.

Fiz a consulta abaixa na qual você informa o período inicial e final e retiro os sábados e domingos.
Acredito que vá lhe atender.

Selecionar tudo

SELECT *
  FROM (SELECT dia
             , TO_CHAR(dia, 'd') dia_semana
             , TO_CHAR(dia, 'day') dia_da_semana
          FROM (SELECT data_inicial + LEVEL - 1 dia
                  FROM (SELECT TO_DATE(&DATA_INI, 'DD/MM/RRRR') data_inicial
                          FROM dual)
                CONNECT BY LEVEL <=  TO_DATE(&DATA_FIM, 'DD/MM/RRRR')  - TO_DATE(&DATA_INI, 'DD/MM/RRRR') + 1))
WHERE 1 = 1
  AND dia_semana NOT IN (1,7) /* não É DOMINGO (1) NEM SÁBADO(7) */
;
Renato Menezes Viana
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 128
Registrado em: Sáb, 18 Nov 2006 11:51 am
Localização: Rio de Janeiro - RJ

Pessoal,

Vale a pena dar uma olhada no link abaixo:

http://glufke.net/oracle/viewtopic.php? ... 992#p38992

Ver a partir da function Z_TIMESTAMP_DIF.

Vejam também no link abaixo a function NEXT_DAY

http://psoug.org/reference/date_func.html

Abs
Avatar do usuário
tiago_pimenta
Rank: DBA Júnior
Rank: DBA Júnior
Mensagens: 213
Registrado em: Qua, 29 Jun 2011 9:49 am
Localização: Barretos / SP

Pessoal, boa tarde.

Com ajuda do pessoal do grupo Oracle_BR, cheguei até o script abaixo:

Selecionar tudo

with periodos as (select 0 num_periodo,
                         to_date('01/01/1901', 'dd/mm/yyyy hh24:mi:ss') dt_ini_periodo,
                         to_date('01/01/2013', 'dd/mm/yyyy hh24:mi:ss') - 1 dt_fin_periodo
                  from dual
                  union
                  select level num_periodo,
                         to_date('01/01/2013', 'dd/mm/yyyy hh24:mi:ss') + ((5 + 2) * (level - 1)) dt_ini_periodo,
                         (to_date('01/01/2013', 'dd/mm/yyyy hh24:mi:ss') + ((5 + 2) * (level - 1)) - 1) + 5 dt_fin_periodo
                  from dual
                  connect by level < trunc((to_date('28/01/2013', 'dd/mm/yyyy hh24:mi:ss') - to_date('01/01/2013', 'dd/mm/yyyy hh24:mi:ss')) / 5)
                  order by 1 asc)

select bta.tipo_nome,
       pe.dt_ini_periodo || ' - ' || pe.dt_fin_periodo periodo,
       sum(1) total
from bt_amostra_pessoa bap, 
     bt_tipo_amostra bta, 
     bt_amostra ba,
     periodos pe
where bap.ampe_ativa = 'S'
  and bta.tipo_id = bap.ampe_tipo_id
  and bap.amostra_conferida = 'S'
  and bap.ampe_id = ba.ampe_id
  and bap.ampe_dt_coleta between pe.dt_ini_periodo and pe.dt_fin_periodo
group by bta.tipo_nome,
         pe.dt_ini_periodo || ' - ' || pe.dt_fin_periodo;
Só que agora estou com um outro problema, preciso que as linhas do período se transforme em colunas, ou seja, preciso que o resultado seja assim:

Selecionar tudo

NOME | 01/01/2013 - 05/01/2013 | 07/01/2013 - 11/01/2013
BLAH || 10 || 20
Tentei usando a função PIVOT, mas estou com dificuldade na clausula in do for, tentei colocar o select que está no WITH dentro do in, mas dá erro de ORA-00936. Já usando o PIVOT XML, eu tenho a possibilidade de colocar "ANY" dentro do IN, o que resolveria o problema, pois ele traria todos os resultados, mas ai a saída é em XML, e eu preciso usar esse select em um relatório (Reports).

Alguém tem alguma ideia de como posso contornar esse problema ou de uma outra maneira de fazer esse select acima ?

[ ]s
Avatar do usuário
tiago_pimenta
Rank: DBA Júnior
Rank: DBA Júnior
Mensagens: 213
Registrado em: Qua, 29 Jun 2011 9:49 am
Localização: Barretos / SP

Pessoal,

Desconsiderem meu último post *rs*, a opção Matriz do Reports faz o que preciso.

[ ]s
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 232
Registrado em: Ter, 19 Jun 2007 2:12 pm
Localização: São Paulo - SP

Bom dia amigos,

antes tarde do que nunca...

Eu estava quebrando a cabeça com PIVOT para passar parâmetro para o FOR ao invés de informações fixas, e só da com PIVOT XML.

Fuçando pela rede e fazendo um monte de testes consegui montar uma rotina.

Não é a coisa mais bonita do mundo mas funcionou.

Segue:

Selecionar tudo

create table Tmp_Vendas_Pivot(
Cd_fabr number(3),
dt_emis date,
qt_item number(12));

Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('1',to_date('01/04/2017','dd/mm/yyyy'),20467);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('1',to_date('02/04/2017','dd/mm/yyyy'),0);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('1',to_date('03/04/2017','dd/mm/yyyy'),53159);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('1',to_date('04/04/2017','dd/mm/yyyy'),59008);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('1',to_date('05/04/2017','dd/mm/yyyy'),52045);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('1',to_date('06/04/2017','dd/mm/yyyy'),52714);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('1',to_date('07/04/2017','dd/mm/yyyy'),51635);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('1',to_date('08/04/2017','dd/mm/yyyy'),21566);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('1',to_date('09/04/2017','dd/mm/yyyy'),0);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('2',to_date('01/04/2017','dd/mm/yyyy'),0);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('2',to_date('02/04/2017','dd/mm/yyyy'),0);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('2',to_date('03/04/2017','dd/mm/yyyy'),0);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('2',to_date('04/04/2017','dd/mm/yyyy'),0);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('2',to_date('05/04/2017','dd/mm/yyyy'),0);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('2',to_date('06/04/2017','dd/mm/yyyy'),166);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('2',to_date('07/04/2017','dd/mm/yyyy'),0);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('2',to_date('08/04/2017','dd/mm/yyyy'),0);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('2',to_date('09/04/2017','dd/mm/yyyy'),0);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('3',to_date('01/04/2017','dd/mm/yyyy'),4405);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('3',to_date('02/04/2017','dd/mm/yyyy'),0);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('3',to_date('03/04/2017','dd/mm/yyyy'),16213);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('3',to_date('04/04/2017','dd/mm/yyyy'),18545);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('3',to_date('05/04/2017','dd/mm/yyyy'),16103);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('3',to_date('06/04/2017','dd/mm/yyyy'),13802);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('3',to_date('07/04/2017','dd/mm/yyyy'),11159);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('3',to_date('08/04/2017','dd/mm/yyyy'),3770);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('3',to_date('09/04/2017','dd/mm/yyyy'),0);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('5',to_date('01/04/2017','dd/mm/yyyy'),4420);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('5',to_date('02/04/2017','dd/mm/yyyy'),0);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('5',to_date('03/04/2017','dd/mm/yyyy'),13574);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('5',to_date('04/04/2017','dd/mm/yyyy'),6120);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('5',to_date('05/04/2017','dd/mm/yyyy'),1004);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('5',to_date('06/04/2017','dd/mm/yyyy'),11133);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('5',to_date('07/04/2017','dd/mm/yyyy'),9693);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('5',to_date('08/04/2017','dd/mm/yyyy'),5840);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('5',to_date('09/04/2017','dd/mm/yyyy'),0);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('6',to_date('01/04/2017','dd/mm/yyyy'),266);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('6',to_date('02/04/2017','dd/mm/yyyy'),0);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('6',to_date('03/04/2017','dd/mm/yyyy'),774);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('6',to_date('04/04/2017','dd/mm/yyyy'),854);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('6',to_date('05/04/2017','dd/mm/yyyy'),791);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('6',to_date('06/04/2017','dd/mm/yyyy'),1170);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('6',to_date('07/04/2017','dd/mm/yyyy'),597);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('6',to_date('08/04/2017','dd/mm/yyyy'),76);
Insert Into Tmp_Vendas_Pivot(Cd_Fabr,Dt_Emis,Qt_Item) Values('6',to_date('09/04/2017','dd/mm/yyyy'),0);

Selecionar tudo

--Declarar a variável
variable v_resultado refcursor  
  
declare
 --
 v_sql     varchar2(32000);
 v_pivot   varchar2(32000); 
 --
 v_dt_inic varchar2(50) := 'to_date('||''''||'01/04/2017'||''''||','||''''||'dd/mm/yyyy'||''''||')';
 v_dt_fina varchar2(50) := 'to_date('||''''||'08/04/2017'||''''||','||''''||'dd/mm/yyyy'||''''||')';
 --
begin
  --
  select listagg( cd_fabr || ' fab'||lpad(to_Char(cd_Fabr),3,'0'), ',') within group (order by cd_fabr) 
  into   v_pivot
  from   (Select distinct cd_fabr from Tmp_Vendas_Pivot);
  --
  v_sql := 'select *
            from ( Select Cd_Fabr, dt_emis, round(Qt_item) qt_item
                   From   Tmp_Vendas_Pivot
                   where  dt_emis between '||v_dt_inic||' and '||v_dt_fina||')
            pivot
            (
             sum(qt_item) it
             for cd_fabr in ('||v_pivot||'))
             where  to_char(dt_emis,'||''''||'d'||''''||') <> 1
             order by dt_emis';
  --
  open :v_resultado for v_sql;
end;
/

-- Imprime o resultado
print v_resultado
Espere que sirva pra alguém.
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 21 visitantes