SQL LAG( ) Function

The LAG () Function is a Value Window Function used to fetch the previous row data along with the current row data of the specified offset, in other words, it is said as the LAG() Function access data of the previous row or the row before the previous row.

These LAG Functions are very useful in case we need to compare the value of the current row with the value of the previous row.

  • Syntax for LAG( ) Function – 

LAG(return_value,Offset,Default_value) OVER ([PARTITION BY expression,..] ORDER BY column1,column2,..ASC|DESC);

Here we have some terms in the syntax, let’s have a look at them –

  • return_value – The return_value is the value of the previous row of the given offset.
  • offset – The offset is the no. of rows back from the current row. This can be an expression or column which should be a positive integer.
  • default – This value is used when the offset reaches beyond the scope of the partition. It is NULL if no value is specified.

Demo Table – 

Product Table

Category Region City Country Sales
Office Supplies Central Chicago United States 15.696
Technology East New York City United States 539.97
Office Supplies East New York City United States 22.58
Office Supplies East Dover United States 19.54
Office Supplies East Dover United States 671.94
Technology West Aurora United States 169.064
Office Supplies West Aurora United States 168.624
Office Supplies East Long Beach United States 16.52
Office Supplies West Pasadena United States 31.92
Furniture West Pasadena United States 433.568

Example – Consider the above Product Table and execute a query to fetch the sales from the previous row along with the current row value using the LAG () Function.

SELECT Category,Region,City,[Country/Region],Sales , LAG (Sales,1,-1) OVER(PARTITION BY Category ORDER BY Sales ) AS LAG FROM Products;

 

 

Category Region City Country Sales LAG
Furniture West Pasadena United States 433.568 -1
Office Supplies Central Chicago United States 15.696 -1
Office Supplies East Long Beach United States 16.52 15.696
Office Supplies East Dover United States 19.54 16.52
Office Supplies East New York City United States 22.58 19.54
Office Supplies West Pasadena United States 31.92 22.58
Office Supplies West Aurora United States 168.624 31.92
Office Supplies East Dover United States 671.94 168.624
Technology West Aurora United States 169.064 -1
Technology East New York City United States 539.97 169.064

In the above example, we have defined the default value as -1, so whenever the number of rows goes beyond the first row in the dataset, it will show -1 for that row.

 

Other SQL topics to check out:

Follow us on TwitterFacebookLinkedin, and Tableau Public to stay updated with our latest blog and what’s new in Tableau.

Automate data analysis pipeline and create report ready dashboards

If you are looking forward to getting your data pipeline built and setting up the dashboard for business intelligence, book a call now from here.