The first time in a SQL class, aggregate functions and window functions can seem confusing because both can perform calculations such as COUNT(), SUM(), and AVG().
The key difference is simple:
Aggregate functions combine rows and return a summary, while window functions perform calculations across related rows without removing the individual rows.
What Are Aggregate Functions?
Aggregate functions perform calculations on multiple rows and return a single result.
Common aggregate functions include:
COUNT()
SUM()
AVG()
MIN()
MAX()
For example, suppose we want to calculate the average salary in each industry.
SELECT
industry,
AVG(salary) AS average_salary
FROM job_salary_prediction_dataset
GROUP BY industry;
The GROUP BY divides the data into groups based on industry.
The AVG() function then calculates the average salary for each group.
The result might look like this:
| industry | average_salary |
|---|---|
| Healthcare | 145760 |
| Finance | 145802 |
| Technology | 145864 |
| Retail | 145400 |
The important thing to notice is that the original employee-level rows are no longer displayed.
Instead, we get one row for each industry.
What Is a Window Function?
A window function performs a calculation across a set of related rows while keeping the individual rows in the result.
Window functions use the OVER() clause.
The basic syntax is:
function_name() OVER (
PARTITION BY column
ORDER BY column
)
For example, we can calculate the average salary for each industry while still displaying each individual record.
SELECT
job_title,
industry,
salary,
AVG(salary) OVER (
PARTITION BY industry
) AS industry_average
FROM job_salary_prediction_dataset;
The result could look like:
| job_title | industry | salary | industry_average |
|---|---|---|---|
| AI Engineer | Healthcare | 109413 | 145760 |
| Data Analyst | Healthcare | 150000 | 145760 |
| Software Engineer | Technology | 180000 | 145864 |
| Data Scientist | Technology | 160000 | 145864 |
Notice something important.
The individual salary is still available, but we have also added the average salary for that employee's industry.
This is the major advantage of a window function.
Understanding the OVER() Clause
The OVER() clause tells SQL that we want to perform a calculation across a group of rows without collapsing them.
For example:
AVG(salary) OVER (
PARTITION BY industry
)
The PARTITION BY industry means:
Calculate the average separately for each industry.
It is similar to GROUP BY, but it behaves differently.
With:
GROUP BY industry
SQL produces one result for each industry.
With:
PARTITION BY industry
inside a window function, SQL calculates the value for each industry but keeps the individual rows.
GROUP BY vs PARTITION BY
Let's compare them.
Using GROUP BY
SELECT
industry,
AVG(salary) AS average_salary
FROM job_salary_prediction_dataset
GROUP BY industry;
This produces:
Healthcare 145760
Technology 145864
Finance 145802
The employee-level records are grouped together.
Using a Window Function
SELECT
job_title,
industry,
salary,
AVG(salary) OVER (
PARTITION BY industry
) AS average_salary
FROM job_salary_prediction_dataset;
This produces something like:
AI Engineer Healthcare 109413 145760
Data Analyst Healthcare 150000 145760
Software Engineer Technology 180000 145864
Data Scientist Technology 160000 145864
The original rows remain.
Why Are Window Functions Useful?
Window functions are particularly useful when we want to compare an individual row with a group or calculate values across rows.
For example, we can compare an employee's salary with the average salary of their industry.
SELECT
job_title,
industry,
salary,
AVG(salary) OVER (
PARTITION BY industry
) AS industry_average,
salary - AVG(salary) OVER (
PARTITION BY industry
) AS difference_from_average
FROM job_salary_prediction_dataset;
Now we can see:
| Job Title | Industry | Salary | Industry Average | Difference |
|---|---|---|---|---|
| AI Engineer | Healthcare | 109413 | 145760 | -36347 |
| Data Analyst | Healthcare | 150000 | 145760 | 4240 |
The window function allows us to perform the calculation while keeping the employee's original salary.
Ranking with Window Functions
Window functions are not limited to AVG().
One of their most common uses is ranking.
For example, we can rank salaries from highest to lowest within each industry.
SELECT
job_title,
industry,
salary,
RANK() OVER (
PARTITION BY industry
ORDER BY salary DESC
) AS salary_rank
FROM job_salary_prediction_dataset;
Here:
PARTITION BY industry
creates a separate ranking for each industry.
And:
ORDER BY salary DESC
puts the highest salary first.
The result could look like:
| Job Title | Industry | Salary | Salary Rank |
|---|---|---|---|
| Data Scientist | Technology | 200000 | 1 |
| Software Engineer | Technology | 180000 | 2 |
| Data Analyst | Technology | 160000 | 3 |
| AI Engineer | Healthcare | 190000 | 1 |
| Data Scientist | Healthcare | 170000 | 2 |
Notice that the ranking starts again when we move to another industry.
RANK(), DENSE_RANK() and ROW_NUMBER()
SQL provides several window functions for ranking.
RANK()
RANK() OVER (ORDER BY salary DESC)
If two employees have the same salary, they receive the same rank.
For example:
Salary Rank
200000 1
190000 2
190000 2
180000 4
Notice that rank 3 is skipped.
DENSE_RANK()
DENSE_RANK() OVER (ORDER BY salary DESC)
DENSE_RANK() also gives employees with the same salary the same rank, but it does not skip the next rank.
Salary Rank
200000 1
190000 2
190000 2
180000 3
ROW_NUMBER()
ROW_NUMBER() OVER (ORDER BY salary DESC)
ROW_NUMBER()` gives every row a unique number.
text
Salary Row Number
200000 1
190000 2
190000 3
180000 4
Even if two employees have the same salary, they receive different row numbers.
Window Functions for Running Totals
Another common use of window functions is calculating a running total. Suppose we have a table containing monthly salary payments.
We could calculate the cumulative salary using:
sql
SELECT
month,
salary,
SUM(salary) OVER (
ORDER BY month
) AS running_total
FROM salary_payments;
The result could look like:
| Month | Salary | Running Total |
|---|---|---|
| January | 10,000 | 10,000 |
| February | 12,000 | 22,000 |
| March | 11,000 | 33,000 |
| April | 13,000 | 46,000 |
The SUM() calculates the total, while the OVER() clause turns it into a window calculation.
Aggregate Function vs Window Function
The easiest way to remember the difference is:
Aggregate function
sql
SELECT
industry,
AVG(salary)
FROM job_salary_prediction_dataset
GROUP BY industry;
Many rows → fewer rows
It summarizes the data.
Window function
sql
SELECT
job_title,
industry,
salary,
AVG(salary) OVER (
PARTITION BY industry
) AS industry_average
FROM job_salary_prediction_dataset;
Many rows → same rows + additional calculation
It analyzes the data without removing the individual rows.
When Should You Use Each?
Use aggregate functions when you want a summary.
For example:
- What is the average salary by industry?
- How many employees are in each industry?
- What is the highest salary?
- What is the total salary?
- What is the minimum salary?
Use window functions when you want to analyze individual rows in relation to other rows.
For example:
- What is each employee's salary rank?
- How does an employee's salary compare with the industry average?
- What is the running total?
- What was the previous employee's salary?
- What is the difference between the current and previous value?
- Who is the highest-paid employee in each industry?
Conclusion
The difference between aggregate functions and window functions becomes much easier when you remember what happens to the rows.
Aggregate functions summarize data and usually reduce the number of rows.( Keyword 'Group by')
Window functions perform calculations across related rows while keeping the original rows.( Keyword 'OVER()')
Once you understand this difference, functions such as RANK(), ROW_NUMBER(), LAG(), LEAD(), SUM() OVER() and AVG() OVER() become much easier to understand and apply to real-world data.
Top comments (0)