Oracle Analytical Functions : Tutorial Part 2 Covers sum avg lag lead.

In this tutorial video we have demonstrated how to use oracle analytical functions like lag , lead , sum and avg etc.

Example 1.
Use of oracle function sum to display running totals with the use of unbounded preceding.

select sum(Salary) over (order by salary rows unbounded preceding) running_total 
, salary , e.*   from employees e

Identify gap in the contiguous sequences with the lag function.
Ex. In Employees table we have contiguous sequence of employees but due to some anomaly we found that there is now gap in the sequences. Ex Employee Id 210 comes after 206 which is not contiguous.

We can write a query as given below to identify such sequence gaps.

Oracle Lag Function

 select  *  from ( select lag (e.employee_id , 1) over (order by e.employee_id) as prev_emp,   e.* from employees e ) tmp
where (tmp.employee_id - tmp.prev_emp) > 1