We all use aggregate functions, such as sum, min, avg, etc., usually in conjunction with group by. Like an aggregate function, a window function evaluates a collection of rows (a collection of rows called a window frame). However, unlike regular aggregate functions, window functions do not combine the computed rows into a single output line. Instead, they bring the computed results back to the computed rows.

Let’s say we want to compare an employee’s salary to the average salary in the department. The traditional method is to calculate the average salary of the department through group by, and then connect the above statistical results with the left side of the employee table.



with dep_avg_salary as (
    select dept_name as dept_name, avg(salary) as dep_avg_salary
      from emp_salary
     group by dept_name 
)
select e.dept_name, e.emp_no, e.salary, das.dep_avg_salary
  from emp_salary e
  left join dep_avg_salary das on das.dept_name = e.dept_name       
 order by e.dept_name
Copy the code

Using window functions can simplify things a lot



SELECT dept_name, 
       emp_no, 
       salary, 
       avg(salary) OVER (PARTITION BY dept_name) as dep_avg_salary
FROM emp_salary;
Copy the code

We see that the data listed here is still the employee data, but with the average salary according to the DepName group.

Special attention:

Window functions can only be used in SELECT and ORDER BY clauses, and not anywhere else, such as the GROUP BY, HAVING, and WHERE clauses

The window function must be used in conjunction with the over clause. The over clause contains two parts, PARTITION BY and ORDER BY, which are used to group and determine the ORDER of output within the group respectively. Both PARTITION BY and ORDER BY are optional.

If both are omitted, the entire table will be grouped together and the average salary of all employees will be queried, which makes sense.

If you omit PARTITION BY and include ORDER BY instead?

Isn’t that a weird result?

Select * from develop where avG_salary (5660) is the average salary of the develop department, and sales (5218.18) is the average salary of all personnel. How does that happen?

Ah, the average salary for develop and Personnel, of course. It makes sense then that the third group is the average salary for all employees, which is the average salary for the develop, Personnel and sales departments. Little friends should pay special attention when using it.

If a query contains multiple window functions, we can write multiple OVER clauses. But if these window functions do the same thing, it can be annoying and error-prone to write more than one. In this case, we can write the contents of the WINDOW as a WINDOW clause and refer to them in multiple OVER clauses. For example, let’s list the average salary and the highest salary in the department:

Using a Windows sentence, it could look like this:

One last word:

All aggregate functions can be used as window functions