What Is a Window Function and Why Should You Care?

Data

What Is a Window Function and Why Should You Care?

by Tim Costello

You might be familiar with aggregate functions in Microsoft SQL Server. These functions give you a way to aggregate your data, for example: a sum of a numeric value grouped by one or more fields in your result set. A common business requirement might be to create a result set that shows for each record in your table, the OrderID, Region and Sales. That query might look like this …

Query example

The results would show every record: [Order ID], Region and Sales amount.

Results 

In my case, there were 8,399 records returned by this query. For each row, I got an [Order ID], Region and Sales amount associated with a sale in my orders table. What if we wanted to get the sum of sales for each state? We might write an aggregate query that looks like the following:

Aggregate query

This query would show one record for each region, with the sum for that region.

Region Results

This is a good way to get to the results we want, but what if we wanted to get the total sales for each region AND state? Working with simple aggregate functions in SQL Server getting the results we want might be difficult.

Region and State total sales

This query gives us the sum of sales at the State level, but not the overall sales at the Region level. For example, we see that in the Central region, South Dakota had $75,172.774 in sales. However, we don’t know what the sum of sales was for the Central region overall.

Sum of Sales at State level

A window function can help us create the query we really want:

Upload function

Now the results show us each region and state with totals at the Region AND State level. Notice that the RegionalSales total is the sum of all the StateSales total. This opens up a lot of options for us. For example, we can now calculate what percent of sales each state contributes to the region overall.

Region and State results

This is just the beginning of the Window Expressions story. In future posts, this series will go into detail about how Windowing Functions work. We will explore how to configure partitioning, ordering and framing in your Window Functions. I’ll show you where you can use Window Functions in your queries and some techniques to bend the rules a bit in order to use Windowing Functions in creative ways to solve real-world business problems.

More About the Author

Tim Costello

Analytics Consultant
3 Reasons Why You Should Want a Data Warehouse I hear a lot of reasons people avoid a data warehouse: It takes too long to build, data warehouse projects are expensive and often end ...
The Tableau Performance Checklist: Filtering – Avoid High-Cardinality Quick Filters The next item in our Filtering checklist is: “Avoid high-cardinality quick filters (multi-select or drop-down lists). ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK