GROUP BY campo do tipo DATE

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
thiago_r_f
Rank: Programador Pleno
Rank: Programador Pleno
Mensagens: 43
Registrado em: Qui, 17 Abr 2008 4:02 pm
Localização: Rio de Janeiro - RJ
O Backup é meu pastor e nada me faltará!
Thiago Rodrigues de Farias

Olá a todos!
Estou enfrentando um problema que até agora não encontrei coisa parecida na internet e queria ver se alguém já sofreu com isso ou dá uma idéia pra tentar resolvê-lo.
Na consulta abaixo ocorre o seguinte: quando considero o campo dt_reg_di (tipo DATE), a totalização dos campos em que ocorre a soma (SUM) gera um valor inferior ao valor da soma sem o campo citado. Ou seja, com o campo dt_reg_di alguns registros parecem ser descartados.
Já criei um índice pra ver se ajudava, mas não colaborou em nada. Gostaria de saber se existem outras maneiras de se agrupar um campo do tipo DATE para testar nessa consulta e ver se consigo atingir o valor correto.

Selecionar tudo

SELECT SUM(qt_estat),
  SUM(kg_liquido),
  SUM(vl_fob_us),
  SUM(vl_frete_us),
  SUM(vl_seguro_us)
FROM
  (SELECT TO_CHAR(di_teste_08.dt_reg_di, 'DD/MM/YYYY'),
--    TO_CHAR(to_date(SUBSTR(di_teste_08.dt_reg_di, 7, 2), 'RRRR'), 'RRRR') AS ano,
--    SUBSTR(di_teste_08.dt_reg_di, 4, 2) AS mês, 
    di_teste_08.co_ncm,
    di_teste_08.co_pais_orig,
    di_teste_08.co_pais_proc,
    bd.t100_ncm.co_ncm,
    SUM(di_teste_08.qt_estat) AS qt_estat,
    SUM(di_teste_08.kg_liquido) AS kg_liquido,
    SUM(di_teste_08.vl_fob_us) AS vl_fob_us,
    SUM(di_teste_08.vl_frete_us) AS vl_frete_us,
    SUM(di_teste_08.vl_seguro_us) AS vl_seguro_us
  FROM importacao.di_teste_08,
    bd.t100_ncm
  WHERE di_teste_08.co_ncm = t100_ncm.co_ncm
  GROUP BY TO_CHAR(di_teste_08.dt_reg_di, 'DD/MM/YYYY'),
--    TO_CHAR(to_date(SUBSTR(di_teste_08.dt_reg_di, 7, 2), 'RRRR'), 'RRRR'),
--    SUBSTR(di_teste_08.dt_reg_di, 4, 2),
    di_teste_08.co_ncm,
    di_teste_08.co_pais_orig,
    di_teste_08.co_pais_proc,
    t100_ncm.co_ncm
  );
O objetivo deste SQL vai além da soma dos 5 campos citados. Postei apenas a parte problemática pra facilitar a leitura.

Valeu a todos!
Abs.
NaPraia
Rank: Analista Júnior
Rank: Analista Júnior
Mensagens: 88
Registrado em: Sex, 22 Fev 2008 8:24 am
Localização: Floripa - SC

Selecionar tudo

SELECT TO_CHAR(di_teste_08.dt_reg_di, 'DD/MM/YYYY'),
    TO_CHAR(to_date(SUBSTR(di_teste_08.dt_reg_di, 7, 2), 'RRRR'), 'RRRR') AS ano,
    SUBSTR(di_teste_08.dt_reg_di, 4, 2) AS mês,
    di_teste_08.co_ncm,
    di_teste_08.co_pais_orig,
    di_teste_08.co_pais_proc,
    bd.t100_ncm.co_ncm,
    (di_teste_08.qt_estat) AS qt_estat,
    (di_teste_08.kg_liquido) AS kg_liquido,
    (di_teste_08.vl_fob_us) AS vl_fob_us,
    (di_teste_08.vl_frete_us) AS vl_frete_us,
    (di_teste_08.vl_seguro_us) AS vl_seguro_us
  FROM importacao.di_teste_08,
    bd.t100_ncm
  WHERE di_teste_08.co_ncm = t100_ncm.co_ncm
