How to use window functions in SQL? Row_number Example Tutorial

Hi folks, In Today's article we shall be talking about the windows function in SQL, How to use a Windows function, and when to use Windows functions. And lastly, the Difference between windows and aggregate Functions.In SQL world, the ability to transform data with precision and finesse is a hallmark of skilled database professionals. One tool that often stands out in this endeavor is the window function, a potent feature that enables complex data analysis and manipulation within SQL queries. Among the arsenal of window functions, ROW_NUMBER() emerges as a versatile choice, allowing you to assign unique row numbers to result sets based on defined criteria.

In this tutorial, you will learn about SQL window functions, with a particular focus on the ROW_NUMBER() function. Whether you're seeking to paginate query results, identify duplicates, or rank data based on specific attributes, understanding and harnessing the power of ROW_NUMBER() will significantly enhance your SQL capabilities.

In this tutorial, you will learn about the syntax, applications, and real-world examples of ROW_NUMBER(), providing you with the knowledge and confidence to wield this indispensable tool in your SQL arsenal. Prepare to elevate your data manipulation skills and unlock new possibilities in SQL query design.

What is Window Function in SQL?

A window function is a function that performs calculations on a set of table rows that are related to the current row. The current row is the row in which the windows function occurs. A windows function can also be called an analytic function.

How to use Window Function in SQL?

Let us say we have an employee table for instance.

EMPLOYEE TABLE

| name        | department   | age   | salary |

|-------------|--------------|-------|--------|

| Wilms cole  | maintenance  | 25    | 100000 |

| Alfred Toni | marketing 19 | 97000 |        |

| Grace Rolli | accounting   | 21    | 150000 |

| Cyndi Lo    | operations   | 20    | 200000 |

| John Smith  | sales        | 23    | 50000  |

| Chukwu Dan  | marketing    | 27    | 75000  |

So this is our employee table above, now we want to write a window function for it in SQL
SELECT name, department, age, salary, AVG(salary)
OVER(PARTITION BY department) AS avg_Salary

FROM employee ;

Now, what this query is saying is that. name column, age column, department, and salary column should be selected from the employee table(check line 3).

In the same line 1, an aggregate function "average" was performed on the salary column and after that has been done, what happens under the hood is that it creates another column as avg_salary and puts each average salary of each employee in its appropriate rows.

 
The "Over" keyword inline two is what makes it a window function, now let us deal with that. You would see that inside the parenthesis of the "over" function there are lines of command to be carried out there. It is simply saying that function is partitioned by department and order by age. (There may be different kinds of things to do here, it all depends on what you what to do). 

This partition keyword groups whatever you have specified into parts. In our case, department. All departments that are the same would be seen together. if we have 5 A's departments you would see them together you won't see any B's departments in between. After that now comes another set of similar departments. and so on and so forth. That is for partitioning.

"Order By" sorts it accordingly. and by default, it is in ascending order. so, After departments are partitioned then it begins to sort it according to each partitioned department. Now let us take a look at our new employee table with the added column "average salary" to store their average salaries:

EMPLOYEE TABLE(NEW).

| name        | department  | age | salary | Avg_Salary |

|-------------|-------------|-----|--------|------------|

| Alfred Toni | maintenance | 19  | 90000  | 45000      |

| Wilms cole  | maintenance | 25  | 100000 | 50000      |

| Cyndi Lo    | operations  | 20  | 200000 | 1000000    |

| Grace Rolli | operations  | 21  | 150000 | 75000      |

| John Smith  | sales       | 23  | 50000  | 25000      |

| Chukwu Dan  | sales       | 27  | 70000  | 35000      |

When to use Window Function in SQL?

A windows function is used if there is a need to have another column as a result of an evaluated row. It means performing an aggregate function on a row and having the result or output in a new or separate column for each row. Like the examples, we had above. 

Our Initial columns were name, department, age, and salary. But we needed to get the average salaries of our dear employees. Hence we had a new column that gives the result of average salaries for each and every one of our employees.

Difference between Window Function and Aggregate Function?

Like it's been rightly said, A windows function performs calculations on a set of table rows that are related to the current row. But for an aggregate function, It takes a different number of values in the table rows and performs some evaluations then returns it as a single value.
Examples are: SUM(), MIN(),MAX(),AVG(),COUNT()

• SUM() function adds all the values and returns the result(sum)
• MIN() function determines the smallest value from the values and returns it
• MAX() function determines the largest value from the values and returns it
• AVG() function finds the average value of all the values
• COUNT() function counts how many values are present and returns the result.

In this article, You have learned what a window function is, How to use it when to use it, and the difference between a window function and an aggregate function. Note that aggregate functions are not the same thing as a window function. The kinds of aggregate functions are listed above with what they do - They return a single value after performing the calculation on a set of values.