SQL window functions —also known as windowing functions, OVER functions, or analytic functions — are tremendously useful. These functions make building complex aggregations much simpler. In this step-by-step tutorial, we will lead you through SQL window functions. At its end, you'll embrace this topic with ease and feel comfortable using window functions in SQL databases.
Window functions are so powerful that they serve as a dividing point in time: people talk about SQL before window functions and SQL after window functions. Because they were introduced into the standard relatively recently, these functions aren't covered in many SQL courses.
Note: Most popular database engines support window functions; MySQL is the only one that does not.
About the Window Functions in SQL Course
This course covers the syntax and semantics of SQL window functions. It shows how powerful they are, what the typical use cases are, and how to use ORDER BY and PARTITION BY to set up a frame for window functions. You'll also learn the difference between ROWS and RANGE clauses.
This course is intended for intermediate users. We assume the user knows the basics of SQL, including:
- How to select from a single table, including writing complex WHERE conditions
- How to JOIN tables
- How GROUP BY and HAVING work
What Am I Going To Get from This SQL Course?
You'll discover how window functions can be used to:
- Compute running totals and running averages
- Build rankings
- Find best and worst performers
- Investigate trends across time
- Calculate contributions to the whole, such as commission percentages
You'll also get a deeper understanding of SQL aggregate functions.