{"id":79311,"date":"2018-06-13T16:01:12","date_gmt":"2018-06-13T16:01:12","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=79311"},"modified":"2021-09-29T16:21:01","modified_gmt":"2021-09-29T16:21:01","slug":"introduction-to-t-sql-window-functions","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/introduction-to-t-sql-window-functions\/","title":{"rendered":"Introduction to T-SQL Window Functions"},"content":{"rendered":"

I\u2019ve spent the past six years traveling around the US telling database professionals about T-SQL Window Functions at SQL Saturdays and other events. I\u2019m amazed at how few people have heard about these functions and even fewer who are using them. At the end of each presentation, one or more people come up to say that they wished they learned about these functions years earlier because they could have been beneficial for so many queries.<\/p>\n

These functions have been promoted to improve performance over other, more traditional methods. I partially agree. They make many queries easier to write, and, sometimes, they improve performance.<\/p>\n

Nothing to do with the Windows OS<\/h2>\n

These functions are part of the ANSI SQL 2003 Standards and, in the case of SQL Server, are T-SQL functions used to write queries. They have nothing to do with the Windows operating system or any API calls. Other database systems, such as Oracle, have also included these as part of their own SQL language.<\/p>\n

Window (also, windowing or windowed) functions perform a calculation over a set of rows. I like to think of \u201clooking through the window\u201d at the rows that are being returned and having one last chance to perform a calculation. The window is defined by the OVER<\/strong> clause which determines if the rows are partitioned into smaller sets and if they are ordered. In fact, if you use a window function you will always use an OVER<\/strong> clause. The OVER<\/strong> clause is also part of the NEXT VALUE FOR<\/strong> syntax required for the sequence object, but, otherwise it\u2019s used with window functions.<\/p>\n

The OVER<\/strong> clause may contain a PARTITION BY<\/strong> option. This breaks the rows into smaller sets. You might think that this is the same as GROUP BY<\/strong>, but it\u2019s not. When grouping, one row per unique group is returned. When using PARTITION BY<\/strong>, all of the detail rows are returned along with the calculations. If you have a window in your home that is divided into panes, each pane is a window. When thinking about window functions, the entire set of results is a partition, but when using PARTITION BY<\/strong>, each partition can also be considered a window. PARTITION BY<\/strong> is supported \u2013 and optional \u2013 for all windowing functions.<\/p>\n

The OVER<\/strong> clause may also contain an ORDER BY<\/strong> option. This is independent of the ORDER BY<\/strong> clause of the query. Some of the functions require ORDER BY<\/strong>, and it\u2019s not supported by the others. When the order of the rows is important when applying the calculation, the ORDER BY<\/strong> is required.<\/p>\n

Window functions may be used only in the SELECT<\/strong> and ORDER BY<\/strong> clauses of a query. They are applied after any joining, filtering, or grouping.<\/p>\n

Ranking Functions<\/h2>\n

The most commonly used window functions, ranking functions, have been available since 2005. That\u2019s when Microsoft introduced ROW_NUMBER<\/strong>, RANK<\/strong>, DENSE_RANK<\/strong>, and NTILE<\/strong>. ROW_NUMBER<\/strong> is used very frequently, to add unique row numbers to a partition or to the entire result set. Adding a row number, or one of the other ranking functions, is not usually the goal, but it is a step along the way to the solution.<\/p>\n

ORDER BY<\/strong> is required in the OVER<\/strong> clause when using ROW_NUMBER<\/strong> and the other functions in this group. This tells the database engine the order in which the numbers should be applied. If the values of the columns or expressions used in the ORDER BY<\/strong> are not unique, then RANK<\/strong> and DENSE_RANK<\/strong> will deal with the ties, while ROW_NUMBER<\/strong> doesn\u2019t care about ties. NTILE<\/strong> is used to divide the rows into buckets based on the ORDER BY<\/strong>.<\/p>\n

One benefit of ROW_NUMBER<\/strong> is the ability to turn non-unique rows into unique rows. This could be used to eliminate duplicate rows, for example.<\/p>\n

To show how this works, start with a temp table containing duplicate rows. The first step is to create the table and populate it.<\/p>\n

CREATE TABLE #Duplicates(Col1 INT, Col2 CHAR(1));\r\nINSERT INTO #Duplicates(Col1, Col2) \r\nVALUES(1,'A'),(2,'B'),(2,'B'),(2,'B'),\r\n\t(3,'C'),(4,'D'),(4,'D'),(5,'E'),\r\n\t(5,'E'),(5,'E');\r\nSELECT * FROM #Duplicates;<\/pre>\n
<\/p>\n
Adding ROW_NUMBER<\/strong> and partitioning by each column will restart the row numbers for each unique set of rows. You can identify the unique rows by finding those with a row number equal to one.<\/p>\n
SELECT Col1, Col2, \r\n   ROW_NUMBER() OVER(PARTITION BY Col1, Col2 ORDER BY Col1) AS RowNum\r\nFROM #Duplicates;<\/pre>\n
<\/p>\n
Now, all you have to do is to delete any rows that have a row number greater than one. The problem is that you cannot add window functions to the WHERE<\/strong> clause.<\/p>\n
DELETE #Duplicates \r\nWHERE ROW_NUMBER() OVER(PARTITION BY Col1, Col2 ORDER BY Col1) <> 1;<\/pre>\n
You\u2019ll see this error message:<\/p>\n
<\/p>\n
The way around this problem is to separate the logic using a common table expression (CTE). You can then delete the rows right from the CTE.<\/p>\n
WITH Dupes AS (\r\n   SELECT Col1, Col2, \r\n     ROW_NUMBER() OVER(PARTITION BY Col1, Col2 ORDER BY Col1) AS RowNum\r\n   FROM #Duplicates)\r\nDELETE Dupes \r\nWHERE RowNum <> 1;\r\nSELECT * FROM #Duplicates;<\/pre>\n
<\/p>\n
Success! The extra rows were deleted, and a unique set of rows remains.<\/p>\n
To see the difference between ROW_NUMBER<\/strong>, RANK<\/strong>, and DENSE_RANK<\/strong>, run this query:<\/p>\n
USE Adventureworks2017; --Or whichever version you have\r\nGO\r\nSELECT SalesOrderID, OrderDate, CustomerID, \r\n\tROW_NUMBER() OVER(ORDER BY OrderDate) As RowNum,\r\n\tRANK() OVER(ORDER BY OrderDate) As Rnk,\r\n\tDENSE_RANK() OVER(ORDER BY OrderDate) As DenseRnk\r\nFROM Sales.SalesOrderHeader\r\nWHERE CustomerID = 11330;<\/pre>\n
<\/p>\n
The ORDER BY<\/strong> for each OVER<\/strong> clause is OrderDate<\/strong> which is not unique. This customer placed two orders on 2013-10-24. ROW_NUMBER<\/strong> just continued assigning numbers and didn\u2019t do anything different even though there is a duplicate date. RANK<\/strong> assigned 6 to both rows and then caught up to ROW_NUMBER<\/strong> with an 8 on the next row. DENSE_RANK<\/strong> also assigned 6 to the two rows but assigned 7 to the following row.<\/p>\n
Two explain the difference, think of ROW_NUMBER<\/strong> as positional<\/em>. RANK<\/strong> is both positional<\/em> and logical<\/em>. Those two rows are ranked logically the same, but the next row is ranked by the position in the set. DENSE_RANK<\/strong> ranks them logically<\/em>. Order 2013-11-04 is the 7th<\/sup> unique date.<\/p>\n
The final function in this group is called NTILE<\/strong>. It assigns bucket numbers to the rows instead of row numbers or ranks. Here is an example:<\/p>\n
SELECT SP.FirstName, SP.LastName,\r\n\tSUM(SOH.TotalDue) AS TotalSales, \r\n\tNTILE(4) OVER(ORDER BY SUM(SOH.TotalDue)) * 1000 AS Bonus\r\nFROM [Sales].[vSalesPerson] SP \r\nJOIN Sales.SalesOrderHeader SOH \r\n     ON SP.BusinessEntityID = SOH.SalesPersonID \r\nWHERE SOH.OrderDate >= '2012-01-01' AND SOH.OrderDate < '2013-01-01'\r\nGROUP BY FirstName, LastName;<\/pre>\n
<\/p>\n
NTILE<\/strong> has a parameter, in this case 4, which is the number of buckets you want to see in the results. The ORDER BY<\/strong> is applied to the sum of the sales. The rows with the lowest 25% are assigned 1, the rows with the highest 25% are assigned 4. Finally, the results of NTILE<\/strong> are multiplied by 1000 to come up with the bonus amount. Since 14 cannot be evenly divided by 4, an extra row goes into each of the first two buckets.<\/p>\n
Window Aggregates<\/h2>\n
Window aggregates were also introduced with SQL Server 2005. These make writing some tricky queries easy but will often perform worse than older techniques. They allow you to add your favourite aggregate function to a non-aggregate query. Say, for example you would like to display all the customer orders along with the subtotal for each customer. By adding a SUM<\/strong> using the OVER<\/strong> clause, you can accomplish this very easily:<\/p>\n
SELECT CustomerID, OrderDate, SalesOrderID, TotalDue, \r\n\tSUM(TotalDue) OVER(PARTITION BY CustomerID) AS SubTotal \r\nFROM Sales.SalesOrderHeader;<\/pre>\n
<\/p>\n
By adding the PARTITION BY<\/strong>, a subtotal is calculated for each customer. Any aggregate function can be used, and ORDER BY<\/strong> in the OVER<\/strong> clause is not supported.<\/p>\n
Window Aggregate Enhancements in 2012<\/h2>\n
Beginning with 2012, you can add an ORDER BY<\/strong> to the OVER<\/strong> clause to window aggregates to produce running totals and moving averages, for example. At the same time, Microsoft introduced the concept of framing. Adding a PARTITION BY<\/strong> is like dividing a window into panes. Adding framing is like creating a stained-glass window. Each row has an individual window where the expression will be applied.<\/p>\n
With this enhancement, you can create running totals even without adding the framing syntax. Here is an example that returns a running total by customer:<\/p>\n
SELECT CustomerID, OrderDate, SalesOrderID, TotalDue, \r\n   SUM(TotalDue) OVER(PARTITION BY CustomerID ORDER BY SalesOrderID) \r\n      AS RunningTotal \r\nFROM Sales.SalesOrderHeader;<\/pre>\n
<\/p>\n
The default frame, which is used if a frame is not specified, is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW<\/strong>. Unfortunately, this will not perform as well as if you specify this frame instead: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW<\/strong>. The difference is the word ROWS<\/strong>. RANGE<\/strong> is only partially implemented at this time, and it\u2019s meant for working with periods of time, while ROWS<\/strong> is positional. The frame, ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW<\/strong>, means that the window consists of the first row of the partition and all the rows up to the current row. Each calculation is done over a different set of rows. For example, when performing the calculation for row 4, the rows 1 to 4 are used.<\/p>\n
<\/p>\n
When performing the calculation for row 5, the rows are 1 to 5. The window grows larger as you move from one row to the next.<\/p>\n
<\/p>\n
You can also use the syntax ROWS BETWEEN N PRECEEDING AND CURRENT ROW<\/strong> or ROWS BETWEEN CURRENT ROW AND N FOLLOWING<\/strong>. This could be useful for calculating a three-month moving average, for example. The following figure represents ROWS BETWEEN 2 PRECEDING AND CURRENT ROW<\/strong>.<\/p>\n
<\/p>\n
When 5 is the current row, the window moves; it doesn\u2019t change size.<\/p>\n
<\/p>\n
Here is the list of terms you need to know when writing the framing option:<\/p>\n
<\/p>\n
I admit that this syntax is a bit confusing but using SQL Prompt<\/a> helps makes writing the framing option easier!<\/p>\n
Offset Functions<\/h2>\n
Also included with the release of SQL Server 2012 are four functions that allow you to include values from other rows \u2013 without doing a self-join. Microsoft calls these \u2018analytic functions\u2019, but I always refer to them as \u2018offset functions\u2019 when presenting on this topic. Two of the functions allow you to pull columns or expressions from a row before (LAG<\/strong>) or after (LEAD<\/strong>) the current row. The other two functions allow you to return values from the first row of the partition (FIRST_VALUE<\/strong>) or last row of the partition (LAST_VALUE<\/strong>). FIRST_VALUE<\/strong> and LAST_VALUE<\/strong> also require framing, so be sure to include the frame when using these functions. All four of the functions require the ORDER BY<\/strong> option of the OVER<\/strong> clause. That makes sense, because the database engine must know the order of the rows to figure out which row contains the value to return.<\/p>\n
Some people have a favourite band; some people have a favourite movie. I have a favourite function \u2013 LAG<\/strong>. It\u2019s easy to use (no frame!) and performs great. Here is an example:<\/p>\n
 SELECT CustomerID, OrderDate, SalesOrderID, \r\n   LAG(SalesOrderID) OVER(PARTITION BY CustomerID ORDER BY SalesOrderID\r\n   ) AS PrevOrder\r\nFROM Sales.SalesOrderHeader\r\nORDER BY CustomerID;<\/pre>\n
<\/p>\n
LAG<\/strong> and LEAD<\/strong> require an argument \u2013 the column or expression you want to return. By default, LAG<\/strong> returns the value from the previous row, and LEAD<\/strong> returns the value from the following row. You can modify that by supplying a value for the OFFSET<\/strong> parameter, which is 1 by default. Notice that the first row of the partition returns NULL<\/strong>. If you wish to override the NULL<\/strong>s, you can supply a DEFAULT<\/strong> value. Here is a similar query that goes back two rows and has a default value:<\/p>\n
SELECT CustomerID, OrderDate, SalesOrderID, \r\n   LAG(SalesOrderID,2,0) OVER(PARTITION BY CustomerID \r\n    ORDER BY SalesOrderID) AS Back2Orders\r\nFROM Sales.SalesOrderHeader;<\/pre>\n
<\/p>\n
FIRST_VALUE<\/strong> and LAST_VALUE<\/strong> can be used to find a value from the very first row or very last row of the partition. Be sure to specify the frame, not only for performance reasons, but because the default frame doesn\u2019t work as you would expect with LAST_VALUE<\/strong>. The default frame, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW<\/strong>, only goes up to the current row. The last row of the partition is not included. To get the expected results, be sure to specify ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING<\/strong> when using LAST_VALUE<\/strong>. Here is an example using FIRST_VALUE<\/strong>:<\/p>\n
SELECT CustomerID, OrderDate, SalesOrderID, \r\n   FIRST_VALUE(SalesOrderID) OVER(PARTITION BY CustomerID \r\n      ORDER BY SalesOrderID\r\n   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS FirstOrder\r\nFROM Sales.SalesOrderHeader;<\/pre>\n
<\/p>\n
Statistical Functions<\/h2>\n
Microsoft groups these four functions \u2013 PERCENT_RANK<\/strong>, CUME_DIST<\/strong>, PERCENTILE_DISC<\/strong>, PERCENTILE_CONT<\/strong> \u2013 along with the offset functions calling all eight the analytic functions. Since I like to distinguish these from the offset functions, I call these statistical.<\/p>\n
PERCENT_RANK<\/strong> and CUME_DIST<\/strong> provide a ranking for each row over a partition. They differ slightly. PERCENT_RANK<\/strong> returns the percentage of rows that rank lower than the current row. \u201cMy score is higher than 90% of the scores.\u201d CUME_DIST<\/strong>, or cumulative distribution, returns the exact rank. \u201cMy score is at 90% of the scores.\u201d Here is an example using the average high temperature in St. Louis for each month. Note that the ranks were determined by the Fahrenheit temperature.<\/p>\n
CREATE TABLE #MonthlyTempsStl(MName varchar(15), AvgHighTempF INT, \r\n     AvgHighTempC INT)\r\nINSERT INTO #MonthlyTempsStl(MName, AvgHighTempF, AvgHighTempC)\r\nVALUES('Jan',40,4),('Feb',45, 7),('Mar',56, 13),('Apr',67, 20),\r\n\t  ('May',76,25),('Jun',85,30),('Jul',89,32),('Aug',88,31),\r\n\t  ('Sep',80,27),('Oct',69,20),('Nov',56,13),('Dec',43,6);\r\nSELECT MName, AvgHighTempF,AvgHighTempC, \r\n   RANK() OVER(ORDER BY AvgHighTempF) AS Rank,\r\n   PERCENT_RANK() OVER(ORDER BY AvgHighTempF)  AS PercentRank,\r\n   CUME_DIST() OVER(ORDER BY AvgHighTempF)  AS CumeDist\r\nFROM #MonthlyTempsStl;<\/pre>\n
<\/p>\n
The ranks are not determined by the relative values, but by the positions of the rows. Notice that March and November have the same average high temp, so they were ranked the same.<\/p>\n
You may be wondering how to calculate PERCENT_RANK<\/strong> and CUME_DIST<\/strong>. Here are the formulas:<\/p>\n
PERCENT_RANK = (Rank -1)\/(Row count -1)\r\nCUME_DIST = (Rank)\/(Row count)<\/pre>\n
PERCENTILE_DISC<\/strong> and PERCENTILE_CONT<\/strong> work in the opposite way. Given a percent rank, find the value at that rank. They differ in that PERCENTILE_DISC<\/strong> will return a value that exists in the set while PERCENTILE_CONT<\/strong> will calculate an exact value if none of the values in the set falls precisely at that rank. You can use PERCENTILE_CONT<\/strong> to calculate a median by supplying 0.5 as the percent rank. For example, which temperature ranks at 50% in St. Louis?<\/p>\n
SELECT MName, AvgHighTempF,AvgHighTempC,\r\n   RANK() OVER(ORDER BY AvgHighTempF) AS Rank,\r\n   PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY AvgHighTempF) \r\n      OVER() AS PercentileCont,\r\n   PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY AvgHighTempF) \r\n      OVER() AS PercentileDisc\r\nFROM #MonthlyTempsStl;<\/pre>\n
<\/p>\n
The PERCENTILE_CONT<\/strong> function takes the average of the two values closest to the middle, 67 and 69, and averages them. PERCENTILE_DISC<\/strong> returns an exact value, 67. Also notice that these two functions have an extra clause not seen in the other functions, WITHIN GROUP<\/strong>, that contains the ORDER BY<\/strong> instead of within the OVER<\/strong> clause.<\/p>\n
Summary<\/h2>\n
This article is a very quick overview of T-SQL window functions. Two types of functions were released with SQL Server 2005, the ranking functions and window aggregates. With 2012, you have enhanced window aggregate with framing and the analytic functions. I like to separate the analytic functions into two groups, the offset and statistical functions. Window functions make many queries easier to write, and I believe that is the main benefit. In some cases, the queries will perform better, too, but that is a discussion for another day.<\/p>\n
I hope this article has inspired you to learn more about these fantastic functions!<\/p>\n","protected":false},"excerpt":{"rendered":"
T-SQL window functions were introduced in 2005 with more functionality added in 2012. Many database professionals are not aware of these useful functions. In this article, Kathi Kellenberger provides a quick overview of just what a window function is as well as examples of each type of function.…<\/p>\n","protected":false},"author":110218,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[5134],"coauthors":[11292],"class_list":["post-79311","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-sql-prompt"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/79311","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/110218"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=79311"}],"version-history":[{"count":11,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/79311\/revisions"}],"predecessor-version":[{"id":83359,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/79311\/revisions\/83359"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=79311"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=79311"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=79311"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=79311"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}