PostgreSQL窗口函数

## 窗口函数的语法

function_name ([expression [, expression ... ]]) OVER window_name
function_name ([expression [, expression ... ]]) OVER ( window_definition )
function_name ( * ) OVER window_name
function_name ( * ) OVER ( window_definition )


## 典型的窗口函数用法

select path, cost, sum(cost) over (partition by path ) as sum_cost from tcost;


## 内置的窗口函数

Function | Return Type | Description :–|:–| row_number() 行号 | bigint | number of the current row within its partition, counting from 1 。返回当前窗口的行数，计数从1开始。主要就是遇到相同排名时的区别，即相同的数值，排名是不同的，而且也不是确定的。 rank() 排名（保持间隔） | bigint | rank of the current row with gaps; same as row_number of its first peer。当前窗口中，相同的数值排名是相同的，但是还是会保留间隔的。比如：1，1，3。如果是 row_number 会是 1，2，3。也可能是：1，2，3这样子下去，这个看数据是否有相同。 dense_rank() 排名（不保持间隔） | bigint | rank of the current row without gaps; this function counts peer groups。这个函数与 rank() 一样，只是它不会保持间隔的，相同的数据在同一排名，然后会是下一个排名。如：1,1,2,3,3,4等。 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)。即小于等于当前行值的行数/总行数。结果为 0<结果<=1 ntile(num_buckets integer) 可以将结果集放到我们指定数目的组中 | integer | integer ranging from 1 to the argument value, dividing the partition as equally as possible。组的数目从1开始计。分组的依据：1,每组的记录数不能大于它上一组的记录数。2,所有组中的记录要么都相同，要么从某组开始后面所有组的记录数都与该组的记录数相同 lag(value any [, offset integer [, 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. Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null 。向前获得相对于当前记录指定距离的那条记录的数据 lead(value any [, offset integer [, 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. Both offset 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, nth integer) | 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。获取窗口中第N个值。