Re: Query information needed

On Oct 13, 2005, at 21:50 , djzanky@gmail.com wrote:

> Dear all,
>
> I have a table created with this specifications:
>
> CREATE TABLE cdr (
>   calldate timestamp with time zone NOT NULL default now(),
>   clid varchar(80) NOT NULL default '',
>   src varchar(80) NOT NULL default '',
>   dst varchar(80) NOT NULL default '',
>   dcontext varchar(80) NOT NULL default '',
>   channel varchar(80) NOT NULL default '',
>   dstchannel varchar(80) NOT NULL default '',
>   lastapp varchar(80) NOT NULL default '',
>   lastdata varchar(80) NOT NULL default '',
>   duration bigint NOT NULL default '0',
>   billsec bigint NOT NULL default '0',
>   disposition varchar(45) NOT NULL default '',
>   amaflags bigint NOT NULL default '0',
>   accountcode varchar(20) NOT NULL default '',
>   uniqueid varchar(32) NOT NULL default '',
>   userfield varchar(255) NOT NULL default ''
> );
>
> I want to extract the number of calls placed in 1 hour and the average
> call duration
>
> I'm working with this query:
>
> SELECT date_trunc('hour',calldate), duration FROM cdr WHERE src=601
> ORDER BY calldate;
>
> i tried several other queries but i'm not able to count the number of
> calls in an hour (better in a time interval) and calculate the average
> duration.

For a first step, I'd build a view of

create view cdr_by_hour_view as
select date_trunc('hour,calldate) as callhour, duration
from cdr;

Then I'd select from this view using aggregates:

select callhour, avg(duration) as avg_duration
from cdr
where src = 601
group by callhour;

You could write it

This should help you with the simple case of just hourly averages.  
For averages on any given interval I think it's a bit more involved  
(as general cases generally are).

Hope this helps!

Michael Glaesemann
grzm myrealbox com