Выборка уникальных значений по строкам в SQL: DISTINCT и ROW_NUMBER()

Быстрый ответ

Для выбора уникальных строк при помощи ROW_NUMBER() необходимо сгруппировать данные и отбирать лишь первое упоминание каждой группы. Вот пример такого запроса:

WITH CTE AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY UniqueColumn ORDER BY OrderColumn) AS rn
    FROM TableName
)
SELECT * FROM CTE WHERE rn = 1;

В данном примере следует заменить UniqueColumn, OrderColumn и TableName на соответствующие значения из ваших данных. Обратите внимание, что этот запрос вернёт только первые строки для каждой группы, сформированные по уникальному полю UniqueColumn и отсортированные по OrderColumn.

Разбираем основу

Применение DENSE_RANK() вместо ROW_NUMBER()

Если перед вами задача получить уникальные значения, а также присвоить им порядковые номера, может оказаться полезнее использовать функцию DENSE_RANK() вместо ROW_NUMBER():

SELECT id, DENSE_RANK() OVER (ORDER BY id) AS RowNum
FROM YourTable
GROUP BY id;

DENSE_RANK() равноценно обрабатывает повторяющиеся значения, а конструкция ORDER BY в клаузуле DENSE_RANK() обеспечивает правильную последовательность результатов.

Подробнее об этом расскажет наш спикер на видео

Обработка запросов с несколькими уникальными столбцами

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

SELECT id, description, ROW_NUMBER() OVER (PARTITION BY id, description ORDER BY creation_date) AS rn
FROM YourTable;

Углубляемся

Эффективное использование оконных функций

Для наиболее эффективного применения функций ROW_NUMBER(), RANK() и DENSE_RANK() необходимо усвоить оконные функции:

SELECT id, sales, RANK() OVER (ORDER BY sales DESC) AS sales_rank
FROM SalesTable;

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

Упрощение с помощью подзапросов и CTE

Сочетание DISTINCT и ROW_NUMBER() через подзапросы показывает свою эффективность:

SELECT DISTINCT id, sub.rn
FROM (
    SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM YourTable
) AS sub;

Также применение Общих Табличных Выражений (CTE), продемонстрированное в разделе "Быстрый ответ", улучшает читаемость запросов, упрощая понимание сложных конструкций.

Извлечение метрик с помощью GROUP BY

Сочетание функции GROUP BY и ROW_NUMBER() позволяет извлекать такие метрики, как max() или COUNT(), вместе с уникальными номерами строк для анализа данных по группам:

SELECT id, COUNT(*) as frequency, ROW_NUMBER() OVER (ORDER BY id) AS rn
FROM YourTable
GROUP BY id;

Визуализация подхода

Попробуем визуализировать использование DISTINCT и Row_Number() на примере с поездом:

Представим поезд, где каждому вагону соответствует город:

[ Нью-Йорк 🚂 | Нью-Йорк 🚃 | Сан-Франциско 🚃 | Лос-Анджелес 🚃 | Сан-Франциско 🚃 | Нью-Йорк 🚃 ]

Применяя DISTINCT, мы отберем уникальные значения:

Уникальные направления: [🗽 Нью-Йорк , 🌉 Сан-Франциско , 🌴 Лос-Анджелес ]

Функция Row_Number() будет присваивать номер каждому уникальному направлению:

Места в поезде: [🗽1, 🌉2, 🌴3]

В SQL это будет выглядеть так:

SELECT DISTINCT city, 
       ROW_NUMBER() OVER (ORDER BY city) as SeatNumber
FROM TrainCarriages;

В итоге мы получаем список мест с присвоенными номерами:

Результат: 
🗽1 (Нью-Йорк), 
🌉2 (Сан-Франциско), 
🌴3 (Лос-Анджелес)

Дополнительные понятия

Оценка необходимости применения DISTINCT

Перед тем как использовать DISTINCT, следует оссесить его необходимость. Если поле и так уникально, лишнее использование DISTINCT может обернуться излишней нагрузкой на ваш запрос:

SELECT DISTINCT id
FROM YourTable;

Проверка совместимости функций и ключевых слов

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

SELECT DISTINCT id, ROW_NUMBER() OVER (ORDER BY id) AS rn
FROM YourTable;

В некоторых случаях применение DISTINCT к полю id может оказаться избыточным, если id уже представляет из себя уникальный идентификатор.

Полезные материалы

1. Обсуждение применения ROW_NUMBER() для выборки уникальных значений в SQL-запросах на Stack Overflow.
2. Официальная документация Microsoft по функции ROW_NUMBER().
3. Статья Pinal Dave о использовании ROW_NUMBER() в сочетании с PARTITION.
4. Уроки по оптимизации производительности SQL-запросов на SQL Sentry от SolarWinds.
5. Учебное пособие по удалению дубликатов с помощью ROW_NUMBER() на CodeProject.