Course Content
Advanced Techniques in SQL
Advanced Techniques in SQL
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:
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 |