PostgreSQL window functions

 

Transferred from: http://time-track.cn/postgresql-window-function.html

PostgreSQL provides the properties of the window function. The window function is also the data that computes some rowset (a collection of multiple rows, which we call window frame), somewhat similar to the aggregation function (aggregate functions). Unlike regular aggregation functions, however, window functions do not merge rows that participate in the calculation into one line of output, but rather retain their original appearance . Take a look at the following example:

There is a table representing the salary of the employee (department, employee ID, salary):

postgres=# d empsal           Table "public.empsal" Column  |       Type        | Modifiers---------+-------------------+-----------Depname | Character varying |  Empno   | integer           |  Salary  | integer           |

The following data is now available in the table:

postgres=# select * from Empsal;  Depname  | empno | salary-----------+-------+--------Develop   |    One |   5200 Develop   |     7 |   4200 Develop   |     9 |   4500 Develop   |     8 |   6000 Develop   |    Ten |   5200 Personnel |     5 |   3500 Personnel |     2 |   3900 Sales     |     3 |   4800 Sales     |     1 |   Sales     |     4 |   4800 (rows)

We now want to compare the salary of each employee to the average salary of his department, how should SQL statements be written? Using window functions, the query can be easily implemented:

postgres=# SELECT Depname, empno, salary, AVG (salary) over (PARTITION by depname) from Empsal;  Depname |  empno | salary          | Avg          -----------+-------+--------+-----------------------Develop   |    One |   5200 | 5020.0000000000000000 Develop   |     7 |   4200 | 5020.0000000000000000 Develop   |     9 |   4500 | 5020.0000000000000000 Develop   |     8 |   6000 | 5020.0000000000000000 Develop   |    Ten |   5200 | 5020.0000000000000000 Personnel |     5 |   3500 | 3700.0000000000000000 Personnel |     2 |   3900 | 3700.0000000000000000 Sales     |     3 |   4800 | 4866.6666666666666667 Sales     |     1 |   5000 | 4866.6666666666666667 Sales     |     4 |   4800 | 4866.6666666666666667 (rows)

As you can see, the meaning of the aggregation function AVG does not change and is still averaged. However, unlike the normal aggregation function, it no longer averages all the salary in the table, but rather averages the salary within the same department (PARTITION by Depname), and the resulting results are shared by all the rows in the same department, and the rows are not merged. In order to better reflect the common aggregation function and window function in the difference between the aggregation function, then look at the following two queries:

postgres=# SELECT avg (Salary) from Empsal;          Avg          -----------------------4710.0000000000000000 (1 row) postgres=# SELECT avg (Salary) over (PARTITION by Depname) from Empsal;          Avg          -----------------------5020.0000000000000000 5020.0000000000000000 5020.0000000000000000 5020.0000000000000000 5020.0000000000000000 3700.0000000000000000) 3700.0000000000000000 4866.6666666666666667 4866.6666666666666667 4866.6666666666666667 (rows)

The window function always contains the over clause, which specifies the name and parameters of the window function, and it is the keyword that distinguishes the regular aggregate function from the window function. The contents of the over clause determine how the data to be processed by the window function will be divided. In the OVER clause we use partition by to divide the data into groups (or partitions). When the aggregation function is processed, it is processed in partitions, and the processing results are shared by all rows within the same partition. For example above, PARTITION by followed by the field is Depname, so the AVG function will be calculated in units. In fact, this partition is the window frame, which is also the origin of the window function name.

We can also use order by in a window to sort the output:

postgres=# SELECT Depname, empno, salary, rank () over (PARTITION by Depname ORDER by salary DESC) from Empsal;  Depname |  Empno | salary | Rank-----------+-------+--------+------Develop   |     8 |   6000 |    1 Develop   |    Ten |   5200 |    2 Develop   |    One |   5200 |    2 Develop   |     9 |   4500 |    4 Develop   |     7 |   4200 |    5 Personnel |     2 |   3900 |    1 Personnel |     5 |   3500 |    2 Sales     |     1 |   |    1 Sales     |     3 |   4800 |    2 Sales     |     4 |   4800 |    2 (rows)

The Rows processed by the window function are derived from the "virtual table" produced by the FROM clause and, if there is a where, GROUP by, and HAVING clause, are filtered by these conditions, and the qualifying clauses are used as input to the window function. In addition, a query can contain multiple window functions.

