WINDOW FUNCTIONS
SYNTAX:
Window Functions:
• Perform calculations within a window.
Using Window Functions with Aggregate Functions:
• Window functions can be applied to aggregate functions like MAX, MIN, AVG, and SUM.
• When we use OVER () after an aggregate function, it creates a window (a subset of rows to calculate the
result).
• If we use just OVER () without any arguments, the window is the entire table.
• This means the aggregate function will calculate the result based on all rows in the table.
• ORDER BY is optional in aggregate functions.
VISHNU VARDHAN
Using Window Functions with PARTITION BY
• In SQL, we can use PARTITION BY within OVER () to divide the data into groups (partitions).
• The aggregate function will then calculate results within each group rather than the entire table.
Key Points:
• PARTITION BY works like a GROUP BY, but it does not collapse rows. Instead, it retains all rows and calculates
the aggregate for each partition.
• You can use aggregate functions like MAX, MIN, SUM, and AVG with PARTITION BY to get group-specific
values.
Explanation of Columns:
• MAX (salary) OVER (PARTITION BY dept_name) gives the highest salary in each department.
• MIN (salary) OVER (PARTITION BY dept_name) gives the lowest salary in each department.
• SUM (salary) OVER (PARTITION BY dept_name) gives the total salary of all employees in each department.
• AVG (salary) OVER (PARTITION BY dept_name) gives the average salary in each department, rounded to 2
decimal places.
Key Concepts:
1. PARTITION BY dept_name:
o Divides the data into partitions (groups) based on the dept_name column.
o All calculations (MAX, MIN, SUM, AVG) are performed within each department separately.
2. ORDER BY dept_name:
o Orders rows within each partition (department) by dept_name.
o Since dept_name is constant within a department, adding this ordering doesn’t change the results. It
is effectively redundant for this specific query.
3. Window Frame (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING):
o Specifies the range of rows to consider for the calculation.
o In this case, it includes all rows in the partition (the entire department).
o This is the default behavior for aggregate window functions, so explicitly stating it is optional.
4. Aggregate Functions:
o MAX (salary): The maximum salary within each department.
o MIN (salary): The minimum salary within each department.
o SUM (salary): The total salary within each department.
o AVG (salary): The average salary within each department, rounded to 2 decimal places.
Final Notes:
• Why use PARTITION BY? It allows calculations to be grouped logically (in this case, by department).
• Why is ORDER BY optional here? For aggregate window functions like MAX, MIN, SUM, and AVG, ordering
does not affect the result unless you’re using specific window frame clauses or analytical functions like
ROW_NUMBER (), RANK (), etc.