Sliding Window Syntax

Database
Enthusiast

Sliding Window Syntax

I am not positive this is a sliding window problem, but it is my best guess. I would like to show the current rows values and the next row, on the same output record, and then advance to the next recor --, repeat.

 I think the simple example below illustrates my problem.  Given the data in table TEST1, I would like to write a query which gives me output similar to my example.  

On a related note, is there a white paper someone which  explains the usage and idiosyncrasis of Ordered Analytic Functions?  I get really confused reading the docs - so many possibilities and so few brain cells.

CREATE TABLE TEST1  (

ID INT,

YR CHAR(4),

VAL CHAR(1)

) UNIQUE PRIMARY INDEX PK (ID, YR) ;

INSERT TEST1 (ID, YR, VAL) VALUES (1,'2001', 'A');

INSERT TEST1 (ID, YR, VAL) VALUES (1,'2002', 'B');

INSERT TEST1 (ID, YR, VAL) VALUES (1,'2003', 'C');

INSERT TEST1 (ID, YR, VAL) VALUES (1,'2004', 'D');

INSERT TEST1 (ID, YR, VAL) VALUES (2,'2001', 'e');

INSERT TEST1 (ID, YR, VAL) VALUES (2,'2002', 'f');

INSERT TEST1 (ID, YR, VAL) VALUES (2,'2003', 'g');

INSERT TEST1 (ID, YR, VAL) VALUES (2,'2004', 'h');

SEL * FROM test1 ORDER BY 1,2;

-- desired output of query

ID  Yr  VALUE   Next_year   Next_year_value

1   2001    A   2002    B

1   2002    B   2003    C

1   2003    C   2004    D

2   2001    e   2002    f

2   2002    f   2003    g

2   2003    g   2004    h

Thanks a lot,

John J

2 REPLIES
Senior Apprentice

Re: Sliding Window Syntax

Hi John,

LEAD and LAG are just shortcuts for a basic OLAP function:

select id, yr, value,
min(yr)
over (partition by id
order by yr
rows between 1 following and 1 following) as next_yr, -- Oracle's LAG
min(value)
over (partition by id
order by yr.
rows between 1 following and 1 following) as next_value
from test1
qualify next_value is not null -- not the last row
order by id, yr

Dieter

Enthusiast

Re: Sliding Window Syntax

Thanks a lot, Dieter.  Exactly what I was looking for.  And now that I have a context, some of the manual in Chpt 11 actually makes sense to me :-)  Appreciate your help.

John