se você executar essa consulta sem agrupar, a soma de todos os registros que aparecem na consulta também é menor?
do que sem o campo do tipo data?
thiago_r_f
Rank: Programador Pleno
Rank: Programador Pleno
Mensagens: 43
Registrado em: Qui, 17 Abr 2008 4:02 pm
Localização: Rio de Janeiro - RJ
O Backup é meu pastor e nada me faltará!
Thiago Rodrigues de Farias

beleza NaPraia?
Cara, sem agrupar, o valor fica correto. O SQL utilizado foi esse aí embaixo:

Selecionar tudo

SELECT SUM(qt_estat),
  SUM(kg_liquido),
  SUM(vl_fob_us),
  SUM(vl_frete_us),
  SUM(vl_seguro_us)
FROM
  (SELECT di_teste_08.dt_reg_di,
    TO_CHAR(to_date(SUBSTR(di_teste_08.dt_reg_di, 7, 2), 'RRRR'), 'RRRR') AS ano,
    SUBSTR(di_teste_08.dt_reg_di, 4, 2) AS mês, 
    di_teste_08.co_ncm,
    di_teste_08.co_pais_orig,
    di_teste_08.co_pais_proc,
    bd.t100_ncm.co_ncm,
    di_teste_08.qt_estat AS qt_estat,
    di_teste_08.kg_liquido AS kg_liquido,
    di_teste_08.vl_fob_us AS vl_fob_us,
    di_teste_08.vl_frete_us AS vl_frete_us,
    di_teste_08.vl_seguro_us AS vl_seguro_us
  FROM importacao.di_teste_08,
    bd.t100_ncm
  WHERE di_teste_08.co_ncm = t100_ncm.co_ncm
  );
estou mexendo nele pra ver se a coisa vai melhorando. Na pior das hipóteses tentarei criar novos campos na tabela decompondo o campo DATE pra poder poder agrupar por mês e ano. Mas isso será a última coisa mermo, só se o prazo apertar e não aparecer solução.
Valeu.
thiago_r_f
Rank: Programador Pleno
Rank: Programador Pleno
Mensagens: 43
Registrado em: Qui, 17 Abr 2008 4:02 pm
Localização: Rio de Janeiro - RJ
O Backup é meu pastor e nada me faltará!
Thiago Rodrigues de Farias

E o erro persiste...
Agrupei aos poucos colocando SELECT dentro de SELECT e o erro persiste... E tudo indica que a causa é o campo dt_reg_di (tipo DATE).

Selecionar tudo

SELECT mês,
  SUM(qt_estat),
  SUM(kg_liquido),
  SUM(vl_fob_us),
  SUM(vl_frete_us),
  SUM(vl_seguro_us)
FROM
  (SELECT inf,
    campo,
    ano,
    mês,
    pais_orig,
    pais_proc,
    co_ncm,
    co_unid,
    SUM(qt_estat)AS qt_estat,
    SUM(kg_liquido)AS kg_liquido,
    SUM(vl_fob_us)AS vl_fob_us,
    SUM(vl_frete_us)AS vl_frete_us,
    SUM(vl_seguro_us)AS vl_seguro_us
  FROM
    (SELECT 'BR' AS inf,
      '1' AS campo,
      SUBSTR(TO_CHAR(di_teste_08.dt_reg_di, 'DD/MM/RRRR'), 7, 4) AS ano, -- Ano
      SUBSTR(TO_CHAR(di_teste_08.dt_reg_di, 'DD/MM/RRRR'), 4, 2) AS mês, -- Mês
      t200a.co_iso3166_3 AS pais_orig,                                   -- País Origem
      t200b.co_iso3166_3 AS pais_proc,                                   -- País Procedência
      di_teste_08.co_ncm AS co_ncm,
      bd.t100_ncm.co_unid AS co_unid,
      di_teste_08.qt_estat AS qt_estat,
      di_teste_08.kg_liquido AS kg_liquido,
      di_teste_08.vl_fob_us AS vl_fob_us,
      di_teste_08.vl_frete_us AS vl_frete_us,
      di_teste_08.vl_seguro_us AS vl_seguro_us
    FROM importacao.di_teste_08,
      bd.t100_ncm,
      bd.t200_pais t200a,
      bd.t200_pais t200b
    WHERE di_teste_08.co_ncm = t100_ncm.co_ncm (+)
    AND di_teste_08.co_pais_orig = t200a.co_pais(+)
    AND di_teste_08.co_pais_proc = t200b.co_pais(+)
      /*  GROUP BY 'BR',
      '1',
      SUBSTR(TO_CHAR(di_teste_08.dt_reg_di, 'DD/MM/RRRR'), 7, 4), -- Ano
      SUBSTR(TO_CHAR(di_teste_08.dt_reg_di, 'DD/MM/RRRR'), 4, 2), -- Mês
      t200a.co_iso3166_3,                                         -- País Origem
      t200b.co_iso3166_3,                                         -- País Procedência
      di_teste_08.co_ncm,
      bd.t100_ncm.co_unid */
    )
  GROUP BY inf,
    campo,
    ano,
    mês,
    pais_orig,
    pais_proc,
    co_ncm,
    co_unid
  )
