Learn What Are Window Functions? | Introduction to Window Functions

Window functions are a powerful feature in SQL that allow you to perform calculations across sets of rows that are related to the current row, without grouping or collapsing the result set. This makes them especially useful for analytics and reporting tasks, such as calculating running totals, moving averages, or ranking rows within partitions of your data. Unlike aggregate functions, which summarize data into a single row per group, window functions let you retain the detail of each row while still performing calculations that consider multiple rows at once.


              1234567891011
            
-- Aggregate SUM: total sales amount (one row)
SELECT SUM(amount) AS total_sales
FROM sales;

-- Window SUM: running total per row
SELECT
  sale_id,
  sale_date,
  amount,
  SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales;

The key to window functions is the OVER() clause. This clause defines the "window" of rows that the function should operate over, relative to the current row. The basic syntax for a window function is:

function_name([arguments]) OVER ([partition_by_clause] [order_by_clause] [frame_clause])

By using the OVER() clause, you can apply aggregate-like calculations—such as SUM(), AVG(), or COUNT()—across a sliding window of rows, without collapsing them into a single output row. This means you can see both the original data and the results of your calculations side by side, making it easier to analyze trends and patterns in your data.


              123456
            
SELECT
  sale_id,
  sale_date,
  amount,
  SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales;

Everything was clear?

Thanks for your feedback!

Section 1. Chapter 1

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Swipe to show menu


              1234567891011
            
-- Aggregate SUM: total sales amount (one row)
SELECT SUM(amount) AS total_sales
FROM sales;

-- Window SUM: running total per row
SELECT
  sale_id,
  sale_date,
  amount,
  SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales;

function_name([arguments]) OVER ([partition_by_clause] [order_by_clause] [frame_clause])


              123456
            
SELECT
  sale_id,
  sale_date,
  amount,
  SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales;

Everything was clear?

Thanks for your feedback!

Section 1. Chapter 1

What Are Window Functions?

1. What is the main difference between an aggregate function and a window function in SQL?

2. Which SQL clause is essential to use window functions?

3. Which of the following operations can window functions perform that aggregate functions cannot?

What Are Window Functions?

1. What is the main difference between an aggregate function and a window function in SQL?

2. Which SQL clause is essential to use window functions?

3. Which of the following operations can window functions perform that aggregate functions cannot?