Chaining Multiple Analytical Functions

Compose complex calculations by layering multiple window functions in a single query.

In this lesson, you will learn how to layer multiple window functions within a single query to perform complex multi-step calculations in one pass.

The Power of Layering

In previous lessons, we learned how to use individual window functions to calculate things like running totals or lead/lag offsets. However, real-world data analysis often requires multiple logical steps. For example, you might need to calculate a moving average, and then calculate the difference between the current row's value and that moving average.

Instead of writing complex subqueries or temporary relations, you can "chain" these functions by simply listing them inside your SELECT statement. Because window functions are calculated after all joins and filtering but before the final SELECT list is projected, the database engine treats each function as an independent operation on the underlying partition.

A Concrete Illustration

Imagine you have a table of daily stock prices. You want to calculate the 7-day moving average, and then immediately find the deviation of the current price from that average:

SELECT 
    price_date,
    price,
    AVG(price) OVER(ORDER BY price_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg,
    price - AVG(price) OVER(ORDER BY price_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS deviation
FROM stock_data;

In this example, the database processes both AVG() window functions. Even though they look similar, the engine evaluates them as separate analytical operations.

Performance Considerations

Since you are now using multiple functions, keep in mind that every window function adds a layer of complexity to the Analytical Frame. If you use different PARTITION BY or ORDER BY clauses for each function, the database must perform separate sorts or partition passes for each one. To optimize performance, try to keep the window definitions (the OVER clause) identical whenever possible; many query optimizers can detect identical partitioning and ordering, allowing them to calculate multiple results in a single sort operation rather than re-sorting the data for every single function.

Key Takeaway: You can execute multiple window functions in one query to layer complex metrics, but keep your framing consistent to help the optimizer process them efficiently.