vsbabu.org

LEAD over rows

Yesterday, I had to solve a problem using Python for data coming from an Access database. Had the data been in Oracle (8i or above), this could have been solved at the SQL level. Essentially, I needed to jump ahead in a query and display values from future records, in the current record. You can think of this like converting row information into column information.

I’ve a table called TRACK_DATES with two columns, viz., TRACK_DESCRIPTION and EFFECTIVE_DATE. Here, I define the quarters on when we need to track the information. For example, for FY 04 Q1, effective date is 6/1/2003. I calculate the end date of the Q1 by finding the effective date of next quarter and subtracting a day. Oracle 8i+ has an analytic function called LEAD to jump over records.

Here’s the transcript from SQL*Plus that should make this clear. In this case, I’ve used it with a date column. It works with other data types too.

SQL> select * from track_dates;

EFFECTIVE TRACK_DESCRIPTIO
--------- ----------------
01-JUN-03 FY04 Q1
01-OCT-03 FY04 Q2
01-JAN-04 FY04 Q3
01-APR-04 FY04 Q4
01-JUN-04 FY05 Q1

SQL> select track_description,
effective_date as quarter_start_date,
(lead(effective_date,1) over (order by effective_date))-1 as quarter_end_date,
(lead(effective_date,1) over (order by effective_date)) as next_quarter_start_date,
(lead(effective_date,2) over (order by effective_date))-1 as next_quarter_end_date
from track_dates
order by effective_date asc;

TRACK_DESCRIPTIO QUARTER_S QUARTER_E NEXT_QUAR NEXT_QUAR
---------------- --------- --------- --------- ---------
FY04 Q1          01-JUN-03 30-SEP-03 01-OCT-03 31-DEC-03
FY04 Q2          01-OCT-03 31-DEC-03 01-JAN-04 31-MAR-04
FY04 Q3          01-JAN-04 31-MAR-04 01-APR-04 31-MAY-04
FY04 Q4          01-APR-04 31-MAY-04 01-JUN-04
FY05 Q1          01-JUN-04


From Oracle manuals:

LEAD 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, LEAD provides access to a row at a given physical offset beyond that position.