Новое в стандарте и реализациях языка SQL

Оконные функции

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

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

Для примера рассмотрим разбивку по 2 записи на страницу строк из таблицы Laptop, упорядоченных по убыванию цены.

Вот таким образом можно добавить столбец, содержащий общее число строк в таблице:

Консоль

Выполнить

SELECT *, COUNT(*) OVER() AS total 
FROM Laptop;

SELECT *, COUNT(*) OVER() AS total FROM Laptop;

    Схема

Заметим, что подобное можно было сделать в рамках стандарта SQL-92 с помощью подзапроса:

Консоль

Выполнить

SELECT *, (SELECT COUNT(*) FROM Laptop) AS total 
FROM Laptop;

SELECT *, (SELECT COUNT(*) FROM Laptop) AS total FROM Laptop;

    Схема

Однако представьте себе, что мы используем не простую таблицу (Laptop), а громоздкий запрос, который может содержать десятки и сотни строк. При этом «оконный» вариант не претерпел бы изменений, а в «классическом» случае пришлось бы полностью дублировать код запроса в подзапросе для вычисления числа строк.