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.
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.
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
Comments
Post a Comment
Please leave your relevant comments and questions only.