Aprenda Ordering and Framing in Window Functions | Introduction to Window Functions

Understanding how to order and frame your data is essential when working with window functions in SQL. The ORDER BY clause within the OVER() statement determines the sequence in which rows are processed for each calculation, directly affecting the results of functions like running totals, moving averages, and rankings. Without specifying ORDER BY inside OVER(), window functions may process rows in an unpredictable order, leading to inconsistent or meaningless results.


              123456789
            
SELECT
    sale_date,
    amount,
    AVG(amount) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM sales
ORDER BY sale_date;

The example above uses AVG(amount) with OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) to calculate a moving average over the current and two previous sales, ordered by sale_date. This type of calculation is common for analyzing trends over time, such as smoothing out daily sales fluctuations.

Window frames define which subset of rows is included in each calculation. The two most common frame types are ROWS BETWEEN and RANGE BETWEEN.

ROWS BETWEEN selects a fixed number of rows relative to the current row, regardless of the values in the ordering column;
RANGE BETWEEN includes all rows with values within a certain range relative to the current row's ordering value.

Use ROWS when you want to include a set number of rows before or after the current row, such as the previous two rows. Use RANGE when you want to include all rows with the same value in the ordering column or within a value range, which is useful for handling ties or grouping by value rather than row position.


              123456789101112131415161718192021
            
-- Using ROWS: includes exactly 2 previous rows plus the current row
SELECT
    sale_date,
    amount,
    SUM(amount) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS sum_rows
FROM sales
ORDER BY sale_date;

-- Using RANGE: includes all rows with sale_date values within 2 days before the current row's date
SELECT
    sale_date,
    amount,
    SUM(amount) OVER (
        ORDER BY sale_date
        RANGE BETWEEN INTERVAL '2 day' PRECEDING AND CURRENT ROW
    ) AS sum_range
FROM sales
ORDER BY sale_date;

1. What is the effect of adding ORDER BY to a window function?

2. Which frame clause would you use to include only the current and previous row?

3. Fill in the blank: ... OVER (ORDER BY sale_date ROWS BETWEEN ____ PRECEDING AND CURRENT ROW)

Tudo estava claro?

Obrigado pelo seu feedback!

Seção 1. Capítulo 5

Pergunte à IA

Pergunte o que quiser ou experimente uma das perguntas sugeridas para iniciar nosso bate-papo

Suggested prompts:

Can you explain the difference between ROWS and RANGE with more examples?

What happens if I omit the ORDER BY clause in the window function?

How does the moving average calculation change if I use RANGE instead of ROWS?

Deslize para mostrar o menu


              123456789
            
SELECT
    sale_date,
    amount,
    AVG(amount) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM sales
ORDER BY sale_date;