The LAG and LEAD analytical functions can be use to specify more than one row in a table at a time without having to join the table to itself.
Options
AS alias
Specifies an alias name for the column/expression.
colname
Specifies a column name.
default
Specifies the value that is returned if the offset goes out of the bounds of the table. If this optional parameter is omitted, the default is NULL. The default must be a value that can be evaluated from looking at data in a single row.
expression
Determines a value using a text or arithmetic formula. The expression must be a value that can be evaluated from looking at data in a single row. Lookup expressions are not supported.
LAG
Specifies the LAG analytic to query more than one row in a table at a time returning values from a previous row in the table.
LEAD
Specifies the LEAD analytic to query more than one row in a table at a time returning values from a next row in the table.
offset
Specifies the physical offset from the current row in the table. If this optional parameter is omitted, the default is 1. The offset must be a value that can be evaluated from looking at data in a single row.
ORDER BY clause
Sorts rows of data, and supports standard ORDER BY clauses with ASC and DESC directions. When used, there should be no other ORDER BY clauses in the command.
OVER
Provides an optional specification for grouping and sorting. By specifying the OVER syntax, the command must also contain an ORDER BY clause. For the overall SELECT, the PARTITION BY and ORDER BY will determine the sequencing of the output.
PARTITION BY
Specifies optional grouping of the data where the LEAD or LAG function is restricted to be computed within each group. It is very similar to how GROUP BY clauses work for functions like MIN and MAX. For example, a table containing several students with several test scores for each student, a PARTITION BY student will mean that a given student's LEAD or LAG functions will come only from other rows that belong to that same student.
Note:
•LEAD and LAG functions are "SELECT only" type expressions and cannot be used in SET VAR commands.
Examples:
Example 01:
The following example uses the LAG function to compare prior sales between California companies. The PARTITION BY clause is specified to divide the rows in the result set by company. The ORDER BY clause in the OVER clause orders the rows in each partition. Notice that because there is no lag value available for the first row, and where a single purchases exists, the default of zero ($0.00) is returned.
SELECT BillToCompany, TransDate, NetAmount, +
LAG(NetAmount,1,0) AS PrevAmount +
OVER (PARTITION BY BillToCompany ORDER BY TransDate) +
FROM InvoiceHeader WHERE BillToState = 'CA'
BillToCompany TransDate NetAmount PrevAmount
---------------------------------------- ---------- --------------- ---------------
Compumasters Computer Supply 01/17/2018 $7,775.00 $0.00
Compumasters Computer Supply 03/01/2018 $3,080.00 $7,775.00
Compumasters Computer Supply 06/12/2018 $8,955.00 $3,080.00
Compumasters Computer Supply 10/12/2018 $4,308.00 $8,955.00
Compumasters Computer Supply 11/13/2018 $3,512.00 $4,308.00
Compumasters Computer Supply 12/01/2018 $1,615.50 $3,512.00
Compumasters Computer Supply 12/22/2018 $20,852.50 $1,615.50
Industrial Concepts Inc. 05/01/2018 $4,477.50 $0.00
Johnson Technologies 05/02/2018 $1,881.00 $0.00
Open Systems I/O 07/06/2018 $2,390.00 $0.00
PC Consultation And Design 07/07/2018 $9,450.00 $0.00
PC Consultation And Design 07/15/2018 $2,772.00 $9,450.00
Example 02:
The following example uses the LEAD function to compare employee sales variations for the first quarter. The ORDER BY clause in the OVER clause orders the last names in each date partition.
SELECT TransDate,(EmpFName&EmpLName)=20,NetAmount, +
LEAD(NetAmount,1,0) AS NextAmount +
OVER (PARTITION BY TransDate ORDER BY EmpLName) +
FROM SalesByEmployee WHERE (IMON(TransDate)) < 4
TransDate (EmpFName&EmpLName) NetAmount NextAmount
---------- -------------------- --------------- ---------------
01/05/2018 Peter Coffin $17,560.00 $13,941.00
01/05/2018 Ernest Hernandez $13,941.00 $0.00
01/06/2018 John Chow $895.50 $0.00
01/14/2018 Ernest Hernandez $13,572.00 $0.00
01/15/2018 Joe Donohoe $19,755.00 $0.00
01/17/2018 Peter Coffin $7,775.00 $0.00
01/22/2018 Joe Donohoe $4,508.75 $1,975.50
01/22/2018 Ernest Hernandez $1,975.50 $0.00
01/23/2018 Sam Donald $8,616.00 $15,551.00
01/23/2018 Ernest Hernandez $15,551.00 $4,972.50
01/23/2018 John Smith $4,972.50 $0.00
02/01/2018 Peter Coffin $1,672.00 $16,155.00
02/01/2018 Sam Donald $16,155.00 $0.00
02/26/2018 Peter Coffin $3,231.00 $0.00
03/01/2018 Sam Donald $3,080.00 $7,024.00
03/01/2018 John Smith $7,024.00 $0.00
03/09/2018 Joe Donohoe $7,182.00 $0.00
03/10/2018 John Smith $2,772.00 $0.00
03/20/2018 Mary Simpson $4,158.00 $0.00
03/21/2018 John Smith $7,392.00 $0.00
03/23/2018 Darnell Williams $2,151.00 $0.00
