postgres 窗口函数

Window Function
* row_number() bigint number of the current row within its partition, counting from 1

• 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