one for the SQL experts - dare I say TRICKY SQL!

[graham.parsons]

Guys,

Hopefully someone can help.

We have a monitoring program that has threads which start and stop
monitoring at various times. There are two tables:

THREADLIFECYCLE

unique_id
start_time (always populated)
end_time (not populated until the thread ends)
MONITORRESULTS

unique_id
time_of_measure ment
value
What I am trying to do is find the average value for each of the
numbers of running threads. To explain further, threads will start,
stop independently and overlap each other.

I want an output that says:

When 1 thread was running: average value was x
When 3 threads were running: average value was y

Due to the start and stop nature there could be 1 thread running at the
beginning of the test, mid way through, a number of occassions, etc.

Also, the number of threads does not necessarily ramp sequantially -
the number running at any time could be like this sequence: 1, 5, 10,
7, 12, 4, 2

ANY help would be much appreciated - it really has stumped me but looks
like it should be so simple .... But aren't they always the hard ones
;-(

Thanks

Graham

[--CELKO--]

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_TIMESTA MP NOT NULL,
stop_stamp DATETIME NULL,
CHECK(start_sta mp < 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_valu e) 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;

[Hugo Kornelis]

On 7 Feb 2006 08:40:14 -0800, pa**********@ya hoo.co.uk wrote:

There are 2 occassions when the user count is 1 at the start of
Threadid 1 and when the last thread 4 is kicked off. Threadid has 2 *
10 (measurements) = 20 and Threadid 4 has 2 * 40 (measurements).
Therefore 100 measurements in total. Two threads therefore average =
50.
Hi paulspratley,

I still can't figure this one out.

First, I'm surprised that you want to factor in both measurements of
thread 1. After all, one of those measurements was taken when a total of
two threads was running. The initial post by Graham suggests to me that
this measurement should not be used here. But maybe I'm misreading the
vague description Graham posted?

Second, with the logic outline above, I can explain the first line of
the expected results, but neither the second, not the third.
Count of Users Avg Rsp Time
1 50
2 30
3 40

There are two active threads when during the lifecycle of thread 2
(overlaps with 1) and 3 (overlaps with 1 as well). According to the
logic above, we'll have to use 2*10=20 for thread 1, 2*20=40 for thread
2, and 2*30=60 for thread 3. A total of 120, for three threads - this
averages out to 40, not 30 as you state in the expected results.

There isn't even one single occasion with three (or more) threads
simultaneously active. So where does the third row come from?
BTW, You posted to both SQL Server and Oracle groups - what DB are you
actually running on? These DBMSes are not 100% compatible.

--
Hugo Kornelis, SQL Server MVP

[Erland Sommarskog]

(pa**********@y ahoo.co.uk) writes:

There are 2 occassions when the user count is 1 at the start of
Threadid 1 and when the last thread 4 is kicked off. Threadid has 2 *
10 (measurements) = 20 and Threadid 4 has 2 * 40 (measurements).
Therefore 100 measurements in total. Two threads therefore average =
50.

It's very difficult to suggest a query, when the sample data does not
really match the description, and when there is not really any any
good description of the business problems.

In the sample data, the result for scriptid = 6 is from an occassion
when no thread was running, not even the thread that was said to be
running.

Why the two measurements for threadid = 1 should count for one user
is beyond me, as when the second measurement is record, there is another
thread.

At no occassion there are three threads running what I can see.

I composed this query, but it does not give the desired result.

SELECT cnt, avg(summeasurem ent)
FROM (SELECT cnt, threadid, summeasurement = sum(measurement )
FROM (SELECT r.measurement, r.threadid,
cnt = (SELECT COUNT(*)
FROM threadstart t
WHERE r.startstamp BETWEEN t.startstamp AND
coalesce(t.stop stamp,
'99991231'))
FROM result r) AS x
GROUP BY threadid, cnt) AS b
GROUP BY cnt
ORDER BY cnt
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Tony Rogerson]

