Window Functions

Зміст курсу

Advanced Techniques in SQL

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:

sales _id	product_id	sales_date	amount
1	1	2023-01-01	100.00
2	3	2023-01-02	150.00
3	2	2023-01-03	200.00
4	2	2023-01-04	120.00
5	1	2023-01-05	180.00
6	3	2023-01-06	220.00
7	3	2023-01-07	250.00
8	2	2023-01-08	190.00
9	1	2023-01-09	300.00
10	2	2023-01-10	280.00

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:

sales_id	product_id	sales_date	amount	total_revenue_per_product
11	1	2023-01-01	100.00	580.00
12	3	2023-01-02	150.00	620.00
13	2	2023-01-03	200.00	790.00
14	2	2023-01-04	120.00	790.00
15	1	2023-01-05	180.00	580.00
16	3	2023-01-06	220.00	620.00
17	3	2023-01-07	250.00	620.00
18	2	2023-01-08	190.00	790.00
19	1	2023-01-09	300.00	580.00
20	2	2023-01-10	280.00	790.00

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.

product_id	total_revenue
1	580.00
2	790.00
3	620.00

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

Implementation

We can get the required result using the following query:

Query Description

Function Name: SUM
Description: Calculates the sum of a specified column within a defined window.
Arguments:
- Amount: The column to be summed.
Window Function:
- OVER: Specifies the window over which the aggregation is performed.
- PARTITION BY Product_ID: Divides the result set into partitions based on the values of the Product_ID column. The sum is calculated separately for each partition.
Alias: Total_Revenue_Per_Product: The name assigned to the calculated sum for each partition.
Usage: This function is used to calculate the total revenue per product, summing up the amounts for each product ID.
Result: The result set includes Sales_ID, Product_ID, Sales_Date, Amount, and Total_Revenue_Per_Product columns from the sales table, where Total_Revenue_Per_Product represents the total revenue for each product ID.
Ordering: The result set is ordered by Sales_Date.

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.

Все було зрозуміло?

Секція 3. Розділ 3

Зміст курсу

Advanced Techniques in SQL