Выбор первых n элементов в группе в T-SQL

Работа с базами данных в .NET Framework --- Оконные функции T-SQL --- Выбор первых n элементов в группе

Такая очень часто встречающая задача предусматривает фильтрацию определенного числа строк из каждой группы или секции на основе определенного упорядочения. Запрос таблицы Sales.Orders, возвращающий три самых последних заказа для каждого клиента — пример задачи выбора первых нескольких элементов в группе. В данном случае элементом секционирования является custid, упорядочение выполняется по orderdate DESC, orderid DESC (самые последние заказы), а N равно 3. Старый параметр TOP и более новый OFFSET/FETCH поддерживают указание параметров фильтрации и упорядочения, но не поддерживают предложение секционирования. Представьте, как было бы здорово, если бы они позволяли в определении фильтра задать предложение секционирования и упорядочения, например так:

-- Не поддерживается в T-SQL
SELECT
  TOP (3) OVER(
    PARTITION BY custid
    ORDER BY orderdate DESC, orderid DESC)
  custid, orderdate, orderid, empid
FROM Sales.Orders;

К сожалению, такой синтаксис не поддерживается, и задачу придется решать другими способами.

Независимо от используемого решения, общие рекомендации по индексированию укладываются в концепцию POC, названную по начальным буквам слов partitioning (секционирование), ordering (упорядочение) и covering (покрытие). Список ключей индексов определяется на основе столбцов секционирования (в нашем случае это custid), за которыми следуют столбцы упорядочения (в нашем случае orderdate DESC, orderid DESC), и для целей покрытия он включает остальные столбцы, которые содержатся в запросе. Ясно, что если индекс является кластеризованным, в любом случае покрываются все столбцы таблицы, поэтому не нужно волноваться о части «C» POC-индекса. Вот код, который генерирует POC-индекс для нашей задачи в предположении, что empid — единственный столбец, который осталось вернуть, помимо custid, orderdate и orderid:

CREATE UNIQUE INDEX idx_cid_odD_oidD_i_empid
  ON Sales.Orders(custid, orderdate DESC, orderid DESC)
  INCLUDE(empid);

При наличии POC-индекса решать задачу можно двумя способами: с помощью функции ROW_NUMBER или оператора APPLY и параметра OFFSET/FETCH или TOP. Выбор способа зависит от плотности столбца секционирования (в нашем случае custid). При низкой плотности, то есть большом числе уникальных клиентов с небольшим числом заказов, больше подходит решение на основе ROW_NUMBER. Номера строкам присваиваются с применением тех же требований по секционированию и упорядочению, что и в запросе, после чего отфильтровываются только строки с номерами, меньшими или равными числу строк, которые надо получить в каждой группе. Вот полное решение, реализующее этот подход:

WITH C AS
(
  SELECT custid, orderdate, orderid, empid,
    ROW_NUMBER() OVER(
      PARTITION BY custid
      ORDER BY orderdate DESC, orderid DESC) AS rownum
  FROM Sales.Orders
)
SELECT *
FROM C
WHERE rownum <= 3
ORDER BY custid, rownum;

На рисунке ниже показан план выполнения этого запроса:

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

Однако при высокой плотности столбца секционирования (небольшое число уникальных клиентов, у каждого их которых много заказов) план, предусматривающий поиск по индексу для каждого клиента, становится предпочтительнее полного просмотра конечной страницы индекса. Чтобы получить такой план, надо запрашивать таблицу, которая содержит уникальные значения, по которым выполняется секционирование, (в нашем случае Sales.Customers) и применить оператор APPLY, чтобы инициировать запрос с параметром OFFSET/FETCH или TOP для каждого клиента:

SELECT C.custid, A.*
FROM Sales.Customers AS C
  CROSS APPLY (SELECT orderdate, orderid, empid
               FROM Sales.Orders AS O
               WHERE O.custid = C.custid
               ORDER BY orderdate DESC, orderid DESC
               OFFSET 0 ROWS FETCH FIRST 3 ROWS ONLY) AS A;

План этого запроса показан на следующем рисунке:

Обратите внимание, что в плане индекс таблицы Customers просматривается для получения идентификаторов всех клиентов. Затем для каждого клиента в плане предусмотрена операция поиска в POC-индексе (переход на начало секции текущего клиента в концевой странице индекса), а затем на концевой странице индекса считываются три строки с самыми последними заказами.

Как вы помните, параметр OFFSET/FETCH появился в SQL Server 2012. В более ранних версиях SQL Server вместо него можно задействовать параметр TOP:

SELECT C.custid, A.*
FROM Sales.Customers AS C
  CROSS APPLY (SELECT TOP (3) orderdate, orderid, empid
               FROM Sales.Orders AS O
               WHERE O.custid = C.custid
               ORDER BY orderdate DESC, orderid DESC) AS A;

Заметьте, что для повышения производительности в обоих способах используется POC-индекс. Если этого индекса нет или вы не можете или не хотите его создавать, есть третий способ, который обычно обеспечивает более высокую производительность, чем предыдущие два. Но этот способ применим, только если N равно единице.

А сейчас удалим POC-индекс:

DROP INDEX idx_cid_odD_oidD_i_empid ON Sales.Orders;

В третьем способе реализована методика, которую можно назвать параллельной сортировкой. Я представил эту методику ранее при обсуждении функций смещения. Идея заключается в том, чтобы создать в каждой секции по строке, в которую конкатенируются сначала упорядочивающие элементы, а затем все элементы, которые необходимо возвратить. Важно использовать именно конкатенацию, в результате которой получается строка, которая сортируется так же, как должна выполняться сортировка по элементам упорядочения. В нашем случае, упорядочение выполняется по orderdate DESC и orderid DESC.

Первый элемент является датой. Чтобы получить строковое представление даты, которая сортируется так же, как исходная дата, нужно перевести дату в форму YYYYMMDD, где YYYY - год, MM — месяц и DD - день. Для этого воспользуемся функцией CONVERT со стилем 112. Что касается элемента ordered, то это положительное целое. Чтобы символьное строковое представление числа сортировалось так же, как исходное целое, нужно отформатировать его как строку фиксированной ширины с ведущими пробелами или нулями. Отформатировать целую величину как строку фиксированной длины можно с помощью функции STR.

Решение предусматривает группировку строк по столбцу секционирования и вычисления верхней (максимальной) конкатенированной строки в группе. Верхняя строка содержит конкатенированные элементы строки, которую нужно возвратить. Затем на основе последнего запроса нужно создать CTE-выражение. Затем во внешнем запросе используем функции SUBSTRING для извлечения отдельных ранее конкатенированных элементов и преобразования их в исходные типы. Вот как выглядит готовое решение:

WITH C AS
(
  SELECT custid, 
    MAX(CONVERT(CHAR(8), orderdate, 112)
        + STR(orderid, 10)
        + STR(empid, 10) COLLATE Latin1_General_BIN2) AS mx
  FROM Sales.Orders
  GROUP BY custid
)
SELECT custid,
  CAST(SUBSTRING(mx,  1,  8) AS DATETIME) AS orderdate,
  CAST(SUBSTRING(mx,  9, 10) AS INT)      AS custid,
  CAST(SUBSTRING(mx, 19, 10) AS INT)      AS empid
FROM C;

Запрос не особо симпатичный, но план содержит только один просмотр данных и этот способ обычно производительнее других способов, если POC-индекса нет. Помните, что если вы можете себе позволить такой индекс, этот способ вам не нужен — лучше применять описанные выше два способа, выбирая их в зависимости плотности столбца секционирования.