DBAsupport.com Forums - Powered by vBulletin
Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 31

Thread: morethan 3 consecutive rows

  1. #21
    Join Date
    Mar 2006
    Posts
    74
    can you not just sort by ROWID to get the natural order that rows were inserted into the table? is it valid?

  2. #22
    Join Date
    Mar 2006
    Posts
    74
    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
    Last edited by cjard; 04-12-2006 at 06:34 AM.

  3. #23
    Join Date
    Mar 2006
    Posts
    74
    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 3
        lead(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

  4. #24
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Quote Originally Posted by cjard
    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?

  5. #25
    Join Date
    Mar 2006
    Posts
    74
    Quote Originally Posted by jmodic
    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!

  6. #26
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Quote Originally Posted by cjard
    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?

  7. #27
    Join Date
    Oct 2002
    Posts
    25
    Hi all,

    Finally, i got the exact requirement from the client. Please find the requirement in the attached word document.

    The requirement is very scary to me. Hope the experts can help.

    Thanks,
    Rajan
    Attached Files Attached Files
    Last edited by rajan1; 05-10-2006 at 08:42 PM.

  8. #28
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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

  9. #29
    Join Date
    Oct 2002
    Posts
    25
    Hi all,

    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.

    Thanks,
    Rajan
    Attached Files Attached Files

  10. #30
    Join Date
    Oct 2002
    Posts
    25
    The three consecutive applies only within a date or for each date.

    Thanks,
    Rajan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width