Frames in SQL

In SQL, a window frame is a subset of rows within a partition defined by a window function's OVER clause. It specifies the range of rows that the window function should consider for its calculations. Window frames are commonly used with window functions like SUM, AVG, RANK, and more, to control which rows contribute to the function's result.

There are different types of window frames that can be used in the OVER clause:

1. Unbounded Preceding / Following:

   • UNBOUNDED PRECEDING: Includes all rows from the start of the partition to the current row.

   • UNBOUNDED FOLLOWING: Includes all rows from the current row to the end of the partition.

2. Numeric Offset Preceding / Following:

   • n PRECEDING: Includes n rows before the current row.

   • n FOLLOWING: Includes n rows after the current row.

3. Between ... and ...:

   • n PRECEDING AND m FOLLOWING: Includes a range of rows from n rows before the current row to m rows after the current row.

Here are a few examples to illustrate window frames:

1. Calculating a Running Total:

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

1. Calculating a Moving Average:

SELECT
    order_date,
    revenue,
    AVG(revenue) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS moving_avg
FROM
    sales;

1. Ranking within Groups:

SELECT
    category,
    product,
    sales,
    RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS rank_in_category
FROM
    products;

These examples demonstrate how window frames help define the range of rows that window functions operate on, whether it's for cumulative calculations, moving averages, or ranking within partitions.

The specific syntax and options available for window frames may vary depending on the SQL dialect you're using (e.g., MySQL, PostgreSQL, SQL Server, etc.), so be sure to refer to the documentation for your specific database system.

1. By default the frame is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, make sure that using the right frames while writing the query