PostgreSQL nth_value() 函数使用指南
PostgreSQL nth_value() 函数从当前行关联的窗口框架的指定的一行中返回评估的值。
nth_value() 语法
这里是 PostgreSQL nth_value() 函数的语法：
nth_value(expr, n)
OVER (
[PARTITION BY partition_column_list]
[ORDER BY order_column_list]
)
nth_value(expr, 1) 等效于 first_value(expr)。
参数
expr- 必需的。它可以是一个列名或者表达式。
n- 必需的。指定行的编号。
partition_column_list- 参与分区的列的列表。
order_column_list- 参与排序的列的列表。
返回值
PostgreSQL nth_value() 函数从当前行关联的窗口框架的指定的一行中返回评估的值。 如果指定的行不存在，此函数将返回 NULL。
nth_value() 示例
演示数据准备
使用下面的 CREATE TABLE 语句创建一个表 student_grade 以存储学生的班级和成绩：
CREATE TABLE student_grade (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
class CHAR(1) NOT NULL,
subject VARCHAR(20) NOT NULL,
grade INT NOT NULL
);
这里创建了一个 student_grade 表，它有 5 个列：
id- 行 ID，主键。name- 学生的姓名。class- 学生所在的班级。subject- 科目的名称。grade- 该科目的成绩。
使用下面的 INSERT 语句向 student_grade 表中插入一些行：
INSERT INTO student_grade
(name, class, subject, grade)
VALUES
('Tim', 'A', 'Math', 9),
('Tom', 'A', 'Math', 7),
('Jim', 'A', 'Math', 8),
('Tim', 'A', 'English', 7),
('Tom', 'A', 'English', 8),
('Jim', 'A', 'English', 7),
('Lucy', 'B', 'Math', 8),
('Jody', 'B', 'Math', 6),
('Susy', 'B', 'Math', 9),
('Lucy', 'B', 'English', 6),
('Jody', 'B', 'English', 7),
('Susy', 'B', 'English', 8);
使用下面的 SELECT 语句检索表中的数据：
SELECT * FROM student_grade;
id | name | class | subject | grade
----+------+-------+---------+-------
1 | Tim | A | Math | 9
2 | Tom | A | Math | 7
3 | Jim | A | Math | 8
4 | Tim | A | English | 7
5 | Tom | A | English | 8
6 | Jim | A | English | 7
7 | Lucy | B | Math | 8
8 | Jody | B | Math | 6
9 | Susy | B | Math | 9
10 | Lucy | B | English | 6
11 | Jody | B | English | 7
12 | Susy | B | English | 8
(12 rows)按照科目查看每个学生的成绩排名
要查看在每个科目中每个学生按照成绩从高到低的排序号和每个科目中的最好成绩，请使用如下语句：
SELECT
*,
nth_value(grade, 1) OVER (
PARTITION BY subject
ORDER BY grade DESC
RANGE BETWEEN
UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) first_grade
FROM student_grade;
id | name | class | subject | grade | first_grade
----+------+-------+---------+-------+-------------
12 | Susy | B | English | 8 | 8
5 | Tom | A | English | 8 | 8
11 | Jody | B | English | 7 | 8
4 | Tim | A | English | 7 | 8
6 | Jim | A | English | 7 | 8
10 | Lucy | B | English | 6 | 8
1 | Tim | A | Math | 9 | 9
9 | Susy | B | Math | 9 | 9
7 | Lucy | B | Math | 8 | 9
3 | Jim | A | Math | 8 | 9
2 | Tom | A | Math | 7 | 9
8 | Jody | B | Math | 6 | 9
(12 rows)注意，上面 SQL 语句中的窗口函数：
nth_value(grade, 1) OVER (
PARTITION BY subject
ORDER BY grade DESC
) first_grade
在 OVER 子句中，
PARTITION BY subject将按照学科进行分区。ORDER BY grade DESC将每个分区内的行按照成绩逆序排列。RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING定义了每个分区中与当前行相关的窗口框架，这里是从分区的第一行到最后一行。nth_value(grade, 1)返回每行在其关联的分区的第一行的成绩（grade）。因为是按照grade逆序排序，于是每行中添加了一列显示最好的成绩。
如果您想在每行中显示每个科目中最差的成绩，可以使用 nth_value(grade, 3) 替换上面语句中的 nth_value(grade, 1)。
按照班级查看每个学生的总成绩排名
要查看在每个班级中每个学生按照总成绩从高到底的排序号和每个班级的最好成绩，请使用下面的语句：
SELECT
t.*,
nth_value(t.sum_grade, 1) OVER (
PARTITION BY class
ORDER BY t.sum_grade DESC
RANGE BETWEEN
UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) first_grade
FROM (
SELECT class,
name,
sum(grade) sum_grade
FROM student_grade
GROUP BY class, name
) t;
class | name | sum_grade | first_grade
-------+------+-----------+-------------
A | Tim | 16 | 16
A | Jim | 15 | 16
A | Tom | 15 | 16
B | Susy | 17 | 17
B | Lucy | 14 | 17
B | Jody | 13 | 17
(6 rows)在上面的语句中，注意这个子查询：
SELECT class,
name,
sum(grade) sum_grade
FROM student_grade
GROUP BY class, name
这个子查询使用 GROUP BY 子句和 sum() 按照班级和学生汇总出每个学生的总成绩。
class | name | sum_grade
-------+------+-----------
A | Tim | 16
A | Jim | 15
A | Tom | 15
B | Jody | 13
B | Lucy | 14
B | Susy | 17
(6 rows)主语句在从这个子查询中按照班级进行分区，然后在每个分区内按照总成绩逆序排序，并使用 nth_value(t.sum_grade, 1) 返回每行在其关联的分区内第一行中的成绩。