type
status
date
slug
summary
tags
category
icon
password
Prelude:
Boss: "I need the average sales for 7 days."
Me: "Sure! Which 7 days?"
Boss: "All the 7 days!"
Me: "Su…Sure…?"
🖋️ Window Functions
Window functions are generally applied after aggregate functions like
MAX()
, MIN()
, AVG()
to perform calculations across a set of table rows(days before, days after) that are related to the current row(current day).Example Table 1: employees
employee_id | department_id | salary |
1 | 101 | 5000 |
2 | 101 | 6000 |
3 | 101 | 5500 |
4 | 102 | 7000 |
5 | 102 | 7200 |
6 | 102 | 6800 |
Example 1: OVER()
Without Partitioning
Calculate the average salary for all employees:
Result:
employee_id | department_id | salary | avg_salary_all_departments |
1 | 101 | 5000 | 6250 |
2 | 101 | 6000 | 6250 |
3 | 101 | 5500 | 6250 |
4 | 102 | 7000 | 6250 |
5 | 102 | 7200 | 6250 |
6 | 102 | 6800 | 6250 |
Example 2: PARTITION BY
Calculate the average salary per department:
Result:
employee_id | department_id | salary | avg_salary_per_department |
1 | 101 | 5000 | 5500 |
2 | 101 | 6000 | 5500 |
3 | 101 | 5500 | 5500 |
4 | 102 | 7000 | 7000 |
5 | 102 | 7200 | 7000 |
6 | 102 | 6800 | 7000 |
Example 3: ORDER BY
with Ranking
Rank employees by salary within their departments:
Result:
employee_id | department_id | salary | salary_rank |
2 | 101 | 6000 | 1 |
3 | 101 | 5500 | 2 |
1 | 101 | 5000 | 3 |
5 | 102 | 7200 | 1 |
4 | 102 | 7000 | 2 |
6 | 102 | 6800 | 3 |
Example Table 2: orders
order_id | order_date | order_total |
1 | 2023-01-01 | 100 |
2 | 2023-01-03 | 200 |
3 | 2023-01-05 | 150 |
4 | 2023-01-07 | 250 |
5 | 2023-01-09 | 300 |
Example 4: RANGE
with Date Intervals
Sum the total orders in the last 7 days for each row: (INTERVAL 6 because included CURRENT ROW)
Result:
order_id | order_date | order_total | sum_last_7_days |
1 | 2023-01-01 | 100 | 100 |
2 | 2023-01-03 | 200 | 300 |
3 | 2023-01-05 | 150 | 450 |
4 | 2023-01-07 | 250 | 700 |
5 | 2023-01-09 | 300 | 950 |
Example 5: ROWS
Between Preceding and Following
Calculate the sum of order totals for the current row and the 2 preceding rows:
Result:
order_id | order_date | order_total | sum_last_3_orders |
1 | 2023-01-01 | 100 | 100 |
2 | 2023-01-03 | 200 | 300 |
3 | 2023-01-05 | 150 | 450 |
4 | 2023-01-07 | 250 | 600 |
5 | 2023-01-09 | 300 | 700 |
References sheet
After max()…
- OVER(…): Applied over ...
- PARTITION BY: Used to divide by ...
- ORDER BY [DESC]
- ROWS
[BETWEEN...AND]
n PRECEDING
n FOLLOWING
CURRENT ROW
UNBOUNDED PRECEDING
All beforeUNBOUNDED FOLLOWING
All after
- RANGE
[BETWEEN...AND]
INTERVAL 'n' DAY
- Author:Parker Chen
- URL:www.parkerchenca.com/article/108f0ccf-d7f8-80ed-a0ad-ee0bf34f356f
- Copyright:All articles in this blog, except for special statements, adopt BY-NC-SA agreement. Please indicate the source!
Relate Posts