Skip site navigation (1) Skip section navigation (2)

Re: Call volume query

From: Jasen Betts <jasen(at)xnet(dot)co(dot)nz>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Call volume query
Date: 2009-01-31 12:03:30
Message-ID: gm1emi$9c0$1@reversiblemaps.ath.cx (view raw or flat)
Thread:
Lists: pgsql-general
On 2009-01-29, Mike Diehl <mdiehl(at)diehlnet(dot)com> wrote:
> Hi all.
>
> I've encountered an SQL problem that I think is beyond my skills...
>
> I've got a table full of records relating to events (phone calls, in
> this case) and I need to find the largest number of events (calls)
> occurring at the same time.

one time when this occurred time this happened will be immediately
after the start of one of the calls.

> The table had a start timestamp and a duration field which contains the
> length of the call in seconds.
>
> I need to find out how many concurrent calls I supported, at peek
> volume.
>
> Can this be done in SQL?  Or do I need to write a perl script?

yes. but possibly not efficiently .

something like this?

SELECT c.start, COUNT(*) as foo 
  FROM calls as c
    JOIN calls as d 
      ON d.start <= c.start 
        AND d.duration  >= (c.start - d.start)
  GROUP BY c.start
  ORDER BY foo DESC,c.start DESC
  LIMIT 1
  
it is almost certainly be possible do this more efficiently with a
custom agregate function. O(n log(n)) instead of O(n^2)


In response to

pgsql-general by date

Next:From: Jasen BettsDate: 2009-01-31 12:12:12
Subject: Re: database/table snapshot
Previous:From: Gregory StarkDate: 2009-01-31 11:13:13
Subject: Re: Pet Peeves?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group