|
|
Posted by --CELKO-- on 02/07/06 21:33
If you had posted DDL, would it look like this?
Since thread_id might actually be a key instead of a non-relational
physical sequence number.
CREATE TABLE Threads
(thread_id INTEGER NOT NULL PRIMARY KEY,
start_stamp DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
stop_stamp DATETIME NULL,
CHECK(start_stamp < stop_stamp));
INSERT INTO Threads VALUES(1, '2006-02-07 02:03:00', '2006-02-07
02:07:00');
INSERT INTO Threads VALUES(2, '2006-02-07 02:04:00', '2006-02-07
02:05:00');
INSERT INTO Threads VALUES(3, '2006-02-07 02:06:00', '2006-02-07
02:07:00');
INSERT INTO Threads VALUES(4, '2006-02-07 02:08:00', '2006-02-07
02:10:00');
The measurements clearly have a key in their time stamp.
CREATE TABLE Measurements
(meas_stamp DATETIME NOT NULL PRIMARY KEY,
meas_value DECIMAL (5,2) NOT NULL);
INSERT INTO Measurements VALUES('2006-02-07 02:03:44', 10.0);
INSERT INTO Measurements VALUES('2006-02-07 02:04:44', 10.0);
INSERT INTO Measurements VALUES('2006-02-07 02:04:45', 20.0);
INSERT INTO Measurements VALUES('2006-02-07 02:04:54', 20.0);
INSERT INTO Measurements VALUES('2006-02-07 02:06:44', 30.0);
INSERT INTO Measurements VALUES('2006-02-07 02:07:44', 30.0);
INSERT INTO Measurements VALUES('2006-02-07 02:08:44', 40.0);
INSERT INTO Measurements VALUES('2006-02-07 02:09:44', 40.0);
Now you can use a between preidcate to place each measurement inside an
on-going event.
CREATE VIEW Summary (meas_stamp, active, meas_tot)
AS
SELECT M.meas_stamp, COUNT(T.thread_id) AS active_tally,
SUM(M.meas_value) AS meas_tot
FROM Threads AS T, Measurements AS M
WHERE M.meas_stamp BETWEEN T.start_stamp AND T.stop_stamp
GROUP BY M.meas_stamp;
meas_stamp active_tally meas_tot
=================================
2006-02-07 02:03:44.000 1 10.00
2006-02-07 02:04:44.000 2 20.00
2006-02-07 02:04:45.000 2 40.00
2006-02-07 02:04:54.000 2 40.00
2006-02-07 02:06:44.000 2 60.00
2006-02-07 02:08:44.000 1 40.00
2006-02-07 02:09:44.000 1 40.00
Put this VIEW (or derived table or CTE) into a another query:
SELECT active_tally, SUM(meas_tot / COUNT(*)) AS meas_avg
FROM Summary
GROUP BY active_tally;
[Back to original message]
|