PostgreSQL 的窗口函数(Window Function)是一种特殊的函数,它允许你对一组行执行计算,这些行与当前行有某种关系,通常是因为它们在某个排序顺序中是相邻的。窗口函数常用于执行如累计总和、移动平均、排名等操作。
以下是 PostgreSQL 中一些常用的窗口函数:
-
ROW_NUMBER() - 为结果集中的每一行分配一个唯一的序号,通常基于某种顺序。
SELECT ROW_NUMBER() OVER (ORDER BY some_column) FROM some_table;
-
RANK() - 为结果集中的每一行分配一个排名,排名相同的情况下会导致排名跳过(例如,两个第一,然后是第三个)。
SELECT RANK() OVER (ORDER BY some_column DESC) FROM some_table;
-
DENSE_RANK() - 与
RANK()
类似,但排名不会跳过(例如,两个第一,然后是第二个)。SELECT DENSE_RANK() OVER (ORDER BY some_column DESC) FROM some_table;
-
NTILE(n) - 将结果集分为
n
个大致相等的组,并为每组分配一个从 1 到n
的数字。SELECT NTILE(4) OVER (ORDER BY some_column) FROM some_table;
-
LEAD(column) - 返回当前行的下一行的
column
值。SELECT LEAD(some_column, 1, default_value) OVER (ORDER BY some_column) FROM some_table;
-
LAG(column) - 返回当前行的上一行的
column
值。SELECT LAG(some_column, 1, default_value) OVER (ORDER BY some_column) FROM some_table;
-
SUM(column) - 计算从当前行到结果集开始的
column
的总和。SELECT SUM(some_column) OVER (ORDER BY some_column) FROM some_table;
-
AVG(column) - 计算从当前行到结果集开始的
column
的平均值。SELECT AVG(some_column) OVER (ORDER BY some_column) FROM some_table;
-
MIN(column) 和 MAX(column) - 分别计算从当前行到结果集开始或结束的
column
的最小值或最大值。SELECT MIN(some_column) OVER (ORDER BY some_column), MAX(some_column) OVER (ORDER BY some_column) FROM some_table;
窗口函数使用 OVER
子句来定义窗口的排序和框架(frame)。框架可以是:
- BETWEEN ... AND ... - 指定一个范围,例如
BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
表示从结果集的开始到当前行。 - ROWS 或 RANGE - 指定基于行或基于范围的窗口。
窗口函数非常有用,因为它们允许你在不改变原始表结构的情况下,对数据进行复杂的分析和转换。