 |
|
|
|
|
|
| |
| |
|
|
|
|
| |
| |
|
|
scott wrote:
>> SELECT *
>> FROM EventRecord AS X, EventRecord AS Y, EventRecord AS Z
>> WHERE
>> X.ExperimentID = Y.ExperimentID AND
>> Y.ExperimentID = Z.ExperimentID AND
>> X.EventID = 0 AND
>> Y.EventID = 2 AND
>> Z.EventID = 1 AND
>> X.EventDateTime < Y.EventDateTime AND
>> Y.EventDateTime < Z.EventDateTime;
>>
>> Or similar. (It's been a while since I did SQL...)
>
> Oh cool that helps a lot, I'm copying and pasting that to a file
> somewhere for reference, thanks. I still need to order myself an SQL
> book...
Out of curiosity... does this work?
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
>>> SELECT *
>>> FROM EventRecord AS X, EventRecord AS Y, EventRecord AS Z
>>> WHERE
>>> X.ExperimentID = Y.ExperimentID AND
>>> Y.ExperimentID = Z.ExperimentID AND
>>> X.EventID = 0 AND
>>> Y.EventID = 2 AND
>>> Z.EventID = 1 AND
>>> X.EventDateTime < Y.EventDateTime AND
>>> Y.EventDateTime < Z.EventDateTime;
>>>
>>> Or similar. (It's been a while since I did SQL...)
>>
>> Oh cool that helps a lot, I'm copying and pasting that to a file
>> somewhere for reference, thanks. I still need to order myself an SQL
>> book...
>
> Out of curiosity... does this work?
Hehe haven't got anywhere near needing to implement that algorithm yet, I'm
still working on parsing text files to populate the database automatically
(lots of RegExs). But I am getting the hang of how the SQL stuff works now.
One thing I thought about your code which might not work, is if you have a
sequence of events stored in the table like this:
EvenDateTime,EventType
0,Turn on
1,Turn off
2,Overheat
3,Turn on
4,Turn off
Then wouldn't your query pick up event 2 as being between an "on" and "off"
event, but obviously the system is in the "off" state when it overheats.
Maybe I need to store the state of the equipment (on/off) in each row to
make things easier, but there are more states than just on/off (more
speicifcally, there are several different sub-parts than can be on/off) so
hmmmmmm.
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
>> Out of curiosity... does this work?
>
> Hehe haven't got anywhere near needing to implement that algorithm yet,
> I'm still working on parsing text files to populate the database
> automatically (lots of RegExs). But I am getting the hang of how the
> SQL stuff works now.
Mmm, yum. :-S
> One thing I thought about your code which might not work, is if you have
> a sequence of events stored in the table like this:
>
> EvenDateTime,EventType
> 0,Turn on
> 1,Turn off
> 2,Overheat
> 3,Turn on
> 4,Turn off
>
> Then wouldn't your query pick up event 2 as being between an "on" and
> "off" event, but obviously the system is in the "off" state when it
> overheats.
Hmm, good point. I had assumed a sequence like that wouldn't appear.
I guess you'd need to find all the overhead events, and for each one,
perform a sub-select to determine the latest turn-on or turn-off event
preceeding it, and filter it out if it's a turn-off event.
I'll see if I can figure out the correct syntax for this. (Gail probably
knows in a heartbeat...)
> Maybe I need to store the state of the equipment (on/off) in each row to
> make things easier, but there are more states than just on/off (more
> speicifcally, there are several different sub-parts than can be on/off)
> so hmmmmmm.
Yeah, probably not such a hot idea to denormalise the data like that...
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
>> One thing I thought about your code which might not work, is if you
>> have a sequence of events stored in the table like this:
>>
>> EvenDateTime,EventType
>> 0,Turn on
>> 1,Turn off
>> 2,Overheat
>> 3,Turn on
>> 4,Turn off
>>
>> Then wouldn't your query pick up event 2 as being between an "on" and
>> "off" event, but obviously the system is in the "off" state when it
>> overheats.
>
> Hmm, good point. I had assumed a sequence like that wouldn't appear.
>
> I guess you'd need to find all the overhead events, and for each one,
> perform a sub-select to determine the latest turn-on or turn-off event
> preceeding it, and filter it out if it's a turn-off event.
>
> I'll see if I can figure out the correct syntax for this. (Gail probably
> knows in a heartbeat...)
OK, here we go. Using a sub-select, you can find the "previous" event in
the log something like this:
SELECT *
FROM EventLog X, EventLog Y
WHERE
X.ExperimentID = Y.ExperimentID AND
Y.Timestamp =
(
SELECT MAX(Timestamp)
FROM EventLog Z
WHERE
Z.ExperimentID = X.ExperimentID AND
Z.Timestamp < X.Timestamp
)
In other words, for each row in X, perform a sub-query where you find
all the rows belonging to the same experiment as X, with timestamps
earlier than X, and then find the latest timestamp. Return that, and
find the row in Y belonging to the same experiment, having the same
timestamp.
If you *now* apply some filtering conditions and you're golden.
Specifically, X must be an overheat event, and Y must be a power-on
event (but Z is still unconstrained to be *any* event). If the previous
event was a power-off event, Z will find it, and the "Y=power-on"
condition will fail, so that row will be excluses. :-D
The trouble starts if there are many possible events, not just on, off
and overheat. But all you really need to do is constrain Z to match only
power-on and power-off events, but nothing else. Then Z (and hence Y)
will end up being the most recent power-on or power-off, and you can put
a condition on Y demanding that it be a power-on.
...if that makes *any* sense at all?
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
> OK, here we go. Using a sub-select, you can find the "previous" event in
> the log something like this:
>
> SELECT *
> FROM EventLog X, EventLog Y
> WHERE
> X.ExperimentID = Y.ExperimentID AND
> Y.Timestamp =
> (
> SELECT MAX(Timestamp)
> FROM EventLog Z
> WHERE
> Z.ExperimentID = X.ExperimentID AND
> Z.Timestamp < X.Timestamp
> )
<snip>
> ...if that makes *any* sense at all?
I'll have to read and work through it some more for it to all make sense,
but it does make me realise that almost anything I want to get from my data
should be possible with SQL - something which I was not sure about at the
start!
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
scott wrote:
>> OK, here we go. Using a sub-select, you can find the "previous" event
>> in the log something like this:
>>
>> SELECT *
>> FROM EventLog X, EventLog Y
>> WHERE
>> X.ExperimentID = Y.ExperimentID AND
>> Y.Timestamp =
>> (
>> SELECT MAX(Timestamp)
>> FROM EventLog Z
>> WHERE
>> Z.ExperimentID = X.ExperimentID AND
>> Z.Timestamp < X.Timestamp
>> )
>
> <snip>
>
>> ...if that makes *any* sense at all?
>
> I'll have to read and work through it some more for it to all make
> sense, but it does make me realise that almost anything I want to get
> from my data should be possible with SQL - something which I was not
> sure about at the start!
There are few things that SQL cannot do. There are many, many things
that *look* like you can't do them, but with a little trickery you can
do a surprising amount using a purely declarative, set-at-once approach
using SQL. It's a very powerful data manipulation paradigm. ;-)
Indeed, I spent much of my time at uni teasing through tricky SQL
problems. It can be quite an exciting challenge. Now I'm not saying I
was the teacher's pet, but I do remember sitting in a lecture hall
containing about 40 people, and the lecturer saying
"And for this reason, we have various forms of normalisation. There's
1st normal form, 2nd normal form, and so forth upto something like 6th
normal form, with each one less redundant than the one before. However,
for all practical purposes, you only really need to worry about
Boyce-Codd normal form, which says - ANDREW..."
[I stand up]
"...every determinent is a candidate key..."
[I sit down]
"...thank you Andrew. Now, what that means is[...]"
One thing that SQL can't handle - as far as I know - is relationships
which are arbitrarily recursive. The classic example is where you have a
part that's made up of several other parts, which in turn are make up of
smaller parts, etc. It's trivial to find all sub-parts down to a given
level of recursion, but AFAIK impossible to recurse until you can't
recurse any further.
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
scott wrote:
>> OK, here we go. Using a sub-select, you can find the "previous" event
>> in the log something like this:
>>
>> SELECT *
>> FROM EventLog X, EventLog Y
>> WHERE
>> X.ExperimentID = Y.ExperimentID AND
>> Y.Timestamp =
>> (
>> SELECT MAX(Timestamp)
>> FROM EventLog Z
>> WHERE
>> Z.ExperimentID = X.ExperimentID AND
>> Z.Timestamp < X.Timestamp
>> )
>
> <snip>
>
>> ...if that makes *any* sense at all?
I'm guessing that's higher overhead than taking your original
multi-AND-clause query and adding
AND NOT EXISTS (Select ...)
to exclude having other records in the range of interest. Of course, it
depends on your index configuration and how good your optimizer is.
--
Darren New, San Diego CA, USA (PST)
My fortune cookie said, "You will soon be
unable to read this, even at arm's length."
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Invisible wrote:
> It's more that when auditors walk in and demand to know why X happened,
> you're not really allowed to say "well that's because some idiot who
> doesn't know the correct authorisation procedure decided to change it
> without bothering to notify me".
Then you definitely need to bump this up the chain of command. Get them to
put in writing that you're to lie to the auditors about why changes were
made without being logged, or something like that. :-)
> Cute, but I suspect they'd simply summarily fire me.
I wouldn't think so. Not if you go far enough up the ladder. I strongly
suspect they'd be in trouble for firing you over your insistence that you
obey the law.
--
Darren New, San Diego CA, USA (PST)
My fortune cookie said, "You will soon be
unable to read this, even at arm's length."
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Invisible wrote:
> I have no power to prevent them changing things (either through
> technology or politics). I don't even really have the ability to detect
> changes when they happen. But I definitely *am* held legally responsible
> for this.
Start gathering documentation showing that they repeatedly make changes
without your knowledge. Then if they ever try to make you legally
responsible, you can show that they weren't following protocol, and you
were.
(Assuming that's the case). Also helps if you can document that you
informed everyone that their actions are against policy (just a copy of
an email you sent).
--
Copywight 1991 Elmer Fudd. All wights wesewved
/\ /\ /\ /
/ \/ \ u e e n / \/ a w a z
>>>>>>mue### [at] nawaz org<<<<<<
anl
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
On Mon, 23 Feb 2009 11:15:40 -0600, Mueen Nawaz wrote:
> Start gathering documentation showing that they repeatedly make
changes
> without your knowledge. Then if they ever try to make you legally
> responsible, you can show that they weren't following protocol, and you
> were.
Seconded. From a legal sense, if it ain't documented, it didn't happen.
Jim
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
|
|
