Re: Call volume query

In article <1233269836.13476.10.camel@ubuntu>,
Mike Diehl <mdiehl@diehlnet.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.