Моды в T-SQL

Работа с базами данных в .NET Framework --- Оконные функции T-SQL --- Моды

Мода (mode) — статистическое значение во множестве наблюдений, которое встречается наиболее часто. Возьмем, к примеру, таблицу Sales.Orders, которая содержит информацию о заказах. Каждый заказ был инициирован определенным клиентом и обслуживался каким-то сотрудником. Допустим, вы хотите узнать, какой сотрудник обслужил наибольшее число заказов определенного клиента. Такой сотрудник будет модой, потому что он чаще всего встречается в заказах этого клиента.

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

Принцип индексации здесь простой — надо создать индекс на основе (icustid, empid):

CREATE INDEX idx_custid_empid ON Sales.Orders(custid, empid);

Я начну с решения, в котором используется функция ROW_NUMBER. Прежде всего надо сгруппировать заказы по custid и empid, а затем вернуть число заказов в каждой группе:

SELECT custid, empid, COUNT(*) AS cnt
FROM Sales.Orders
GROUP BY custid, empid;

Затем надо добавить вычисление с применением ROW_NUMBER, результаты которого надо секционировать по custid и упорядочить по COUNT(*) DESC, empid DESC. У каждого клиента строке с наибольшим числом (в случае совпадений, с наибольшим идентификатором сотрудника) присваивается номер 1:

SELECT custid, empid, COUNT(*) AS cnt,
  ROW_NUMBER() OVER(PARTITION BY custid
                    ORDER BY COUNT(*) DESC, empid DESC) AS rn
FROM Sales.Orders
GROUP BY custid, empid;

Наконец, надо отобрать только строки с номером «1» с помощью CTE:

WITH C AS
(
  SELECT custid, empid, COUNT(*) AS cnt,
    ROW_NUMBER() OVER(PARTITION BY custid
                      ORDER BY COUNT(*) DESC, empid DESC) AS rn
  FROM Sales.Orders
  GROUP BY custid, empid
)
SELECT custid, empid, cnt
FROM C
WHERE rn = 1;

Так как для разрешения совпадений в определении упорядочения окна содержится empid DESC, нужно вернуть только по одной строке для каждого клиента. Если разрешать совпадения не нужно, вместо ROW_NUMBER используем функцию RANK и удаляем empid из предложения упорядочения окна:

WITH C AS
(
  SELECT custid, empid, COUNT(*) AS cnt,
    RANK() OVER(PARTITION BY custid
                ORDER BY COUNT(*) DESC) AS rn
  FROM Sales.Orders
  GROUP BY custid, empid
)
SELECT custid, empid, cnt
FROM C
WHERE rn = 1;

Как вы помните, в отличие от ROW_NUMBER функция RANK не различает дубликаты. Это означает, что при одинаковом значении упорядочения — в нашем случае это COUNT(*) — мы получим одинаковый ранг. Поэтому все строки с наибольшим (и равным) числом заказов получат ранг «1» и попадут в результаты. Заметьте, что в нашем случае есть два сотрудника с идентификаторами 1 и 4, которые обработали наибольшее число заказов клиента 1 — по два заказа каждый, поэтому они указаны в результатах оба.

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

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

SELECT custid,
  STR(COUNT(*), 10) + STR(empid, 10) COLLATE Latin1_General_BIN2 AS cntemp
FROM Sales.Orders
GROUP BY custid, empid;

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

Далее на основе этого запроса надо определить CTE, а затем во внешнем запросе сгруппировать строки по клиенту и определить максимальную (первую) строку в группе. В конце надо разбить строку на компоненты и привести их к исходным типам:

WITH C AS
(
  SELECT custid,
    STR(COUNT(*), 10) + STR(empid, 10) COLLATE Latin1_General_BIN2 AS cntemp
  FROM Sales.Orders
  GROUP BY custid, empid
)
SELECT custid,
  CAST(SUBSTRING(MAX(cntemp), 11, 10) AS INT) AS empid,
  CAST(SUBSTRING(MAX(cntemp),  1, 10) AS INT) AS cnt
FROM C
GROUP BY custid;

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

DROP INDEX idx_custid_empid ON Sales.Orders;