5. Window Functions for Data Analysis

Learning Objectives

By the end of this chapter, you will be able to:

• Explain what a window function is
• Write basic window functions
• Use common window functions to calculate statistics
• Analyze sales data using window functions and a window frame

In this chapter, we will cover window functions, functions similar to an aggregate function but that allow a new range of capabilities and insights.

Introduction

In the previous chapter, we discussed aggregate functions, functions that can take a large group of rows and output a single value for them. Often, being able to summarize a group of rows to a single value is important and useful. However, there are times when you want to keep the individual rows as well as gaining a summarizing value. To do this, in this chapter, we will introduce a new set of functions named window functions, which can calculate aggregate statistics while keeping individual rows. These functions are very useful for being able to calculate new types of statistics, such as ranks and rolling averages, with relative ease within SQL. In this chapter, we will learn about what window functions are, and how we can use them to calculate statistics.

Window Functions

Aggregate functions allow us to take many rows and convert those rows into one number. For example, the COUNT function takes in the rows of a table and returns the number of rows there are. However, we sometimes want to be able to calculate multiple rows but still keep all the rows following the calculation. For example, let's say you wanted to rank every user in order according to the time they became a customer, with the earliest customer being ranked 1, the second-earliest customer being ranked 2, and so on. You can get all the customers using the following query:

SELECT *
FROM customers
ORDER BY date_added;

You can order customers from the earliest to the most recent, but you can't assign them a number. You can use an aggregate function to get the dates and order them that way:

SELECT date_added, COUNT(*)
FROM customers
GROUP BY date_added
ORDER BY date_added

The following is the output of the preceding code:

Figure 5...

Statistics with Window Functions

Now that we understand how window functions work, we can start using them to calculate useful statistics, such as ranks, percentiles, and rolling statistics.

In the following table, we have summarized a variety of statistical functions that are useful. It is also important to emphasize again that all aggregate functions can also be used as window functions (AVG, SUM, COUNT, and so on):

Figure 5.10: Statistical window functions

Exercise 17: Rank Order of Hiring

ZoomZoom would like to promote salespeople at their regional dealerships to management and would like to consider tenure in their decision. Write a query that will rank the order of users according to their hire date for each dealership:

1. Open your favorite SQL client and connect to the sqlda database.
2. Calculate a rank for every salesperson, with a rank of 1 going to the first hire, 2 to the second hire, and so on, using the RANK() function:

   SELECT *,...

Summary

In this chapter, we learned about the power of window functions. We looked at how to construct a basic window function using OVER, PARTITION BY, and ORDER BY. We then looked at how to calculate statistics using window functions, and how to adjust a window frame to calculate rolling statistics.

In the next chapter, we will look at how to import and export data in order to utilize SQL with other programs. We will use the COPY command to upload data to your database in bulk. We will also use Excel to process data from your database and then simplify your code using SQLAlchemy.