本文共 2046 字，大约阅读时间需要 6 分钟。

Window Function

• rank() bigint rank of the current row with gaps; same as row_number of its first peer

• dense_rank() bigint rank of the current row without gaps; this function counts peer groups

• percent_rank() double precision relative rank of the current row: (rank - 1) / (total rows - 1)

• cume_dist() double precision relative rank of the current row: (number of rows preceding or peer with current row) / (total rows)

• ntile(num_buckets integer) integer integer ranging from 1 to the argument value, dividing the partition as equally as possible

• lag(value any [, offsetinteger [, default any ]]) same type as value returns value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead return default. Bothoffset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null

• lead(value any [, offsetinteger [, default any ]]) same type as value returns value evaluated at the row that is offset rows after the current row within the partition; if there is no such row, instead return default. Bothoffset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null

• first_value(value any) same type as value returns value evaluated at the row that is the first row of the window frame

• last_value(value any) same type as value returns value evaluated at the row that is the last row of the window frame

• nth_value(value any, nthinteger) same type as value returns value evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row

postgres=# select depname, empno, salary, row_number() over() from empsalary; depname | empno | salary | row_number----------+-------+--------+------------ develop  | 11 | 5200 | 1 develop  | 7  | 4200 | 2 develop  | 9  | 4500 | 3 personel | 5  | 3500 | 4 personel | 6  | 6500 | 5 personel | 12 | 6500 | 6 personel | 15 | 8900 | 7

转载地址：http://keyvb.baihongyu.com/