Звучит запутанно... :) Однако если по-простому, то это та же нумерация, что и в ROW_NUMBER(), которая начинается с той же единицы. Различие в том, что одинаковые записи получают одинаковый номер, а следующая отличающаяся от них запись получает такой номер, как если бы ROW_NUMBER() и использовалась, и все предыдущие записи получили свои уникальные номера. Таким образом, образуется дырка в нумерации, равная количеству одинаковых записей минус единица.
DENSE_RANK()
Эта функция выполняет «плотное» ранжирование, то есть делает ровно то же самое, что и предыдущая, но без «дырок» в нумерации.
NTILE()
Данная функция позволяет разделить записи внутри «окна» на указанное количество групп. Для каждой записи она вернет номер группы, к которой принадлежит данная запись. Нумерация групп также начинается с единицы. Если количество записей в «окне» не делится на количество групп, то получится два типа групп с разным количеством записей, отличающимся на единицу, при этом сначала будут выведены группы с большим количеством записей, а затем – с меньшим.
Для демонстрации различий функций ранжирования можно выполнить следующий запрос:
| SELECT ID_Customer, Amount, ROW_NUMBER() OVER(PARTITION BY ID_Customer ORDER BY Amount DESC) N_Row, RANK() OVER(PARTITION BY ID_Customer ORDER BY Amount DESC) RANK, DENSE_RANK() OVER(PARTITION BY ID_Customer ORDER BY Amount DESC) DENSE_RANK, NTILE(2) OVER(PARTITION BY ID_Customer ORDER BY Amount DESC) NTILE–- выведем только одну группу для экономии местаFROM sample WHERE ID_Customer = 2 ID_Cust Amnt N_Row RANK D_RANK NTILE-------------------------------------------------------2 400 1 1 1 12 220 2 2 2 12 202 3 3 3 22 200 4 4 4 22 200 5 4 4 32 100 6 6 5 4 |
Некоторые примеры использования
Как уже говорилось, практически все, что можно сделать с помощью аналитических функций, можно сделать и без них, но с их использованием требуемого эффекта можно добиться проще и, зачастую, оптимальнее...
Поскольку теперь появилась возможность нумеровать записи в выборке, можно воспользоваться этим для постраничной выдачи результата. Запрос будет выглядеть примерно так:
| WITH Numbered ( SELECT ROW_NUMBER() OVER(ORDER BY name) N_Row, * FROM sysobjects)SELECT * FROM Numbered WHERE N_Row between @First AND @Last |
Как ни странно, этот запрос будет выполняться примерно в два раза быстрее классического:
| EXECUTE ('SELECT * FROM (SELECT TOP ' + @Count + ' * FROM (SELECT TOP ' + @Last + ' * FROM sysobjects ORDER BY name ASC ) SO1 ORDER BY name DESC) SO2ORDER BY name') |
Так что сбылась еще одна мечта, об эффективной и простой постраничной выборке.. :)
Еще один пример, где использование аналитических функций может быть и удобным, и эффективным. Нередко требуется вывести, например, два самых крупных заказа для каждого клиента. Может случиться так, что заказов с максимальной суммой окажется больше двух. Для случая, когда заказов должно быть именно два, запрос может выглядеть так:
| WITH Ranked as ( SELECT *, Row_Number() OVER (PARTITION BY ID_Customer ORDER BY amount DESC) [rank] FROM sample)SELECT * FROM Ranked WHERE [rank] < 3 |
Такой запрос на этих данных примерно в 10 раз эффективнее, чем этот же запрос, выполненный в «старом стиле»:
| SELECT * FROM sample s1 WHERE ID_Trans in ( SELECT top 2 ID_Trans FROM sample s2 WHERE s1.ID_Customer = s2.ID_Customer ORDER BY amount DESC ) |
Более того, разница в скорости будет ощутимо расти с увеличением количества данных в таблице, поскольку в первом случае алгоритм довольно прост – внутренним запросом нумеруются записи внутри групп, практически за одну сортировку, а затем фильтром во внешнем запросе отсекаются все лишние записи. Во втором же случае, внутренний подзапрос выполняется заново, для каждой записи в таблице. Все это очень хорошо видно на планах запросов. На втором плане количество ожидаемых выполнений подзапроса – пятнадцать, так как в тестовой табличке 15 записей.
План запроса с аналитической функцией:
| Операция Стоимость Количество---------------------------------------------------------- |--Filter(WHERE:([Expr1003]<(3))) 0.022873 1 |--Sequence Project(...) 0.022866 1 |--Segment 0.022866 1 |--Segment 0.022866 1 |--Sort(ORDER BY:(...)) 0.022864 1 |--Clustered Index Scan(...) 0.006423 1 |
План запроса без использования аналитической функции:
| Операция Стоимость Количество ----------------------------------------------------------- |--Nested Loops(Left Semi Join …) 0.18998 1 |--Clustered Index Scan(…) 0.00642 1 |--Filter(WHERE:(…)) 0.18350 15 |--Top(TOP EXPRESSION:((2))) 0.18348 15 |--Filter(WHERE:(…)) 0.18348 15 |--Sort(ORDER([Amount] DESC)) 0.18343 15 |--Clustered Index Scan(…) 0.00665 15 |
Все это, конечно, здорово и замечательно, но есть некоторые негативные моменты, которые уменьшают радость от получения нового инструмента. Он, конечно, хорош, но пока что еще очень беден и не развит. Не считая встроенных агрегирующих функций, в SQL 2005 реализовано всего 4 ранжирующих функции, в то время как в ANSI SQL 2003 больше 30 различных типов аналитических функций...
Обидно и другое.. Как можно заметить, в синтаксисе для аналитических агрегатов отсутствует возможность указать сортировку внутри «окна». Для обычных, встроенных агрегатов это не имеет никакого значения, но в SQL Server 2005 появится возможность писать свои собственные агрегаты на CLR-совместимых языках, которыми, при желании, можно было бы расширить список функций, и вот для этих самодельных агрегирующих функций подобная возможность могла бы быть весьма полезной. Без возможности указать порядок сортировки записей в «окне» невозможно использовать целый класс агрегирующих функций, зависимых от порядка обработки данных. В принципе, ничто не мешает в процессе работы собственной агрегирующей функции складывать данные в некоторую коллекцию, сортировать их там должным образом, а затем обрабатыватать в требуемом порядке, но, очевидно, это не идеальное решение, так как приходится выполнять работу сервера.
Самое забавное, что при написании пользовательской агрегирующей функции можно указать с помощью специального атрибута, зависит ли результат от порядка обработки записей, но в отсутствие возможности указать этот самый порядок обработки, данный атрибут бесполезен. Видимо, те ребята, которые писали поддержку пользовательских агрегирующих функций, предусмотрели возможность создания функций, зависимых от порядка обработки данных, а вот у тех, кто писал аналитические функции, руки пока не дошли. Очень хочется верить, что к релизу дойдут...
Так же навевает грустные мысли очень бедный механизм указания «окна» для аналитической функции. На данный момент есть только один способ задать это «окно» – группировка. То есть, «окно» можно задать только с помощью указания колонки, одинаковые значения записей в которой являются признаком принадлежности к «окну». Однако возможности указания «окна» могут быть гораздо шире, но в текущей версии все это великолепие пока что отсутствует.