TIL : Analytic functions in Oracle.

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.

Lag

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.

Example :
Suppose we have following table.

City Population Year
San Jose 1,000,000 2014
San Francisco 1,230,000 2014
San Diego 900,000 2014
Las Vegas 904,000 2014
San Jose 1,200,000 2015
San Francisco 1,330,000 2015
San Diego 910,000 2015
Las Vegas 909,000 2015

CityPopulation 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.

SQL

select city, population,
LAG (population,1) over (ORDER BY year) AS prev_population
from CityPopulation;

City Population Prev_Population
San Jose 1,200,000 1,000,000
San Francisco 1,330,000 1,230,000
San Diego 910,000 900,000
Las Vegas 909,000 904,000

Lead

As shown above. Similar to Lag function,  to return a value from the next row,  use the LEAD function.

Advertisements