Window Functions Part 4

Hello again! For those of you that have not followed my previous posts, here you can find the part 1, part 2 and part 3.

To continue with the series, today we’re going to talk about Window Aggregates, for those wondering what these are, they are basically your standard Aggregate functions, but with the option to add a “window” or an OVER() clause to them.

WINDOW AGGREGATES

In this post, I’ll only cover the most common for us: AVG, COUNT, MAX, MIN and SUM.

These window aggregate functions were available from SQL Server 2005, but there was a little flaw: the ORDER BY component that we learned with other window functions was not supported. All these functions support PARTITION BY as well, however, if you don’t want to use it, you will end up with an empty OVER() clause and the function will be applied to the entire set of rows.

With SQL Server 2012, ORDER BY clause was available on window aggregates, but we’re going to learn about the 2005 version first. For this example, we’re going to use couple of tables from Northwind database (yes, I’m that old).

If you look at the query, see that I’m not using either a PARTITION BY nor an ORDER BY clauses, this means these calculations are being performed over the entire set of rows.

On rows 1 to 6, we see CustomerID “ALFKI” and even though it only has six orders, you can see the OrderCount of 830 which is the total number of orders, as well as FirstOrderDate and LastOrderDate is showing the first and last orders in the table. Let’s check on another example so you can see the difference.

In the query above, notice that I’m using the PARTITION BY clause. See how it’s now giving us different numbers. For the first CustomerID “ALFKI” again, we see it has 6 orders, and it showing the first and last order dates for that customer only, same happens for all the other customers, all the values are specific for every customer.

In this example I used the same OVER clause, but you can have different ones depending on what you are being requested in the query.

As I previously mentioned, SQL Server 2005 didn’t support ORDER BY clause, now with version 2012, you can add the ORDER BY clause, but the window will change.

In the examples I already showed you, the window was determined by the PARTITION BY expression. In this new version, the PARTITION BY, ORDER BY and a “frame” will define which rows go in the window. This is also known as accumulating aggregates.

Let me show you an example of this so you can better understand it. We’re going to still be using the same database.

In this query, I’m calculating the running total for the sum of the Freight, with the functionality implemented in SQL Serve 2012 the window, by default, consists on the first row in the results and includes the following rows up to the current one.

Another popular “metric” in business are the moving totals and moving averages. The difference with the running totals and running averages is that, in the latter, the window is growing within the partition, but for moving totals and averages, we want the window to stay the same. We can do this by adding a “frame” to our query. Let’s see an example of this:

In here, we’re looking at the three-month average, we added our frame using ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, suppose the current row is row 5, it will take from row 3 up to row 5.

This post is only covering a very basic introduction to window aggregates and framing, there are more functions and you can find out more in the following link:  https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-2017#arguments

Also, this post will be the last on the Window Function series. I hope you guys have a better understanding of window functions and what you can accomplish with them. In the future I might be writing about the performance implications of the window functions but that’ll be on my to-do list.

If you have any question or comment, please let me know and I’ll reply as soon as I can.

Here’s the link for the code in case you want to follow along the examples in the post: https://github.com/learningsql/sql-code

Don’t forget to like and share, see you soon!