DML. Сортировки и пагинация

Сегодня поговорим о двух важных инструментах работы с данными. На первый взгляд они могут показаться не связанными между собой, но это не так.

Оператор ORDER BY

Собственно, начнем с того, что интуитивно понятно из названия: сортировок.

Полагаю, очевидно, что мало уметь доставать из БД какую-то информацию. Мало даже доставать эту информацию отфильтрованной по нужным критериям – нужно, также, чтобы эта информация было предоставлено в правильном порядке.

Для определения порядка записей в выборке и существует оператор ORDER BY. Рассмотрим подробнее на примерах:

select * from passenger order by first_name;

Извлекаем из таблицы записи по всем колонкам, отфильтрованным по значению имени. Самый базовый пример.

select id from passenger order by first_name;

Извлекаем из таблицы только id, но отсортированные по first_name. Не для всех очевидный фактор: не обязательно извлекать колонку, чтобы сортировать по ней.

select * from passenger order by concat(first_name, last_name);

Также, как и после SELECT, после ORDER BY можно располагать композитные значения. В данном случае – конкатенация значений из двух колонок.

select * from passenger order by first_name desc;

Сортировка в обратном порядке. Сортировка «по убыванию». Грубо говоря, после каждой указанной сортировки мы можем оставить модификатор:

·      ASC – не обязательное значение по умолчанию, указывает прямой порядок сортировки сортировки (по возвастанию);

·      DESC – обратный порядок (по убыванию).

select * from passenger order by last_purchase nulls first;

Следующий полезный модификатор – указание порядке null-значений по отношению к существующим. По умолчанию, null имеет наименьший приоритет, таким образом записи с null в соответствующей колонке (или результате выражения) буду в самом конце выборки для ASC и в самом начале – для DESC.

Но требования к запросу могут требовать иного поведения. В таком случае мы можем явно указать расположение null-значений относительно остальных:

·      NULLS FIRST – значения с null в условии сортировки будут располагаться в начале выборки. Это поведение по умолчанию для DESC;

·      NULLS LAST – значения с null в условии сортировки будут располагаться в конце. Очевидно, поведение по умолчанию для ASC;

select * from passenger order by first_name, last_name;

Также вполне очевидна актуальность множественной сортировки: сначала сортируем по первому значению, потом – по второму (третьему, десятому) для всех записей, у которых совпало значение предыдущей сортировки.

Проводя аналогии с Java и Comparator’ами, применение нескольких сортировок будет аналогично применению Comparator:thenComparing().

При этом каждая из сортировок может обладать (или не обладать) собственными модификаторами:

select * from passenger order by first_name desc, last_name asc nulls first;

Как видите, возможности сортировки данных в SQL достаточно гибкие, но достаточно простые и интуитивно-понятные. По крайней мере, проще, чем в той же Java:)

Пагинация. Операторы LIMIT и OFFSET

Следующим шагом в обработке отфильтрованных и отсортированных данных может стать их пагинация – разбиение на «страницы». Под страницей, в данном случае, подразумевается часть выборки с числом записей, не превышающих заданную.

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

Очевидно, что на один экран не поместится более 7 миллиардов записей (строк). Более того, загрузить такой массив данных в оперативную память и держать там – не самая лучшая идея. Логичным решением выглядит реализация постраничной загрузки. Скажем, 1-100 запись, 101-200… При этом на последней странице вполне может оказаться меньше 100 записей (не факт, что число жителей кратно 100).

Именно такую возможность пагинирования в SQL предоставляют операторы LIMIT и OFFSET. Подробнее в примерах (советую добавить в таблицу хотя бы с десяток записей, чтобы результаты запросов были наглядны):

select * from passenger limit 3;

Такой запрос лишь ограничивает число выдываемых записей. В данном случае – не больше 3. Задача оператора LIMIT – именно ограничить число записей в выборке.

select * from passenger offset 3;

Оператор OFFSET имеет менее очевидную функциональность – он пропускает указанное число записей. Так, в результате данного запроса в выборку попадут все записи таблицы, кроме первых трех.

Вместе же эти операторы дают то, с чего мы начали – возможность пагинации:

select * from passenger limit 3 offset 0;      --  1-3 записи
select * from passenger limit 3 offset 3;      --  4-6 записи
select * from passenger limit 3 offset 6;      --  7-9 записи

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

Дело в том, что большинство СУБД не имеет гарантированного порядка записей по умолчанию. Таким образом, при использовании запросов выше может (но не обязательно возникнет) ситуация, когда какие-то записи попадают в несколько страниц. Таким образом, пагинация будет работать не предсказуемо, но явно не так, как хотелось бы пользователю.

Поэтому при использовании пагинации рекомендуется использовать сортировку. Даже если у вас нет требований к порядку данных из запроса – всегда можно отсортировать их по значению id, например. Вероятно, внешне данные будут казаться не отсортированными, но это гарантирует корректную работу пагинации:

select * from passenger order by id limit 3 offset 0;

Такой запрос будет медленнее, чем запрос без сортировки. Но это неизбежное зло, если требуется корректный порядок отображения.

Более того, даже наличие «какой-то» сортировки не гарантирует избавление от описанной проблемы. Ведь вполне может быть огромное число записей с одинаковым значением в колонке, по которой производится сортировка. И в рамках такого подмножества данные остаются не упорядоченными.

Лечится это ровно также: последней сортировкой добавляем сортировку по уникальному полю (обычно id):

select * from passenger order by first_name, id limit 3 offset 0;

Вот теперь запрос точно работает корректно:)

Небольшой бонус с демонстрацией порядка блоков фильтрации, сортировки и пагинации.

select * from passenger 
where last_purchase is not null 
order by first_name, id 
limit 3 offset 0;

Итого: сначала WHERE, потом ORDER BY. LIMIT и OFFSET – всегда в конце запроса.

С теорией на сегодня все!

Переходим к практике:

Задача 1

Получите записи по всем совершеннолетним пассажирам. Отсортируйте их по имени.

Задача 2

Получите только вторую и третью записи из предыдущего запроса.

Если что-то непонятно или не получается – welcome в комменты к посту или в лс:)

Канал: https://t.me/ViamSupervadetVadens

Мой тг: https://t.me/ironicMotherfucker

Дорогу осилит идущий!