ok i finally whacked this one but for one little bug:
Code:
create table tmp_test as
select '3/1/2006 7:00a.m' as d,'a' as t,'3' as v from dual union all
select '3/1/2006 7:00a.m','a','1' from dual union all
select '3/1/2006 7:00a.m','a','1' from dual union all
select '3/1/2006 7:03a.m','a','3' from dual union all
select '3/1/2006 7:03a.m','a','4' from dual union all
select '3/1/2006 7:03a.m','a','5' from dual union all
select '3/1/2006 7:06a.m','a','6' from dual union all
select '3/1/2006 7:06a.m','a','1' from dual union all
select '3/1/2006 7:06a.m','a','2' from dual union all
select '3/1/2006 7:09a.m','a','3' from dual union all
select '3/1/2006 7:09a.m','a','5' from dual union all
select '3/1/2006 7:09a.m','a','4' from dual union all
select '3/1/2006 7:12a.m','b','3' from dual union all
select '3/1/2006 7:12a.m','b','2' from dual union all
select '3/1/2006 7:12a.m','b','1' from dual;
following my own advice (in english) given last post i made this query:
Code:
select
d,
t,
v,
case when
SUM(v) OVER (ORDER BY d, rowid ROWS 2 PRECEDING)>= 3 and
MIN(v) OVER (ORDER BY d, rowid ROWS 2 PRECEDING)>= 3 then
1
end as consec
from tmp_test
it reliably identifies consecutives and for run length of N, the last N-2 rows are flagged with a 1. i called this column CONSEC:
Code:
3/1/2006 7:00a.m a 1
3/1/2006 7:00a.m a 1
3/1/2006 7:03a.m a 3
3/1/2006 7:03a.m a 4
3/1/2006 7:03a.m a 5 1
3/1/2006 7:06a.m a 6 1
3/1/2006 7:06a.m a 1
3/1/2006 7:06a.m a 2
so now we can use a lookahead to see if the CONSEC column holds a 1 character, and because our CONSEC currently shows all but the first 2 of a consecutive range, we look ahead 2, or 1, or look at the current position:
Code:
select
d,
t,
v,
consec,
case when
consec = 1 or
lead(consec,1) OVER (ORDER BY d, rowid) = 1 or
lead(consec,2) OVER (ORDER BY d, rowid) = 1
then 'Y'
end as flag
from
(
select
d,
t,
v,
case when
SUM(v) OVER (ORDER BY d, rowid ROWS 2 PRECEDING)>= 9 and
MIN(v) OVER (ORDER BY d, rowid ROWS 2 PRECEDING)>= 3 then
1
end as consec
from tmp_test
);
results:
Code:
D T V CONSEC FLAG
3/1/2006 7:00a.m a 3
3/1/2006 7:00a.m a 1
3/1/2006 7:00a.m a 1
3/1/2006 7:03a.m a 3 Y
3/1/2006 7:03a.m a 4 Y
3/1/2006 7:03a.m a 5 1 Y
3/1/2006 7:06a.m a 6 1 Y
3/1/2006 7:06a.m a 1
3/1/2006 7:06a.m a 2
3/1/2006 7:09a.m a 3 Y
3/1/2006 7:09a.m a 5 Y
3/1/2006 7:09a.m a 4 1 Y
3/1/2006 7:12a.m b 3 1 Y
3/1/2006 7:12a.m b 2
3/1/2006 7:12a.m b 1
3/1/2006 7:13a.m c 4 Y
3/1/2006 7:14a.m d 4 Y
3/1/2006 7:14a.m d 5 1 Y
3/1/2006 7:14a.m d 1
3/1/2006 7:14a.m d 3
3/1/2006 7:14a.m d 3
3/1/2006 7:14a.m d 1
(i added some more values to the table after the initial create table sql)
stomped! and we have learned some interesting things about analytic functions too
please note i forgot the question and even ignored my own advice a little, i prepared an sql for more than 2 consecutive rows, and poster wanted more than 3, so heres how we change the sql:
Code:
select
d,
t,
v,
consec,
case when
consec = 1 or
lead(consec,1) OVER (ORDER BY d, rowid) = 1 or
lead(consec,2) OVER (ORDER BY d, rowid) = 1 or
--add a lead row for every blank. more than 3 consec needs 3 lead rows
--more than 5 consec (i.e. 6+ consec) needs 5 leads
--note that each lead row must lead a higher lookahead
--the lookahead of this row is 3lead(consec,3) OVER (ORDER BY d, rowid) = 1
then 'Y'
end as flag
from
(
select
d,
t,
v,
case when
--the sum over the last N rows (more than 3 rows requires 3 PRECEDING)
--must be CONSECUTIVES_REQD * MIN_THRESHOLD
--in our case we want a run of 4 or more rows with value >= 3
--so sum must be >= 4rows*3orMore == 4*3 == 12
--remember to change the X PRECEDING value of X
SUM(v) OVER (ORDER BY d, rowid ROWS 3 PRECEDING)>= 12 and
MIN(v) OVER (ORDER BY d, rowid ROWS 3 PRECEDING)>= 3 then
1
end as consec
from tmp_test
);
so for a consecutive run length N of values greater than Y you need
N-1 entries in the LEAD() section of the sql, with lead values 1 to N-1
a SUM() over the preceding N-1 rows of value >= N*Y
a MIN() over the preceding N-1 rows of value >= Y
ok, now you can edit this sql for any run length of any value
can you not just sort by ROWID to get the natural order that rows were inserted into the table? is it valid?
Of course not! ROWID has got nothing to do with what you call "natural order that rows were inserted into the table". There's no such thing in the Oracle database as built-in indicator of the inserted rows ordering. ROWID merely represents *the possition* of a particular row on the disk storage, it has no information about the time the row was inserted. And by comparing ROWIDs of two rows, you can not say which one was inserted befor the other one.
So, in short: ROWID can not be used for ordering rows in the way that original poster needs to.
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Of course not! ROWID has got nothing to do with what you call "natural order that rows were inserted into the table". There's no such thing in the Oracle database as built-in indicator of the inserted rows ordering. ROWID merely represents *the possition* of a particular row on the disk storage, it has no information about the time the row was inserted. And by comparing ROWIDs of two rows, you can not say which one was inserted befor the other one.
So, in short: ROWID can not be used for ordering rows in the way that original poster needs to.
well, dude's going to ahve to decide on something else that is precise enough to order his rows properly then, eh? i've only ever seen rowid increment for the data work that ive been doing and its been quite a reliable thing that for two rows inserted in the same second, the rowid has been able to order them in order of creation. i'll take your advice on board though should i need it in the future. thanks!
i've only ever seen rowid increment for the data work that ive been doing and its been quite a reliable thing that for two rows inserted in the same second, the rowid has been able to order them in order of creation.
Then I suppose you've never ever observed how new rows can take place that has been releeased by deleted rows from the same table, thus obtaining a "lower" ROWIDs compared to some rows that were inserted before them? Or new table extents beeng allocated in the tablespace in such location that all rows that will end in that extent in the future will result in ROWIDs that are lower than any currently existing table's row? Or that ROWIDs of the existing rows can change for various reasons, thus making the insertion time even less corelated to the row's ROWID?
In short, as I've allready said: by comparing ROWIDs of two rows, you can definitely not conclude which of those two rows was inserted before the other one. All you can do based on their ROWIDs is *to guess*.
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
The requirements STILL don't explicitly include a sort order. Your client must specify that.
Is it by (id, testdate)?
Does this produce a UNIQUE sequence?
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
I was bouncing back and forth with the client to confirm on the requirement. As a result i modified the requirement please find the modified requirement attached. I am trying my best to clearly define the requirement to our experts here.
1. Sort is by id and testdate
2. Select all the rows where the difference between (testdate column) current row and the lead row or the next row is 3 minutes.
I don't know how to proceed with consecutive times. Experts please help.