Message-ID: <45068ABE.8040907@telefor.hu>
Date: Tue, 12 Sep 2006 12:23:58 +0200
From: =?UTF-8?B?TWV6ZWkgWm9sdMOhbg==?= <mezei.zoltan@telefor.hu>
Organization: Telefor Kft.
User-Agent: Thunderbird 1.5.0.5 (Windows/20060719)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Two optimization questions
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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