PostgreSQL 窗口函数参考

PostgreSQL 窗口函数提供了跨与当前行关联的分组的计算能力。 窗口函数与聚合函数不同，聚合函数对每个分组进行计算并为每个分组返回一行，而窗口函数不会将每个分组输出到一行，而是将每个分组的计算结果合并到与之关联的行中。

窗口函数都依赖于 OVER 子句， OVER 子句可用于行进行分组或组内排序。 窗口函数的调用语法如下：

window_function(args)
OVER (
  [PARTITION BY partition_column_list]
  [ORDER BY order_column_list]
)

这里：

  • window_function(args) 是需要调用的窗口函数以及它的参数。 除了特定的窗口函数，您还可以将聚合函数用在此处。
  • PARTITION BY 指定需要分区的列名。它是可选的。默认为整个结果集为一个分区。
  • ORDER BY 指定排序列名。它是可选的。

PostgreSQL 提供了很多窗口函数，可用于获取分区内的排名、序号、累积分布、桶排名等信息。

  1. cume_dist

    PostgreSQL cume_dist() 函数返回当前行的累积分布，即从第一行到与当前行值相同的最后一行的行数在分区内的总行数中的占比。

  2. dense_rank

    PostgreSQL dense_rank() 函数返回当前行所在的分区内的排名，从 1 开始，但没有间隔。

  3. first_value

    PostgreSQL first_value() 函数从当前行关联的窗口框架的第一行中返回评估的值。

  4. lag

    PostgreSQL lag() 函数返回来自当前行所在的分区内当前行之前的指定行之内的行的值。

  5. last_value

    PostgreSQL last_value() 函数从当前行关联的窗口框架的最后一行中返回评估的值。

  6. lead

    PostgreSQL lead() 函数返回来自当前行所在的分区内当前行之后的指定行之内的值。

  7. nth_value

    PostgreSQL nth_value() 函数从当前行关联的窗口框架的指定的一行中返回评估的值。

  8. ntile

    PostgreSQL ntile() 函数将当前行所在的分区内的所有行尽可能平均的分成指定数量的区间，并返回当前行所在的区间编号。

  9. percent_rank

    PostgreSQL percent_rank() 函数返回当前行所在的分区内的相对排名，也就是 (rank() - 1) / (分区总行数 - 1)

  10. rank

    PostgreSQL rank() 函数返回当前行所在的分区内的排名，从 1 开始，但有间隔。

  11. row_number

    PostgreSQL row_number() 函数返回当前行所在的分区内的序号，从 1 开始。