> INSERT INTO Threads VALUES(1, '2006-02-07 02:03:00', '2006-02-07

02:07:00');
This is very dangerous code, its worse than SELECT * and relies columns
being in order which we know in a set is just not the case.

ALWAYS specify the columns on your INSERT...

INSERT INTO Threads ( thread_id, start_stamp, stop_stamp ) VALUES(1,
'2006-02-07 02:03:00', '2006-02-07 02:07:00')

Also, use standard formatting for the dates - '2006-02-07T02:07:00'
SELECT M.meas_stamp, COUNT(T.thread_ id) AS active_tally,
SUM(M.meas_valu e) 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;
Stop using that outdated column syntax nobody except oldbies unwilling to
change use.

SELECT M.meas_stamp, COUNT(T.thread_ id) AS active_tally, SUM(M.meas_valu e)
AS meas_tot
FROM Threads AS T
CROSS JOIN Measurements AS M
WHERE M.meas_stamp BETWEEN T.start_stamp AND T.stop_stamp
GROUP BY M.meas_stamp;

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jc*******@eart hlink.net> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.com.. . 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_TIMESTA MP NOT NULL,
stop_stamp DATETIME NULL,
CHECK(start_sta mp < 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_valu e) 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;

[fitzjarrell]

Comments embedded.
Tony Rogerson wrote:

INSERT INTO Threads VALUES(1, '2006-02-07 02:03:00', '2006-02-07
02:07:00');
This is very dangerous code, its worse than SELECT * and relies columns
being in order which we know in a set is just not the case.

ALWAYS specify the columns on your INSERT...

INSERT INTO Threads ( thread_id, start_stamp, stop_stamp ) VALUES(1,
'2006-02-07 02:03:00', '2006-02-07 02:07:00')

To this I heartily agree.
Also, use standard formatting for the dates - '2006-02-07T02:07:00'

Standard to which DBMS? Certainly not Oracle. I will submit passing
date strings without a proper format specifier is poor coding:

INSERT INTO Threads ( thread_id, start_stamp, stop_stamp )
VALUES(1, to_date( '2006-02-07 02:03:00', 'YYYY-MM-DD HH24:MI:SS'),
to_date( '2006-02-07 02:07:00', 'YYYY-MM-DD
HH24:MI:SS'))

For those using SQL Server:

INSERT INTO Threads ( thread_id, start_stamp, stop_stamp )
VALUES(1, convert(datetim e, '2006-02-07 02:03:00', 120),
convert(datetim e, '2006-02-07 02:07:00', 120))

One should never assume a universal date/time format.

SELECT M.meas_stamp, COUNT(T.thread_ id) AS active_tally,
SUM(M.meas_valu e) 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;

Stop using that outdated column syntax nobody except oldbies unwilling to
change use.

Nothing wrong with using it as it returns the proper results. I will
admit once one is accustomed to using the ANSI join syntax it is easier
to write and prettier to view. But, ugliness doesn't make it wrong.
SELECT M.meas_stamp, COUNT(T.thread_ id) AS active_tally, SUM(M.meas_valu e)
AS meas_tot
FROM Threads AS T
CROSS JOIN Measurements AS M
WHERE M.meas_stamp BETWEEN T.start_stamp AND T.stop_stamp
GROUP BY M.meas_stamp;

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

And, unfortunately for the SQL Server crowd this was also posted to
comp.databases. oracle.server. Unfortunate because the modified example
provided throws an error from SQL*Plus:

ERROR at line 3:
ORA-00933: SQL command not properly ended

and is the result of using AS to declare the table aliases. Oracle
simply doesn't accept it, and I'm fairly certain SQL Server can get by
without it as well. To make the previously posted code 'palatable' to
SQL*Plus:

SQL> SELECT M.meas_stamp, COUNT(T.thread_ id) AS active_tally,
SUM(M.meas_valu e)
2 AS meas_tot
3 FROM Threads T
4 CROSS JOIN Measurements M
5 WHERE M.meas_stamp BETWEEN T.start_stamp AND T.stop_stamp
6 GROUP BY M.meas_stamp;
MEAS_STAM ACTIVE_TALLY MEAS_TOT
--------- ------------ ----------
07-FEB-06 1 10
07-FEB-06 2 20
07-FEB-06 2 40
07-FEB-06 2 40
07-FEB-06 2 60
07-FEB-06 1 40
07-FEB-06 1 40

7 rows selected.

Note it's still using the 'prettier' ANSI syntax (and, again, simply
because it's possibly ugly doesn't make the old style join syntax
wrong), it simply removes the offensive (to SQL*Plus) AS verbiage when
declaring the table aliases.
David Fitzjarrell

[Mike Hodgson]

fi*********@cox .net wrote:

And, unfortunately for the SQL Server crowd this was also posted to
comp.databases .oracle.server. Unfortunate because the modified example
provided throws an error from SQL*Plus:

ERROR at line 3:
ORA-00933: SQL command not properly ended

and is the result of using AS to declare the table aliases. Oracle
simply doesn't accept it, and I'm fairly certain SQL Server can get by
without it as well. To make the previously posted code 'palatable' to
SQL*Plus:

SQL> SELECT M.meas_stamp, COUNT(T.thread_ id) AS active_tally,
SUM(M.meas_val ue)
2 AS meas_tot
3 FROM Threads T
4 CROSS JOIN Measurements M
5 WHERE M.meas_stamp BETWEEN T.start_stamp AND T.stop_stamp
6 GROUP BY M.meas_stamp;
MEAS_STAM ACTIVE_TALLY MEAS_TOT
--------- ------------ ----------
07-FEB-06 1 10
07-FEB-06 2 20
07-FEB-06 2 40
07-FEB-06 2 40
07-FEB-06 2 60
07-FEB-06 1 40
07-FEB-06 1 40

7 rows selected.

Note it's still using the 'prettier' ANSI syntax (and, again, simply
because it's possibly ugly doesn't make the old style join syntax
wrong), it simply removes the offensive (to SQL*Plus) AS verbiage when
declaring the table aliases.

Just a quick (minor) note: 'AS' is defined in the ANSI SQL-99 standard
(ISO/IEC 9075-2:1999) as an optional keyword in the FROM clause between
the object name (table, view, derived table, whatever) and the alias for
it. So, while it's true that SQL Server can get by without it (it's
optional in the T-SQL grammar, as it is in SQL-99) and it's not defined
in the PL/SQL grammar, technically speaking, Tony's code was right in
that it conformed to SQL-99.

--
*mike hodgson*
http://sqlnerd.blogspot.com

[--CELKO--]

>>Standard to which DBMS? <<

Unh? That senternce makes no sense in the database world. ANSI/ISO
Standards apply to all vendor products. The product either meets or
fails them. Vendors do not set their own privates Standards (note the
capital S).

Certainly not Oracle. <<
Oracle is still a nightmare of non-conformance to ANSI/ISO, X/Open, etc
Standards, as well as expensive and hard to use. It is a kind of
"Hillbilly dialect" of SQL :)
I will submit passing date strings without a proper format specifier is poor coding: <<

