count specific data changes in adjacent ordered rows
cate
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.
Charles Hooper
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.
cate
> On Dec 11, 8:04 pm, cate <catebekens...@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
>
> I recently showed someone how to use the LEAD function in this recent
>
> You can then compare the previous and next values using the DECODE
>
> 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'