Friday 18 April 2008

LAG, the Analytic Function

LAG is a nice analytic function in Oracle SQL which lets you access previous row of a row at the same time you access the current row.

The Oracle documentation says:

LAG is an analytic function. It provides access to more than one row of a table at the same time without a self join. Given a series of rows returned from a query and a position of the cursor, LAG provides access to a row at a given physical offset prior to that position. Found here:

Lets suppose you want to profile your customer by looking in the orders table and trying to figure out when was the last time the customer made an order. Even more you want to see the frequency of his/her ordering. How often he/she is making orders. That is, when was the last time you took an order and when was the last time before the last time ...etc.

Suppose your ORDERS table is this:

---------- --------- --------------
1 25-FEB-08 1
2 25-MAY-06 1
3 25-JAN-08 1
5 29-JAN-07 3
6 25-JAN-08 3
88 20-JAN-04 2

Then if you wanted to look at FK_CUSTOMER_ID=1 and see the history, you can write something like this with the LAG analytic function:

select FK_CUSTOMER_ID, order_date, lag(order_date, 1) over (order by order_date) "PREVIOUS ORDER DATE",
order_date - (lag(order_date, 1) over (order by order_date)) "DAYS AGO"
from orders
order by 2 desc

Which returns the history of the customer orders in a way which gives you an idea on what sort of the customer the customer is:

-------------- --------- --------- ----------
1 25-FEB-08 25-JAN-08 31
1 25-JAN-08 25-MAY-06 610
1 25-MAY-06

Analytics are cool!

No comments: