MS SQL 2005: оконные функции

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

MS SQL 2005: оконные функции

Информация

Компьютеры, программирование

Другие материалы по предмету

Компьютеры, программирование

Сдать работу со 100% гаранией
ко это не основное назначение данной функции… Все-таки она призвана нумеровать записи в указанном порядке внутри «окна». Но если в конструкции OVER опустить секцию PARTITION BY, то за «окно» будет принята вся выборка что дает возможность пронумеровать все записи в должном порядке, причем порядок нумерации может не совпадать с порядком записей в результирующей выборке, то есть оператор ORDER BY внутри OVER(…), определяющий порядок сортировки записей внутри «окна», и, соответственно, порядок нумерации записей может не совпадасть с оператором ORDER BY в конструкции SELECT, определяющей порядок выдачи записей клиенту. Нумерация всегда начинается с единицы.

RANK()

Эта функция предназначена для ранжирования записей внутри «окна», но опять-таки, если колонка для группировки не задана явным образом, то за «окно» принимается вся выборка. Рангом каждой записи является количество уже ранжированных записей с более высоким рангом, чем текущая, плюс единица. Если встретятся несколько записей с одинаковым значением, по которому производится ранжирование, то этим записям будет присвоен одинаковый ранг. Однако при этом следующая запись с новым значением получит такой ранг, как будто бы предыдущие записи получили свой уникальный номер, то есть образуется дырка.

Звучит запутанно... :) Однако если по-простому, то это та же нумерация, что и в 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 1

2 220 2 2 2 1

2 202 3 3 3 2

2 200 4 4 4 2

2 200 5 4 4 3

2 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) SO2

ORDER 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-совместимых языках, которыми, при желании, можно было бы расширить список функций, и вот для этих самодельных агрегирующих функций подобная возможность могла бы быть весьма полезной. Без возможности указать порядок сортировки записей в «окне» невозможно использовать целый класс агрегирующих функций, зависимых от порядка обработки данных. В принципе, ничто не мешает в процессе работы собственной агрегирующей функции складывать данные в некоторую коллекцию, сортировать их там должным образом, а затем обрабатыватать в требуемом порядке, но, очевидно, это не идеальное решение, так как приходится выполнять работу сервера.

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

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

Список литературы

Для подготовки данной работы были использованы материалы с сайта http://www.rsdn.ru/

 

Похожие работы

< 1 2