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_nameROW_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()函数将整数值分配给结果集中的行。