Re: Query Help
From: Joe Celko (joe.celko_at_northface.edu)
Date: 02/25/04
- Next message: Erland Sommarskog: "Re: execute SP at same time??"
- Previous message: Erland Sommarskog: "Re: Stored procedure - long execution times"
- In reply to: abc_at_helloall.com: "Re: Query Help"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 25 Feb 2004 15:39:21 -0800
>> More over its amazing how you could tell my background just with the
recognition of one GOTO keyword. <<
Ever read THE PSYCHOLOGY OF COMPUTER PROGRAMMING by Weinberg? There is
an anniversary edition now. He goes into the details of how to tell the
native language of people in PL/I classes (this was written when PL/I
was the language du jour).
I am back to teaching college, so I am trying to look for the mental
models that lead to errors in new SQL users. People seldom make random
errors; OO programmers make these mistakes because they think in
objects; IMS users make these errors because they think in hierarchies;
assembly language and C guys do this becuase they think in bits and
bytes; etc.
I find it takes about a year of full time DB work under someone who
knows what they are doing to become a SQL programmer. But the biggest
block is moving from a concrete procedural code model to an abstract
declarative set-oriented one. The only other popular language close to
SQL is LISP --- aarrrrrgh!
>> 1. When I said each minute in a 6 min block should be > 0 or not null
what I meant was each reading for each minute in a six minute block has
to be >0 and not null to be counted in an average for the 6 min block.
<<
Okay that makes sense. But you want to distinquish between a zero and
NULL for other reasons, like a zero reading is possible, but a NULL
means the gauge was turned off.
>> 2. If minimum of 5 readings within a 6 minute block are > 0 and not
null then look for the onoff value in IncOnOff table for 30 minutes
prior to the start time of the 6 minute block, if onoff value is 1 check
the 6 minute block average reading value > 20 then return a 1. <<
Let me reword this and see if I have it. And let me get the specs in
procedural code :)
I am looking at a block X = ('2004-01-01 01:00:00' to '2004-01-01
01:05:00').
IF I have 5 or 6 readings in X
THEN IF incinerator at '2004-01-01 00:30:00' = 1
THEN BEGIN
compute average(X);
IF average(X) > 20
THEN RETURN 1
ELSE RETURN (??)
END
ELSE RETURN (??)
ELSE -- I have 4 or fewer readings in X
RETURN (??);
Fill in the (??) and we hav covered all the bases!
>> if I have a temp table with equally spaced 6 minute start and end
times for all the dates in a year for example, then I will be able to
get what I need. Is there
any other method? <<
Make it a persistent table; why re-generate it every time you do a
report? It is small: (10 * 24 * 365.3422) = 87660 rows.
Calendar tables are a common SQL programming trick; the language is not
computational and wants to work with tables. Cursors and procedural
code are ORDERS of magnitude slower than pure set code. I've written
five cursors in my career and I know that 3 of them could have been
avoided if I had a CASE expression back then.
>> Other thing is I can't change the IncOnOff table to PollingSchedule
table structure. Because I need a reading for every minute stating the
status of incinerator we run, i.e. if incinerator is on or off during
that minute. <<
This is a random thought, but could we put it all in one table?
CREATE TABLE IncineratorHistory
(sample_time DATETIME NOT NULL PRIMARY KEY,
blk_nbr INTEGER NOT NULL,
current_incinerator_status INTEGER DEFAULT 0 NOT NULL
CHECK (status IN (0,1)),
prior_incinerator_status INTEGER DEFAULT 0 NOT NULL
CHECK (status IN (0,1)),
reading REAL);
The block number would give us the six minute groupings; the reading
would start as a NULL and stay that way until overwritten; the current
and prior incinerator status codes explain themselves. The table is
managed by one UPDATE:
UPDATE IncineratorHistory
SET reading = :my_new_reading,
current_incinerator_status = :my_new_status
prior_incinerator_status
= (SELECT H1.current_incinerator_status
FROM IncineratorHistory AS H1
WHERE H1.sample_time
= IncineratorHistory.sample - INTERVAL 30 MINUTES)
WHERE sample_time = :my_sample_time;
The basic average is something like:
SELECT blk_nbr, AVG (reading)
FROM IncineratorHistory AS H1
WHERE current_incinerator_status = 1
AND reading > 0
GROUP BY blk_nbr
HAVING COUNT (reading) >= 5;
And we can add the other condition when we know what they are in a WHERE
clause or CASE expression. This is much different from procedural
programming, ain't it :)
--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
- Next message: Erland Sommarskog: "Re: execute SP at same time??"
- Previous message: Erland Sommarskog: "Re: Stored procedure - long execution times"
- In reply to: abc_at_helloall.com: "Re: Query Help"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
