Somatório de horas

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Cayo Magno Fontana
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 23
Registrado em: Sex, 16 Nov 2007 8:12 am
Localização: ES

Boa tarde amigos,

Através de um sistema web em .NET, são inseridos, em uma tabela no oracle, registros de usuários sobre o tempo que eles estão logados em determinadas sub-aplicações.
Gostaria de fazer a somatória dos campos de hora dos registros da tabela.
Exemplo:

Registros na tabela:

Selecionar tudo

CAMPANHA MATRICULA DIA                             HORA

CLICK 21	847123	21/02/2008	01/01/0001 0:01:37
Mundo21	839301	22/02/2008	01/01/0001 0:01:23
CLICK 21	839301	22/02/2008	01/01/0001 0:01:16
Mundo21	847123	22/02/2008	01/01/0001 0:02:41
CLICK 21	847123	22/02/2008	01/01/0001 0:01:03
CLICK 21	839301	22/02/2008	01/01/0001 0:01:48
CLICK 21	847123	22/02/2008	01/01/0001 0:05:27
O campo campanha são as sub-aplicações, matrícula, obviamente, são as matrículas dos usuários, dia representa o dia que eles logaram, e por fim, a hora (está com data padrão 01/01/0001 que inseri junto de cada insert porque não estava conseguindo inserir sem data)

Em uma consulta em uma campanha X, usuário Y, e dia Z, me retornam mais de uma linha. Por exemplo:

Selecionar tudo

CLICK 21	847123	22/02/2008	01/01/0001 0:01:03
CLICK 21	847123	22/02/2008	01/01/0001 0:05:27
Gostaria de saber como faço para me retornar uma única linhas com as horas somadas, exemplo, neste caso acima, ficaria:

Selecionar tudo

CLICK 21	847123	22/02/2008	01/01/0001 0:06:30
Desde já agradeço a atenção.
rogenaro
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 232
Registrado em: Sex, 30 Mar 2007 7:26 pm
Localização: Londrina - PR
Rafael O. Genaro

Assumindo que sempre no campo hora irá constar a data 01/01/0001, e que você deseja agrupar seus resultados por Matricula, Campanha e Dia:

Selecionar tudo

select campanha
     , matricula
     , dia
     , tempo_login
     , trunc(tempo_login)||' dias '
     ||to_char(to_date('00010101', 'yyyymmdd') + (tempo_login), 'hh24:mi:ss')
from
(
  select campanha, matricula, dia, sum(hora - trunc(hora)) tempo_login
  from   t05
  where  matricula = 847123
  and    campanha  = 'CLICK 21'
  group by campanha, matricula, dia
);
rogenaro
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 232
Registrado em: Sex, 30 Mar 2007 7:26 pm
Localização: Londrina - PR
Rafael O. Genaro

ops.. teve um errinho na query..

Selecionar tudo

select campanha
     , matricula
     , dia
     , trunc(tempo_login)||' dias '
     ||to_char(to_date('00010101', 'yyyymmdd') + (tempo_login), 'hh24:mi:ss')
from
(
  select campanha, matricula, dia, sum(hora - to_date('00010101', 'yyyymmdd')) tempo_login
  from   t05
  where  matricula = 839301
  and    campanha  = 'CLICK 21'
  group by campanha, matricula, dia
);
Cayo Magno Fontana
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 23
Registrado em: Sex, 16 Nov 2007 8:12 am
Localização: ES

Rafael,

Muito obrigado pela sua ajuda, porém, a query interna responde corretamente, mas o INTERVALO está em tipo NUMBER.

Entendo que na query externa, fará a conversão para o tipo DATA, mas ocorre um erro.

Selecionar tudo

ORA-00933 - SQL command not property ended
Segue adaptação que fiz na tabela, apenas trocando alguns nomes para os originais

Selecionar tudo

select campanha 
     , matricula 
     , dt_dia 
     , trunc(intervalo)||' dias ' 
     ||to_char(to_date('00010101', 'yyyymmdd') + (intervalo), 'hh24:mi:ss') 
