Oracle supports many useful analytics functions. Unless you unearth them from Oracle documentation it’s not easy to understand and use them often. This is my attempt to write about Analytic functions supported in Oracle and how to use them.
The Oracle/PLSQL LAG function is an analytic function that lets you query more than one row in a table at a time without having to join the table to itself.
It returns values from a previous row in the table.
Suppose we have following table.
And we need to find out each city’s current year population and previous year’s population.
This can be done using Lag(lagging) function.
select city, population,
LAG (population,1) over (ORDER BY year) AS prev_population
As shown above. Similar to Lag function, to return a value from the next row, use the LEAD function.