Моды в T-SQL
110Работа с базами данных в .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;