Wednesday, December 21, 2011

Oracle SQL to calculate value based on previous row value

Oracle database allows you to use SQL to calculate value based on the values of the previous row in a query result set. Use LAG and LEAD analytic function to calculate based on previous rows values.

Both functions have the same usage, as shown below.
LAG (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)

value_expression - Can be a column or a built-in function, except for other analytic functions.
offset - The number of rows preceeding/following the current row, from which the data is to be retrieved. The default value is 1.
default - The value returned if the offset is outside the scope of the window. The default value is NULL.


select salary,lag(salary+300,1,0) over (order by salary) as new_sal,lag(salary+200,2,0) over (order by salary) as new_sal2 from employees;

Related Posts Plugin for WordPress, Blogger...