Постраничная разбивка записей (пейджинг)

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

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

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

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

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

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

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

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

Чтобы посчитать число страниц, воспользуемся следующим простым алгоритмом:

• если число строк запроса нацело делится на число записей на странице, то результат целочисленного деления одного на другое дает число страниц;

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

Это алгоритм реализуется стандартными средствами с помощью оператора CASE:

SELECT *,   
       CASE 
            WHEN total % 2 = 0 
            THEN total/2 
            ELSE total/2 + 1 
       END AS num_of_pages  
FROM (  
    SELECT *, COUNT(*) OVER() AS total   
    FROM Laptop  
     ) X;

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

SELECT *,   
       CASE 
           WHEN num % 2 = 0 
           THEN num/2 
           ELSE num/2 + 1 
       END AS page_num,   
       CASE 
           WHEN total % 2 = 0 
           THEN total/2 
           ELSE total/2 + 1 
       END AS num_of_pages  
FROM (  
      SELECT *, 
           ROW_NUMBER() OVER(ORDER BY price DESC) AS num,   
           COUNT(*) OVER() AS total   
      FROM Laptop  
     ) X;

Хранимая процедура, о которой говорилось выше, может выглядеть так:

CREATE PROCEDURE paging   
 @n int, -- число записей на страницу  
 @p int =1 -- номер страницы, по умолчанию - первая  
AS  
SELECT * 
FROM (SELECT *,   
             CASE WHEN num % @n = 0 THEN num/@n ELSE num/@n + 1 END AS page_num,   
             CASE WHEN total % @n = 0 THEN total/@n ELSE total/@n + 1 END AS num_of_pages  
      FROM (SELECT *,   
                   ROW_NUMBER() OVER(ORDER BY price DESC) AS num,   
                   COUNT(*) OVER() AS total FROM Laptop  
           ) X  
     ) Y  
WHERE page_num = @p;  
GO

Таким образом, если нам нужно получить вторую страницу при условии размещения 2-х записей на странице, достаточно написать

exec paging @n=2, @p=2

В результате получим:

А вот так будет выглядеть неполная вторая страница, если число записей на странице будет равно четырем:

exec paging @n=4, @p=2

Новые возможности стандарта, которые были реализованы в SQL Server 2012, делают разбивку на страницы очень простой операцией. Речь идет о новых необязательных конструкциях в предложении ORDER BY, а именно, OFFSET и FETCH. C их помощью можно указать сколько строк из результата запроса возвращать (FETCH) клиенту и начиная с какой строки (OFFSET) это делать.

Теперь расширенный синтаксис предложения ORDER BY имеет вид:

ORDER BY <выражение>
    [ ASC | DESC ]
    [ ,...n ]
[
OFFSET <целочисленное_выражение_1> { ROW | ROWS }
[FETCH { FIRST | NEXT } <целочисленное_выражение_2> { ROW | ROWS } ONLY]
]

FIRST и NEXT являются синонимами, как и ROW с ROWS, т.е. можно использовать любой из двух вариантов.

целочисленное_выражение_2 определяет число возвращаемых строк, а целочисленное_выражение_1 - количество строк от начала отсортированного набора, которое следует пропустить перед выводом. Если предложение FETCH отсутствует, то выводиться будут все строки, начиная с целочисленное_выражение_1 + 1.

С учетом новых возможностей процедуру постраничного вывода строк, которая была рассмотрена выше, можно реализовать совсем просто:

create proc paging
 @n int =2, -- число записей на страницу, по умолчанию 2
 @p int =1 -- номер страницы, по умолчанию - первая
AS
SELECT * FROM Laptop
ORDER BY price DESC offset @n*(@p-1) rows fetch next @n rows only;

Заметим, что стандартный синтаксис предложения ORDER BY поддерживает также PostgreSQL.

Рекомендуемые упражнения: 107