From: Mezei Zoltán <mezei.zoltan@telefor.hu>
To: pgsql-sql@postgresql.org
Subject: Two optimization questions
Date: Tue, 12 Sep 2006 12:23:58 +0200
Message-ID: <45068ABE.8040907@telefor.hu> (raw)
Hi,
I think it can be done better than I did and I want to learn...
1. I have a table that registers the history of messages:
output_message_history(id, event_type, event_time)
I need those ID-s from the table where there is one 'MESSAGE SENT' event
and one 'MESSAGE SUBMITTED' event and there are no more events on that
message.
select id
from output_message_history
group by content_id
having
sum(case when event_type='MESSAGE SENT' then 1 else 0 end) = 1
and
sum(case when event_type='MESSAGE SUBMITTED' then 1 else 0 end) = 1
and count(*) = 2
Can it be done another, more optimal way?
2. I have to create reports like this one:
time count
12:00 3
12:01 2
12:02 4
12:03 0 <--- now this one is problematic for me
12:04 5
So I need something like this:
select date_trunc('minute', crd), count(*) from subscriber
where crd between '2006-09-08' and '2006-09-12'
group by date_trunc('minute', crd)
But the output of this query won't show minutes with 0 count. I searched
the archives and found an example using a view to solve this problem,
but creating such a view for grouping by minutes or seconds doesn't seem
to be nice solution. Any ideas how to solve this problem?
Thanks for any answers,
Zizi
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mezei.zoltan@telefor.hu
Subject: Re: Two optimization questions
In-Reply-To: <45068ABE.8040907@telefor.hu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox