PostgreSQL 教程: row_number 函数

八月 25, 2023

摘要：在本教程中，您将学习如何使用 PostgreSQL 的ROW_NUMBER()函数为结果集中的每一行分配唯一的整数值。

目录

PostgreSQL ROW_NUMBER 函数简介

ROW_NUMBER()函数是一个窗口函数，它将一个连续整数分配给结果集中的每一行。下面说明了ROW_NUMBER()函数的语法：

ROW_NUMBER() OVER(
    [PARTITION BY column_1, column_2,…]
    [ORDER BY column_3,column_4,…]
)

ROW_NUMBER()函数操作的行集称为窗口。

PARTITION BY子句将窗口划分为更小的集合或分区。如果指定PARTITION BY子句，则每个分区的行号从 1 开始并递增 1。

由于PARTITION BY子句对于ROW_NUMBER()函数来说是可选的，因此可以省略它，ROW_NUMBER()函数会将整个窗口视为一个分区。

OVER子句内的ORDER BY子句决定了编号的分配顺序。

PostgreSQL ROW_NUMBER 函数示例

我们将使用 PostgreSQL 窗口函数教程中创建的products表来演示ROW_NUMBER()函数的功能。

products_product_groups_tables

products表中数据如下：

img

请参阅以下查询。

SELECT
	product_id,
	product_name,
	group_id,
	ROW_NUMBER () OVER (ORDER BY product_id)
FROM
	products;

img

因为我们没有使用PARTITION BY 子句，所以ROW_NUMBER()函数将整个结果集视为一个分区。

该ORDER BY子句按product_id排序结果集，因此，ROW_NUMBER()函数根据product_id顺序将整数值分配给行。

在下面的查询中，我们将ORDER BY子句中的列更改为product_name，ROW_NUMBER()函数根据产品名称顺序将整数值分配给每行。

SELECT
	product_id,
	product_name,
	group_id,
	ROW_NUMBER () OVER (
           ORDER BY product_name
        )
FROM
	products;

PostgreSQL ROW_NUMBER order by product name

在下面的查询中，我们使用PARTITION BY子句根据 group_id列中的值将窗口划分为子集。在这种情况下，ROW_NUMBER()函数为每个分区的起始行分配 1，并为同一分区内的下一行加 1。

其中，ORDER BY子句按product_name列中的值对每个分区中的行进行排序。

SELECT
	product_id,
	product_name,
	group_id,
	ROW_NUMBER () OVER (
		PARTITION BY group_id
		ORDER BY
			product_name
	)
FROM
	products;

PostgreSQL ROW_NUMBER with PARTITION example

PostgreSQL ROW_NUMBER 函数和 DISTINCT 运算符

以下查询使用ROW_NUMBER()函数将整数分配给products表中的不同（distinct）价格。

SELECT DISTINCT
	price,
	ROW_NUMBER () OVER (ORDER BY price)
FROM
	products
ORDER BY
	price;

PostgreSQL ROW_NUMBER and DISTINCT

然而，结果并非预期，因为它包含重复的价格。原因是在应用DISTINCT之前对结果集进行了ROW_NUMBER()操作。

为了解决这个问题，我们可以在 CTE 中获取不同价格的列表，然后在外部查询中应用ROW_NUMBER()函数，如下所示：

WITH prices AS (
	SELECT DISTINCT
		price
	FROM
		products
) SELECT
	price,
	ROW_NUMBER () OVER (ORDER BY price)
FROM
	prices;

PostgreSQL ROW_NUMBER and CTE

或者我们可以在FROM子句中使用子查询来获取唯一价格的列表，然后在外部查询中应用ROW_NUMBER()函数。

SELECT
	price,
	ROW_NUMBER () OVER (ORDER BY price)
FROM
	(
		SELECT DISTINCT
			price
		FROM
			products
	) prices;

PostgreSQL ROW_NUMBER and subquery

使用 ROW_NUMBER 函数进行分页

在应用程序开发中，您可以使用分页技术来显示行的子集而不是表中的所有行。

除了使用 LIMIT 子句之外，您还可以使用ROW_NUMBER()函数进行分页。

例如，以下语句查询从行号 6 开始的五行：

SELECT
	*
FROM
	(
		SELECT
			product_id,
			product_name,
			price,
			ROW_NUMBER () OVER (ORDER BY product_name)
		FROM
			products
	) x
WHERE
	ROW_NUMBER BETWEEN 6 AND 10;

PostgreSQL ROW_NUMBER with pagination

使用 ROW_NUMBER 函数获取第 n 个最高/最低行

例如，要获取第三个最贵的产品，首先，我们从产品表中获取不同的价格，并选择行号为 3 的价格。然后，在外部查询中，我们获取价格等于第三个最高价格的产品。

SELECT
	*
FROM
	products
WHERE
	price = (
		SELECT
			price
		FROM
			(
				SELECT
					price,
					ROW_NUMBER () OVER (
						ORDER BY price DESC
					) nth
				FROM
					(
						SELECT DISTINCT
							(price)
						FROM
							products
					) prices
			) sorted_prices
		WHERE
			nth = 3
	);

PostgreSQL ROW_NUMBER nth highest lowest example

在本教程中，我们向您展示了如何使用 PostgreSQL 的ROW_NUMBER()函数将整数值分配给结果集中的行。

了解更多

PostgreSQL 教程：窗口函数

PostgreSQL 文档：窗口函数

搜索

分类

标签