[SQL] Sum up network events by type, interval and network

[Axel Rau]

Hi all,

For each event of each type in each interval I want to compute the sum  
of events referencing the same relayNet (via host) and insert/update 1  
row in SumOfEvents.
All intervals start at a time, minimum 5 minutes in the past, rounded  
down modulo 5 minutes like:

SELECT DATE_TRUNC('MINUTES', NOW () - ('0:' || (SELECT  
(EXTRACT('MINUTE' FROM NOW())::INT % 5) + 5 ) || ':0')::INTERVAL);



CREATE TABLE host (
  id            SERIAL  PRIMARY KEY,
  relayNetFK    INT
                REFERENCES relayNet
                        ON DELETE CASCADE
)

CREATE TABLE event (
  id            SERIAL  PRIMARY KEY,
  type          CHAR         NOT NULL
                CHECK ( type IN ('C', 'A', 'D', 'S', 'R') ),
  timeOfEvent   timestamp       NOT NULL
                        DEFAULT NOW(),
  hostfk        INT          NOT NULL
                REFERENCES host
                        ON DELETE CASCADE
)

CREATE TABLE relayNet (
  id            SERIAL  PRIMARY KEY,
  name          TEXT            NOT NULL UNIQUE
)

CREATE TYPE eventIntervals AS ENUM ('5m', '30m', '3h', '24h', '30d');
CREATE TABLE SumOfEvents (
  id            SERIAL  PRIMARY KEY,
  type          CHAR         NOT NULL
                CHECK ( type IN ('C', 'A', 'D', 'S', 'R') ),
  startTime     timestamp       NOT NULL,
  interval      eventIntervals  NOT NULL,
  value         INT,        -- sum
  relayNetFK    INT
                REFERENCES relayNet
                        ON DELETE CASCADE,

  UNIQUE (type, interval, relayNetFK)
)

Can this be done w/o procedural code?
Any hints?

Thanks, Axel
---
axel....@chaos1.de  PGP-Key:29E99DD6  +49 151 2300 9283  computing @  
chaos claudius


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql