Windows functions are one of the most important concepts of SQL language. They are also known as Analytical functions.
SQL window functions exist so that we can bypass the flaws of SQL aggregate functions.
The primary flaw of SQL aggregate functions is that they collapse the rows to a single row whenever we use them.
Below is the demo table(emp) which we will use to understand the use of window function.
Now if we will use some aggregate functions to calculate the avaerage salary you will get this result.
SELECT AVG(SALARY) AS AVERAGE_SALARY
FROM EMP;
You can see the rows collapse to a single row.
Even if we group by the department_id they still collapse to lesser rows than before.
SELECT DEPARTMENT_ID,AVG(SALARY) AS AVERAGE_SALARY
FROM EMP
GROUP BY DEPARTMENT_ID;
This is why we use the windows function to preserve the structure of our table for easy future use.
Syntax:
WINDOW_FUNC_NAME(COLUMN)
OVER (
PARTITION BY clause
ORDER CLAUJSE
FRAME CLAUSE
)You add the windows function as new column.
Specify the windows function you will be using like ROW_NUMBER(), RANK(), AVG(), etc.
Inside the OVER, PARTITION BY clause is used to specify name of the category(column) by which you want to divide the table.
In ORDER BY clause, you specify the order by which the calculation will take place inside the partition.
NOTE: In some databases you don’t need to add the order by clause
EXAMPLE:
SELECT *,
AVG(SALARY) OVER(PARTITION BY DEPARTMENT_ID) AS AVERAGE_DEPT_SALARY
FROM EMP;
You can see we added a new column named AVERAGE_DEPT_SALARY. In the new column we are displaying the average of salaries per department.
There are several types of Windows Functions:
- ROW_NUMBER(), RANK() and DENSE_RANK() functions
- LEAD() and LAG() functions
- etc.
Thanks for reading!
SOURCE: MICROSOFT DOCUMENTATION T-SQL
