Re: Call volume query

From: Harald Fuchs <hari(dot)fuchs(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Call volume query
Date: 2009-01-30 15:38:20
Message-ID: puk58cg5xv.fsf@srv.protecting.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

In article <1233269836(dot)13476(dot)10(dot)camel(at)ubuntu>,
Mike Diehl <mdiehl(at)diehlnet(dot)com> writes:

> 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.

> 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?

Try something like the following:

CREATE TABLE calls (
id serial NOT NULL,
start timestamp(0) NOT NULL,
nsec int NOT NULL,
PRIMARY KEY (id)
);

COPY calls (start, nsec) FROM stdin;
2009-01-30 10:09:00 10
2009-01-30 10:10:00 10
2009-01-30 10:10:02 10
2009-01-30 10:10:04 10
2009-01-30 10:10:06 10
2009-01-30 10:10:08 10
2009-01-30 10:10:10 10
2009-01-30 10:10:12 10
2009-01-30 10:11:00 10
\.

SELECT ts, count(c.id)
FROM (
SELECT (SELECT min(start) FROM calls) + s.a * interval '1 sec' AS ts
FROM generate_series(0, (
SELECT extract(epoch FROM (max(start + nsec * interval '1 sec') -
min(start)))::bigint
FROM calls
)) AS s(a)
) AS t
LEFT JOIN calls c
ON t.ts BETWEEN c.start AND c.start + c.nsec * interval '1 sec'
GROUP BY t.ts
ORDER BY t.ts;

Here I use generate_series to create timestamp values for every second
of the table range and join them to the table itself to see how many
calls were active at this time.

You could simplify that somewhat by using the "period" datatype
available on PgFoundry.

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Igor Katson 2009-01-30 16:29:19 Re: [Plproxy-users] Plproxy functions inside transactions and Pl/pgsql exception handling
Previous Message Asko Oja 2009-01-30 15:32:17 Re: database/table snapshot