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

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 (view raw or flat)
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

pgsql-general by date

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

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