WINDOW/ANALYTIC FUNCTIONS

Window functions were first made available in structured query language (SQL) in the early 2000s and have since garnered a lot of attention and hype. I’d argue the hype behind window functions is well-deserved given the power they afford the user and the ease in which they can be implemented.

With minimal code, sometimes even a one-liner, aggregates can be computed over groups of rows (e.g., 3-day moving average, average salary for each department), ranking systems can be implemented based on the user’s specification (e.g., rank each student’s performance based on GPA), and calculations referencing those values in rows before or after the current row can be made. Let’s begin looking at the syntax allowing window functions to be implemented so easily.

To use a window function, simply provide the window function name, the OVER clause, and the clauses applicable to your scenario within the OVER clause. The OVER clause and the clauses embedded within it are used to specify how the function will operate on the result set. The clauses commonly found within the OVER clause are the PARTITION BY, ORDER BY, and WINDOW clauses. The PARTITION BY, ORDER BY, and WINDOW clauses are optional; however, depending on the analytic function being used, a default WINDOW clause of RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is implied and used by default. Regarding defaults, consult your specific RDBMS documentation to be certain. The number of clauses requiring understanding may seem overwhelming at first, but I promise each of them are quite intuitive. Let’s walk through each of them in turn.

PARTITION BY Clause
The PARTITION BY clause specifies how the individual rows within a result set should be grouped, if applicable. The window function specified operates on each of these groups, separately. To illustrate the idea of partitioning, consider the result set below based on the employees table within the human resource (HR) schema at livesql.oracle.com. The data has been partitioned by the job title attribute with the individual rows containing the same job title value belonging to the same partition.

As previously mentioned, the specified window function will operate on each of these groups. Using the same data above, suppose there is a need to compute the average salary across those employees’ salaries sharing the same job and compare that value to the employee’s salary. The AVG function can be used as a window function and the result set partitioned by the job title attribute to achieve this. See the code and corresponding result set below.

SELECT
    hr.employees.employee_id,
    hr.jobs.job_title,
    hr.employees.salary,
    AVG(hr.employees.salary) OVER(
        PARTITION BY hr.jobs.job_title
    ) AS average_job_salary,
    hr.employees.salary
    - AVG(hr.employees.salary) OVER(
    	PARTITION BY hr.jobs.job_title
    ) AS difference
FROM
	hr.employees
	INNER JOIN
	hr.jobs
		ON hr.employees.job_id = hr.jobs.job_id
WHERE
	hr.employees.job_id IN (
    	'ST_MAN', 'SA_MAN',
    	'AD_VP', 'AD_PRES'
    );

Notice in the result set above, each row contains an average_job_salary value. This value is the average of those employees’ salaries sharing the same job with respect to the current row’s job title value. For example, the average salary for the five sales managers is $12200. This is the result of incorporating a PARTITION BY clause using the job title attribute. Employees are separated into partitions and the average salary is computed within each partition. This value is then reflected within each row belonging to the corresponding partition. If it helps, when reading PARTITION BY clauses, read them as “for each…do this.” In this case, “for each group of employees sharing the same job title value, compute the average salary of those employees.” The derived attribute, difference, goes the extra mile to make it easier for the consumers of the data by computing the difference between each employee’s salary and the average salary value for those employees with the same job.

To reiterate why this functionality is useful, the window function allowed a row-level difference to be computed using values originating from two different levels of detail, the pre-existing employee salary within each row and the average job salary for those employees with the same job title value as the current row’s job title value. It is also worth mentioning, prior to the existence of window functions, a JOIN to a derived table would likely be the go-to strategy for achieving the same thing.

In the previous example, the PARTITION BY clause contains a single expression (i.e., job_title); however, based on the specific task, the partitions may need to be based on multiple expressions or partitioning may not be required at all.

PARTITION BY Clause Not Specified
In the absence of a PARTITION BY clause, it is implied the window function will be computed using all rows (i.e., a single group). To further reinforce the idea of a window encompassing all rows, consider the task of comparing each employee’s salary to the average salary of all employees. This can be achieved with the single line of code shown in the query below.

SELECT
	hr.employees.employee_id,
	hr.employees.salary,
    AVG(hr.employees.salary) OVER() AS average_salary,
    hr.employees.salary - AVG(hr.employees.salary) OVER() AS difference
FROM
	hr.employees;

Line 3 computes the average salary across all employees (i.e., rows) and is represented within each row. Line 4 computes the difference between each employee’s salary and the average employee salary returned by the window function. For clarity, see the result set below. Note the average salary and the difference is rounded to two decimal places.

Like the previous example, there is another way to achieve the same result. A CROSS JOIN could be used to cascade the average employee salary down the entire column, allowing the row-level comparison to be made and difference computed. That said, I find the use of the window function more intuitive when reading code I’ve written in the past and when reviewing the code of others.

