Обсуждение: Call volume query

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?

Thank you,
Mike.

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.

On 2009-01-29, Mike Diehl <mdiehl@diehlnet.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)