Window Functions: Running Totals and Moving Averages

From a Window to a Window Frame

So far, you've seen OVER(PARTITION BY ... ORDER BY ...) define a "window" of rows. Today, we introduce the concept of a window frame. A window frame is a more specific subset of rows within that window, defined relative to the current row being processed.

By precisely defining this frame, we can tell aggregate functions like SUM() and AVG() exactly which rows to include in their calculation for each line of our output.

1. Running Totals: The Cumulative View

A running total (or cumulative sum) calculates the sum of a value for all rows from the beginning of a partition up to and including the current row. Think of it as your bank account statement, where each line shows the new balance after a transaction.

The key to creating a running total is defining a window frame that expands with each row.

As the video explained, the syntax for a running total is:

SELECT
  order_date,
  sales,
  SUM(sales) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total_sales
FROM daily_sales;

Let's break down the new part: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

• ROWS BETWEEN ... AND ...: This is the clause that defines the window frame.
• UNBOUNDED PRECEDING: This sets the starting point of the frame to be the very first row in the partition.
• CURRENT ROW: This sets the ending point of the frame to be the row currently being calculated.

For each row the query processes, this frame includes that row and all the ones that came before it (based on the ORDER BY clause).

2. Moving Averages: The Sliding Window View

While running totals look at the entire history, a moving average (or rolling average) is used to smooth out data by averaging values over a specific, fixed-size window of recent entries. For example, a "7-day moving average" of website traffic helps you see the general trend without being distracted by a single slow or busy day.

This is achieved with a "sliding" window frame.

As you just read, the syntax for a 3-day moving average of sales would be:

SELECT
  order_date,
  sales,
  AVG(sales) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS 3_day_moving_avg
FROM daily_sales;

Here, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW tells the AVG() function to only consider a frame consisting of:

1. The current row.
2. The row immediately preceding it.
3. The row two positions before it.

As the query processor moves to the next row, the entire frame "slides" forward by one.

3. Mastering the Window Frame Syntax

The ROWS BETWEEN clause is highly flexible. Understanding its components allows you to define any frame you need. Your experience with low-level programming and system logic should make the precise, physical nature of ROWS quite intuitive.

ROWS defines the frame based on a physical number of rows relative to the CURRENT ROW.

The general syntax is ROWS BETWEEN <start> AND <end>, where <start> and <end> can be:

• UNBOUNDED PRECEDING: The first row of the partition.
• n PRECEDING: n rows before the current row.
• CURRENT ROW: The row being processed.
• n FOLLOWING: n rows after the current row.
• UNBOUNDED FOLLOWING: The last row of the partition.

Common Frames:

• Running Total: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
• 3-Period Moving Average: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
• Centered 3-Period Average: ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING (includes the row before, the current row, and the row after)

4. Combining Frames with Partitions

The true power of these calculations becomes apparent when you combine them with the PARTITION BY clause you already know. This allows you to perform running total or moving average calculations independently for different groups within your data.

For example, you could calculate the running total of sales for each product separately. When the product_id changes, the partition changes, and the running total resets.

The syntax is a straightforward combination of what you've learned:

SELECT
  product_id,
  order_date,
  sales,
  SUM(sales) OVER (PARTITION BY product_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total_per_product
FROM sales_data;

This is a fundamental pattern for complex reports and analysis.

SELECT
  log_time,
  server_id,
  cpu_usage,
  AVG(cpu_usage) OVER (
    PARTITION BY server_id 
    ORDER BY log_time 
    ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
  ) AS moving_avg_cpu
FROM server_logs;

Conclusion

You've now learned how to precisely control aggregations within a window by defining a window frame. This is a significant step up in analytical capability, moving beyond static groups to dynamic, rolling calculations.

Key Takeaways:

• A window frame (ROWS BETWEEN ...) defines a specific subset of rows relative to the current row for an aggregate function to process.
• Running totals use an expanding frame, typically ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, to create cumulative metrics.
• Moving averages use a fixed-size, sliding frame, like ROWS BETWEEN 6 PRECEDING AND CURRENT ROW for a 7-day average, to smooth data and identify trends.
• These frames operate independently within each group defined by PARTITION BY, allowing for powerful, segmented analysis.

Preview of the Next Lesson:
We've now seen how to rank rows (RANK), and how to aggregate over a set of rows in a frame (SUM, AVG). In the next lesson, we will cover LAG() and LEAD(). These functions are different: instead of aggregating a group of rows, they allow you to reach back or forward to access data from a single, specific row at a given offset. This is perfect for calculating period-over-period changes, like comparing this month's sales to last month's.