PARTITION BY Clause With Multiple Expressions
In many instances, the instructions for partitioning or grouping individual rows will require more than a single expression. Building onto the example above, suppose there is a desire to compute the average salary for each job ID and manager ID combination and have those values represented in the corresponding rows. Achieving this is as easy as specifying so in the PARTITION BY clause by separating the two expressions with a comma. See the code and corresponding result set below.

SELECT
    hr.employees.employee_id,
    hr.employees.manager_id,
    hr.employees.job_id,
    hr.employees.salary,
    AVG(hr.employees.salary) OVER(
        PARTITION BY
    		hr.employees.manager_id,
    		hr.employees.job_id
    ) AS average_job_salary,
    hr.employees.salary
    - AVG(hr.employees.salary) OVER(
    	PARTITION BY
    		hr.employees.manager_id,
    		hr.employees.job_id
    ) AS difference
FROM
    hr.employees
ORDER BY
    hr.employees.manager_id,
    hr.employees.job_id,
    difference DESC;

Why might it be useful to perform such a computation? To answer this question, recall the first two examples above. In the second example, the average salary was computed across all employees and returned within each row to permit a row-level difference and a comparison to be made between the employee’s salary and the average salary of all employees. Such a comparison is not likely to be very meaningful given the large variances in salary values across jobs. In the first example, the average salary was computed using those employee salary values aligned to each job title (i.e., partition specified). This is likely to yield more meaningful information given a comparison that is closer to an apples-to-apples comparison. It allows consumers of the data to understand how the average salaries look across job titles and where each employee’s salary is relative to the average salary of employees having the same job. The latest query above returns a result set enabling data consumers to easily understand how salary values differ among employees holding the same job and reporting to the same manager. With little effort, data consumers can also explore how salaries for a particular job vary depending on the manager the employee reports to.

Keep in mind, while an expression was added to the PARTITION BY clause in this example, the complexity or number of expressions specified in the clause should be driven by the task at hand. Additional or fewer expressions will in most cases change the output of the window function.

ORDER BY Clause
When used within the OVER clause, the ORDER BY clause is used to specify the sorting of the rows within each partition. In the previous examples, the sort order of the individual rows within each partition was not specified and is unnecessary for those specific tasks; however, sorting is particularly useful for implementing the concept of RANK and computing moving/rolling aggregates. Before exploring a couple of examples, I feel it is important to point out the nuance of the ORDER BY clause when used within the OVER clause. Using ORDER BY within the OVER clause doesn’t guarantee the result set output by the query will be in the order specified. It is simply the explicit instruction on how to sort the rows within each partition. If it is desirable to order the result set, use the ORDER BY clause outside of the window function as seen in the examples above.

With that nuance mentioned, let’s now explore the example tasks.

Ranking
For the first example, consider the employee data below. There is one row per employee and captured within each row is the corresponding employee’s name and salary.

Additionally, imagine there is a need to rank the employees above by their individual salary values in descending order (i.e., greatest to least). A subset of window functions often referred to as ranking functions can be used to fulfill this task. See the code and corresponding result set below.

SELECT
	hr.employees.employee_id,
    (
    	hr.employees.first_name 
    	|| ' '
        || hr.employees.last_name
    ) AS employee_name,
    hr.employees.salary,
    DENSE_RANK() OVER (
        ORDER BY hr.employees.salary DESC
    ) AS salary_rank
FROM
	hr.employees
ORDER BY
	hr.employees.salary DESC;

When using ranking functions, the ORDER BY clause within the OVER clause is required. As previously mentioned, this clause is used to specify how to order the rows within each partition. In this example, the PARTITION BY clause is absent, indicating the ranking function will be applied across all employees. The highest paid employee will be assigned a salary rank value of 1, the second highest paid employee a salary rank value of 2, and so on. Because the DENSE_RANK function is being used, ties in salary values are resolved by assigning the same integer value to those rows involved in each specific tie and the row with the next highest salary value being assigned the next consecutive integer value. This example can be taken a step further by slightly changing the ranking requirement. Instead of ranking employees by their individual salary values when compared to all other employees, each employee’s rank can be computed by comparing the employee’s salary against the salaries of those employees holding the same job. This can be achieved by simply including the job title attribute within a PARTITION BY clause. As a result of doing so, each partition consists of employees with the same job and the ranking function is applied to each partition, individually. For clarity, see the code and corresponding result set below.

SELECT
	hr.employees.employee_id,
    (
    	hr.employees.first_name 
    	|| ' '
        || hr.employees.last_name
    ) AS employee_name,
    hr.jobs.job_title,
    hr.employees.salary,
    DENSE_RANK() OVER (
    	PARTITION BY hr.jobs.job_title
        ORDER BY hr.employees.salary DESC
    ) AS salary_rank
FROM
	hr.employees
	INNER JOIN
	hr.jobs
		ON hr.employees.job_id = hr.jobs.job_id
ORDER BY
	hr.jobs.job_title,
	hr.employees.salary DESC;

For an in-depth discussion on ranking functions and more involved, real-world examples, see my page dedicated to the topic here.

