From: | Axel Rau <Axel(dot)Rau(at)Chaos1(dot)DE> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Sum up network events by type, interval and network |
Date: | 2010-11-16 16:29:08 |
Message-ID: | 65652460-8130-4406-96BB-C9EB1A0D65C2@Chaos1.DE |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(dot)rau(at)chaos1(dot)de PGP-Key:29E99DD6 +49 151 2300 9283 computing @
chaos claudius
From | Date | Subject | |
---|---|---|---|
Next Message | Ferruccio Zamuner | 2010-11-17 13:22:34 | PostgreSQL array, recursion and more |
Previous Message | Jasen Betts | 2010-11-16 08:21:46 | Re: multi table import from 1 denormalized source table |