Re: count specific data changes in adjacent ordered rows

From: cate <catebekensail_at_yahoo.com>
Date: Sat, 12 Dec 2009 07:09:25 -0800 (PST)
Message-ID: <3421d319-0b2d-46c2-bd32-1ad9d3f007a6_at_d10g2000yqh.googlegroups.com>



On Dec 11, 8:43 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Dec 11, 8:04 pm, cate <catebekens..._at_yahoo.com> wrote:
>
> > a b c time
> > a b d time
> > a b e time
> > a b c time
> > a b x time
> > a b d time
>
> > I want to count all the c -> x transitions found in adjacent rows.
> > The table is ordered.  The count is one in the above example.
> > Oracle SQL
>
> > Thank you.
>
> I hope that you mean that you are using an ORDER BY clause when
> retrieving the rows from the table.  Inserting the rows into a table
> in a specific order does not mean that Oracle stores the rows in that
> particular order, nor does it mean that Oracle will retrieve the rows
> in that specific order.
>
> Take a look at the LAG or LEAD analytic functions to look at the
> previous or next row, respectively:http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/analys...
>
> I recently showed someone how to use the LEAD function in this recent
> thread:http://groups.google.com/group/comp.databases.oracle.server/browse_th...
>
> You can then compare the previous and next values using the DECODE
> function:http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functi...
>
> SUM(DECODE(MY_COL,'x',1,0)*DECODE(LAG(MY_COL,1) OVER (ORDER BY
> MYCOL2),'c',1,0))
>
> Charles Hooper
> Co-author of "Expert Oracle Practices: Oracle Database Administration
> from the Oak Table"http://hoopercharles.wordpress.com/
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.

Worked great (lag - was a new one on me). Thanks SELECT *
  FROM ( SELECT one,

                     two,
                     three "this_three",
                     four,
                     LAG (three) OVER (ORDER BY four) AS Next_three
              FROM   tablex
          ORDER BY   four) lagit

 WHERE this_three = 'c' AND next_three = 'x' Received on Sat Dec 12 2009 - 09:09:25 CST

Original text of this message