The database that we have: SQL> desc saldate; Name Null? Type ----------------------------------------- -------- ---------------------------- SDATE DATE AMOUNT NUMBER The content of the database: SQL> select * from saldate order by sdate; SDATE AMOUNT --------- ---------- 01-JAN-07 5 02-JAN-07 2 03-JAN-07 26 04-JAN-07 15 05-JAN-07 5 06-JAN-07 18 07-JAN-07 7 08-JAN-07 2 09-JAN-07 4 10-JAN-07 4 11-JAN-07 12 SDATE AMOUNT --------- ---------- 12-JAN-07 2 14-JAN-07 7 15-JAN-07 20 18-JAN-07 8 19-JAN-07 16 20-JAN-07 4 21-JAN-07 2 22-JAN-07 3 25-JAN-07 2 20 rows selected. S_SUM: the sum of three rows (the current row together with 2 rows preceding it) SQL> select sdate, amount, sum(amount) over (order by sdate asc rows 2 preceding) s_sum from saldate ; SDATE AMOUNT S_SUM --------- ---------- ---------- 01-JAN-07 5 5 02-JAN-07 2 7 03-JAN-07 26 33 04-JAN-07 15 43 05-JAN-07 5 46 06-JAN-07 18 38 07-JAN-07 7 30 08-JAN-07 2 27 09-JAN-07 4 13 10-JAN-07 4 10 11-JAN-07 12 20 SDATE AMOUNT S_SUM --------- ---------- ---------- 12-JAN-07 2 18 14-JAN-07 7 21 15-JAN-07 20 29 18-JAN-07 8 35 19-JAN-07 16 44 20-JAN-07 4 28 21-JAN-07 2 22 22-JAN-07 3 9 25-JAN-07 2 7 20 rows selected. lag(amount, 1, 0): the value of the previous row, 0 is the default SQL> select sdate, amount, lag(amount, 1, 0) over (order by sdate asc) as previous_day from saldate; SDATE AMOUNT PREVIOUS_DAY --------- ---------- ------------ 01-JAN-07 5 0 02-JAN-07 2 5 03-JAN-07 26 2 04-JAN-07 15 26 05-JAN-07 5 15 06-JAN-07 18 5 07-JAN-07 7 18 08-JAN-07 2 7 09-JAN-07 4 2 10-JAN-07 4 4 11-JAN-07 12 4 SDATE AMOUNT PREVIOUS_DAY --------- ---------- ------------ 12-JAN-07 2 12 14-JAN-07 7 2 15-JAN-07 20 7 18-JAN-07 8 20 19-JAN-07 16 8 20-JAN-07 4 16 21-JAN-07 2 4 22-JAN-07 3 2 25-JAN-07 2 3 20 rows selected. lag(amount, 2, 0): the value of the row that is offset by two rows from the current row (prior to the current row) SQL> select sdate, amount, lag(amount, 2, 0) over (order by sdate asc) from saldate; SDATE AMOUNT LAG(AMOUNT,2,0)OVER(ORDERBYSDATEASC) --------- ---------- ------------------------------------ 01-JAN-07 5 0 02-JAN-07 2 0 03-JAN-07 26 5 04-JAN-07 15 2 05-JAN-07 5 26 06-JAN-07 18 15 07-JAN-07 7 5 08-JAN-07 2 18 09-JAN-07 4 7 10-JAN-07 4 2 11-JAN-07 12 4 SDATE AMOUNT LAG(AMOUNT,2,0)OVER(ORDERBYSDATEASC) --------- ---------- ------------------------------------ 12-JAN-07 2 4 14-JAN-07 7 12 15-JAN-07 20 2 18-JAN-07 8 7 19-JAN-07 16 20 20-JAN-07 4 8 21-JAN-07 2 16 22-JAN-07 3 4 25-JAN-07 2 2 20 rows selected. lead(amount, 1): the value of the row that is offset by one row from the current row (after to the current row) SQL> select sdate, amount, lead(amount, 1) over (order by sdate asc) as day_after from saldate; SDATE AMOUNT DAY_AFTER --------- ---------- ---------- 01-JAN-07 5 2 02-JAN-07 2 26 03-JAN-07 26 15 04-JAN-07 15 5 05-JAN-07 5 18 06-JAN-07 18 7 07-JAN-07 7 2 08-JAN-07 2 4 09-JAN-07 4 4 10-JAN-07 4 12 11-JAN-07 12 2 SDATE AMOUNT DAY_AFTER --------- ---------- ---------- 12-JAN-07 2 7 14-JAN-07 7 20 15-JAN-07 20 8 18-JAN-07 8 16 19-JAN-07 16 4 20-JAN-07 4 2 21-JAN-07 2 3 22-JAN-07 3 2 25-JAN-07 2 20 rows selected. AVG_3: the average of three values (current, one before, one after) SQL> select sdate, amount, lag(amount, 1, 0) over (order by sdate asc) as previous, lead(amount, 1) over (order by sdate asc) as after, (lag(amount, 1, 0) over (order by sdate asc) + lead(amount, 1) over (order by sdate asc) + amount)/3 as avg_3 from saldate order by sdate asc ; SDATE AMOUNT PREVIOUS AFTER AVG_3 --------- ---------- ---------- ---------- ---------- 01-JAN-07 5 0 2 2.33333333 02-JAN-07 2 5 26 11 03-JAN-07 26 2 15 14.3333333 04-JAN-07 15 26 5 15.3333333 05-JAN-07 5 15 18 12.6666667 06-JAN-07 18 5 7 10 07-JAN-07 7 18 2 9 08-JAN-07 2 7 4 4.33333333 09-JAN-07 4 2 4 3.33333333 10-JAN-07 4 4 12 6.66666667 11-JAN-07 12 4 2 6 SDATE AMOUNT PREVIOUS AFTER AVG_3 --------- ---------- ---------- ---------- ---------- 12-JAN-07 2 12 7 7 14-JAN-07 7 2 20 9.66666667 15-JAN-07 20 7 8 11.6666667 18-JAN-07 8 20 16 14.6666667 19-JAN-07 16 8 4 9.33333333 20-JAN-07 4 16 2 7.33333333 21-JAN-07 2 4 3 3 22-JAN-07 3 2 2 2.33333333 25-JAN-07 2 3 20 rows selected. SUM_3: total of three values (the current row, the one within the one day from the current row) Note that this is different than the previous example. SQL> select sdate, amount, sum(amount) over (order by sdate asc range between interval '1' day preceding and interval '1' day following) as sum_3 from saldate; SDATE AMOUNT SUM_3 --------- ---------- ---------- 01-JAN-07 5 7 02-JAN-07 2 33 03-JAN-07 26 43 04-JAN-07 15 46 05-JAN-07 5 38 06-JAN-07 18 30 07-JAN-07 7 27 08-JAN-07 2 13 09-JAN-07 4 10 10-JAN-07 4 20 11-JAN-07 12 18 SDATE AMOUNT SUM_3 --------- ---------- ---------- 12-JAN-07 2 14 14-JAN-07 7 27 15-JAN-07 20 27 18-JAN-07 8 24 19-JAN-07 16 28 20-JAN-07 4 22 21-JAN-07 2 9 22-JAN-07 3 5 25-JAN-07 2 2 20 rows selected.