Window Functions

Window functions are a category of SQL functions that perform calculations across a set of rows related to the current row within a defined window or partition.
They are used to perform calculations and analysis over a subset of rows without reducing the result set, unlike aggregate functions that typically reduce the number of rows returned by a query.

Explanation

Assume we have the following Sales table:

If our goal is to compute the total revenue for each specific product and display it in an additional column within the main table rather than generating a new grouped table, the outcome might appear as follows:

But how can we do it?
Using GROUP BY is not suitable for this task because this clause reduces the number of rows by grouping them according to specified criteria, resulting in only the IDs and their corresponding sum values being returned.

That's why window functions are essential for addressing this issue.

Implementation

We can get the required result using the following query:


              1234567
            
SELECT sales_id, 
       product_id, 
       sales_date, 
       amount,
       SUM(amount) OVER (PARTITION BY product_id) AS Total_Revenue_Per_Product
FROM Sales;

A general syntax for creating a window function can be described as follows:

SELECT: Indicates that a query is about to begin;
aggregation_func(): The aggregate function (e.g., SUM, AVG, COUNT) that performs a calculation over a set of rows defined by the window;
OVER: Keyword that introduces the window function;
PARTITION BY: Divides the result set into partitions based on the values of the specified column(s). The window function operates separately on each partition;
- partition_column: The column used to partition the result set.
ORDER BY: Specifies the order of the rows within each partition;
- order_column: The column used to order the rows within each partition.
FROM: Indicates the source table from which the data is retrieved;
- table_name: The name of the table from which data is being selected.

Tudo estava claro?

Obrigado pelo seu feedback!

Seção 3. Capítulo 3