Window Functions in SQL Server

When SQL Server introduced Window Functions in SQL Server 2005, it was done in a rather tentative way, with only a handful of functions being introduced. This was frustrating, as they remove the last excuse for cursor-based operations by providing aggregations over a partition of the result set, and imposing an ordered sequence over a partition. Now, with SQL Server 2012, we are soon to enjoy a full range of Window Functions. They are going to make for some much simpler SQL queries.

Hi Folks, I’m going to start a new series of three articles about Window functions, starting off by explaining what they are, and how they are used to provide details of an aggregation.

Window functions

Window functions belong to a type of function known as a ‘set function’, which means a function that applies to a set of rows. The word ‘window’ is used to refer to the set of rows that the function works on.

Windowing functions were added to the standard SQL:2003 that is managed by the ISO and it was specified in more detail in SQL:2008 For some time, other DBMSs such as Oracle, Sybase and DB2 have had support for window functions. Even the open source RDBMS PostgreSQL has a full implementation. SQL Server has had only a partial implementation up to now, but it is coming in SQL 2012.

One of the most important benefits of window functions is that we can access the detail of the rows from an aggregation. To see an example, let’s first suppose we have this table and data:

This is what the data looks like:

1378-image001.png

If we sum the column Value grouping by ID, by using a conventional GROUP BY, we would have the following query and result:

1378-image002.png

Here we can see a sample of the partitions of rows, or sets that the SUM aggregation function is working on.

1378-image003.png

In the blue we have partition 1, green as partition 2 and red as partition 3. Because we applied the aggregation function in the column value, grouping the results by ID, we the lose the details of the data. In this case the details are the values of the columns of the rows in the partitions 1, 2 and 3.

Let’s suppose I need to write a query to return the total value of sales, the average value of sales and the quantity of sales for each ID, and still return the actual values of the rows, then we might think that we could use something like this to return this data:

Msg 8120, Level 16, State 1, Line 2

Column ‘TestAggregation.Value’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Unfortunately it is against the way that aggregations work. If you group by something, then you lose access to the details.

A very commonly used alternative is to write every aggregation into a subquery, and then correlate with the main query using a join, something like:

1378-image004.png

But a neater and faster solution is this…

A very elegant alternative is to use the clause OVER() implemented in the aggregation functions, it allow-us to access the details of the rows that have been aggregated.

For instance we could try to get the result we want with this …

1378-image005.png

…but, with this query, we didn’t return the expected results. In fact, we returned the aggregate values for the entire table rather than for each ID. Using the clause OVER() with the aggregation function (SUM, AVG and COUNT) we have accessed the details of the grouped data along with a total aggregation of the data, but, in In fact, we want the aggregates for the data grouped by ID. To do this, we should use the clause PARTITION BY clause. For instance:

1378-image004.png

Now we can see the same results as in the subquery alternative, but with a much more simple and elegant code.

In the following picture we can see that even the results are grouped by ID we can see access the details of the aggregated partitions through the clause OVER and PARTITION BY.

1378-image007.jpg

Set based thinking

It can get tedious to hear about this set base thing, but in fact it’s not so easy to think set based. Sometimes it’s very hard to find a set based solution to a query we have. Window Functions give us more set-based solutions to awkward problems.

The main point of windowing functions it’s that they were created to work with a set. SQL Server never was good on processing queries row by row, that’s why you always hearing that ‘cursors are evil’, and are ‘not good for performance’, ‘you should avoid them’ and so on. SQL Server was not built to work row by row.

Let’s illustrate this. SQL Server takes an average of 50 seconds to run a loop of 1 hundred million times against 100 milliseconds of a Win32 app.

1378-image008.png

Delphi Code:

1378-image009.png

Of course it is an unfair comparison. The compiler of a win32 application is totally different from SQL Server, what I wanted to show here is the fact that SQL Server was not supposed to run row by row.

I once was in London doing training with my colleague Itzik Ben-Gan when I remember he said: “There is no row-by-row code that you cannot run an equivalent set-based version, the problem is that you didn’t figured out how to do it”. Yes it’s a heavy phrase but, who could tell Itzik that this is not true? Not I!

Window functions on SQL Server 2005 and 2008

Since SQL Server 2005 we have had support for some window functions, they are: ROW_NUMBER, RANK, DENSE_RANK and NTILE.

In this first article we’ll review how these functions works, and how they can help-us to write better and efficient set-based codes.

Test database

To test the functions we’ll use a table called Tab1. The code to create the table is the following:

Row_Number()

The ROW_NUMBER function is used to generate a sequence of numbers based in a set in a specific order, in easy words, it returns the sequence number of each row inside a set in the order that you specify.

For instance:

1378-image010.png

