Сама по себе каждая вещь и велика, и мала (Анаксагор).

Функция ROW_NUMBER в MySQL

3 мин для чтения
FavoriteLoadingДобавить в избранное
1 Звезда2 Звезды3 Звезды4 Звезды5 Звезд (4 оценок, среднее: 5,00 из 5)
Загрузка...
8 ноября 2019
MySQL. Математические функции
Описание : в этой статье вы узнаете о функции ROW_NUMBER() в MySQL и о том, как использовать ее для генерации последовательного числа для каждой строки в наборе результатов.

 

Синтаксис ROW_NUMBER() в MySQL

MySQL представил эту функцию ROW_NUMBER() с версии 8.0. ROW_NUMBER() является оконной функцией или аналитическая функция, которая присваивает порядковый номер каждой строке, к которому она применяется, начиная с одной.

Обратите внимание, что если вы используете MySQL с версией ниже 8.0, вы можете эмулировать некоторые функции функции ROW_NUMBER(), используя различные методы.

Ниже показан синтаксис функции ROW_NUMBER():

ROW_NUMBER() OVER (<partition_definition> <order_definition>)

 

partition_definition

partition_definition имеет следующий синтаксис:

PARTITION BY <expression>,[{,<expression>}...]

 

Предложение PARTITION BY разбивает строки на меньшие множества. Выражение может быть любым допустимым выражением, которое будет использоваться в предложении GROUP BY. Вы можете использовать несколько выражений, разделенных запятыми.

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

 

order_definition

order_definition синтаксически выглядит следующим образом :

ORDER BY <expression> [ASC|DESC],[{,<expression>}...]

 

Цель ORDER BY – установить порядок строк. Предложение ORDER BY не зависит от запроса ORDER BY.

 

Примеры функции ROW_NUMBER() MySQL

Давайте использовать таблицу products из примера базы данных для демонстрации:

 

1) Присвоение последовательных номеров строкам

Следующий оператор использует функцию ROW_NUMBER() для назначения последовательного номера каждой строке таблицы products:

SELECT
ROW_NUMBER() OVER (
ORDER BY productName
) row_num,
productName,
msrp
FROM
products
ORDER BY
productName;

 

 

2) Нахождение верхних N строк каждой группы

Вы можете использовать функцию ROW_NUMBER() для запросов, которые находят первые N строк для каждой группы, например, три лучших сотрудника по продажам каждого канала продаж, пять лучших высокопроизводительных продуктов каждой категории.

Следующее утверждение находит три лучших продукта, которые имеют самый высокий запас в каждой товарной линейке:

WITH inventory
AS (SELECT
productLine,
productName,
quantityInStock,
ROW_NUMBER() OVER (
PARTITION BYproductLine
ORDER BY quantityInStock DESC) row_num
FROM
products
)
SELECT
productLine,
productName,
quantityInStock
FROM
inventory
WHERE
row_num <= 3;

 

В этом примере

  • Во-первых, мы использовали функцию ROW_NUMER() для ранжирования запасов всех продуктов в каждой продуктовой линейке путем разделения всех продуктов по продуктовой линейке и упорядочения их по количеству на складе в порядке убывания. В результате каждому продукту присваивается ранг в зависимости от его количества на складе. и ранг сбрасывается для каждой продуктовой линейки.
  • Затем мы выбрали только продукты, чей ранг меньше или равен трем.

 

3) Удаление повторяющихся строк

Вы можете использовать, ROW_NUMBER() чтобы превратить неуникальные строки в уникальные строки, а затем удалить дублирующиеся строки . Рассмотрим следующий пример.

Сначала создайте таблицу с некоторыми повторяющимися значениями:

CREATE TABLE t (
id INT,
name VARCHAR(10) NOT NULL
);

INSERT INTOt(id,name)
VALUES(1,'A'),
(2,'B'),
(2,'B'),
(3,'C'),
(3,'C'),
(3,'C'),
(4,'D');

 

Во-вторых, используйте функцию ROW_NUMBER() для разделения строк на разделы по всем столбцам. Номер строки будет перезапущен для каждого уникального набора строк.

SELECT
id,
name,
ROW_NUMBER() OVER (PARTITION BY id, name ORDER BY id) AS row_num
FROM t;

 

Функция ROW_NUMBER в MySQL

 

Как видно из вывода, уникальные строки – это те, чей номер строки равен единице.

В-третьих, вы можете использовать общее табличное выражение (CTE) для возврата дублирующихся строк и удалить оператор для удаления:

WITH dups AS (SELECT
id,
name,
ROW_NUMBER() OVER(PARTITION BY id, name ORDER BY id) AS row_num
FROM t)

DELETE FROM t USING t JOIN dups ON t.id = dups.id
WHERE dups.row_num <> 1;

 

Обратите внимание, что MySQL не поддерживает удаление на основе CTE, поэтому нам пришлось объединить исходную таблицу с CTE в качестве обходного пути.

 

4) нумерация страниц с использованием функции ROW_NUMBER()

Поскольку ROW_NUMBER() присваивает каждой строке в наборе результатов уникальный номер, вы можете использовать его для нумерации страниц.

Предположим, вам нужно отобразить список продуктов с 10 продуктами на странице. Чтобы получить товары для второй страницы, вы используете следующий запрос:

SELECT *
FROM
(SELECT productName,
msrp,
row_number()
OVER (order by msrp) AS row_num
FROM products) t
WHERE row_num BETWEEN 11 AND 20;

 

Из этой статьи вы узнали, как использовать функцию ROW_NUMBER() в MySQL для генерации последовательного номера для каждой строки в наборе результатов.

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

Просмотров: 192

Если статья понравилась, то поделитесь ей в социальных сетях:

Читайте также

    Добавить комментарий

    Войти с помощью: 

    Ваш e-mail не будет опубликован. Обязательные поля помечены *

    Сообщить об опечатке

    Текст, который будет отправлен нашим редакторам:

    Заполните форму и наш менеджер перезвонит Вам в самое ближайшее время!

    badge
    Обратный звонок 1
    Отправить
    galka

    Спасибо! Ваша заявка принята

    close
    galka

    Спасибо! Ваша заявка принята

    close