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: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions070.htm#i1327527

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:

  ORDER_ID ORDER_DAT FK_CUSTOMER_ID
---------- --------- --------------
         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
where FK_CUSTOMER_ID=1
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:

FK_CUSTOMER_ID ORDER_DAT PREVIOUS    DAYS AGO
-------------- --------- --------- ----------
             1 25-FEB-08 25-JAN-08         31
             1 25-JAN-08 25-MAY-06        610
             1 25-MAY-06

Analytics are cool!