from tbl_wbo_log
( 
  select campanha, matricula, dt_dia, sum(intervalo - to_date('00010101', 'yyyymmdd')) intervalo 
  from   tbl_wbo_log 
  where  matricula = 839301 
  and    campanha  = 'CLICK 21' 
  group by campanha, matricula, dt_dia 
)
Cayo Magno Fontana
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 23
Registrado em: Sex, 16 Nov 2007 8:12 am
Localização: ES

Rafael, esqueça o comentário anterior.

Fiz as seguintes adaptações:

Selecionar tudo

select campanha 
     , matricula 
     , dt_dia 
     , trunc(intervalo)||' dias ' 
     ||to_char(to_date('00010101', 'yyyymmdd') + (intervalo), 'hh24:mi:ss') 
from 
( 
  select campanha, matricula, dt_dia, sum(intervalo - to_date('00010101', 'yyyymmdd')) intervalo 
  from   tbl_wbo_log 
  where  matricula = 839301 
  and    campanha  = 'CLICK 21' 
  group by campanha, matricula, dt_dia 
)  TBL_LOG
Ficou ótimo! Muito obrigado mesmo!
Cayo Magno Fontana
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 23
Registrado em: Sex, 16 Nov 2007 8:12 am
Localização: ES

Rafael, abri um tópico novo com o seguinte problema:

Boa noite amigos,

Antes de tudo, muito obrigado pela ajuda no tópico anterior. Rafael, valeu mesmo, foi extremamente útil.

Este problema creio que seja semelhante ao anterior. Nesta consulta,

Selecionar tudo

select 
      MATRICULA, 
      CAMPANHA, 
      SUBSTR(DT_FECHAMENTO, 0, 10) DT_FECHAMENTO
from   
      tbl_wbo_osrep
where
      matricula = matricula 
      and    campanha  = campanha
      and    status ='Fechado'
      and    dt_fechamento = dt_fechamento
tenho o seguinte retorno:

Selecionar tudo

MATRICULA  CAMPANHA            DT_FECHAMENTO

847123	   CLICK 21	21/02/2008
847123	   CLICK 21	21/02/2008
847123	   CLICK 21	21/02/2008
847123	   CLICK 21	21/02/2008
847123	   CLICK 21	21/02/2008
847123	   CLICK 21	21/02/2008
847123	   CLICK 21	21/02/2008
847123	   CLICK 21	22/02/2008
847123	   CLICK 21	22/02/2008
839301	   CLICK 21	22/02/2008
847123	   CLICK 21	22/02/2008
847123	   CLICK 21	22/02/2008
839301	   CLICK 21	22/02/2008
Como podemos notar, há varias linhas repetidas.

Gostaria que, nesta consulta, fosse retornado uma coluna adicional, sendo que, nesta nova consulta, nenhuma linha se repetisse. Porém nesta coluna adicional, tivesse o número de registros iguais encontrados na consulta. Seguindo o exemplo acima, ficaria assim:

Selecionar tudo

MATRICULA  CAMPANHA    DT_FECHAMENTO	QUANTIDADE

847123	   CLICK 21	21/02/2008	    7
847123	   CLICK 21	22/02/2008	    4
839301	   CLICK 21	22/02/2008	    2
Desde já, deixo meu muito obrigado novamente à questão anterior.

Poderia me dar um help novamente?
ruevers
Rank: DBA Sênior
Rank: DBA Sênior
Mensagens: 324
Registrado em: Sex, 02 Jun 2006 1:48 pm
Localização: sp
Contato:

Criança, primeiro...que se fosse o admin do forum, já teria trocado o seu topico, deveria estar em SQL e não PL/SQL.
Outra coisa...de novo SRs...desculpe o jeito...mas vou falar com ele...as vezes que puxa a orelha quer o bem...
Já tentou pesquisar.
já tentou outras coisas.
Já entrou no google??e digitou sobre o tema...
Quero que seja bom...mas vamos tentar algo e pensquisar antes de postar algo no forum...vamos subir o nível.
Tenho visto suas mensagens...a cada pedrinha que encontra, posta algo aqui...tenta se virar sozinho, este é o que faz diferente um DBA ou Analista de sistema das outras pessoas. Aposto que consegue e vai ver o prazer que é resolver um problema desses sozinho, se não conseguir, estou aqui pra te dar a direção e não a resposta.
Mas se continuar assim, desiste da informática e vai procurar outra profissão.
Cayo Magno Fontana
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 23
Registrado em: Sex, 16 Nov 2007 8:12 am
Localização: ES