GROUP BY mês
estou quase criando os campos na tabela...
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

Uma vez me aconteceu algo semelhante:
* Ao fazer um select SEM nenhum WHERE, ele retornava as linhas que eu queria. Quando eu filtrava usando um dos valores que apareceu, faltava umas linhas!!!

Daí, descobrimos o seguinte:
* havia um índice corrompido! Ao fazer SELECT * sem nenhum WHERE, ele fazia FULL_SCAN e buscava todas linhas da tabela. Ao filtrar, ele usava o índice e "perdia" a linha, por causa do índice corrompido.

A solução foi recriar o índice que tudo voltou ao normal. Isso é uma situação muito rara. Só vi isso acontecendo 2 vezes até hoje! Não sei dizer se é o mesmo caso aí... Mas que está estranho, está... O GROUP BY não deveria "perder" linhas...
thiago_r_f
Rank: Programador Pleno
Rank: Programador Pleno
Mensagens: 43
Registrado em: Qui, 17 Abr 2008 4:02 pm
Localização: Rio de Janeiro - RJ
O Backup é meu pastor e nada me faltará!
Thiago Rodrigues de Farias

Olá Thomas!
Vou fazer esse teste então... atualmente esta tabela possui um índice que atua sobre 3 campos, e um deles é o problemático.
Agora, aproveito pra perguntar: há diferença em criar um índice pra cada campo ao invés de criar um índice abrangendo todos os campos desejados?
Seriam as 2 situações:

Selecionar tudo


CRIAÇÃO DE ÍNDICES DO TIPO 1
---------------------------------------
CREATE INDEX IND_CO_NCM_DI_TESTE_08 ON DI_TESTE_08 (CO_NCM) TABLESPACE DADOS_DI;
CREATE INDEX IND_CO_CGC_DI_TESTE_08 ON DI_TESTE_08 (CO_CGC) TABLESPACE DADOS_DI;
CREATE INDEX IND_DT_REG_DI_DI_TESTE_08 ON DI_TESTE_08 (DT_REG_DI) TABLESPACE DADOS_DI;

====
  OU
====

CRIAÇÃO DE ÍNDICES DO TIPO 2
---------------------------------------
CREATE INDEX IMPORTACAO.IND_DI_TESTE_08
    ON IMPORTACAO.DI_TESTE_08 (CO_CGC, CO_NCM, DT_REG_DI)

Valeu a ajuda!
Abs
abonacin
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 7
Registrado em: Ter, 30 Set 2008 2:35 pm
Localização: são paulo - sp
Adriano Bonacin

Fala thiago...

A questão das colunas envolvidas no índice depende do tipo de consulta que você faz. Se você sempre consulta passando no WHERE os três campos, segue o jogo, crie um índice nas três colunas. Se por acaso, você usa alguma coluna em outra consulta, acho que seria mais conveniente criar o índice em uma só.