As mentioned earlier, we use partition by to divide the window, and if the keyword is omitted, then the entire table will be treated as a window:

postgres=# SELECT salary, sum (salary) over () from Empsal; Salary |  Sum  --------+-------   5200 | 47100   4200 | 47100   4500 | 47100   6000 | 47100   5200 | 47100   3500 | 4 7100   3900 | 47100   4800 | 47100 | 47100   4800 | 47100 (rows)

However, it is important to note that if partition by is omitted from the over clause and the ORDER BY clause is included, the situation will not be the same as above:

postgres=# SELECT salary, sum (salary) over (ORDER by salary) from Empsal; Salary |  Sum  --------+-------   3500 |  3500   3900 |  7400   4200 | 11600   4500 | 16100   4800 | 25700   4800 | 25700 | 30700   5200 | 41100   5200 | 41100   6000 | 47100 (rows)

It can be seen from the result that, in the case of omitting partition by but containing the ORDER BY clause, the entire table is not a window, but rather the line from the lowest (in this case the salary, so here is the lowest word) as a window. This is to pay special attention to.

Finally, let's pay attention to the scene used by the window function:

• Can only be used in SELECT and ORDER BY clauses and cannot be used anywhere else, such as group by, have, and WHERE clauses. This is because the input to the window functions is the output of these clauses. The logic of this succession cannot be changed.
• You can use the aggregate function in the parameters of a window function, but you cannot take the in-window function as an argument to the aggregate function. Because the window function is to be executed after the aggregation function. The logic of the succession cannot change.

If we really need to use a window function as input to a clause, we can construct a SELECT clause, such as:

Select Depname, Empno, Salaryfrom  (select Depname, empno, salary,          rank () over (PARTITION by Depname ORDER by Salar Y DESC, empno) as Pos from     empsal  ) as Sswhere Pos < 3;postgres=# SELECT depname, empno, salarypostgres-# Fromp  ostgres-#   (SELECT depname, Empno, Salary,postgres (#           rank () over (PARTITION by Depname ORDER by salary DESC, empno) As Pospostgres (# from      empsalpostgres (#   ) as sspostgres-# WHERE Pos < 3;  Depname  | empno | salary-----------+-------+--------Develop   |     8 |   6000 Develop   |    Ten |   5200 Personnel |     2 |   3900 Personnel |     5 |   3500 Sales     |     1 |   Sales     |     3 |   4800 (6 rows)

If a query contains more than one window function, then we can write multiple over clauses, but if the functions of these windows are the same, then it is a repetitive work and error prone to write multiple separate writes. In this case, we can write the contents of the window into a single Window clause and then reference it in multiple over clauses. Look at the two ways in the following example:

First type: SELECT sum (Salary) over (PARTITION by depname order by salary DESC), AVG (Salary) over (PARTITION by depname order B Y salary DESC) from empsal;postgres=# SELECT sum (Salary) through (PARTITION by Depname ORDER by Salary DESC), avg (Salary) OVE  R (PARTITION by Depname ORDER by salary DESC) from Empsal;          Sum | Avg-------+-----------------------6000 | 6000.0000000000000000 16400 | 5466.6666666666666667 16400 | 5466.6666666666666667 20900 | 5225.0000000000000000 25100 | 5020.0000000000000000 3900 | 3900.0000000000000000 7400 | 3700.0000000000000000 5000 | 5000.0000000000000000 14600 | 4866.6666666666666667 14600 | 4866.6666666666666667 (ten rows) the second type: SELECT sum (Salary) over W, avg (Salary) over W from Empsal WINDOW W as (PARTITION by de PName ORDER by Salary DESC);p ostgres=# SELECT sum (Salary) over W, avg (Salary) over wpostgres-# from empsalpostgres-# W  Indow W as (PARTITION by Depname ORDER by salary DESC);          Sum | Avg-------+-----------------------6000 | 6000.0000000000000000 16400 | 5466.6666666666666667 16400 | 5466.6666666666666667 20900 | 5225.0000000000000000 25100 | 5020.0000000000000000 3900 | 3900.0000000000000000 7400 | 3700.0000000000000000 5000 | 5000.0000000000000000 14600 | 4866.6666666666666667 14600 | 4866.6666666666666667 (rows)

PostgreSQL window function (GO)