Caro Anderson,

Vou dar uma breve resposta a sua repressão as minhas dúvidas Nem deveria porque estou super ocupado mas você é o tipo do cara que merece ;).

Primeiro lugar: não sou DBA E NEM pretendo, sou desenvolvedor em C# e nesta diretiva estou muito bem, estou no 8º período de Ciência da Computação. E "criança" como insiste em falar comigo em todos os tópicos... hahaha.. bem que gostaria de voltar a ser uma.

Segundo lugar: trabalho em uma grande empresa de telecomunicações, e tenho certeza de que, dependendo da sua região, você é um cliente nosso, e talvez nem saiba. Sou o ÚNICO na parte de desenvolvimento, de design e de banco. Portanto "GRANDE MESTRE" não tenho tempo para pesquisar pela internet as minhas dúvidas. Se tivesse tempo, teria, sem dúvidas, já feito há muito tempo. Recorro a este fórum sempre que tenho dúvidas e sempre tenho tido excelentes esclarecimentos.

As vezes, finais de semana, entro em fóruns de .NET como o MSDN-BR. Tiro dúvidas, muitas delas bastante "primárias", como pode ter sido pa você, quando postei aqui. Mas NUNCA reprimi alguém por isso.

Não preciso de lição de vida para como aprender sobre oracle, "GRANDE MESTRE" guarde seus conselhos tanto quanto agressivos para seus filhos, caso venha a ter/ou tenha. Mas se vier com conselhos construtivos, sempre estarei a disposição.

À TODOS DESTE FÓRUM, ME DESCULPEM PELAS PALAVRAS, E PEÇO DESCULPAS PELO CIDADÃO QUE COMEÇOU COM AS AGRESSÕES. VOCÊS SEMPRE ME AJUDARAM MUITO. À VOCÊS O MEU MUITO OBRIGADO. E ESTE TÓPICO CONTINUA EM ABERTO!!

UM GRANDE ABRAÇO
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Isso é uma coisa que nós moderadores sempre conversamos entre nós:
* Este é um local para se aprender e ensinar. É um local principalmente de REPOSTAS, mesmo que a resposta seja apenas um LINK.

Repostas como "procura no google", normalmente não ajudam ninguém. Pedimos a todos para ignorar os comentários acima e voltar ao assunto principal do tópico. Se isso não ocorrer, teremos que bloquear o tópico, e isso é uma coisa lastimável.

Pessoal, vamos ter calma... Se vocês acharem algum comentário estúpido, apenas ignore-o! Não vamos fazer confusão a toa ok?

DE VOLTA AO TOPICO!!!

tenta fazer um group by simples!

Selecionar tudo

select MATRICULA ,CAMPANHA ,DT_FECHAMENTO, count(*)
from sua_tabela
group by MATRICULA CAMPANHA DT_FECHAMENTO
O tópico será movido para o forum SQL!
Cayo Magno Fontana
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 23
Registrado em: Sex, 16 Nov 2007 8:12 am
Localização: ES

Boa tarde Thomas,

Antes de tudo, obrigado pela atenção e desculpe-me pelo incidente, da minha parte, isso nunca aconteceu antes e gostaria que jamais repetissemos o episódio aqui. Vamos ao que interessa.

Fiz essa query utilizando o group by assim Thomas:

Selecionar tudo

SELECT T1.MATRICULA, T1.CAMPANHA, MAX(T1.DT_INICIA) DT_INICIA, T1.LOGADO
FROM TBL_WBO_LOGONLINE T1
GROUP BY T1.MATRICULA, T1.CAMPANHA, T1.LOGADO
me retornou isto:

Selecionar tudo

839301	CLICK 21	25/02/2008 18:32:43	0
839301	CLICK 21	25/02/2008 18:32:30	1
847123	Mundo21	25/02/2008 16:50:34	0
847123	Mundo21	25/02/2008 21:49:04	1
847123	CLICK 21	25/02/2008 21:51:12	0
847123	CLICK 21	25/02/2008 21:50:34	1
Só que preciso que me retorne apenas:

Selecionar tudo

847123	CLICK 21	25/02/2008 21:51:12	0
839301	CLICK 21	25/02/2008 18:32:43	0
ou seja, para conseguir estes 2 registros, a query ficaria

