Re: count specific data changes in adjacent ordered rows

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Fri, 11 Dec 2009 18:43:06 -0800 (PST)
Message-ID: <5278dd7a-fe98-4ee3-b4e2-6f982a5fc535_at_m16g2000yqc.googlegroups.com>



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/analysis.htm#i1007032

I recently showed someone how to use the LEAD function in this recent thread:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/d9c7420e0e36231d

You can then compare the previous and next values using the DECODE function:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions040.htm

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. Received on Fri Dec 11 2009 - 20:43:06 CST

Original text of this message