Thursday, February 14, 2019
Tip of the Day: New LAG and LEAD Analytic Functions for SELECT Commands
Product.......: R:BASE X.5 and R:BASE X.5 Enterprise (Version 10.5)
Build.........: 10.5.1.30214 or higher
Sections......: Functions
Keywords......: SELECT, LAG, LEAD, Analytic
Did you know two analytical functions, LAG and LEAD, have been implemented
for SELECT statements?
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.
LAG accesses data from a previous row in the same result set. LAG provides
access to a row at a given physical offset that comes before the current
row. Use this analytic function in a SELECT statement to compare values in
the current row with values in a previous row.
LEAD accesses data from a next row in the same result set. LEAD provides
access to a row at a given physical offset that comes after the current
row. Use this analytic function in a SELECT statement to compare values
in the current row with values in a next row.
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 $895.50
01/06/2018 John Chow $895.50 $13,572.00
01/14/2018 Ernest Hernandez $13,572.00 $19,755.00
01/15/2018 Joe Donohoe $19,755.00 $7,775.00
01/17/2018 Peter Coffin $7,775.00 $4,508.75
01/22/2018 Joe Donohoe $4,508.75 $1,975.50
01/22/2018 Ernest Hernandez $1,975.50 $8,616.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 $1,672.00
02/01/2018 Peter Coffin $1,672.00 $16,155.00
02/01/2018 Sam Donald $16,155.00 $3,231.00
02/26/2018 Peter Coffin $3,231.00 $3,080.00
03/01/2018 Sam Donald $3,080.00 $7,024.00
03/01/2018 John Smith $7,024.00 $7,182.00
03/09/2018 Joe Donohoe $7,182.00 $2,772.00
03/10/2018 John Smith $2,772.00 $4,158.00
03/20/2018 Mary Simpson $4,158.00 $7,392.00
03/21/2018 John Smith $7,392.00 $2,151.00
03/23/2018 Darnell Williams $2,151.00 $0.00
Very Best R:egards,
Razzak.
https://www.rbase.com
http://www.facebook.com/rbase/
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google Groups "RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
