[pgbr-geral] Ajuda com Consulta
Bom dia a todos. Tenho uma tabela que registra o afastamento dos funcionários. Esta tabela está em linhas, ou seja, um registro para o inicio e um registro para a volta do funcionário. Preciso fazer uma consulta que traga dois campos com o período em que o funcionário ficou afastado. O primeiro registro encontrado seria o campo 1 e o registro seguinte (volta) o campo 2. As próximas ocorrências voltaria a ser 1 e 2 na sequência. Tenho os seguintes registros. Código Data 1 01/01/2014 2 01/02/2014 1 01/02/2014 1 01/03/2014 Preciso ter o seguinte resultado: Código DataSaida DataRetorno 1 01/01/2014 01/02/2014 1 01/03/2014 2 01/02/2014 Como posso fazer essa consulta? Estou utilizando a versão 9.3.4 do PostgreSQL. Obrigado. ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] Ajuda com Consulta
2015-02-02 9:14 GMT-02:00 Zan zan...@farmaponte.com.br: Preciso fazer uma consulta que traga dois campos com o período em que o funcionário ficou afastado. O primeiro registro encontrado seria o campo 1 e o registro seguinte (volta) o campo 2. As próximas ocorrências voltaria a ser 1 e 2 na sequência. Tenho os seguintes registros. Código Data 1 01/01/2014 2 01/02/2014 1 01/02/2014 1 01/03/2014 Preciso ter o seguinte resultado: Código DataSaida DataRetorno 1 01/01/2014 01/02/2014 1 01/03/2014 2 01/02/2014 Como posso fazer essa consulta? Isto está chorando por uso de WINDOW FUNCTIONS... Veja em [1] e [2] para mais detalhes. Eu faria algo do tipo: SELECT codigo, data_saida, data_retorno FROM ( SELECT codigo, data AS data_saida, lead(data) OVER(PARTITION BY codigo ORDER BY data) AS data_retorno, row_number() OVER(PARTITION BY codigo ORDER BY data) AS rn FROM sua_tabela ) t WHERE t.rn % 2 = 1 ORDER BY codigo, data_saida; A ideia é usar a função lead para recuperar a próxima data partindo da atual, depois filtrar a row_number para pegar apenas os números ímpares (t.rn%2 = 1), o que significa que é um registro de saída. De qualquer forma recomendo você mudar seu modelo para ao menos registrar o que é entrada e o que é saída, desta forma me parece um pouco fraco/frágil. Sem mais detalhes é difícil dizer, mas me parece que a melhor escolha seria exatamente como o resultado da consulta, uma tabela com as duas datas, deixando a data de retorno como NULL quando o funcionário ainda não retornou. [1] http://www.dextra.com.br/window-functions-no-postgresql-parte1/ [2] http://www.dextra.com.br/window-functions-no-postgresql-parte-2/ Atenciosamente, -- Matheus de Oliveira Analista de Banco de Dados Dextra Sistemas - MPS.Br nível F! www.dextra.com.br/postgres ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
[pgbr-geral] ajuda com consulta
Ola a todos da lista, Tenho uma tabela com possíveis combinações entre valores, e essas combinações repetem-se mas numa ordem distinta. Exemplo: 5;12842 5;62409 5;62410 12842;5 12842;62409 12842;62410 62409;5 62409;12842 62409;62410 62410;5 62410;12842 62410;62409 Como posso fazer uma consulta para selecionar apenas as combinações possíveis? 5;12842 5;62409 5;62410 Obrigado. Cumprimentos, Eloi ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] ajuda com consulta
Em 27/10/14, Eloi Ribeiroe...@openmailbox.org escreveu: Ola a todos da lista, Tenho uma tabela com possíveis combinações entre valores, e essas combinações repetem-se mas numa ordem distinta. Exemplo: 5;12842 5;62409 5;62410 12842;5 12842;62409 12842;62410 62409;5 62409;12842 62409;62410 62410;5 62410;12842 62410;62409 Como posso fazer uma consulta para selecionar apenas as combinações possíveis? 5;12842 5;62409 5;62410 Obrigado. Cumprimentos, Não entendi o resultado desejado. Por que, por ex., o par (12842, 62410) não faz parte do resultado? Osvaldo ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] ajuda com consulta
2014-10-27 11:07 GMT-02:00 Eloi Ribeiro e...@openmailbox.org: Tenho uma tabela com possíveis combinações entre valores, e essas combinações repetem-se mas numa ordem distinta. Exemplo: 5;12842 5;62409 5;62410 12842;5 12842;62409 12842;62410 62409;5 62409;12842 62409;62410 62410;5 62410;12842 62410;62409 Como posso fazer uma consulta para selecionar apenas as combinações possíveis? 5;12842 5;62409 5;62410 Não entendi porque 12842;62409, 12842;62410 e 62410;62409 não entram no resultado, poderia explicar melhor? Atenciosamente, -- Matheus de Oliveira Analista de Banco de Dados Dextra Sistemas - MPS.Br nível F! www.dextra.com.br/postgres ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] ajuda com consulta
Não entendi porque 12842;62409, 12842;62410 e 62410;62409 não entram no resultado, poderia explicar melhor? Não entendi o resultado desejado. Por que, por ex., o par (12842, 62410) não faz parte do resultado? Vou tentar explicar melhor. Esta tabela indica-me que: 5=12842 5=62409 5=62410 12842=5 12842=62409 12842=62410 62409=5 62409=12842 62409=62410 62410=5 62410=12842 62410=62409 Então se 5=12842, 5=62409 e 5=62410, já sei que 12842=62409, 12842=62410 e 62410=62409. Estes três últimos resultados estão a repetir a mesma informação. Esqueci-me de indicar isto no primeiro correio. Obrigado. Cumprimentos, Eloi ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] ajuda com consulta
2014-10-27 11:28 GMT-02:00 Eloi Ribeiro e...@openmailbox.org: Vou tentar explicar melhor. Esta tabela indica-me que: 5=12842 5=62409 5=62410 12842=5 12842=62409 12842=62410 62409=5 62409=12842 62409=62410 62410=5 62410=12842 62410=62409 Então se 5=12842, 5=62409 e 5=62410, já sei que 12842=62409, 12842=62410 e 62410=62409. Estes três últimos resultados estão a repetir a mesma informação. Agora está mais claro. Vamos lá, supondo a tabela dados(a int, b int), se não me enganei em algo, a seguinte consulta trará o resultado esperado: WITH elements(a,b) AS ( SELECT DISTINCT least(a, b), greatest(a, b) FROM dados ) SELECT e1.* FROM elements e1 WHERE NOT EXISTS(SELECT 1 FROM elements e2 WHERE e1.a = e2.b); a | b ---+--- 5 | 12842 5 | 62410 5 | 62409 (3 rows) Verifique em seus dados se atingem o resultado esperado. Atenciosamente, -- Matheus de Oliveira Analista de Banco de Dados Dextra Sistemas - MPS.Br nível F! www.dextra.com.br/postgres ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] ajuda com consulta
On 2014-10-27 15:13, Matheus de Oliveira wrote: Agora está mais claro. Vamos lá, supondo a tabela dados(a int, b int), se não me enganei em algo, a seguinte consulta trará o resultado esperado: WITH elements(a,b) AS ( SELECT DISTINCT least(a, b), greatest(a, b) FROM dados ) SELECT e1.* FROM elements e1 WHERE NOT EXISTS(SELECT 1 FROM elements e2 WHERE e1.a = e2.b); a | b ---+--- 5 | 12842 5 | 62410 5 | 62409 (3 rows) Verifique em seus dados se atingem o resultado esperado. Perfeito! Passei de 82978 resultados para 21407 esperados. Muito obrigado! Eloi ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] Ajuda com consulta
2013/7/26 Glauco Torres torres.gla...@gmail.com
Tenho o seguinte select que me traz todas as tabelas que possui o campo
cod_id:
select table_name from information_schema.columns
where table_schema not in(' pg_catalog ', ' information_schema ')
and(column_name = 'cod_id')
Isso pode te dar uma luz de como fazer seu select
Pois é essa consulta eu já tinha conseguido fazer ela, o problema é o
depois, mais mesmo assim obrigado.
O PostgreSQL não vai te dar o resultado que você espera de uma só vez, você
vai ter que usar a consulta acima para gerar o comando SQL para buscar os
dados nas tabelas.
Por exemplo (não testado):
SELECT 'SELECT ' || quote_ident(column_name) || '::text FROM '
|| quote_ident(table_schema) ||'.'||quote_ident(table_name) || '
UNION ALL '
FROM information_schema.columns
WHERE table_schema NOT IN(' pg_catalog ', ' information_schema ')
AND (column_name = 'id')
UNION ALL
SELECT 'SELECT * FROM (SELECT null::text LIMIT 0) AS last;' -- Gambiarra
para ultima linha
;
Com isso, basta executar essa consulta, gravar o resultado dela numa
variável, e executar a consulta dessa variável. Isso pode ser feito
facilmente via PL/pgSQL com o comando EXECUTE. Veja que converti tudo para
text, se você garantir que é tudo do mesmo tipo, nem vai precisar disso.
Atenciosamente,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres
___
pgbr-geral mailing list
pgbr-geral@listas.postgresql.org.br
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
[pgbr-geral] Ajuda com consulta
Bom dia Pessoal! Seguinte preciso encontrar em todas as minhas tabelas a coluna de nome 'numeronota' e trazer seus respectivos conteúdos. Pesquisei algumas coisas mais não encontrei nada para PostgreSQL, o nome disso se não estou enganado é reflexão de colunas. Grato Att Glauco Torres ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] Ajuda com consulta
Tenho o seguinte select que me traz todas as tabelas que possui o campo
cod_id:
select table_name from information_schema.columns
where table_schema not in(' pg_catalog ', ' information_schema ')
and(column_name = 'cod_id')
Isso pode te dar uma luz de como fazer seu select
Marcelo Silva
-
Desenvolvedor Delphi / PHP
Em 26 de julho de 2013 11:51, Glauco Torres torres.gla...@gmail.comescreveu:
Bom dia Pessoal!
Seguinte preciso encontrar em todas as minhas tabelas a coluna de nome
'numeronota' e trazer seus respectivos conteúdos.
Pesquisei algumas coisas mais não encontrei nada para PostgreSQL, o nome
disso se não estou enganado é reflexão de colunas.
Grato
Att Glauco Torres
___
pgbr-geral mailing list
pgbr-geral@listas.postgresql.org.br
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
--
Marcelo Silva
Desenvolvedor Delphi / PHP
My Postgres database
Cel.: (11) 99693-4251
___
pgbr-geral mailing list
pgbr-geral@listas.postgresql.org.br
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] Ajuda com consulta
Tenho o seguinte select que me traz todas as tabelas que possui o campo
cod_id:
select table_name from information_schema.columns
where table_schema not in(' pg_catalog ', ' information_schema ')
and(column_name = 'cod_id')
Isso pode te dar uma luz de como fazer seu select
Pois é essa consulta eu já tinha conseguido fazer ela, o problema é o
depois, mais mesmo assim obrigado.
___
pgbr-geral mailing list
pgbr-geral@listas.postgresql.org.br
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] Ajuda com consulta
Veja se consegue juntar ao select recursivo depois de listas as tabelas:
with recursive tabelarec (campos) as
(
select campos from tabela
where (condicao)
UNION ALL
select campos from tabela a
INNER JOIN tabela b ON(b.campo = a.campo)
select campos from tabelarec
where (condicao)
Em 26 de julho de 2013 14:26, Glauco Torres torres.gla...@gmail.comescreveu:
Tenho o seguinte select que me traz todas as tabelas que possui o campo
cod_id:
select table_name from information_schema.columns
where table_schema not in(' pg_catalog ', ' information_schema ')
and(column_name = 'cod_id')
Isso pode te dar uma luz de como fazer seu select
Pois é essa consulta eu já tinha conseguido fazer ela, o problema é o
depois, mais mesmo assim obrigado.
___
pgbr-geral mailing list
pgbr-geral@listas.postgresql.org.br
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
--
Marcelo Silva
Desenvolvedor Delphi / PHP
My Postgres database
Cel.: (11) 99693-4251
___
pgbr-geral mailing list
pgbr-geral@listas.postgresql.org.br
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] Ajuda com consulta
On 05/12/2012 17:16, Matheus de Oliveira wrote: 2012/12/5 Thiago zan...@farmaponte.com.br mailto:zan...@farmaponte.com.br Boa tarde. Tenho as seguintes tabelas: tb_mensagem id id_mensagem_pai dth_insert tb_mensagem_leitura id_mensagem dth_insert A tabela de mensagens, sempre que existe uma mensagem com id_mensagem_pai, significa que é uma resposta, as mensagens originais (primeiras) contém 0 (zero) no campo id_mensagem_pai. Na tabela tb_mensagem_leitura eu tenho um histórico de todas as vezes que o usuário leu a mensagem, sendo gravado no campo id_mensagem dessa tabela sempre o id da mensagem original. Preciso identificar quantas mensagens não lidas existe na para o usuário, sendo que uma mensagem não lida é: 1) sem registro na tabela tb_mensagem_leitura; 2) o registro da tabela tb_mensagem_leitura é com data menor que a última resposta da mensagem original. Alguém poderia me ajudar com este select? Ou até mesmo com a forma em que estruturei as tabelas, pois para chegar neste resultado está um pouco complicado. Cara, se eu não boiei na maionese, a consulta abaixo resolve o seu problema (não testado): WITH RECURSIVE mensagens AS ( SELECT id AS id_original, id, dth_insert FROM tb_mensagem WHERE id_mensagem_pai = 0 UNION ALL SELECT pai.id_original, filha.id http://filha.id, filha.dth_insert FROM tb_mensagem AS filha INNER JOIN mensagens AS pai ON pai.id http://pai.id = filha.id_mensagem_pai ), ultimas AS ( SELECT m.id_original, MAX(m.dth_insert) AS dth_insert FROM mensagens AS m GROUP BY m.id_original ) SELECT COUNT(*) FROM ultimas AS u LEFT JOIN tb_mensagem_leitura AS l ON l.id_mensagem = u.id_original WHERE l.id_mensagem IS NULL OR l.dth_insert u.dth_insert; Não me parece muito performático, uma forma de melhorar seria SEMPRE guardar o id da mensagem original em tb_mensagem (não só a da pai, mas a da primeira na hierarquia), o que evitaria toda essa sobrecarga da query recursiva e da outra sub-query, que na prática só estão pegando a data da última mensagem. Atenciosamente, Mateus, muito obrigado pela resposta. Acho que não expliquei direito. Na tabela tb_mensagem, o campo id_mensagem_pai é sempre o id da mensagem original. Todas as respostas estão amarradas a uma única mensagem e não em um formato de árvore. Dessa forma você acha que existe uma solução melhor com uma melhor performance? Essa será uma leitura muitas vezes executada, pois cada página do portal que o usuário acessa essa consulta é refeita para atualizar as mensagens não lidas. ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] Ajuda com consulta
On 06/12/2012 07:41, Thiago wrote: On 05/12/2012 17:16, Matheus de Oliveira wrote: 2012/12/5 Thiago zan...@farmaponte.com.br mailto:zan...@farmaponte.com.br Boa tarde. Tenho as seguintes tabelas: tb_mensagem id id_mensagem_pai dth_insert tb_mensagem_leitura id_mensagem dth_insert A tabela de mensagens, sempre que existe uma mensagem com id_mensagem_pai, significa que é uma resposta, as mensagens originais (primeiras) contém 0 (zero) no campo id_mensagem_pai. Na tabela tb_mensagem_leitura eu tenho um histórico de todas as vezes que o usuário leu a mensagem, sendo gravado no campo id_mensagem dessa tabela sempre o id da mensagem original. Preciso identificar quantas mensagens não lidas existe na para o usuário, sendo que uma mensagem não lida é: 1) sem registro na tabela tb_mensagem_leitura; 2) o registro da tabela tb_mensagem_leitura é com data menor que a última resposta da mensagem original. Alguém poderia me ajudar com este select? Ou até mesmo com a forma em que estruturei as tabelas, pois para chegar neste resultado está um pouco complicado. Cara, se eu não boiei na maionese, a consulta abaixo resolve o seu problema (não testado): WITH RECURSIVE mensagens AS ( SELECT id AS id_original, id, dth_insert FROM tb_mensagem WHERE id_mensagem_pai = 0 UNION ALL SELECT pai.id_original, filha.id http://filha.id, filha.dth_insert FROM tb_mensagem AS filha INNER JOIN mensagens AS pai ON pai.id http://pai.id = filha.id_mensagem_pai ), ultimas AS ( SELECT m.id_original, MAX(m.dth_insert) AS dth_insert FROM mensagens AS m GROUP BY m.id_original ) SELECT COUNT(*) FROM ultimas AS u LEFT JOIN tb_mensagem_leitura AS l ON l.id_mensagem = u.id_original WHERE l.id_mensagem IS NULL OR l.dth_insert u.dth_insert; Não me parece muito performático, uma forma de melhorar seria SEMPRE guardar o id da mensagem original em tb_mensagem (não só a da pai, mas a da primeira na hierarquia), o que evitaria toda essa sobrecarga da query recursiva e da outra sub-query, que na prática só estão pegando a data da última mensagem. Atenciosamente, Mateus, muito obrigado pela resposta. Acho que não expliquei direito. Na tabela tb_mensagem, o campo id_mensagem_pai é sempre o id da mensagem original. Todas as respostas estão amarradas a uma única mensagem e não em um formato de árvore. Dessa forma você acha que existe uma solução melhor com uma melhor performance? Essa será uma leitura muitas vezes executada, pois cada página do portal que o usuário acessa essa consulta é refeita para atualizar as mensagens não lidas. ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral Na verdade eu tenho um outro problema, que deixou as coisas um pouco mais difíceis. Na tabela de mensagem, existem os campos id_usuario_des (destinatário) e id_usuario_rem (remetente). Uma mensagem deve aparecer na caixa de entrada do usuário quando a mensagem original é id_usuario_des on existe pelo menos uma resposta da mensagem original como id_usuario_des igual ao usuário logado. Eu consegui chegar ao resultado desejado da seguinte forma: select count(*) from ( select tm.id_mensagem, max(tm.dth_insert) from ( select case when cr.id_mensagem_pai = 0 then cr.id else cr.id_mensagem_pai end as id_mensagem, cr.dth_insert from portal.tb_correio cr where cr.id_usuario_des = 2 ) as tm where ( select hl.id from portal.tb_correio_historico_leitura hl where hl.id_mensagem = tm.id_mensagem and hl.dth_insert tm.dth_insert and hl.dth_nao_lido is null and hl.id_usuario = 2 limit 1 ) is null group by tm.id_mensagem ) as tp Porém eu fico com medo quanto a questão de performance. No momento estou com poucas mensagens na base de dados, por isso a query está sendo executada rapidamente, mas daqui há alguns meses ela vai começar a ficar lenta. Alguma sugestão? Obrigado! ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] Ajuda com consulta
2012/12/6 Thiago zan...@farmaponte.com.br On 05/12/2012 17:16, Matheus de Oliveira wrote: 2012/12/5 Thiago zan...@farmaponte.com.br mailto:zan...@farmaponte.com.br Boa tarde. Tenho as seguintes tabelas: tb_mensagem id id_mensagem_pai dth_insert tb_mensagem_leitura id_mensagem dth_insert A tabela de mensagens, sempre que existe uma mensagem com id_mensagem_pai, significa que é uma resposta, as mensagens originais (primeiras) contém 0 (zero) no campo id_mensagem_pai. Na tabela tb_mensagem_leitura eu tenho um histórico de todas as vezes que o usuário leu a mensagem, sendo gravado no campo id_mensagem dessa tabela sempre o id da mensagem original. Preciso identificar quantas mensagens não lidas existe na para o usuário, sendo que uma mensagem não lida é: 1) sem registro na tabela tb_mensagem_leitura; 2) o registro da tabela tb_mensagem_leitura é com data menor que a última resposta da mensagem original. Alguém poderia me ajudar com este select? Ou até mesmo com a forma em que estruturei as tabelas, pois para chegar neste resultado está um pouco complicado. Cara, se eu não boiei na maionese, a consulta abaixo resolve o seu problema (não testado): WITH RECURSIVE mensagens AS ( SELECT id AS id_original, id, dth_insert FROM tb_mensagem WHERE id_mensagem_pai = 0 UNION ALL SELECT pai.id_original, filha.id http://filha.id, filha.dth_insert FROM tb_mensagem AS filha INNER JOIN mensagens AS pai ON pai.id http://pai.id = filha.id_mensagem_pai ), ultimas AS ( SELECT m.id_original, MAX(m.dth_insert) AS dth_insert FROM mensagens AS m GROUP BY m.id_original ) SELECT COUNT(*) FROM ultimas AS u LEFT JOIN tb_mensagem_leitura AS l ON l.id_mensagem = u.id_original WHERE l.id_mensagem IS NULL OR l.dth_insert u.dth_insert; Não me parece muito performático, uma forma de melhorar seria SEMPRE guardar o id da mensagem original em tb_mensagem (não só a da pai, mas a da primeira na hierarquia), o que evitaria toda essa sobrecarga da query recursiva e da outra sub-query, que na prática só estão pegando a data da última mensagem. Atenciosamente, Mateus, muito obrigado pela resposta. Acho que não expliquei direito. Também acho... =P Na tabela tb_mensagem, o campo id_mensagem_pai é sempre o id da mensagem original. Todas as respostas estão amarradas a uma única mensagem e não em um formato de árvore. Agora entendi, é mais simples então. Dessa forma você acha que existe uma solução melhor com uma melhor performance? Essa será uma leitura muitas vezes executada, pois cada página do portal que o usuário acessa essa consulta é refeita para atualizar as mensagens não lidas. Sim. A forma abaixo: SELECT COUNT(*) FROM ( SELECT CASE m.id_mensagem_pai WHEN 0 THEN m.id ELSE m.id_mensagem_pai END AS id_original, MAX(m.dth_insert) AS dth_insert FROM tb_mensagem AS m GROUP BY CASE m.id_mensagem_pai WHEN 0 THEN m.id ELSE m.id_mensagem_pai END ) AS u LEFT JOIN tb_mensagem_leitura AS l ON l.id_mensagem = u.id_original WHERE l.id_mensagem IS NULL OR l.dth_insert u.dth_insert; Eu modelaria na mensagem original o id_mensagem_pai = id ao invés de 0 na mensagem original, assim tira esse CASE daí. Atenciosamente, -- Matheus de Oliveira Analista de Banco de Dados PostgreSQL Dextra Sistemas - MPS.Br nível F! www.dextra.com.br/postgres ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] Ajuda com consulta
On 06/12/2012 09:30, Matheus de Oliveira wrote: 2012/12/6 Thiago zan...@farmaponte.com.br mailto:zan...@farmaponte.com.br On 05/12/2012 17:16, Matheus de Oliveira wrote: 2012/12/5 Thiago zan...@farmaponte.com.br mailto:zan...@farmaponte.com.br mailto:zan...@farmaponte.com.br mailto:zan...@farmaponte.com.br Boa tarde. Tenho as seguintes tabelas: tb_mensagem id id_mensagem_pai dth_insert tb_mensagem_leitura id_mensagem dth_insert A tabela de mensagens, sempre que existe uma mensagem com id_mensagem_pai, significa que é uma resposta, as mensagens originais (primeiras) contém 0 (zero) no campo id_mensagem_pai. Na tabela tb_mensagem_leitura eu tenho um histórico de todas as vezes que o usuário leu a mensagem, sendo gravado no campo id_mensagem dessa tabela sempre o id da mensagem original. Preciso identificar quantas mensagens não lidas existe na para o usuário, sendo que uma mensagem não lida é: 1) sem registro na tabela tb_mensagem_leitura; 2) o registro da tabela tb_mensagem_leitura é com data menor que a última resposta da mensagem original. Alguém poderia me ajudar com este select? Ou até mesmo com a forma em que estruturei as tabelas, pois para chegar neste resultado está um pouco complicado. Cara, se eu não boiei na maionese, a consulta abaixo resolve o seu problema (não testado): WITH RECURSIVE mensagens AS ( SELECT id AS id_original, id, dth_insert FROM tb_mensagem WHERE id_mensagem_pai = 0 UNION ALL SELECT pai.id_original, filha.id http://filha..id http://filha.id, filha.dth_insert FROM tb_mensagem AS filha INNER JOIN mensagens AS pai ON pai.id http://pai.id http://pai.id = filha.id_mensagem_pai ), ultimas AS ( SELECT m.id_original, MAX(m.dth_insert) AS dth_insert FROM mensagens AS m GROUP BY m.id_original ) SELECT COUNT(*) FROM ultimas AS u LEFT JOIN tb_mensagem_leitura AS l ON l.id_mensagem = u.id_original WHERE l.id_mensagem IS NULL OR l.dth_insert u.dth_insert; Não me parece muito performático, uma forma de melhorar seria SEMPRE guardar o id da mensagem original em tb_mensagem (não só a da pai, mas a da primeira na hierarquia), o que evitaria toda essa sobrecarga da query recursiva e da outra sub-query, que na prática só estão pegando a data da última mensagem. Atenciosamente, Mateus, muito obrigado pela resposta. Acho que não expliquei direito. Também acho... =P Na tabela tb_mensagem, o campo id_mensagem_pai é sempre o id da mensagem original. Todas as respostas estão amarradas a uma única mensagem e não em um formato de árvore. Agora entendi, é mais simples então. Dessa forma você acha que existe uma solução melhor com uma melhor performance? Essa será uma leitura muitas vezes executada, pois cada página do portal que o usuário acessa essa consulta é refeita para atualizar as mensagens não lidas. Sim. A forma abaixo: SELECT COUNT(*) FROM ( SELECT CASE m.id_mensagem_pai WHEN 0 THEN m.id http://m.id ELSE m.id_mensagem_pai END AS id_original, MAX(m.dth_insert) AS dth_insert FROM tb_mensagem AS m GROUP BY CASE m.id_mensagem_pai WHEN 0 THEN m.id http://m.id ELSE m.id_mensagem_pai END ) AS u LEFT JOIN tb_mensagem_leitura AS l ON l.id_mensagem = u.id_original WHERE l.id_mensagem IS NULL OR l.dth_insert u.dth_insert; Não deu certo pelo fato de que na tabela de leitura eu vou ter n registros de leitura, por esse motivo o join está multiplicando pelas quantidades de leitura. Eu modelaria na mensagem original o id_mensagem_pai = id ao invés de 0 na mensagem original, assim tira esse CASE daí. Puxa, essa idéia foi ótima, ajudou e muito. Eu cheguei a duas formas de fazer tal consulta: 1) select count(*) from ( select cr.id_mensagem_pai as id_mensagem, max(cr.dth_insert) as dth_insert from portal.tb_correio cr where cr.id_usuario_des = 2 group by cr.id_mensagem_pai ) as cr left join ( select hl.id_mensagem,max(hl.dth_insert) as dth_insert from portal.tb_correio_historico_leitura hl where hl.id_usuario = 2 group by hl.id_mensagem ) as hl on hl.id_mensagem = cr.id_mensagem where hl.id_mensagem is null or hl.dth_insert cr.dth_insert 2) select count(*) from ( select
[pgbr-geral] Ajuda com consulta
Boa tarde. Tenho as seguintes tabelas: tb_mensagem id id_mensagem_pai dth_insert tb_mensagem_leitura id_mensagem dth_insert A tabela de mensagens, sempre que existe uma mensagem com id_mensagem_pai, significa que é uma resposta, as mensagens originais (primeiras) contém 0 (zero) no campo id_mensagem_pai. Na tabela tb_mensagem_leitura eu tenho um histórico de todas as vezes que o usuário leu a mensagem, sendo gravado no campo id_mensagem dessa tabela sempre o id da mensagem original. Preciso identificar quantas mensagens não lidas existe na para o usuário, sendo que uma mensagem não lida é: 1) sem registro na tabela tb_mensagem_leitura; 2) o registro da tabela tb_mensagem_leitura é com data menor que a última resposta da mensagem original. Alguém poderia me ajudar com este select? Ou até mesmo com a forma em que estruturei as tabelas, pois para chegar neste resultado está um pouco complicado. Obrigado@ ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] Ajuda com consulta
2012/12/5 Thiago zan...@farmaponte.com.br Boa tarde. Tenho as seguintes tabelas: tb_mensagem id id_mensagem_pai dth_insert tb_mensagem_leitura id_mensagem dth_insert A tabela de mensagens, sempre que existe uma mensagem com id_mensagem_pai, significa que é uma resposta, as mensagens originais (primeiras) contém 0 (zero) no campo id_mensagem_pai. Na tabela tb_mensagem_leitura eu tenho um histórico de todas as vezes que o usuário leu a mensagem, sendo gravado no campo id_mensagem dessa tabela sempre o id da mensagem original. Preciso identificar quantas mensagens não lidas existe na para o usuário, sendo que uma mensagem não lida é: 1) sem registro na tabela tb_mensagem_leitura; 2) o registro da tabela tb_mensagem_leitura é com data menor que a última resposta da mensagem original. Alguém poderia me ajudar com este select? Ou até mesmo com a forma em que estruturei as tabelas, pois para chegar neste resultado está um pouco complicado. Cara, se eu não boiei na maionese, a consulta abaixo resolve o seu problema (não testado): WITH RECURSIVE mensagens AS ( SELECT id AS id_original, id, dth_insert FROM tb_mensagem WHERE id_mensagem_pai = 0 UNION ALL SELECT pai.id_original, filha.id, filha.dth_insert FROM tb_mensagem AS filha INNER JOIN mensagens AS pai ON pai.id = filha.id_mensagem_pai ), ultimas AS ( SELECT m.id_original, MAX(m.dth_insert) AS dth_insert FROM mensagens AS m GROUP BY m.id_original ) SELECT COUNT(*) FROM ultimas AS u LEFT JOIN tb_mensagem_leitura AS l ON l.id_mensagem = u.id_original WHERE l.id_mensagem IS NULL OR l.dth_insert u.dth_insert; Não me parece muito performático, uma forma de melhorar seria SEMPRE guardar o id da mensagem original em tb_mensagem (não só a da pai, mas a da primeira na hierarquia), o que evitaria toda essa sobrecarga da query recursiva e da outra sub-query, que na prática só estão pegando a data da última mensagem. Atenciosamente, -- Matheus de Oliveira Analista de Banco de Dados PostgreSQL Dextra Sistemas - MPS.Br nível F! www.dextra.com.br/postgres ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] Ajuda com consulta (VIEW)
2008/10/15 Wagner Bonfiglio [EMAIL PROTECTED] (...)Acho que todas as trivias tiveram pelo menos um voto, então não tem nenhuma trivia que satisfaça tv.idtrivia IS NULL... Bom dia Wagner. Sua VIEW não contém dados... Você diz que quer triviasnaovotadas mas diz que TODAS tiveram votos!? Tem certeza que é isso que quer? Acho que ainda não conseguiu descrever o que deseja... O que eu precisava era filtrar apenas as trivias que um determinado usuário não votou.. Acabei de fazer uma pequena mudança na consulta e ela funcionou: SAIU FROM rfc.trivias t LEFT OUTER JOIN rfc.triviavotos tv ENTROU FROM rfc.trivias t LEFT OUTER JOIN (select * from rfc.triviavotos where idclien = 8382) tv Assim aconteceu o que eu falei, entraram apenas os votos que o usuário 8382 deu.. Mas eu acho que não consigo colocar isso dentro de uma VIEW... Apenas para lembrar, eu preciso fazer uma consulta do estilo SELECT * FROM rfc.v_triviasnaovotadas WHERE idclien = 8382 LIMIT 1; E de preferência com ORDER BY random() dentro da VIEW também... Use a consulta que o Oswaldo sugeriu sem o WHERE... Recomendo não colocar ORDER BY dentro da VIEW. Ponha na sua consulta, pois em algum momento alguém pode querer ordenar de outra forma os mesmos dados, e estará gastando mais tempo que o necessário. Alguma idéia? Valeu, Wagner Bonfiglio ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral -- William Leite Araújo Analista de Banco de Dados - QualiConsult ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] Ajuda com consulta (VIEW)
2008/10/16 Wagner Bonfiglio [EMAIL PROTECTED] Olá William, o que eu quero é o seguinte... Eu tenho várias trivias e várias pessoas... Todas as trivias tiveram pelo menos 1 voto eu quis dizer, mas o que eu quero é que a VIEW me retorne uma trivia que determinado usuário não votou... No caso, caso eu consulte a VIEW sem um WHERE idclien = vai dar errado, talvez retornando muitos resultados, mas a minha real intenção é realmente buscar uma trívia que o usuário não tenha votado de modo randomico... Aha... entendi. Uma primeira opção seria : *SELECT* t.rowid, t.created, t.updated, t.idcriador, t.pergunta, t.resp1, t.resp2, t.resp3, t.resp4, t.respcerta, t.linkfoto, t.idfilmescorr, t.atorescorr, t.status, u.rowid as idclien *FROM* rfc.trivias t, rfc.usuarios u *WHERE *(t.rowid,u.rowid) *NOT IN* ( *SELECT* idtrivia, idclien *FROM * rfc.triviavotos) Consegui ser mais claro agora? Caso tenha alguma dúvida em aberto pode falar... Valeu, Wagner Bonfiglio 2008/10/16 William Leite Araújo [EMAIL PROTECTED] 2008/10/15 Wagner Bonfiglio [EMAIL PROTECTED] (...)Acho que todas as trivias tiveram pelo menos um voto, então não tem nenhuma trivia que satisfaça tv.idtrivia IS NULL... Bom dia Wagner. Sua VIEW não contém dados... Você diz que quer triviasnaovotadas mas diz que TODAS tiveram votos!? Tem certeza que é isso que quer? Acho que ainda não conseguiu descrever o que deseja... O que eu precisava era filtrar apenas as trivias que um determinado usuário não votou.. Acabei de fazer uma pequena mudança na consulta e ela funcionou: SAIU FROM rfc.trivias t LEFT OUTER JOIN rfc.triviavotos tv ENTROU FROM rfc.trivias t LEFT OUTER JOIN (select * from rfc.triviavotos where idclien = 8382) tv Assim aconteceu o que eu falei, entraram apenas os votos que o usuário 8382 deu.. Mas eu acho que não consigo colocar isso dentro de uma VIEW... Apenas para lembrar, eu preciso fazer uma consulta do estilo SELECT * FROM rfc.v_triviasnaovotadas WHERE idclien = 8382 LIMIT 1; E de preferência com ORDER BY random() dentro da VIEW também... Use a consulta que o Oswaldo sugeriu sem o WHERE... Recomendo não colocar ORDER BY dentro da VIEW. Ponha na sua consulta, pois em algum momento alguém pode querer ordenar de outra forma os mesmos dados, e estará gastando mais tempo que o necessário. Alguma idéia? Valeu, Wagner Bonfiglio ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral -- William Leite Araújo Analista de Banco de Dados - QualiConsult ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral -- William Leite Araújo Analista de Banco de Dados - QualiConsult ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] Ajuda com consulta (VIEW)
Uma primeira opção seria : *SELECT* t.rowid, t.created, t.updated, t.idcriador, t.pergunta, t.resp1, t.resp2, t.resp3, t.resp4, t.respcerta, t.linkfoto, t.idfilmescorr, t.atorescorr, t.status, u.rowid as idclien *FROM* rfc.trivias t, rfc.usuarios u *WHERE *(t.rowid,u.rowid) *NOT IN* ( *SELECT* idtrivia, idclien *FROM * rfc.triviavotos) Fiz alguns testes aqui e aparentemente funcionou! Retornou o mesmo resultado da VIEW original e quase 10x mais rápido (tempo confirmado pelo EXPLAIN ANALYZE)... Bom, vou fazer mais alguns testes antes de realmente fazer a mudança, porque lembro que a primeira versão da VIEW teve alguns problemas não pensados (e eu não lembro quais eram), e preciso garantir que nada aconteça né... Mas de qualquer forma agradeço a ajuda, caso algo aconteça volto a postar aqui ;) Abraço, Wagner Bonfiglio ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
[pgbr-geral] Ajuda com consulta (VIEW)
Boa tarde moçada... Seguinte, tenho uma consulta aqui que parece ser tranquila mas está me dando dor de cabeça... Eu tenho um sistema de QUIZ no meu site, em que tenho várias perguntas e tenho vários usuários respondendo as mesmas. As tabelas que precisam ser citadas: usuarios tem o campo rowid como chave primária trivias tem todas as informações da pergunta, respostas, etc, e também tem o rowid como chave primária triviavotos armazena as respostas que os clientes deram... tem o idtrivia (trivias.rowid), idclien (usuarios.rowid), status (certo 1 - errado 0), respdada (1,2,3,4) A questão é quando eu quero pegar uma nova pergunta para um determinado usuário... Eu pedi para o DBA fazer uma VIEW em que eu passe o rowid do usuario e ele me retorne uma pergunta que o usuário não tenha votado... A solução que ele achou foi essa: SELECT t.rowid, t.created, t.updated, t.idcriador, t.pergunta, t.resp1, t.resp2, t.resp3, t.resp4, t.respcerta, t.linkfoto, t.idfilmescorr, t.atorescorr, t.status, q1.idclien FROM rfc.trivias t, ( SELECT t.rowid AS idtrivia, u.rowid AS idclien FROM rfc.trivias t, rfc.usuarios u EXCEPT SELECT tv.idtrivia, tv.idclien FROM rfc.triviavotos tv) q1 WHERE t.rowid = q1.idtrivia; Vou admitir que eu não entendi essa consulta.. Nunca usei esse EXCEPT e não sei qual a funcionalidade dele... Mas a questão é que tenho cerca de 5 mil usuários e cerca de 500 perguntas e essa view já está demorando um bocado... Caso eu pudesse usar SQL diretamente eu usaria algo como: select * from rfc.trivias where rowid not in (select idtrivia from rfc.triviavotos where idclien = 8382) limit 1; Mas por algumas limitações eu não posso usar SQL direto, então preciso fazer uma VIEW para essa consulta. A questão é: como eu posso fazer uma view que eu digite apenas select * from rfc.v_triviasnaovotadas where idclien = 8382 limit 1; e esta consulta me retorne uma trívia que eu ainda não votei, de maneira mais rápida?? Se não for pedir demais, gostaria de entender porque a VIEW que o DBA criou está lenta, mas isso é mais curiosidade mesmo... Agradeço desde já, Wagner Mariotto Bonfiglio ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] Ajuda com consulta (VIEW)
2008/10/15 Wagner Bonfiglio [EMAIL PROTECTED]: usuarios tem o campo rowid como chave primária Hm, você já tem um problema aqui... essas tabelas têm chave natural declarada? triviavotos armazena as respostas que os clientes deram... tem o idtrivia [...] A questão é quando eu quero pegar uma nova pergunta para um determinado usuário... Eu pedi para o DBA fazer uma VIEW em que eu passe o rowid do usuario e ele me retorne uma pergunta que o usuário não tenha votado... A solução que ele achou foi essa: SELECT t.rowid, t.created, t.updated, t.idcriador, t.pergunta, t.resp1, t.resp2, t.resp3, t.resp4, t.respcerta, t.linkfoto, t.idfilmescorr, t.atorescorr, t.status, q1.idclien FROM rfc.trivias t, ( SELECT t.rowid AS idtrivia, u.rowid AS idclien FROM rfc.trivias t, rfc.usuarios u EXCEPT SELECT tv.idtrivia, tv.idclien FROM rfc.triviavotos tv) q1 WHERE t.rowid = q1.idtrivia; Vou admitir que eu não entendi essa consulta.. Nunca usei esse EXCEPT e não sei qual a funcionalidade dele... De acordo com a documentação http://www.postgresql.org/docs/8.3/interactive/sql-select.html é um MINUS, ou seja, uma exclusão; o primeiro SELECT interno verifica quais possíveis combinações de votos, o segundo exclui o que já ocorreu. Mas a questão é que tenho cerca de 5 mil usuários e cerca de 500 perguntas e essa view já está demorando um bocado... Teria de ver um plano de execução, mas ele está pegando todas as possíveis combinações que ainda não se realizaram... faça contas de padeiro, pode dar um volume de dados razoável, da ordem de 2 milhões de tuplas. como eu posso fazer uma view que eu digite apenas select * from rfc.v_triviasnaovotadas where idclien = 8382 limit 1; e esta consulta me retorne uma trívia que eu ainda não votei, de maneira mais rápida?? Traga o plano de execução, e pense numa junção externa talvez... -- skype:leandro.gfc.dutra?chat Yahoo!: ymsgr:sendIM?lgcdutra +55 (11) 3040 7344 gTalk: xmpp:[EMAIL PROTECTED] +55 (11) 9406 7191ICQ/AIM: aim:GoIM?screenname=61287803 BRAZIL GMT-3 MSN: msnim:[EMAIL PROTECTED] ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] Ajuda com consulta (VIEW)
moviemago= EXPLAIN ANALYZE select * from rfc.v_triviasnaovotadas where idclien = 8382 limit 1; QUERY PLAN --- Limit (cost=387174166.50..387174184.02 rows=1 width=252) (actual time=17577.132..17577.132 rows=1 loops=1) - Hash Join (cost=387174166.50..397535996.66 rows=591597 width=252) (actual time=17577.129..17577.129 rows=1 loops=1) Hash Cond: (outer.idtrivia = inner.rowid) - Subquery Scan q1 (cost=387174130.10..397527086.30 rows=591597 width=8) (actual time=17575.697..17575.697 rows=1 loops=1) Filter: (idclien = 8382) - SetOp Except (cost=387174130.10..396048092.56 rows=118319499 width=8) (actual time=16678.852..17386.845 rows=770652 loops=1) - Sort (cost=387174130.10..390132117.59 rows=1183194994 width=8) (actual time=16678.845..16952.476 rows=778157 loops=1) Sort Key: idtrivia, idclien - Append (cost=35.15..3803.68 rows=1183194994 width=8) (actual time=73.201..4883.079 rows=2325117 loops=1) - Subquery Scan *SELECT* 1 (cost=35.15..3652.00 rows=1183191360 width=8) (actual time=73.195..3927.630 rows=2318360 loops=1) - Nested Loop (cost=35.15..23723738.40 rows=1183191360 width=8) (actual time=73.191..2782.667 rows=2318360 loops=1) - Seq Scan on usuarios u (cost=0.00..59876.05 rows=1422105 width=4) (actual time=72.895..1140.204 rows=4840 loops=1) - Materialize (cost=35.15..43.47 rows=832 width=4) (actual time=0.000..0.110 rows=479 loops=4840) - Seq Scan on trivias t (cost=0.00..34.32 rows=832 width=4) (actual time=0.225..0.936 rows=479 loops=1) - Subquery Scan *SELECT* 2 (cost=0.00..151.68 rows=3634 width=8) (actual time=0.183..7.356 rows=6757 loops=1) - Seq Scan on triviavotos tv (cost=0.00..115.34 rows=3634 width=8) (actual time=0.179..4.026 rows=6757 loops=1) - Hash (cost=34.32..34.32 rows=832 width=248) (actual time=1.227..1.227 rows=479 loops=1) - Seq Scan on trivias t (cost=0.00..34.32 rows=832 width=248) (actual time=0.197..0.831 rows=479 loops=1) Total runtime: 23346.981 ms (19 rows) Como eu não tenho muitos conhecimentos não consigo analisar isso direito =/ Se tiver algum lugar que eu possa estudar os detalhes do EXPLAIN ANALYZE estou disposto a investir este tempo e adiar esta discussão para depois que eu tenha mais conhecimentos para isto... Valeu, Wagner Bonfiglio ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] Ajuda com consulta (VIEW)
Bom, comecei a dar uma olhada em : http://explain-analyze.info/ E na documentação... Vou ver o que consigo descobrir sozinho, mas só acho dificil conseguir melhorar a VIEW, nisso eu ainda gostaria de uma ajuda se possível =X Valeu, Wagner Bonfiglio On Wed, Oct 15, 2008 at 5:49 PM, Wagner Bonfiglio [EMAIL PROTECTED]wrote: moviemago= EXPLAIN ANALYZE select * from rfc.v_triviasnaovotadas where idclien = 8382 limit 1; QUERY PLAN --- Limit (cost=387174166.50..387174184.02 rows=1 width=252) (actual time=17577.132..17577.132 rows=1 loops=1) - Hash Join (cost=387174166.50..397535996.66 rows=591597 width=252) (actual time=17577.129..17577.129 rows=1 loops=1) Hash Cond: (outer.idtrivia = inner.rowid) - Subquery Scan q1 (cost=387174130.10..397527086.30 rows=591597 width=8) (actual time=17575.697..17575.697 rows=1 loops=1) Filter: (idclien = 8382) - SetOp Except (cost=387174130.10..396048092.56 rows=118319499 width=8) (actual time=16678.852..17386.845 rows=770652 loops=1) - Sort (cost=387174130.10..390132117.59 rows=1183194994 width=8) (actual time=16678.845..16952.476 rows=778157 loops=1) Sort Key: idtrivia, idclien - Append (cost=35.15..3803.68 rows=1183194994 width=8) (actual time=73.201..4883.079 rows=2325117 loops=1) - Subquery Scan *SELECT* 1 (cost=35.15..3652.00 rows=1183191360 width=8) (actual time=73.195..3927.630 rows=2318360 loops=1) - Nested Loop (cost=35.15..23723738.40 rows=1183191360 width=8) (actual time=73.191..2782.667 rows=2318360 loops=1) - Seq Scan on usuarios u (cost=0.00..59876.05 rows=1422105 width=4) (actual time=72.895..1140.204 rows=4840 loops=1) - Materialize (cost=35.15..43.47 rows=832 width=4) (actual time=0.000..0.110 rows=479 loops=4840) - Seq Scan on trivias t (cost=0.00..34.32 rows=832 width=4) (actual time=0.225..0.936 rows=479 loops=1) - Subquery Scan *SELECT* 2 (cost=0.00..151.68 rows=3634 width=8) (actual time=0.183..7.356 rows=6757 loops=1) - Seq Scan on triviavotos tv (cost=0.00..115.34 rows=3634 width=8) (actual time=0.179..4.026 rows=6757 loops=1) - Hash (cost=34.32..34.32 rows=832 width=248) (actual time=1.227..1.227 rows=479 loops=1) - Seq Scan on trivias t (cost=0.00..34.32 rows=832 width=248) (actual time=0.197..0.831 rows=479 loops=1) Total runtime: 23346.981 ms (19 rows) Como eu não tenho muitos conhecimentos não consigo analisar isso direito =/ Se tiver algum lugar que eu possa estudar os detalhes do EXPLAIN ANALYZE estou disposto a investir este tempo e adiar esta discussão para depois que eu tenha mais conhecimentos para isto... Valeu, Wagner Bonfiglio ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] Ajuda com consulta (VIEW)
Em 15/10/08, Wagner Bonfiglio[EMAIL PROTECTED] escreveu: Boa tarde moçada... Seguinte, tenho uma consulta aqui que parece ser tranquila mas está me dando dor de cabeça... Eu tenho um sistema de QUIZ no meu site, em que tenho várias perguntas e tenho vários usuários respondendo as mesmas. As tabelas que precisam ser citadas: usuarios tem o campo rowid como chave primária trivias tem todas as informações da pergunta, respostas, etc, e também tem o rowid como chave primária triviavotos armazena as respostas que os clientes deram... tem o idtrivia (trivias.rowid), idclien (usuarios.rowid), status (certo 1 - errado 0), respdada (1,2,3,4) A questão é quando eu quero pegar uma nova pergunta para um determinado usuário... Eu pedi para o DBA fazer uma VIEW em que eu passe o rowid do usuario e ele me retorne uma pergunta que o usuário não tenha votado... Avalie se esta alternativa é mais rápida: SELECT t.rowid, t.created, t.updated, t.idcriador, t.pergunta, t.resp1, t.resp2, t.resp3, t.resp4, t.respcerta, t.linkfoto, t.idfilmescorr, t.atorescorr, t.status, tv.idclien FROM rfc.trivias t LEFT OUTER JOIN rfc.triviavotos tv ON t.rowid = tv.idtrivia WHERE tv.idtrivia IS NULL; Se desejar em ordem aleatória acrescente um: ORDER BY random() Osvaldo ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] Ajuda com consulta (VIEW)
Ola Oswaldo, eu rodei exatamente a consulta que você falou e não me retornou nada. Ainda coloquei o id do usuário, pois eu preciso disso na função, e fiz um EXPLAIN ANALYZE... Eis o resultado: moviemago= EXPLAIN ANALYZE SELECT t.rowid, t.created, t.updated, t.idcriador, t.pergunta, moviemago- t.resp1, t.resp2, t.resp3, t.resp4, t.respcerta, t.linkfoto, moviemago- t.idfilmescorr, t.atorescorr, t.status, tv.idclien moviemago- FROM rfc.trivias t LEFT OUTER JOIN rfc.triviavotos tv moviemago- ON t.rowid = tv.idtrivia moviemago- WHERE tv.idtrivia IS NULL and tv.idclien = 8382; QUERY PLAN -- Nested Loop (cost=0.00..8.92 rows=1 width=252) (actual time=0.408..0.408 rows=0 loops=1) - Index Scan using idx_triviavotos_idclien on triviavotos tv (cost=0.00..5.89 rows=1 width=8) (actual time=0.407..0.407 rows=0 loops=1) Index Cond: (idclien = 8382) Filter: (idtrivia IS NULL) - Index Scan using trivias_pkey on trivias t (cost=0.00..3.01 rows=1 width=248) (never executed) Index Cond: (t.rowid = outer.idtrivia) Total runtime: 0.455 ms (7 rows) Depois fiz o EXPLAIN ANALYZE da consulta original, e acho que descobri o erro da consulta: moviemago= EXPLAIN ANALYZE SELECT t.rowid, t.created, t.updated, t.idcriador, t.pergunta, moviemago- t.resp1, t.resp2, t.resp3, t.resp4, t.respcerta, t.linkfoto, moviemago- t.idfilmescorr, t.atorescorr, t.status, tv.idclien moviemago- FROM rfc.trivias t LEFT OUTER JOIN rfc.triviavotos tv moviemago- ON t.rowid = tv.idtrivia moviemago- WHERE tv.idtrivia IS NULL; QUERY PLAN Hash Left Join (cost=126.00..184.39 rows=832 width=252) (actual time=9.593..9.593 rows=0 loops=1) Hash Cond: (outer.rowid = inner.idtrivia) Filter: (inner.idtrivia IS NULL) - Seq Scan on trivias t (cost=0.00..34.32 rows=832 width=248) (actual time=0.152..0.662 rows=479 loops=1) - Hash (cost=116.80..116.80 rows=3680 width=8) (actual time=7.681..7.681 rows=6772 loops=1) - Seq Scan on triviavotos tv (cost=0.00..116.80 rows=3680 width=8) (actual time=0.238..4.212 rows=6772 loops=1) Total runtime: 9.689 ms (7 rows) Acho que todas as trivias tiveram pelo menos um voto, então não tem nenhuma trivia que satisfaça tv.idtrivia IS NULL... O que eu precisava era filtrar apenas as trivias que um determinado usuário não votou.. Acabei de fazer uma pequena mudança na consulta e ela funcionou: SAIU FROM rfc.trivias t LEFT OUTER JOIN rfc.triviavotos tv ENTROU FROM rfc.trivias t LEFT OUTER JOIN (select * from rfc.triviavotos where idclien = 8382) tv Assim aconteceu o que eu falei, entraram apenas os votos que o usuário 8382 deu.. Mas eu acho que não consigo colocar isso dentro de uma VIEW... Apenas para lembrar, eu preciso fazer uma consulta do estilo SELECT * FROM rfc.v_triviasnaovotadas WHERE idclien = 8382 LIMIT 1; E de preferência com ORDER BY random() dentro da VIEW também... Alguma idéia? Valeu, Wagner Bonfiglio ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
