This post is part of the series discussing the new Analytic functions in SQL Server “Denali”, CTP3.

Analytic Functions in SQL Server “Denali”

All of the new Analytic functions require the use of the OVER clause. To see what changes to the OVER clause that “Denali” brings, please view my article The OVER Clause enhancements in SQL Server code named “Denali”, CTP3 on SQLServerCentral.com.

Introducing the LAG / LEAD functions

Starting the Analytic Functions series off are the LAG and LEAD functions. The LAG/LEAD functions return a column from a previous/following row in the partition, with respect to the current row, as specified by the row offset in the function, without the use of a self-join. The syntax for these functions is:

Note that the partition by clause is optional, the order by clause is required, and the windowing clause (ROWS|RANGE) is not supported.

The scalar_expression is the value to be returned – this will normally be a column, but it can also be a subquery or any other expression that results in a single value.

The offset is the number of previous (LAG) or following (LEAD) rows from the current row from which to obtain a value, and if not specified it will default to 1.

The default is the value to use if the value at the offset is NULL, and if not specified it will default to NULL.

Using the LEAD function

An example for using LEAD would be to determine the gaps of a numeric column. For the following table, we want to determine the starting and ending range of values for each gap:

The gaps are determined by comparing col1 in the current row to col1 in the next row, and if the difference is > 1 then there is a gap. For each row that has a gap, the next row’s value -1 is the end of the gap, and the current row’s value +1 is the start of the gap. Since we want to compare the current row to the next row, we’ll use the LEAD function. The code for this query is:

and this query produces the following results:

 

Okay, that’s one example. Let’s look at another.

Using the LAG function

Let’s say that we have RFID tags attached to different items. Sensors periodically determine the location of each tag, and store the item, the location where it was picked up at, and a timestamp of when the sensor picked up the item. The following code creates a sample table, and then builds 100 rows of data – 10 items for being at each of 10 locations, with a random timestamp. The code then replaces all occurrences of rows 3, 6 and 9 for each item (as ordered by the timestamp) with the previous rows location value – giving us for each item some rows that are changing location, and some rows that are in the same location.

Here the LAG function was utilized to get the previous row’s Location within each Item, where the previous row is determined by its timestamp value. It’s already been a helpful function, and I still have the rest of the code to show!

Using both the LAG and LEAD functions

Now let’s see a lists of items that did not move – or where the current location and the previous location are the same. And for good measure, let’s look at where the item next moved to.

Here the LAG function was used to get the previous value; the LEAD function was used to get the next value, and then return just the rows where the current location and prior location are the same. The results are:

I think that these two functions offer some incredible possibilities, by taking a peek at the rows adjacent to the current row. Hopefully this post gives you some ideas of how you can use these functions.

References:

LAG
LEAD

Analytic Function Series:

Overview