PG Built-in function __ function

a window function

PG is a feature that adds some window functions to the version after 8.4. The window function is to return grouped results for each row in the group, and the aggregate function is to return only one grouping result for each group in the group, which you may not understand, but it doesn't matter, the following is explained by an example.

1, PG window function has (Huang Di for the common window function):

SUM (): Sum in Group

AVG (): Average in Group

Row_number (): Group within sort, uninterrupted, such as 1,2,3,4,5,6

Rank (): Sort within the group, will be interrupted, such as 1,2,2,4,5,6

Dense_rank (): Sort within a group, but will repeat, as 1,2,2,3,4,5

First_value (value Any): Returns the first value in the window frame

Last_value (value Any): Returns the last value in the window frame

COUNT (*) over (partition by Col_name)

Max (*) over (partition by Col_name)

MIN (*) over (partition by Col_name)

Nth_value (value any, nth integer): Returns the specified value in the window frame, such as Nth_value (salary,2), the second window function value that returns the field salary

Lag (value any [, offset integer [, Defaultany]]): The offset function, which takes a lag value, such as lag (column_name,2,0), to indicate that the field offset is 2, instead of the default value, which is 0, Do not write default is null

Leads (value any [, offset integer [, Defaultany]]): Offset function, take advance value, class

Percent_rank (): Similar to the cume_dist (cumulative allocation) function, for a given row in a group, when the ordinal of that row is computed (if there is a sort case, then the sorted ordinal) is reduced by 1 and then divided by N-1 (n is the number of rows in the group). The function always returns the number between 0~1 (including 1)

Cume_dist (): Cumulative allocation, for a given row in a group, when the ordinal of that row is computed (if there is a sort case, the ordinal number is sorted) and then divided by N (n is the number of rows in the group). The function always returns the number between 0~1 (including 1)

Ntile (Num_buckets integer): A hash that divides a group into "expressions", for example, if the expression = 4, assign a number to each row in the group (from 1 to 4), and if there are 20 rows in the group, assign 5 to the first 1 rows, and 5 to the next 2, and so on. If the cardinality of a group cannot be separated evenly by an expression value, when the rows are allocated, there are no percentile rows in the group that are more than one row per percentile. For example, if the expression = 4, the number of rows = 21, then the percentile=1 has 6 rows, percentile=2 5 rows, and so on.

2, the data required to build the example, as follows:

droptableifexists empsalary;

createtable empsalary (

Depname varchar,

Empno bigint,

Salary int,

Enroll_date Date

);

insertinto empsalaryVALUES(' Develop ', 10,5200, ' 2007/08/01 ');

insertinto empsalaryVALUES(' Sales ', 1,5000, ' 2006/10/01 ');

insertinto empsalaryVALUES(' personnel ', 5,3500, ' 2007/12/10 ');

insertinto empsalaryVALUES(' Sales ', 4,4800, ' 2007/08/08 ');

insertinto empsalaryVALUES(' Sales ', 6,5500, ' 2007/01/02 ');

insertinto empsalaryVALUES(' personnel ', 2,3900, ' 2006/12/23 ');

insertinto empsalaryVALUES(' Develop ', 7,4200, ' 2008/01/01 ');

insertinto empsalaryVALUES(' Develop ', 9,4500, ' 2008/01/01 ');

insertinto empsalaryVALUES(' Sales ', 3,4800, ' 2007/08/01 ');

insertinto empsalaryVALUES(' Develop ', 8,6000, ' 2006/10/01 ');

insertinto empsalaryVALUES(' Develop ', 11,5200, ' 2007/08/15 ');

3, SUM (): Statistics of the total salary of each department, the results of the window function are as follows:

Selectsum (Salary) Over (Partitionby depname), * from Empsalary;

The results of the aggregate function are as follows:

Selectsum (salary), Depname from empsalaryGROUPBY depname;

From the results of the two statements above, you can see that aggregate functions return only the summary results of each grouping, and the window function returns the summary result of the Department on the basis of the original table, and with this function we can easily solve a business scenario, asking everyone to pay the proportion of the entire portion, as follows

SELECT *,

salary/(SUM(Salary)over (PARTITIONby Depname))::numeric(10,2)

from Empsalary;

Of course, this function can also solve this business problem, but the writing is slightly complex, performance slightly worse.

4 avg () Average salary of statistic department

Selectavg (Salary) Over (Partitionby depname), * from Empsalary;

5, Row_number () \rank () \dense_rank (), sorting function in the group, according to department statistics salary from low to high rank:

--If the department has the same salary rank and does not repeat

Selectrow_number ()over (partitionby depname by salary), *

from Empsalary;

-If the department has the same salary ranking repeat, but the next ranking will be interrupted

Selectrank ()over (partitionby depname by salary), *

from Empsalary;

-If the department has the same salary ranking repeat, but the next ranking will not be interrupted

Selectdense_rank ()over (partitionby depname by salary), *

from Empsalary;

6, first_value\last_value\ nth_value The specified value in the group

--Take the highest salary in each department

SELECT first_value (Salary)over (partitionby depname by Salary Descrowsbetweenunboundedprecedingandunboundedfollowing),

*

from Empsalary;

--Take the lowest salary in every department

SELECT last_value (Salary)over (partitionby depname by Salary Descrowsbetweenunboundedprecedingandunboundedfollowing),

*

from Empsalary;

--Take the second highest salary in every department

SELECT nth_value (salary,2)over (partitionby depname by Salary Descrowsbetweenunboundedprecedingandunboundedfollowing),

*

from Empsalary;

7, Lag\lead in the group offset row value

--Departmental salary is offset 2 lines backwards

SELECT LAG (salary,2,NULL) Over (partitionby depname by salaryDESC),

*

from Empsalary;

--Department salaries are offset 2 lines forward

SELECT Lead (salary,2,NULL) Over (partitionby depname by salaryDESC),

*

from Empsalary;

8, Percent_rank () \cume_dist () \ntile () distribution function

SELECT Percent_rank ()over (partitionby depname by