Did you know that SQL has one and only one allowed date format?
Apparently not. It is based on ISO-8601, a Standard used in many other
ISO standards.

"Caesar: Pardon him, Theodotus. He is a barbarian and thinks the
customs of his tribe and island are the laws of nature." - Caesar and
Cleopatra; George Bernard Shaw 1898

[--CELKO--]

>> 'AS' is defined in the ANSI SQL-99 Standard ... an optional keyword in the FROM clause between the object name (table, view, derived table, whatever) and the alias for
it. <<

I like it because it separates things nicely for the eye. In real old
days, leaving out a comma in a FROM clause could accidently create an
alias for a table. I find it funny that people who use the wordy
infixed join syntax for INNER JOINs often skip the AS keyword.

[Tony Rogerson]

> Standard to which DBMS? Certainly not Oracle. I will submit passing

date strings without a proper format specifier is poor coding:
The ISO standard rather than vendor specific.
INSERT INTO Threads ( thread_id, start_stamp, stop_stamp )
VALUES(1, convert(datetim e, '2006-02-07 02:03:00', 120),
convert(datetim e, '2006-02-07 02:07:00', 120))
You do not and would not code it like that in SQL Server, you would simply
write...

INSERT INTO Threads ( thread_id, start_stamp, stop_stamp )
VALUES(1, '2006-02-07T02:03:00', '2006-02-07T02:07:00')

The rest of the post, basically I was refering to the ANSI 92 INNER JOIN,
CROSS JOIN syntax over the ANSI 89 comma syntax.

We got the ANSI 92 syntax in version 6.5 of MS SQL Server which was around
96/97, the majority 99.9% of people in the MS SQL Server space using ANSI 92
now and convert what I term the 'out-dated' syntax to ANSI 92.

I didn't see the cross posting news groups so the syntax specific stuff
refers to MS SQL Server, not sure Oracle and Sybase got it until the last
few years so you'll go through a similar curver imho.

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
<fi*********@co x.net> wrote in message
news:11******** **************@ g14g2000cwa.goo glegroups.com.. . Comments embedded.
Tony Rogerson wrote:

