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
> 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
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 Betts||Date: 2009-01-31 12:12:12|
|Subject: Re: database/table snapshot|
|Previous:||From: Gregory Stark||Date: 2009-01-31 11:13:13|
|Subject: Re: Pet Peeves?|