Нумерация строк в PostgreSQL

Как получить номер строки в PostgreSQL, когда результаты упорядочены по некоторым столбцам?

например.

SELECT 30+row_number() AS position, * 
FROM users 
ORDER BY salary DESC 
LIMIT 30 
OFFSET 30

Я предположил, что запрос вернет список следующим образом:

position | name | salary
31       | Joy  | 4500
32       | Katie| 4000
33       | Frank| 3500

Собственно, мне нужно дублировать предложение ORDER в запросе, чтобы сделать его функциональным:

SELECT 30+row_number(ORDER BY salary DESC) AS position, * 
FROM users 
ORDER BY salary DESC 
LIMIT 30 
OFFSET 30

Есть ли другой способ возврата упорядоченных и нумерованных результатов без необходимости дублирования кода?

Я знаю, что это можно решить, увеличив некоторую переменную в самом приложении, но я хочу сделать это на уровне базы данных и вернуться к уже пронумерованным результатам приложения...

Ответы

Ответ 1

нет - order by в функции окна и предложение order by оператора select функционально две разные вещи.

Кроме того, ваш оператор производит: ERROR: window function call requires an OVER clause, поэтому:

SELECT 30+row_number(ORDER BY salary DESC) AS position, * FROM users ORDER BY salary DESC LIMIT 30 OFFSET 30

должен быть:

SELECT 30+row_number() OVER(ORDER BY salary DESC) AS position, * FROM users ORDER BY salary DESC LIMIT 30 OFFSET 30

Обратите внимание, что если заработная плата не является уникальной, то нет никакой гарантии, что они будут производить одинаковый заказ. Возможно, было бы лучше сделать:

SELECT * 
FROM ( SELECT 30+row_number() OVER(ORDER BY salary DESC) AS position, * 
       FROM users )
ORDER BY position LIMIT 30 OFFSET 30

Также обратите внимание, что если вы выполняете этот запрос несколько раз с разными смещениями, вам необходимо:

  • установите уровень изоляции для сериализации
  • убедитесь, что все, что вы заказываете, уникально

или вы можете получить дубликаты и отсутствующие строки. См. Комментарии этого ответа для чего.