> INSERT INTO Threads VALUES(1, '2006-02-07 02:03:00', '2006-02-07
> 02:07:00');

This is very dangerous code, its worse than SELECT * and relies columns
being in order which we know in a set is just not the case.

ALWAYS specify the columns on your INSERT...

INSERT INTO Threads ( thread_id, start_stamp, stop_stamp ) VALUES(1,
'2006-02-07 02:03:00', '2006-02-07 02:07:00')

To this I heartily agree.

Also, use standard formatting for the dates - '2006-02-07T02:07:00'

Standard to which DBMS? Certainly not Oracle. I will submit passing
date strings without a proper format specifier is poor coding:

INSERT INTO Threads ( thread_id, start_stamp, stop_stamp )
VALUES(1, to_date( '2006-02-07 02:03:00', 'YYYY-MM-DD HH24:MI:SS'),
to_date( '2006-02-07 02:07:00', 'YYYY-MM-DD
HH24:MI:SS'))

For those using SQL Server:

INSERT INTO Threads ( thread_id, start_stamp, stop_stamp )
VALUES(1, convert(datetim e, '2006-02-07 02:03:00', 120),
convert(datetim e, '2006-02-07 02:07:00', 120))

One should never assume a universal date/time format.

> SELECT M.meas_stamp, COUNT(T.thread_ id) AS active_tally,
> SUM(M.meas_valu e) 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;

Stop using that outdated column syntax nobody except oldbies unwilling to
change use.

Nothing wrong with using it as it returns the proper results. I will
admit once one is accustomed to using the ANSI join syntax it is easier
to write and prettier to view. But, ugliness doesn't make it wrong.

SELECT M.meas_stamp, COUNT(T.thread_ id) AS active_tally,
SUM(M.meas_valu e)
AS meas_tot
FROM Threads AS T
CROSS JOIN Measurements AS M
WHERE M.meas_stamp BETWEEN T.start_stamp AND T.stop_stamp
GROUP BY M.meas_stamp;

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

And, unfortunately for the SQL Server crowd this was also posted to
comp.databases. oracle.server. Unfortunate because the modified example
provided throws an error from SQL*Plus:

ERROR at line 3:
ORA-00933: SQL command not properly ended

and is the result of using AS to declare the table aliases. Oracle
simply doesn't accept it, and I'm fairly certain SQL Server can get by
without it as well. To make the previously posted code 'palatable' to
SQL*Plus:

SQL> SELECT M.meas_stamp, COUNT(T.thread_ id) AS active_tally,
SUM(M.meas_valu e)
2 AS meas_tot
3 FROM Threads T
4 CROSS JOIN Measurements M
5 WHERE M.meas_stamp BETWEEN T.start_stamp AND T.stop_stamp
6 GROUP BY M.meas_stamp;
MEAS_STAM ACTIVE_TALLY MEAS_TOT
--------- ------------ ----------
07-FEB-06 1 10
07-FEB-06 2 20
07-FEB-06 2 40
07-FEB-06 2 40
07-FEB-06 2 60
07-FEB-06 1 40
07-FEB-06 1 40

7 rows selected.

Note it's still using the 'prettier' ANSI syntax (and, again, simply
because it's possibly ugly doesn't make the old style join syntax
wrong), it simply removes the offensive (to SQL*Plus) AS verbiage when
declaring the table aliases.
David Fitzjarrell

[DA Morgan]

--CELKO-- wrote:

Oracle is still a nightmare of non-conformance to ANSI/ISO, X/Open, etc
Standards, as well as expensive and hard to use. It is a kind of
"Hillbilly dialect" of SQL :)
Not to disparage standards but to be intellectually honest you should
acknolwedge that all SQL RDBMS's are non-conformant in one manner or
another. If they weren't they would have a product that was only
marginally capable of handling the real-world environment.
Did you know that SQL has one and only one allowed date format?
Apparently not. It is based on ISO-8601, a Standard used in many other
ISO standards.
An good example of precisely what I meant by my statement above.
as well as expensive and hard to use.

But please let me strongly dispute the above. How can you claim Oracle
as expensive when it provides functionality not available in SQL Server
for any price. Need RAC? No price will get it. Want on-line object
redefinition. No price will get it for you. Want humongous numbers of
other high-end capabilities. Better start writing them yourself in C#.
If all you need is tables and indexes then I'd suggest MySQL. I wouldn't
pay either Microsoft or Oracle a dime.

And hard to use? Maybe a decade ago. Fly on into Seattle and I'll get
you both the best scotch you've ever had and a good lesson on using
the Grid.
--
Daniel A. Morgan
http://www.psoug.org
da******@x.wash ington.edu
(replace x with u to respond)