The first set of T-SQL window functions have been available for 10 years, but many SQL Server professionals are not quite sure what window functions are. I am frequently asked by people I meet at conferences and customer sites “What are window functions?” I always answer with another question, “Do you use ROW_NUMBER?” Almost every time, the answer is “Yes. I didn’t know that was a window function.”
T-SQL window functions are different than other T-SQL functions because they operate over a set, or window, of rows. Notice that I am not capitalizing “window.” They have nothing to do with the Windows operating system. They are part of the ANSI SQL 2003 standard.
T-SQL window functions give you the ability to perform a calculation over the rows that are the result of the FROM, WHERE, GROUP BY and HAVING clauses. You can do things like include a column from a different row, calculate running totals, and perform summary calculations without losing details or grouping.
My favorite T-SQL window function is called LAG. LAG, introduced with SQL Server 2012, lets you bring in a column from a previous row. This is useful for calculating year over year performance, for example.
Here is the syntax of the LAG function:
LAG(<expression>[,offset][,default]) OVER([PARTITION BY <expression>] ORDER BY <expression>)
You must provide the column name that you wish to bring forward, and you must provide an ORDER BY expression. The ORDER BY is used to determine just which row is the previous row. By default, the value returned is from the previous row. By specifying an offset, you can go back any number of rows. You can also specify a default value to replace any NULLs that are returned.
Run the following script to create and populate a table for testing LAG.
CREATE TABLE #Sales ( SalesYear INT NOT NULL, SalesQtr INT NOT NULL, SalesAmt MONEY CONSTRAINT PK_Sales PRIMARY KEY(SalesYear, SalesQtr)); INSERT INTO #Sales( SalesYear, SalesQtr, SalesAmt ) VALUES (2009,1,1400),(2009,2,2200),(2009,3,2800),(2009,4,1000), (2010,1,1200),(2010,2,2300),(2010,3,2850),(2010,4,900), (2011,1,1550),(2011,2,2600),(2011,3,2900),(2011,4,1100), (2012,1,1500),(2012,2,2500),(2012,3,2800),(2012,4,1000), (2013,1,1300),(2013,2,2800),(2013,3,2750),(2013,4,900), (2014,1,1550),(2014,2,2700),(2014,3,2700),(2014,4,1200);
The following query uses the LAG function with an offset value of four to return the sales from the same quarter of the previous year.
SELECT SalesYear, SalesQtr, SalesAmt, LAG(SalesAmt,4) OVER(ORDER BY SalesYear, SalesQtr) AS PrevYearQtrSales FROM #Sales;
Figure 1: The partial results of using LAG
To calculate year-over-year sales, you subtract the previous year’s sales from the current sales and divide by the previous sales. To make the calculation a bit simpler, I’ll add the query to a CTE and perform the calculation in the outer query.
WITH Sales AS ( SELECT SalesYear, SalesQtr, SalesAmt, LAG(SalesAmt,4) OVER(ORDER BY SalesYear, SalesQtr) AS PrevYearSales FROM #Sales) SELECT SalesYear, SalesQtr, SalesAmt, Sales.PrevYearSales, FORMAT((SalesAmt - PrevYearSales)/PrevYearSales,'P') AS YOY FROM Sales;
Figure 2: Using LAG for year-over-year calculation
If you want to get started with SQL Server with the help of experts, read more over at Fix Your SQL Server.
Reference: Pinal Dave (https://blog.sqlauthority.com)
Excellent article! Pinal and Kathi’s articles and books have helped me so much over the years. I am buying Kathi’s Function book immediately!
Order By clause is not working inside the aggregate window function in sql server 2014 ??