Sempre atendo ao fato de que se você criar o índice, como sugerido, ele não funcionará caso passe no WHERE o TO_CHAR...

falou
thiago_r_f
Rank: Programador Pleno
Rank: Programador Pleno
Mensagens: 43
Registrado em: Qui, 17 Abr 2008 4:02 pm
Localização: Rio de Janeiro - RJ
O Backup é meu pastor e nada me faltará!
Thiago Rodrigues de Farias

Valeu Adriano!!!
Já clareou mais o assunto! Vou colocar em prática esse esquema do índice pra ver qual é... mas de qualquer forma o pepino do campo do tipo DATE persistirá pois precisarei decompor a data para agrupar valores por mês e ano, através de uma função que invalidaria o índice, conforme dito por você.
Estou revirando a internet e lendo algumas coisas sobre índice tb pra ver se clareia mais. Vou até tentar usar um ANALYSE pra ver se ajuda tb.
Conforme o Thomas disse, pode estar ocorrendo algum problema de índices nessa tabela. Esta é uma tabela de 5.010.862 registros...
thiago_r_f
Rank: Programador Pleno
Rank: Programador Pleno
Mensagens: 43
Registrado em: Qui, 17 Abr 2008 4:02 pm
Localização: Rio de Janeiro - RJ
O Backup é meu pastor e nada me faltará!
Thiago Rodrigues de Farias

Senhores,
nada funcionou... mudei os índices, excluí todos, adicionei outros, fiz o analyze e não consigo fazer um GROUP BY no campo do tipo DATE sem que ocorra a exclusão de registros de maneira involuntária.
Agradeço a colaboração de todos, mas, por favor, continuem a ler este tópico pra compartilhar informações sobre esse problema.
Revirei a internet e não consegui indentificar a razão pela qual não é feito um GROUP BY de maneira correta na tabela.
Continuarei pesquisando.
Valeu!
Abs.
thiago_r_f
Rank: Programador Pleno
Rank: Programador Pleno
Mensagens: 43
Registrado em: Qui, 17 Abr 2008 4:02 pm
Localização: Rio de Janeiro - RJ
O Backup é meu pastor e nada me faltará!
Thiago Rodrigues de Farias

Senhores,
venho mais uma vez compartilhar minha luta com esta tabela de aproximadamente 6.000.000 de registros. Nada funcionou ainda!
Vários tipos de consultas foram montadas e sempre houve perda de registros no resultado final. Já criei um outro banco pra testar esta tabela e aquelas que são relacionadas a ela e ainda não ganhei esta luta. A única coisa boa dessa história é q já li pra caramba, e isso me trouxe uma série de conhecimentos novos.
A última tentativa foi particionar a tabela (por range partition) e até agora nada de bom... vou tentar desabilitar as constraints pra ver se algo interfere, tentar deixá-la crua e e ir adicionando constraints, índices e o q for necessário até ela resolver funcionar direito.
É isso aí! Continuo aceitando opiniões!
Espero voltar com notícias melhores!
Abs
thiago_r_f
Rank: Programador Pleno
Rank: Programador Pleno
Mensagens: 43
Registrado em: Qui, 17 Abr 2008 4:02 pm
Localização: Rio de Janeiro - RJ
O Backup é meu pastor e nada me faltará!
Thiago Rodrigues de Farias

Senhores,
não tive tempo de postar antes, mas após muito tempo de luta identifiquei um meio de a consulta dar certo. Na cláusula WHERE eu coloquei um range para o período desejado, ou seja dt_reg_di > valor_inicial ou dt_reg_di < valorfinal. Dessa maneira deu certinho. Não entendi ainda o porquê, mas dessa maneira está funcionando.
Valeu a todos q acompanharam o drama!
Abs
Avatar do usuário
Toad
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 253
Registrado em: Sex, 18 Nov 2005 2:14 pm
Localização: Seattle, WA
Contato:
Matheus Gonçalves
matheus.dev
twitter.com/developer__c

Thiago,

Você já tentou fazer o group by normal, sem a data [que, conforme você disse, funciona ok], mas usando um filtro HAVING e aí sim passar a restrição de data?
Responder
  • Informação
  • Quem está online

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