了解 PostgreSQL 中的 rank() 和 dense_rank() 函数

在 PostgreSQL 中，rank() 和 dense_rank() 是通用的窗口函数，用于对值进行排序，并根据它们之间的相对位置，以数字形式为其分配排名。

这两个函数都与 OVER 子句一起使用，同时还有 PARTITION BY、ORDER BY 或 ROWS BETWEEN。与 rank() 和 dense_rank() 一起使用时，它们会以以下方式影响窗口：

• ORDER BY：指定要对其值进行排名的列

• PARTITION BY：对排名进行分组

rank() 和 dense_rank() 之间的区别在于它们如何处理相同的值。假设您根据分数值分配排名，并且最终得到两个结果都等于 85。如果您使用 rank()，则这两个值将被赋予相同的排名，并且下一个排名将被跳过。因此，如果它们并列第 3，则它们都将获得 3，4 将被跳过，下一个最高排名将是 5。如果您在同一示例中使用 dense_rank()，则 4 不会被跳过。它将是下一个排名值。

rank() 语法

rank () → bigint

dense_rank() 语法

dense_rank () → bigint

rank() 语法与窗口函数

rank() OVER ([PARTITION BY <列>] [ORDER BY <列>])

dense_rank() 语法与窗口函数

dense_rank() OVER ([PARTITION BY <列>] [ORDER BY <列>])

rPostgreSQL 中的 rank() 和 dense_rank()：示例

我们将用于示例的数据集包含三名学生在四门科目中的成绩。

查找每行的排名

如果我们想知道哪些学生在特定科目中需要最多的帮助，我们可以运行如下查询

SELECT
	student,
	subject,
	grade,
	rank() OVER(ORDER BY grade DESC)
FROM grades;

我们希望排名 1 对应最高分。为此，我们按降序对窗口框架中的结果进行排序，以便最高分位于顶部。

以下是结果

由此我们可以看出，吉姆在历史和英语方面都需要帮助，即使他在数学方面名列班级前茅。萨姆在历史方面也需要帮助。现在，你会注意到 7 和 10 都重复出现，而没有 8 和 11。这是因为我们使用的是 rank()。当我们改用 dense_rank() 时，就不会出现间隔。

以下是该结果

使用带有 rank() 的分区

如果我们想查看每个学生在每个科目中的排名，我们可以添加一个 PARTITION BY 子句，它可以对窗口中的记录进行分组。以下是查询

SELECT
	student,
	subject,
	RANK() OVER(PARTITION BY subject ORDER BY grade DESC)
FROM
	grades
ORDER BY student, rank;

以下是结果

查找平均值的排名

要查看每个学生的平均成绩在所有科目中的排名，我们可以使用子查询来实现。以下是该查询

SELECT
	student,
	average,
	RANK() OVER(ORDER BY average DESC)
FROM
	(
	SELECT
		student,
		avg(grade) average
	FROM
		grades
	GROUP BY
		student) AS subquery;

以下是结果

查找分组行中值的排名

对于此示例，我们使用另一个数据集。它包含几个城市的温度和降水数据。

假设我们有一个温度，我们想看看它在我们表中的温度中排名如何。例如，我们想看看 68 度在迈阿密和亚特兰大这两个城市在这个日期范围内的排名。

以下是查询

SELECT
	city,
	rank(68) WITHIN GROUP (
	ORDER BY temperature DESC)
FROM city_data
GROUP BY city;

我们按城市对结果进行分组，以便获得 68 度在每个城市中的排名。然后我们使用 WITHIN GROUP 子句，它指定如何对按聚合函数分组的行进行排序。在这里，我们按降序对行进行排序，因为我们希望最高温度的排名为 1。

以下是结果

这告诉我们，68 度将是亚特兰大第二高的温度，也是迈阿密在表中日期范围内第四高的温度。

后续步骤

要详细了解 rank() 和 dense_rank() 以及如何在 PostgreSQL 中使用它们，请查看 PostgreSQL 关于窗口函数的文档。有关如何在您自己的 TimescaleDB SQL 查询中使用它们的更多示例，请参阅以下 Timescale 文档部分

• 执行高级分析查询

• 物联网简介：纽约市出租车