Razzak, Cool stuff on the two analytical functions. They enable a class of problem solving in sql that’s otherwise more difficult.
On Thu, Feb 14, 2019 at 8:04 AM A. Razzak Memon <[email protected]> wrote: > 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. > -- Sent from Gmail Mobile -- 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.
