Window aggregation in databases and data processing systems is a technique where you perform calculations across a set of rows that are related to the current row. This "set of rows" is the window. Unlike traditional aggregate functions (like SUM, AVG, COUNT) that collapse groups of rows into single values, window aggregates preserve the individual rows while still providing insights calculated across those relevant rows.

Key Concepts

• Window Frame: The window frame defines the set of rows that are included with the current row for a calculation. Window frames can be defined in terms of:
  • Number of Rows: A fixed number of rows preceding or following the current row.
  • Range: A range of values relative to the current row's value within a column.
• Window Functions: Special functions designed to operate over window frames. Examples include:
  • SUM(), AVG(), MIN(), MAX(): Standard aggregate functions applied within the window frame.
  • RANK(), DENSE_RANK(), ROW_NUMBER(): Ranking functions within the window.
  • LAG(), LEAD(): Accessing values from previous or subsequent rows within the window frame.

Why Use Window Aggregations?

• Moving Calculations: Compute moving averages, cumulative sums, and other calculations that track trends across a changing set of data points.
• Time Series Analysis: Perform analyses over time-based windows for stock price trends or user behavior patterns.
• Peer Comparison: Rank or compare entities within groups (e.g., ranking sales performance within a region).
• Filling Gaps: Use LAG() and LEAD() to address missing values in a sequence.

Example

Let's say you have a sales dataset with columns date, region, and sales_amount. You could use a window function to calculate the moving average of sales for each region over the last 7 days:

SQL

SELECT date, region, sales_amount, AVG(sales_amount) OVER (PARTITION BY region ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_average FROM sales_data;