Selecionar tudo

SELECT T1.MATRICULA, MAX(T1.DT_INICIA) DT_INICIA
FROM TBL_WBO_LOGONLINE T1
GROUP BY T1.MATRICULA
Mas desta forma, não tenho as informações de CAMPANHA e LOGADO, como tinha na query anterior.

Como conseguiria isso?
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Eu não tentei, mas isso deve funcionar:

Selecionar tudo

select x.*, (select CAMPANHA,LOGADO 
             from TBL_WBO_LOGONLINE y 
             where y.matricula=x.matricula 
               and y.dt_inicia=x.dt_inicia
               and rownum=1)
from 
  (
  SELECT T1.MATRICULA, MAX(T1.DT_INICIA) DT_INICIA 
  FROM TBL_WBO_LOGONLINE T1
  GROUP BY T1.MATRICULA
  ) x
Coloquei um sub-select na clausula SELECT que busca os campos que você está procurando, baseado na hora!
Cayo Magno Fontana
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 23
Registrado em: Sex, 16 Nov 2007 8:12 am
Localização: ES

Thomas,

Tentei o que você orientou, já até tinha feito algo parecido, mas sem o rownum que não entendo pra que serve.

Fazendo também da sua maneira, retorna um erro ORA-00913, que pelo que entendi, diz que a subquery retorna mais colunas que a query principal.

Mas se setamos somente os outro 2 campos, não consegui entender.
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

O rownum serve pra limitar o número de linhas em 1. Não pode retornar mais que uma linha.

Acho que o erro é que foi colocado mais de um campo no select de cima. tenta colocar só 1.
Cayo Magno Fontana
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 23
Registrado em: Sex, 16 Nov 2007 8:12 am
Localização: ES

Não estou conseguindo de jeito nenhum Thomas, tentei também fazer dessa maneira:

Selecionar tudo

select x.*, (select MATRICULA, LOGADO
             from TBL_WBO_LOGONLINE y 
             where y.matricula in (select x.matricula from x)
               and y.dt_inicia in (select x.dt_inicia from x) 
               AND ROWNUM=1
               ) 
from 
  ( 
  SELECT T1.MATRICULA, MAX(T1.DT_INICIA) DT_INICIA 
  FROM TBL_WBO_LOGONLINE T1 
  GROUP BY T1.MATRICULA 
  ) x
Seria interessante a utilização de um inner join? O que acha?
Cayo Magno Fontana
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 23
Registrado em: Sex, 16 Nov 2007 8:12 am
Localização: ES

Amigos, consegui!

Thomas, muito obrigado pela ajuda, vou postar a solução caso seja interesse de alguém posteriormente.

Primeiro, criei uma view com o select primário:

Selecionar tudo

CREATE VIEW VW_AUX_LOGONLINE
AS
SELECT T1.MATRICULA, MAX(T1.DT_INICIA) DT_INICIA 
FROM TBL_WBO_LOGONLINE T1 
GROUP BY T1.MATRICULA
Depois, montei um inner join da view com a tabela original, onde necessitávamos dos outros campos. Ficando assim:

Selecionar tudo

SELECT V1.MATRICULA, T1.CAMPANHA, V1.DT_INICIA, T1.LOGADO
FROM TBL_WBO_LOGONLINE T1 
INNER JOIN VW_AUX_LOGONLINE V1 
ON T1.MATRICULA = V1.MATRICULA AND T1.DT_INICIA = V1.DT_INICIA
Um abraço a todos!
rogenaro
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 232
Registrado em: Sex, 30 Mar 2007 7:26 pm
Localização: Londrina - PR
Rafael O. Genaro

Cayo,

Uma solução alternativa (neste caso, se uma mesma matrícula tiver dois registros com a mesma dt_inicio, apenas um deles será retornado):

Selecionar tudo

select t1.matricula
     , t1.campanha
     , t1.dt_inicia
     , t1.logado 
from
(
  select t1.matricula
       , t1.campanha
       , t1.dt_inicia
       , t1.logado 
       , row_number() over (partition by t1.matricula order by t1.dt_inicia desc) rnbr
  from   tbl_wbo_logonline t1
)
where rnbr = 1
;
Responder
  • Informação