Window Frame

The window frame in SQL window functions is a critical feature that defines a specific subset of rows within a partition for the function to evaluate. This subset is specified using the ROWS or RANGE clauses within the OVER() function. The window frame determines the precise range of rows over which the window function will perform its calculation, allowing for more granular and contextually relevant analysis.

Syntax Overview

window_function() OVER (PARTITION BY column_name ORDER BY column_name 
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

Components of Window Frame

• ROWS: Specifies a physical set of rows.
• RANGE: Specifies a logical range of values.
• BETWEEN: Defines the frame’s start and end points.

Common Frame Specifications

• UNBOUNDED PRECEDING: All rows before the current row.
• CURRENT ROW: The current row.
• UNBOUNDED FOLLOWING: All rows after the current row.

Examples

ROWS Example

SELECT employee_id, 
       salary, 
       SUM(salary) OVER (PARTITION BY department_id 
                         ORDER BY salary 
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM employees;

RANGE Example

SELECT employee_id, 
       salary, 
       SUM(salary) OVER (PARTITION BY department_id 
                         ORDER BY salary 
                         RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS range_total
FROM employees;

Use Cases

1. Running Totals:
   Calculate cumulative sums within a frame.
2. Moving Averages:
   Smooth data points over a specific range.
3. Custom Aggregations:
   Define flexible calculation windows.

Benefits

• Flexible Calculations:
  Tailor the window frame to specific analytical needs.
• Enhanced Insights:
  Provides dynamic and continuous data analysis.
• Improved Data Smoothing:
  Ideal for trend analysis and data smoothing.