Aggregates with Window Clauses
The next example explores the concepts of computing moving aggregates and user-defined window clauses. Imagine you’ve been asked to compute a 3-day moving average for sales using the data in the table below. The data contains one row per day and the corresponding daily sales value.

The code used to generate the fictional data set above, compute and the 3-day moving average values, and the corresponding result set can be found below. Following the result set is a detailed explanation of the solution.

EXEC DBMS_RANDOM.SEED(42);

WITH daily_report (report_date, daily_order_total) AS (
    SELECT
		TRUNC(SYSDATE) AS report_date,
		FLOOR(DBMS_RANDOM.VALUE(1000, 5001)) AS daily_order_total
    FROM
		dual
	UNION ALL
    SELECT
		daily_report.report_date - 1,
		FLOOR(DBMS_RANDOM.VALUE(1000, 5001))
    FROM
		daily_report
    WHERE
		daily_report.report_date > TO_DATE('2023-10-01', 'YYYY-MM-DD')
)
SELECT
	daily_report.report_date,
	daily_report.daily_order_total,
	ROUND(
    	AVG(daily_report.daily_order_total) OVER (
			ORDER BY daily_report.report_date
			ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
		),
    	2
    ) AS three_day_moving_average
FROM
	daily_report
ORDER BY
	daily_report.report_date;

In the code above, a new clause is introduced within the OVER clause often referred to as the window or window clause. This clause allows a user to specify the row(s) or value(s) to be used when calculating the current row’s value. Using the code and corresponding result set above, the 3-day moving average for the first row is the row’s sales value (1467), the value for the second row is the average of the first and second rows’ values ((1467 + 1192)/2), and the value for the third row is the average of the two rows prior and the current row ((1467 + 1192 + 4663)/3). To compute the 3-day moving average value for the fourth row (04-OCT-23), the window must slide down one row. The computation will then consist of computing the average sales value of the second, third, and fourth rows’ sales values. For each subsequent row, the window slides down one row and uses the sales value in the current row and those values within the two rows prior to the current row for the computation. Perhaps not immediately obvious is the importance of the ORDER BY clause within the OVER clause in this example. It plays a crucial role in ensuring the rows are in chronological order and consequently fall into the correct window or 3-day period when performing each row’s computation.

Window Clause Options
Similar to the real-world task incorporated into the example above where each window is comprised of the current row and the two rows preceding the current row, the task of computing a 5-day moving average requires the window to be defined as ROWS BETWEEN 5 PRECEDING AND CURRENT ROW. What is more, the window may even be comprised of rows beyond the current row. In such cases, the FOLLOWING keyword can be used to specify the number of rows following the current row to use in the current row’s computation. The window specification is task dependent! Below is a list of keywords and a brief explanation of how they can be used.

ROWS
Is used to specify a fixed or unbounded number of rows used in each window relative to the current row.

RANGE
Is used to specify the values used in each window relative to the current row’s value. For example, suppose you have data on the price of a stock over time and wish to compute the average stock price for the current row’s stock date value and those stock price values in those rows with stock date values within a range of one day preceding and one day following. In such a case, the window specification could be RANGE BETWEEN INTERVAL ‘1’ DAY PRECEDING AND INTERVAL ‘1’ DAY FOLLOWING.

BETWEEN
Is used to specify the first and last row to be used in each window, if applicable. Some tasks may not require two endpoints, or the endpoint may be implied when not expressed explicitly.

PRECEDING
Is used to specify the number of rows prior to the current row to be used in each row’s computation. In some cases, a task will require each window to be comprised of the current row and all rows prior to the current row. For example, a rolling/cumulative sum will have a window specification of ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

FOLLOWING
Is used to specify the number of rows after the current row to be used in each row’s computation. Like the PRECEDING keyword, some tasks will require each window to be comprised of the current row and all rows after the current row. A task requiring the current row, the row preceding the current row, and the row following the current row will have a window specification of ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING. The PARTITION BY Clause Not Specified section above walked through the task of capturing the average salary of all employees within each row. In that example, the OVER clause is not populated and the window of ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING is implied.

For additional discussion and more real-world examples covering aggregates with window clause specifications, see my page dedicated to the topic here.

Closing Thoughts
Evidenced by the handful of real-world problems covered, it’s hard to understate the power of window functions. Minimal effort in terms of lines of code and overall SQL knowledge is required to solve each problem. Keep in mind, these are real, everyday problems and you will likely have to solve some flavor or variation of these problems at some point. When solving problems requiring window functions, remember to let the task drive the choice of window function and its corresponding arguments by first understanding what you are trying to accomplish. Additionally, do not fall into the trap of believing every problem is best solved by window functions.

Additional Information
In the final example, a recursive common table expression (CTE) was used to generate report dates and their corresponding daily order total values. If you’re unfamiliar with the concepts of recursion and/or common table expressions, consider reading my recursion and common table expression pages to learn how to incorporate these concepts into real-world coding solutions.

Knowledge Check
If you’d like to confirm your understanding on the use of window functions and other fundamental concepts, consider solving a handful of my practice problems here.