Теория: ROW_NUMBER

В этом уроке мы познакомимся с функцией ROW_NUMBER(), «номер строки». Эта функция присваивает каждой строке уникальный номер в рамках группы. Соответственно, максимальное значение ROW_NUMBER() у нас может совпадать с количеством строк в таблице, но это не обязательно.

Функция ROW_NUMBER() помогает задать нумерацию, которая будет отличаться от порядка сортировки строк результирующего набора. Допустим, вам нужно пронумеровать строки — то есть поставить номер строки. При этом нужно работать не со всеми записями в таблицах, а только в определенных разрезах или по определенной логике.

Использование ROW_NUMBER

Используем ROW_NUMBER() OVER (), чтобы пронумеровать продажи

SELECT
      id,
      sale_date,
      quantity,
      price,
      ROW_NUMBER() OVER () AS sale_number
  FROM sales;

View on DB Fiddle

В столбце sale_number каждая строка имеет уникальный номер продажи. Нумерация начинается с 1 и увеличивается на 1 для каждой следующей строки.

Расширим функцию и добавим партици. Следующий запрос присваивает уникальный номер каждой строке внутри каждой группы, определенной PARTITION BY.

SELECT
    id,
    sale_date,
    quantity,
    price,
    ROW_NUMBER() OVER (PARTITION BY sale_date) AS sale_number
FROM sales;

View on DB Fiddle

Разберем подробно запрос. PARTITION BY sale_date - создает группы, окна, по уникальной дате продаже. ROW_NUMBER() OVER (PARTITION BY sale_date) AS sale_number - применяет функцию ROW_NUMBER() к каждой строке данных внутри группы. Таким образом, каждая строка внутри каждой группы будет иметь уникальный номер, который будет отображаться в столбце sale_number.

Внутри оператора OVER () можем использовать сортировку, чтобы указать порядок для нумерации строк.

SELECT
    id,
    sale_date,
    quantity,
    price,
    row_number() OVER (PARTITION BY sale_date ORDER BY quantity) AS sale_number
FROM sales;

View on DB Fiddle

Теперь порядок строк изменился. На первом месте запись с id = 4, но результат может меняться, так как в колонке quantity у нескольких записей одинаковые значения. При сортировке лучше использовать поля или значения, которые помогут однозначно отсортировать значения, например дата продажи с точностью до секунды.

Для сортировки можно также использовать вычисляемые значения

SELECT
    id,
    sale_date,
    quantity,
    price,
    (quantity * price) AS amount,
    row_number()
        OVER (PARTITION BY sale_date ORDER BY quantity * price DESC)
    AS sale_number
FROM sales
WHERE sale_date = '01.01.2023' ORDER BY sale_number ASC;

View on DB Fiddle

Удаление дубликатов с помощью ROW_NUMBER

С помощью ROW_NUMBER() можно избавиться от дубликатов в запросе. Для этого в PARTITION BY указывают столбцы, по котором необходимо найти уникальность, а также используют подзапрос для фильтрации:

WITH tab AS (
    SELECT
        sale_date,
        row_number() OVER (PARTITION BY sale_date ORDER BY id) AS rn
    FROM sales
)

SELECT sale_date FROM tab WHERE rn = 1;

View on DB Fiddle

Здесь запрос можно разделить на две логические части. Сперва мы создали группы по дате и пронумеровали строки внутри каждой группы. Затем офильтровали строки, оставив лишь те, что в группе под номером 1. Таким образом, мы избавились от дубликатов.

Выводы

• ROW_NUMBER() - это аналитическая функция в SQL, которая присваивает каждой строке результата запроса уникальный числовой идентификатор. Синтаксис ROW_NUMBER() выглядит примерно так:

ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY column3)

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