The column called “ROW_NUMBER()” is one of a series of numbers created in the order of Col1 descending. The clause OVER(ORDER BY Col1 DESC) is used to specify the order of the sequence for which the number should be created. It is necessary because rows in a relational table have no ‘natural’ order.

Rank() & Dense_Rank()

Return the position in a ranking for each row inside a partition. The ranking is calculated by 1 plus the number of previews rows.

It’s important to mention that the function RANK returns the result with a GAP after a tie, whereas the function DENSE_RANK doesn’t. To understand this better, let’s see some samples.

1378-image011.png

Notice that in the RANK result, we have the values 1,1,3 and 4. The value Col1 = “5” is duplicated so any ordering will produce a ‘tie’ for position between them. They have the same position in the rank, but, when the ordinal position for the value 3 is calculated, this position isn’t 2 because the position 2 was already used for the value 5, in this case the an GAP is generated and the function returns the next value for the tank, in this case the value 3.

NTILE()

The NTILE function is used for calculating summary statistics. It distributes the rows within an ordered partition into a specified number of “buckets” or groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs. It makes it easy to calculate n-tile distributions such as percentiles.

Let’s see a sample:

1378-image012.png

In the result above we can see that 4 rows were divided by 3 it’s 1, the remaining row is added in the initial group. Let’s see another sample without remained rows.

1378-image013.png

It is good practice to order on a unique key, ensure that there are more buckets than rows, and to have an equal number of rows in each bucket

The Power of Window functions

Now let’s see some examples where window functions could be used to return some complex queries.

Example 1

Let’s suppose I need to write a query to return those employees that receive more than his department’s average.

Let’s start by creating two sample tables called Departamentos (departments in Portuguese) and Funcionarios (employees in Portuguese):

This is what the data looks like:

1378-image014.png

To write this query I could do something like this:

1378-image015.png

As we can see, the employees Luciano, Nogare and Diego are receiving much more than the average of their department.

Using the OVER clause, I was able to partition the data per each department, and then I could access the average with the details of the salary.

You can avoid using window functions by using a query like this.

Example 2

Another very common problem is the “running totals”. To show this sample I’ll use a very simple data. Let’s started by creating a table called tblLancamentos where I’ve a column with a date and a column with a numeric value.

One of the alternatives to return the running total of the column ValorLancamento ordered by DataLancamento is to write a query like the following:

1378-image016.png

In the query above we join the table with the aggregation to return the total. Another easier, quicker and more elegant alternative would be:

1378-image017.png

In the query above I’m using the clause OVER() with an ORDER BY. Unfortunately this will only possible with next version of SQL Server, SQL Server 2012.

Conclusion

In the next article I’ll show in more details the limitations of the window functions on SQL Server 2008 R2, and compare the performance of a running aggregation on SQL Server 2012 (Denali).

In the final and third article I’ll show in details how the window frame works. Also I’ll show how the windowing functions LEAD, LAG, FIRST_VALUE, LAST_VALUE, PERCENT_RANK and CUME_DIST works in SQL Server.

That’s all folks, see you soon with the second part of this article.

Tags: , , , , ,

  • 99319 views

  • Rate
    [Total: 10    Average: 4.6/5]
  • Dante Rosati

    excellent explanation
    Great Fabiano!!!!

  • Jes Samsøe Hansen

    Great post
    I’m really looking forward to parts 2 and 3. Let it be sooner rather than later.

  • Anonymous

    N/A
    Oracle have this kind of thing 10 years ago.
    SQL Server just copy it.

  • Mitchell Land

    Excellent post
    Great information and well presented.

  • mcflyamorim

    .
    Thanks for the comments guys, I expect to publish the next 2 articles on next two weeks.

    N/A, It’s not coping anything, it’s supporting the Standard (ISO), it’s supposed to do it anyway. Also, I’m glad we are coping this from Oracle , let’s copy the best thinks for the competitor and make it even better on SQL Server 😉

    Cheers

  • Anonymous

    English please
    Don’t use Portuguese. It makes examples a lot harder to use. You thought that will be cool or what? It is not!

  • Jes Samsøe Hansen

    SEQUEL is a great language.
    SEQUEL is written and spoken with a simple and declarative tongue with only a few main verbs, prepositions and nouns.
    The meaning – in between the sometimes many lines – SHALL be derived from Boolean and algebraic logic and WILL be appreciated by those who like that sort of challenge.
    Now that’s really cool!
    Post scriptum: N/A and Anonymous were newer keywords in this universal language.

  • Pushkar

    Excellent
    Simple and straight explanation.

  • Simon Jones

    Fantastic Article
    Thanks very much Fabiano,

    Great article well illustrated an clearly explained.

    You have made a fairly complex subject (for me anyway) straightforward to understand.

    Probably the best SQL tutorial I have ever seen.

    Thanks again.

    Simon

  • Red

    simple and clear
    I’m really looking forward to other parts, thank you.