SQL windows functions, it would seem, get lost in the shuffle most of the time when dealing with SQL. Why do you think that is? I honestly think that it is more or less developers, DBA’s, and data professionals are oblivious to some of the cool features that can be accomplished with its utilization. To put it simply windows functions could be viewed as looking in “windows” of your data. That is about as simple as an explanation as I received when I first started using these types of functions.

Example

Take the below table structure that I created on my local environment in SQL. My table name I created is called CustomerLog within that table I have two columns CustomerFirstName and CustomerLastName (represented below):

For example(s) sake lets say I have a vast amount of data in this customer table and I want to partition it out and view just how many (categories) or windows I am looking at. This is where the windows function comes in nicely. With the below query I can find the results I am looking for pretty quick and easy:

As you can garner with the above SQL windows function code you can derive how many customers share the same first letter in their name (result set below)

Bottom Line

As you go through the SQL journey note that with any aggregate function being used you can turn that into a windows function by utilizing the over clause. You can only use windows functions though within the select or order by clause. This example only showed you how to partition using windows functions and only scratched the surface. It would behoove you to do some more research on what all you can do with SQL and window functions. Also, while you are at it; check out some of my colleagues as well

Mickey Stuewe: A Date At The End of The Month

Jeffrey Verheul: Write readable and high-performance queries with Window Functions

Julie Koesmarno: ABC Classification With SQL Server Window Function

Don’t sell yourself short. I guarantee once you start utilizing windows functions  you will want to use them everywhere. Just make sure you know the positives and negatives and as always just don’t blindly copy code from the net. Explore it, set something up on your Dev or VM sandbox. Failure to learn is not an option!