Window Functions as Algebraic Shifts

In this lesson, you will learn how to use window functions to perform running calculations without collapsing your data into a single summary row.

The Problem with Grouping

Up until now, whenever you wanted a total—like the total sales per region—you used GROUP BY. This effectively "collapsed" your set of individual sales into one row per category. But what if you want to keep the individual transaction details and see how the running total of sales grows as you move down the list?

Using GROUP BY destroys the individual row identity, making it impossible to see the "algebraic shift" of each new value added to the previous sum.

Window Functions as Algebraic Shifts

Think of a window function as a way to "slide" a frame over your data set. Instead of squashing the rows together, you calculate a value for each row based on its neighbors.

Consider a table of daily_sales with columns sale_date and amount. To calculate a running total, we use the OVER clause:

SELECT 
    sale_date, 
    amount,
    SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM daily_sales;

In this query, the window function acts as an algebraic shift. For every row, it looks at the current row's amount and shifts in the sum of all preceding rows. Because we haven't used GROUP BY, the original schema remains intact: you still have your individual sale_date and amount, now paired with the cumulative state of the set at that point in time.

Why this is different from Joins

In our previous lessons, we used Self-Joins to compare rows to other rows. To get a running total with a Self-Join, you would have to join the table to itself on a condition like t1.sale_date >= t2.sale_date, which is computationally heavy and syntactically messy. Window functions handle this "shift" logic internally, keeping the set membership stable while allowing you to compute values that depend on the relative order of elements within the relation.

Key Takeaway: Window functions allow you to perform calculations on relative subsets of data without collapsing the individual rows of your relation.