ROW NUMBER: примеры (SQL)
ROW_NUMBER( OVER [PARTITION BY ] ORDER BY ): bigintФункция ROW_NUMBER в MS SQL Server относится к оконным функциям, которые выполняют вычисления над набором строк. Она присваивает последовательный номер каждой строке в результирующем наборе, начиная с 1.
Описание и использование
Функция ROW_NUMBER используется, когда требуется нумерация строк в определенном порядке или разделении на группы. Часто применяется для пагинации, удаления дубликатов или выделения записей по рангу.
Синтаксис функции:
ROW_NUMBER() OVER ( [ PARTITION BY partition_expression ] ORDER BY order_expression [ ASC | DESC ] )
Аргументы:
- PARTITION BY - необязательное выражение, разделяющее результирующий набор на группы (партиции). Нумерация начинается заново для каждой партиции.
- ORDER BY - обязательное выражение, определяющее порядок нумерации строк внутри партиции или всего набора.
- ASC | DESC - направление сортировки: по возрастанию или убыванию.
Возвращаемое значение: целое число (int), начиная с 1 для первой строки в каждой партиции и увеличивающееся на 1 для каждой последующей строки.
Простые примеры использования
Пример 1: Базовая нумерация строк
SELECT
ROW_NUMBER() OVER (ORDER BY Name) AS RowNum,
Name,
ListPrice
FROM Production.Product
WHERE ListPrice > 0;
RowNum Name ListPrice 1 Adjustable Race 105.90 2 All-Purpose Bike Stand 159.00 3 AWC Logo Cap 8.99 ...
Пример 2: Нумерация с разделением на группы
SELECT
ROW_NUMBER() OVER (PARTITION BY ProductCategoryID ORDER BY Name) AS RowNum,
Name,
ProductCategoryID
FROM Production.Product
WHERE ProductCategoryID IN (1, 2);
RowNum Name ProductCategoryID 1 Road-150 Red, 52 1 2 Road-150 Red, 48 1 3 Road-150 Red, 62 1 1 Mountain-100 Silver, 38 2 2 Mountain-100 Silver, 42 2
Пример 3: Нумерация с сортировкой по убыванию
SELECT
ROW_NUMBER() OVER (ORDER BY ListPrice DESC) AS RowNum,
Name,
ListPrice
FROM Production.Product
WHERE ListPrice > 0;
RowNum Name ListPrice 1 Road-150 Red, 62 3578.27 2 Mountain-100 Silver, 48 3399.99 3 Road-250 Red, 44 2443.35 ...
Похожие функции в MS SQL
В MS SQL Server существуют другие функции ранжирования с отличиями в поведении:
- RANK() - присваивает одинаковый номер строкам с одинаковыми значениями ORDER BY, пропускает следующие порядковые номера.
- DENSE_RANK() - как RANK(), но не пропускает номера при равенстве.
- NTILE(n) - разбивает набор на указанное количество групп.
ROW_NUMBER предпочтительнее, когда требуется уникальная нумерация каждой строки. RANK и DENSE_RANK подходят для ранжирования с учетом одинаковых значений.
SELECT
Name,
ListPrice,
ROW_NUMBER() OVER (ORDER BY ListPrice DESC) AS RowNum,
RANK() OVER (ORDER BY ListPrice DESC) AS Rank,
DENSE_RANK() OVER (ORDER BY ListPrice DESC) AS DenseRank
FROM Production.Product
WHERE ListPrice > 0;
Name ListPrice RowNum Rank DenseRank Road-150 Red, 62 3578.27 1 1 1 Mountain-100 Silver, 48 3399.99 2 2 2 Road-250 Red, 44 2443.35 3 3 3 Road-250 Red, 44 2443.35 4 3 3 ...
Распространенные ошибки
Ошибка 1: Отсутствие ORDER BY в OVER()
-- Неверно
SELECT ROW_NUMBER() OVER () AS RowNum, Name
FROM Production.Product;Сообщение об ошибке: Оконная функция ROW_NUMBER должна содержать предложение ORDER BY.
Ошибка 2: Использование в WHERE для фильтрации по номеру строки
-- Неверно
SELECT ROW_NUMBER() OVER (ORDER BY Name) AS RowNum, Name
FROM Production.Product
WHERE RowNum <= 10;Сообщение об ошибке: Недопустимое имя столбца "RowNum".
Правильный подход - использование общего табличного выражения:
WITH NumberedProducts AS (
SELECT ROW_NUMBER() OVER (ORDER BY Name) AS RowNum, Name
FROM Production.Product
)
SELECT * FROM NumberedProducts WHERE RowNum <= 10;RowNum Name 1 Adjustable Race 2 All-Purpose Bike Stand 3 AWC Logo Cap ...
Изменения в последних версиях
Функция ROW_NUMBER() появилась в SQL Server 2005 и с тех пор не претерпела значительных синтаксических изменений. Однако улучшения касаются оптимизации производительности и взаимодействия с другими функциями:
- SQL Server 2012: улучшена производительность при использовании с секционированием.
- SQL Server 2016: улучшена обработка в сочетании с индексами columnstore.
- SQL Server 2019: улучшения в интеллектуальной обработке запросов для оконных функций.
Расширенные примеры применения
Пример 1: Удаление дубликатов
WITH CTE AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY Email ORDER BY CreateDate DESC) AS rn
FROM Users
)
DELETE FROM CTE WHERE rn > 1;Пример 2: Пагинация с пропуском строк
DECLARE @PageSize INT = 10, @PageNumber INT = 3;
WITH NumberedProducts AS (
SELECT
ROW_NUMBER() OVER (ORDER BY ProductID) AS RowNum,
Name,
ListPrice
FROM Production.Product
)
SELECT *
FROM NumberedProducts
WHERE RowNum > (@PageNumber - 1) * @PageSize
AND RowNum <= @PageNumber * @PageSize;RowNum Name ListPrice 21 LL Bottom Bracket 53.99 22 ML Bottom Bracket 44.99 23 HL Bottom Bracket 53.99 ...
Пример 3: Поиск разрывов в последовательностях
WITH Numbered AS (
SELECT
OrderID,
ROW_NUMBER() OVER (ORDER BY OrderID) AS RowNum
FROM Orders
)
SELECT
prev.OrderID + 1 AS GapStart,
curr.OrderID - 1 AS GapEnd
FROM Numbered curr
JOIN Numbered prev ON curr.RowNum = prev.RowNum + 1
WHERE curr.OrderID > prev.OrderID + 1;Пример 4: Присвоение номеров в иерархических данных
SELECT
ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY HireDate) AS EmpNumberInDept,
EmployeeID,
FirstName,
DepartmentID,
HireDate
FROM HumanResources.Employee
ORDER BY DepartmentID, HireDate;Аналоги в других СУБД
PostgreSQL - идентичный синтаксис ROW_NUMBER().
SELECT
ROW_NUMBER() OVER (ORDER BY product_name) as row_num,
product_name,
price
FROM products;Oracle - также поддерживает ROW_NUMBER() с аналогичным синтаксисом.
SELECT
ROW_NUMBER() OVER (ORDER BY product_name) as row_num,
product_name,
price
FROM products;MySQL (8.0+) - поддерживает ROW_NUMBER() с тем же синтаксисом.
SELECT
ROW_NUMBER() OVER (ORDER BY product_name) as row_num,
product_name,
price
FROM products;SQLite (3.25.0+) - поддерживает ROW_NUMBER() аналогично.
SELECT
ROW_NUMBER() OVER (ORDER BY product_name) as row_num,
product_name,
price
FROM products;Основные отличия могут проявляться в производительности и поддержке в старых версиях.